Postgresql

Postgresql의 특징

1. 객체 관계형 데이터베이스 관리 시스템(ORDBMS)
2. 복잡한 쿼리
3. 트랜잭션 무결성
4. 다중 버전 동시성 제어

PostgreSQL은 데이터 유형, 함수, 연산자, 인덱스 메서드, 절차적 언어 등을 사용자가 직접 정의하고 확장할 수 있다는 특징을 지니고 있습니다.

규칙

문서나 쿼리 설명에서 사용되는 기호의 의미는 다음과 같습니다.

• [] 는 선택 사항을 나타냅니다.
• {}, | 는 나열된 항목 중 하나의 대안을 선택해야 함을 나타냅니다.
• ... 은 앞의 요소를 반복할 수 있음을 의미합니다.
• () 는 기호 문자를 그대로 사용해야 함을 의미합니다.

명확성을 높이기 위해 SQL 명령어 (SELECT, CREATE, INSERT 등) 앞에는 => 라는 프롬프트가, 쉘(Shell) 명령 앞에는 $ 라는 프롬프트가 표시될 수 있습니다.

Connection Pool

데이터베이스와 클라이언트가 연결되면, 데이터베이스 서버는 클라이언트와의 연결을 위해 많은 자원을 사용합니다. 이에 대해서 자세하게 이야기 해봅시다!

우선 메모리에 대한 이야기를 해보겠습니다. 데이터베이스 서버가 클라이언트와 연결을 유지 하기 위해서는 메모리가 소비됩니다.

메모리 중에서도 CPU에서 직접 데이터를 불러 쓰고 속도가 빠른 주메모리, DRAM을 사용합니다. DRAM의 특징은 휘발성 메모리라는 것입니다. 휘발성 메모리의 특징은 무엇일까요? 정보가 영구히 저장되지 않고 전원이 끊어질 경우 데이터가 사라진다는 특징을 지니고 있습니다. DRAM은 정보를 데이터를 계속해서 유지하기 위해서는 Reflash가 필요합니다.

데이터베이스와의 연결 또한 마찬가지입니다. 클라이언트와 데이터베이스 서버가 연결되기 위해서는 클라이언트의 IP 주소, 클라이언트의 포트 번호, 세션의 ID, 연결 상태와 같은 데이터들이 DRAM에 저장되어야 합니다. 이는 초기의 연결 당시에는 많은 메모리를 소비하지 않을 수 있습니다. 하지만 이 연결이 해제 되지 않고 계속해서 연결되어 있다면 어떤 일이 일어날까요?

메모리는 현재 갖고 있는 메모리의 데이터를 유지하기 위해서 계속해서 Refresh를 시도할 것이고 또한 이미 데이터를 저장하고 있는 메모리는 재사용할 수 없기 때문에 이는 성능 저하로 연결될 수 밖에 없습니다.

성능저하가 이어지게 되면 데이터베이스는 메모리에서 DB 버퍼 캐쉬에 데이터를 저장하고 사용할 수 없기 때문에 메모리보다 성능이 느린 디스크를 통하여 데이터를 저장하고 사용하기 때문에 원하는 데이터를 읽고 쓰는데 걸리는데 경합이 발생하여 대기 시간이 길어질 수 밖에 없습니다.

그렇기 때문에 데이터베이스 서버와 클라이언트의 연결이 혹시나 계속해서 사용되지 않음에도 계속해서 사용되는 문제를 해결하기 위하여 등장한 것이 Connection Pool 입니다.

1. 데이터베이스 서버가 시작되면 일정 수의 연결을 미리 만들어 둡니다.
2. 클라이언트가 데이터베이스와 연결을 요청하면, 데이터베이스 서버는 연결 풀에서 연결을 하나 빌려줍니다.
3. 클라이언트가 연결을 사용하지 않으면 다시 데이터베이스 서버에 반납합니다.
4. 데이터베이스 서버는 연결 풀에 반납된 연결을 다시 사용 가능한 연결로 만듭니다.

Connection Pool 은 위와 같은 방식으로 서버와 클라이언트간의 연결을 다루며 서버의 자원을 효율적으로 관리하고 성능을 향상 시키기 위한 방법입니다.

메모리에 관하여

요즘 SSD와 SD카드 등에서 사용되는 메모리는 플래시 메모리로 읽고 쓰는 데 시간이 오래 걸리는 느린 메모리이지만 비휘발성이며 소자 미세화에 유리하며 매우 싸게 큰 용량을 만들 수 있다는 장점이 있다.

컴퓨터는 컴퓨터내의 연산 작업을 담당하고 있는 CPU와 CPU에서 직접 데이터를 불러 쓰고 속도가 빠른 주메모리와 장기 데이터 저장을 위해 속도는 느리지만 저렴하고 저장 용량이 큰 보조저장장치로 구분이 된다.

SRAM, DRAM Static Random Access Memory, Dynamic Random Access Memory의 약자이며 SRAM은 전원이 켜져 있는 동안 데이터를 유지할 수 있고 DRAM에 비해서 속도가 빠르지만 용량이 적고 비용이 비싸다는 특징이 있습니다. DRAM은 전원이 켜져 있는 동안에도 정기적으로 데이터를 새로고치는 작업이 필요하고 SRAM에 비해 속도는 느리지만 용량이크고 비용이 저렴하다는 특징이 있습니다.

SQL 문법

SQL의 데이터를 작성하기 위해서는 테이블을 작성하여야 합니다. 이유는 sql이 테이블을 이용하여 각 데이터 간의 관계를 표현하여 데이터간의 관계를 효율적으로 관리하고 무결성을 높이며 이해도를 높이는데 매우 효과적이기 때문입니다.

여기서 이해도를 테이블을 통하여 높인다는 말은 이해가가지만 다른 말들은 잘 이해가 가지 않습니다. 이 다른 경우들이 어째서 테이블의 형태로 구성하였을 경우 효과를 높일 수 있는지 알아보겠습니다.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
);

// 위의 경우 에러가 발생한다.  SQL 문장의 마지막에는 ","로 구분되는 문장으로 끝나서는 안된다.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int           -- high temperature
);

CREATE TABLE IF NOT EXISTS weather (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    pin VARCHAR(4) NOT NULL CHECK (LENGTH(pin) = 4 AND pin ~ '[0-9]+$') -- 은행에 로그인시 필요한 PIN 번호
);

위의 VARCHAR과 CHAR의 차이점은 VARCHAR은 동적으로 길이가 변경될 수 있는 문자열을 테이블에 입력받을 경우 사용되고 CHAR의 경우는 길이가 정해져 있는 문자열을 테이블 내의 데이터로 입력 받을 경우 사용한다.

• VARCHAR vs CHAR: VARCHAR(n)은 최대 n자 내에서 동적으로 길이가 변경되는 문자열을 저장하며, CHAR(n)은 항상 지정된 n자의 길이를 차지하는 고정 길이 문자열을 저장합니다.

또한 PostgreSQL은 POSIX 정규표현식을 자체적으로 지원하며 MySQL의 REGEXP 키워드 대신 ~ 연산자를 사용합니다. 위 예시의 pin ~ '^[0-9]+$'는 pin 번호가 숫자로만 이루어져야 함을 검증합니다.

데이터 조작 (CRUD)

데이터베이스의 기본 작업은 생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)로 이루어집니다.

1. 데이터 삽입 (INSERT)

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Upsert (ON CONFLICT) 데이터를 삽입할 때, 이미 존재하는 데이터라면 오류를 내는 대신 무시하거나 업데이트하는 기능입니다.

INSERT INTO users (username, password, date)
VALUES ($1, $2, $3)
ON CONFLICT (username) DO NOTHING;

ON CONFLICT (username) DO NOTHING은 삽입하려는 username 값이 테이블에 이미 존재할 경우 어떠한 동작도 하지 않고 건너뛴다는 의미입니다.

필수 조건: ON CONFLICT를 사용하기 위해서는 충돌을 감지할 기준이 되는 컬럼(username)에 반드시 UNIQUE 제약 조건이나 **PRIMARY KEY(기본키)**가 설정되어 있어야 합니다.

2. 데이터 조회 (SELECT & WHERE)

WHERE 절은 SELECT, UPDATE, DELETE 문에서 조건을 지정하여 특정 행만 필터링할 때 사용합니다.

-- 샌프란시스코의 비가 온 날씨 조회
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0;

-- 도시 기준 오름차순, 최저 온도 기준 오름차순 정렬
SELECT * FROM weather
ORDER BY city, temp_lo;

WHERE 절 연산자:

• 비교: =, != (또는 <>), >, <, >=, <=
• 논리: AND, OR, NOT
• 집합/패턴: IN, NOT IN, BETWEEN, LIKE (문자열 패턴 매칭)

3. 데이터 수정 (UPDATE)

기존 데이터를 수정합니다. WHERE 절을 생략하면 테이블의 모든 데이터가 변경되므로 주의해야 합니다.

UPDATE weather
SET temp_hi = 60
WHERE city = 'San Francisco' AND date = '1994-11-29';

4. 데이터 삭제 (DELETE)

특정 데이터를 삭제합니다. 역시 WHERE 절이 없으면 모든 데이터가 삭제됩니다.

DELETE FROM weather
WHERE city = 'San Francisco';

테이블 간의 연결 (JOIN)

관계형 데이터베이스의 꽃은 분리된 테이블을 연결하여 조회하는 JOIN입니다.

-- users 테이블과 orders 테이블을 연결하여 사용자 이름과 주문 내역을 함께 조회
SELECT users.username, orders.product_name
FROM users
JOIN orders ON users.id = orders.user_id;

JOIN(기본적으로 INNER JOIN)은 두 테이블에 모두 일치하는 데이터가 있을 때만 결과를 반환합니다. 데이터의 무결성을 유지하기 위해 보통 한 테이블의 PRIMARY KEY와 다른 테이블의 FOREIGN KEY를 연결합니다.

인덱스 (Index)

데이터가 많아질 경우 WHERE 절을 통한 검색 속도가 현저히 느려질 수 있습니다. 이때 인덱스를 생성하면 검색 속도를 비약적으로 향상시킬 수 있습니다.

CREATE INDEX idx_weather_city ON weather(city);

인덱스는 책의 '찾아보기(색인)'와 같습니다. 검색 속도(SELECT)는 빨라지지만, 데이터가 추가/수정/삭제(INSERT/UPDATE/DELETE)될 때마다 인덱스도 업데이트해야 하므로 쓰기 성능은 다소 저하됩니다. 따라서 검색이 자주 일어나는 컬럼에만 선택적으로 생성해야 합니다.

결론

PostgreSQL은 객체 지향적 특성을 포함한 강력한 오픈소스 관계형 데이터베이스(ORDBMS)입니다. 효율적인 자원 관리를 위해 Connection Pool을 활용하며, 관계형 모델을 통해 데이터의 중복을 최소화하고 무결성을 보장합니다. SQL 문법을 통한 CRUD 연산, JOIN을 이용한 데이터 조립, 그리고 인덱스를 활용한 성능 최적화는 PostgreSQL을 비롯한 관계형 데이터베이스를 실무에서 다루기 위해 필수적으로 이해해야 하는 핵심 개념입니다.