1. 이산형 확률 변수 (Discrete Random Variable)

정의

이산형 확률 변수는 특정 값들이 분리되어 있는 경우를 말하며, 셀 수 있는 유한 개 또는 무한 개의 값을 가질 수 있습니다.

• 예를 들어, 주사위를 굴려 나오는 값 ( X )는 1, 2, 3, 4, 5, 6과 같은 개별적인 값으로 구성되므로 이산형 확률 변수입니다.

특징

• 값이 정확히 구분됨 (분리된 값만 가능).
• ( P(X = x) )로 특정 값의 확률을 직접 계산.
• 확률 질량 함수(PMF)로 표현.

예시

• 고객 수: 하루 동안 콜센터에 걸려온 전화의 수 ( X )는 0, 1, 2, ... 등의 값만 가질 수 있습니다.
• 주사위: 주사위를 한 번 던졌을 때의 결과 ( X )는 ( {1, 2, 3, 4, 5, 6} ).

2. 연속형 확률 변수 (Continuous Random Variable)

정의

연속형 확률 변수는 특정 범위 내에서 무한히 많은 값을 가질 수 있으며, 값이 연속적입니다.

• 예를 들어, 사람의 키 ( X )는 160.1cm, 160.12cm, 160.123cm와 같이 정밀도가 계속 증가할 수 있습니다.

특징

• 특정 값에서의 확률은 항상 0이며, 구간의 확률로 계산.
• 확률 밀도 함수(PDF)로 표현.
• ( P(a \leq X \leq b) ) = PDF의 적분 값으로 계산.

예시

• 키: 성인의 키 ( X )는 150cm ~ 200cm 사이의 모든 실수 값.
• 온도: 하루 최고 기온 ( X )는 ( -10.5^\circ C ), ( 20.75^\circ C ) 등 연속적 실수 값.

3. 이산형 vs 연속형 비교표

4. 현업 활용 예시

이산형 확률 변수

• 주문 수량 분석
  하루 동안 온라인 쇼핑몰에서 발생한 주문 수 ( X )는 이산형 확률 변수입니다.
  • 데이터: ( {10, 20, 15, 18, 22, ...} )
  • 활용: 특정 주문 수량 이상 발생할 확률 계산.

연속형 확률 변수

• 제조업 품질 관리
  부품의 크기 ( X )는 연속형 확률 변수입니다.
  • 데이터: ( {10.01, 10.15, 9.98, 10.03, ...} )
  • 활용: 특정 크기 범위에 들어올 확률 계산 (( P(9.9 \leq X \leq 10.1) )).

PMF, PDF, CDF의 개념과 차이점

PMF, PDF, CDF 비교표

PMF, PDF, CDF의 현업 활용 사례

1. 확률 질량 함수: PMF (이산 분포에서 특정 값의 확률)

• 현업 사례: 콜센터에서 특정 시간대에 고객이 3명 전화를 걸 확률
  • 사용 분포: 포아송 분포 ( P(X = 3) = \text{poisson.pmf}(3, \lambda=5) )
  • 결과 해석: 결과값이 0.14라면, 14% 확률로 특정 시간대에 3명이 전화를 걸 가능성이 있음.
  • 의사결정: 이를 기반으로 시간대별 상담원 수를 조정.

2. 확률 밀도 함수: PDF (연속 분포에서 특정 값 근처 확률 밀도)

• 현업 사례: 제조업에서 부품 크기가 평균 10cm, 표준편차 0.2cm인 제품이 특정 크기 주변일 확률 분석
  • 사용 분포: 정규 분포
  • 특정 구간 ( P(9.8 \leq X \leq 10.2) = \int_{9.8}^{10.2} f(x) dx ) 계산
  • 결과 해석: 제품 크기가 해당 구간 내에 들어올 확률은 약 68%.
  • 의사결정: 제품 크기 기준을 9.8~10.2cm로 설정.

3. 누적 분포 함수: CDF (누적된 확률로 구간 계산)

• 현업 사례: 물류센터에서 배송 시간이 평균 2일, 표준편차 0.5일인 경우, 3일 이하로 배송될 확률 계산
  • 사용 분포: 정규 분포 ( P(X \leq 3) = \text{norm.cdf}\left(\frac{3-2}{0.5}\right) )
  • 결과 해석: 3일 이하로 배송될 확률이 약 97.7%.
  • 의사결정: 배송 목표를 3일 이내로 설정 가능.

PMF, PDF, CDF 간의 관계

• PMF: 이산형에서 특정 값의 확률 (막대 그래프)
• PDF: 연속형에서 특정 구간 확률의 밀도 (곡선 그래프)
• CDF: 이산/연속형 모두 특정 값 이하 확률 누적 (누적 곡선)

그래프 비교 예시

이처럼 세 가지 개념을 구분하여 사용하면 데이터를 더 깊이 이해하고, 의사결정을 위한 유용한 정보를 도출할 수 있습니다.

양자화(Quantization)는 딥러닝 모델의 크기를 줄이고 계산 효율성을 높이는 기술입니다. 원래 딥러닝 모델은 부동소수점(32비트 또는 16비트) 데이터를 사용하지만, 양자화를 통해 이 데이터를 정수(8비트 또는 4비트 등)로 변환합니다. 이를 통해 모델이 더 적은 메모리를 사용하고, 추론 속도를 높일 수 있습니다.

왜 양자화가 중요한가요?

• 리소스 절약: 메모리 사용량이 줄어듭니다.
• 속도 향상: 계산량이 줄어듭니다.
• 적은 전력 소모: 모바일 기기, 임베디드 시스템에서 유리합니다.

예를 들어, 원래 "32.7621"이라는 숫자가 있다면, 양자화를 통해 "33"으로 단순화해 저장하고 계산합니다. 이때, 모델의 성능(정확도)을 최대한 유지하면서 경량화를 추구하는 것이 목표입니다.

2. 양자화의 접근법

양자화는 두 가지 주요 방법으로 진행됩니다.

• Post-Training Quantization (PTQ)

  • 학습이 완료된 모델을 양자화.
  • 간단하지만, 성능 저하가 발생할 수 있습니다.

• Quantization-Aware Training (QAT)

  • 학습 과정에 양자화를 포함.
  • 성능 손실이 적지만 시간이 더 걸립니다.

3. 주요 양자화 방식 비교 (GGUF, bitsandbytes, AWQ, GPTQ)

(1) GGUF (General GPU Unified Format)

주요 특징: GGUF는 CPU 기반 환경에서 동작하도록 설계된 최신 양자화 기술입니다. llama.cpp에서 많이 사용됩니다. CPU에서 효율적인 메모리 사용과 빠른 추론 속도를 제공합니다.

장점:

• GPU 없이도 경량화된 모델 실행 가능.
• 다양한 하드웨어와 호환성.

단점:

• GPU 환경에서는 최적화가 부족함.

예시: → CPU 서버에서 대규모 언어 모델을 실행해야 할 때 GGUF로 양자화된 모델을 사용하면 GPU 없이도 실행 가능합니다.

(2) bitsandbytes

주요 특징:

• GPU 환경에서 메모리를 효율적으로 사용하도록 설계된 도구입니다.
• 8비트 옵티마이저로 유명하며, 주로 PyTorch와 함께 사용됩니다.

장점:

• GPU 메모리 사용량 감소.
• 학습 및 추론 속도 증가.

단점:

• GPU에만 특화되어 있어 CPU 환경에서는 효과가 제한적임.

예시: → GPT 모델을 학습할 때 bitsandbytes를 사용하면, GPU 메모리 부족 문제를 해결할 수 있습니다.

(3) AWQ (Activation-aware Weight Quantization)

주요 특징:

활성화 값(activation)과 가중치(weight)를 동시에 고려하여 양자화를 진행합니다. 이는 더 정밀한 결과를 제공하며, 특정 작업에 맞게 모델을 최적화할 수 있습니다.

장점:

• PTQ보다 성능 손실이 적음.
• 특정 데이터셋에 맞게 최적화 가능.

단점:

• 설정이 복잡하고 구현 난이도가 높음.

예시: → 이미지 분류 모델에서 AWQ를 적용하면, 양자화 후에도 높은 정확도를 유지할 수 있습니다.

(4) GPTQ (Grouped-Quantization for Transformers)

주요 특징:

• GPTQ는 트랜스포머 기반 모델을 양자화하기 위해 설계되었습니다.
• 트랜스포머 구조를 고려하여 성능 손실을 최소화합니다.

장점:

• GPT 모델과 같은 대규모 언어 모델에서 효율적.
• 양자화 후에도 안정적인 추론 성능 제공.

단점:

• 트랜스포머 외의 모델에는 적용이 어려움. 예시: → GPT-4 같은 언어 모델에 GPTQ를 적용하면, 더 작은 메모리로 높은 추론 성능을 유지할 수 있습니다.

데이터베이스 작업에서 GROUP BY 절의 성능은 대규모 데이터셋에서 중요한 영향을 미칩니다. PostgreSQL은 데이터를 그룹화하거나 정렬할 때, 정렬 순서에 따라 쿼리의 속도와 자원 소모량이 달라질 수 있습니다.

통계를 활용한 GROUP BY 절 순서 최적화

PostgreSQL 사용자는 종종 데이터를 다양한 기준으로 정렬하고 그룹화하는 분석 쿼리를 사용합니다. 이러한 연산자를 최적화하면 쿼리 실행 시간과 비용을 크게 줄일 수 있습니다. 이번 포스트에서는 GROUP BY 표현식에서 열의 순서를 선택하는 최적화 방법에 대해 다룹니다.

PostgreSQL은 이미 ORDER BY 조건에 따라 그룹화된 표현식 리스트를 재정렬하여 추가 정렬 작업을 제거하고 계산 리소스를 절약할 수 있습니다.

문제의 간단한 소개

DBMS는 일반적으로 한 개 이상의 열로 테이블 데이터를 그룹화하기 위해 해싱(HashAgg) 방법 또는 사전 정렬 후 순차적으로 집계하는 방법(SortAgg)을 사용합니다. 여러 열을 기준으로 데이터를 정렬할 때, PostgreSQL은 각 값 쌍마다 비교 연산자를 호출해야 합니다.

예를 들어, ('UserX1', 'Saturday', $100)라는 행과 ('UserX1', 'Monday', $10)라는 행을 비교한다고 가정해 봅시다. 첫 번째 값이 동일하면 두 번째 값을 비교하고, 두 번째 값이 다르면 세 번째 값을 비교할 필요가 없습니다.

제안된 SortAgg 최적화 메커니즘은 바로 이러한 원칙을 기반으로 합니다. 비교 시 중복값이 적은 열(예: UserID)을 먼저 비교하면 비교 연산자 호출 횟수가 줄어들어 성능이 향상됩니다.

PostgreSQL의 GROUP BY 동작 방식

PostgreSQL은 데이터를 그룹화할 때 다음 두 가지 주요 집계 방법을 사용합니다:

Hash Aggregate (해시 집계)

• 데이터 그룹별로 고유 키를 해시 테이블에 저장.
• 메모리 사용량은 많지만 데이터 정렬이 필요하지 않을 경우 유리.

Sort Aggregate (정렬 집계)

• 데이터를 정렬 후 그룹별로 순차 처리.
• 비교 연산자 호출 횟수에 따라 성능이 크게 좌우됨.

정렬 순서와 성능의 관계

PostgreSQL에서 데이터를 정렬하거나 그룹화할 때는 여러 열에 걸쳐 값을 비교합니다.

다음과 같은 순서를 따릅니다:

1. 첫 번째 열의 값 비교.
2. 첫 번째 열의 값이 같으면 두 번째 열 비교.
3. 계속 같을 경우, 이후 열 순서대로 비교.

이 과정에서 고유 값(카디널리티, Cardinality)가 높은 열을 먼저 비교하면, 불필요한 비교 연산이 줄어들고 쿼리 속도가 개선됩니다.

카디널리티(Cardinality)란?

카디널리티는 한 열에 존재하는 서로 다른 고유 값의 개수를 의미합니다.

• CustomerId: 고유 값이 많음 → 높은 카디널리티.
• WeekDay: 고유 값이 7개(요일) → 낮은 카디널리티.

고유 값이 많을수록, 정렬이나 비교 과정에서 일찍 차이가 발생해 성능이 좋아집니다.

실험: 열 순서 변경으로 인한 성능 차이

CREATE TABLE shopping (
  CustomerId bigint, CategoryId bigint, WeekDay text, Total money
);

INSERT INTO shopping (CustomerId, CategoryId, WeekDay, Total)
  SELECT random()*1E6, random()*100, 
         'Day ' || (random()*7)::integer,
         random()*1000::money
  FROM generate_series(1, 1E6) AS gs;

VACUUM ANALYZE shopping;

쿼리 1: 카디널리티가 낮은 열부터 정렬

WeekDay(고유 값 7개)부터 정렬.

• 쿼리

  EXPLAIN (ANALYZE, TIMING OFF)
  SELECT CustomerId, CategoryId, WeekDay, Total
  FROM shopping
  ORDER BY WeekDay, Total, CategoryId, CustomerId;

• 결과

  Sort  (cost=124363.84..126863.84 rows=1000000 width=30) (actual rows=1000000 loops=1)
  Sort Key: weekday, total, categoryid, customerid
  Sort Method: quicksort  Memory: 102702kB
  ->  Seq Scan on shopping  (cost=0.00..24706.00 rows=1000000 width=30) (actual rows=1000000 loops=1)
  Planning Time: 1.699 ms
  Execution Time: 1772.805 ms

쿼리 2: 카디널리티가 높은 열부터 정렬

CustomerId(고유 값 많음)부터 정렬.

• 쿼리

  EXPLAIN (ANALYZE, TIMING OFF)
  SELECT CustomerId, CategoryId, WeekDay, Total
  FROM shopping
  ORDER BY CustomerId, CategoryId, WeekDay, Total;
  

- 결과

Sort (cost=124363.84..126863.84 rows=1000000 width=30) (actual rows=1000000 loops=1) Sort Key: customerid, categoryid, weekday, total Sort Method: quicksort Memory: 102702kB -> Seq Scan on shopping (cost=0.00..24706.00 rows=1000000 width=30) (actual rows=1000000 loops=1) Planning Time: 0.197 ms Execution Time: 466.041 ms

**비교 결과**

- 카디널리티가 낮은 열 우선: 1.699 ms
- 카디널리티가 높은 열 우선: 0.197 ms


## GROUP BY 절 최적화

###  기존 GROUP BY

**CustomerId와 WeekDay의 순서가 고정되어 있음.**

SELECT CustomerId, WeekDay, SUM(Total) FROM shopping GROUP BY CustomerId, WeekDay;

### 최적화된 GROUP BY

**카디널리티 높은 열 CustomerId를 먼저 그룹화.
결과적으로 더 적은 연산으로 그룹화 가능.**

SELECT CustomerId, WeekDay, SUM(Total) FROM shopping GROUP BY WeekDay, CustomerId;

## PostgreSQL 통계 활용

PostgreSQL은 pg_statistic 테이블에 저장된 통계를 사용하여 최적화합니다:

- ndistinct: 열의 고유 값 개수를 나타냄.
- 히스토그램 분포 정보: 열 값의 분포도를 보여줌.

**통계 확인**

SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'shopping';

**결과 예시**

| **컬럼명**     | **값**        |
|----------------|---------------|
| customerid     | -0.511195    |
| categoryid     | 101.0        |
| weekday        | 8.0          |
| total          | 92032.0      |


## PostgreSQL의 미래: 동적 최적화
- PostgreSQL 17 버전에서는 GROUP BY 최적화를 자동화할 계획.
- 쿼리 실행 계획 단계에서 통계 기반으로 열 순서를 자동 재배열하여 최적화.


## 결론 및 실전 활용 팁

1. 카디널리티 높은 열 우선
- GROUP BY나 ORDER BY에서 카디널리티 높은 열을 먼저 정렬하도록 쿼리 설계.

2. PostgreSQL 실행 계획 분석
- EXPLAIN (ANALYZE) 명령으로 쿼리 성능을 정밀 분석.

3. 정기적인 통계 업데이트
- ANALYZE 명령을 주기적으로 실행해 최신 통계 유지.

4. 최신 PostgreSQL 기능 사용
- PostgreSQL 17 버전에서 제공될 자동 최적화 기능 활용.

효율적인 쿼리 최적화는 CPU 자원을 절약하고 대규모 데이터셋에서 놀라운 성능 향상을 가져옵니다. 여러분의 PostgreSQL 환경에서도 통계를 활용한 열 순서 최적화를 시도해 보세요!

PostgreSQL에서 Dead Tuple은 DELETE나 UPDATE에 의해 생성된다고 많이 알려져 있습니다. 하지만 놀랍게도, Duplicate Key 에러에서도 Dead Tuple이 발생하는 반면, 자릿수 초과나 도메인 제약 조건 위반에서는 Dead Tuple이 생성되지 않는 사실을 아시나요? 이번 글에서는 이러한 차이를 자세히 살펴보며 PostgreSQL의 MVCC(Multi-Version Concurrency Control)와 관련된 동작 원리를 설명해드리겠습니다.

1. PostgreSQL의 MVCC와 Dead Tuple

PostgreSQL은 MVCC를 사용하여 데이터의 동시성 및 트랜잭션 격리성을 보장합니다. MVCC의 핵심은 동일한 데이터에 대해 여러 버전의 tuple을 유지하는 것입니다.

• INSERT: 새로운 데이터를 삽입하면서 새로운 tuple이 생성됩니다.
• UPDATE: 기존 tuple을 "삭제 상태"로 표시하고 새로운 tuple을 삽입합니다.
• DELETE: tuple을 "삭제 상태"로 표시합니다.

이처럼, 삭제된 것처럼 보이는 tuple이 Dead Tuple입니다.

2. Duplicate Key 에러와 Dead Tuple 생성

Duplicate Key 에러는 PRIMARY KEY 또는 UNIQUE 제약 조건 위반 시 발생하며, 이 경우 Dead Tuple이 생성됩니다. 그 이유는 PostgreSQL이 트랜잭션 내에서 새로운 데이터를 삽입하려고 새로운 tuple을 생성하지만, 중복된 키로 인해 삽입이 실패하고 해당 tuple이 롤백되기 때문입니다. 이때 생성된 tuple은 Dead Tuple로 남게 됩니다.

예시: Duplicate Key 에러 발생

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value TEXT
);

BEGIN; -- 트랜잭션 시작
INSERT INTO test_table (id, value) VALUES (1, 'A');  -- 데이터 삽입

-- 다른 세션에서 같은 키로 데이터 삽입 시도
BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 'B');  -- duplicate key 에러 발생

이 경우, 첫 번째 트랜잭션이 삽입한 데이터가 Dead Tuple로 남게 됩니다.

3. 자릿수 초과와 도메인 에러에서 Dead Tuple이 생성되지 않는 이유

자릿수 초과, 타입 불일치, 도메인 제약 조건 위반은 데이터가 물리적으로 테이블에 기록되기 전에 발생합니다. 이 과정에서 tuple이 생성되지 않기 때문에 Dead Tuple이 생기지 않습니다.

예시: 자릿수 초과

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value NUMERIC(5, 2) -- 최대 5자리, 소수점 이하 2자리
);

INSERT INTO test_table (value) VALUES (123456.78); -- 자릿수 초과 에러 발생

PostgreSQL은 데이터가 테이블에 기록되기 전에 제약 조건을 먼저 검증합니다. 자릿수 초과 에러가 발생하면, 데이터는 저장되지 않고 트랜잭션이 롤백되며, 이때 Dead Tuple이 생성되지 않습니다.

예시: 도메인 제약 조건 위반

CREATE DOMAIN pos_int AS INTEGER CHECK (VALUE > 0);

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value pos_int
);

INSERT INTO test_table (value) VALUES (-10); -- 도메인 제약 조건 위반 에러 발생

도메인 제약 조건 역시 데이터가 테이블에 기록되기 전 검증되므로, 위반된 데이터는 저장되지 않으며 Dead Tuple이 생성되지 않습니다.

4. Duplicate Key 에러와 자릿수 초과/도메인 에러 비교

5. Dead Tuple 최소화 방법

• Duplicate Key 방지: ON CONFLICT 구문을 사용하여 Dead Tuple 생성 가능성을 줄일 수 있습니다.

INSERT INTO test_table (id, value)
VALUES (1, 'B')
ON CONFLICT (id) DO NOTHING;

• VACUUM을 통한 Dead Tuple 관리: Dead Tuple이 누적되지 않도록 autovacuum을 최적화하거나 수동으로 VACUUM을 실행합니다.

• 제약 조건 사전 검증: 트랜잭션 설계 시 데이터 충돌 및 제약 조건 위반 가능성을 사전에 최소화합니다.

6. Duplicate Key 에러와 제약 조건 비교하기

Step 1: 환경 설정

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value NUMERIC(5, 2) -- 최대 5자리, 소수점 이하 2자리
);

Step 2: Duplicate Key 에러 확인

BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 123.45);

-- 다른 세션에서 동일 키 삽입 시도
BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 678.90); -- duplicate key 에러

Step 3: 자릿수 초과 에러 확인

INSERT INTO test_table (value) VALUES (123456.78); -- 자릿수 초과 에러

Step 4: 도메인 제약 조건 에러 확인

CREATE DOMAIN pos_int AS INTEGER CHECK (VALUE > 0);
ALTER TABLE test_table ADD COLUMN domain_value pos_int;

INSERT INTO test_table (domain_value) VALUES (-10); -- 도메인 제약 조건 위반 에러

7. Dead Tuple 관리 포인트와 해결책

Dead Tuple은 PostgreSQL 성능 최적화에 있어 중요한 요소입니다. 이를 효과적으로 관리하면 스토리지 효율성과 쿼리 성능을 크게 개선할 수 있습니다. 아래에서는 Dead Tuple을 줄이고, 관리하기 위한 주요 방법과 함께 예제 쿼리를 소개합니다.

1. ON CONFLICT 구문 활용

Duplicate Key 에러로 인해 Dead Tuple이 생성되는 것을 방지하려면 ON CONFLICT 구문을 사용하세요. 이를 통해 충돌 상황에서 새로운 데이터를 삽입하지 않거나 기존 데이터를 업데이트하도록 처리할 수 있습니다.

예제: ON CONFLICT를 사용한 Dead Tuple 방지

INSERT INTO test_table (id, value)
VALUES (1, 'B')
ON CONFLICT (id) DO NOTHING;

또는, 충돌 시 데이터를 업데이트:

INSERT INTO test_table (id, value)
VALUES (1, 'Updated Value')
ON CONFLICT (id)
DO UPDATE SET value = EXCLUDED.value;

2. AUTOVACUUM 최적화

Dead Tuple이 누적되지 않도록 PostgreSQL의 autovacuum을 적절히 설정하세요. autovacuum이 비활성화되었거나 설정이 부적절하면 Dead Tuple이 쌓여 성능이 저하될 수 있습니다.

권장 autovacuum 설정:

• postgresql.conf 파일에서 다음 매개변수를 조정합니다:

autovacuum_vacuum_cost_limit = 2000   # autovacuum의 리소스 한계 설정
autovacuum_vacuum_scale_factor = 0.05 # 테이블 데이터 5% 변경 시 VACUUM 실행
autovacuum_analyze_scale_factor = 0.02 # 테이블 데이터 2% 변경 시 ANALYZE 

--변경 후 PostgreSQL 재시작
sudo systemctl restart postgresql

3. 수동 VACUUM 실행

Dead Tuple이 많아져 성능 저하가 발생하면, 수동으로 VACUUM 또는 VACUUM FULL을 실행하여 테이블을 최적화하세요.

예제: Dead Tuple 정리

VACUUM test_table; -- Dead Tuple 정리
VACUUM FULL test_table; -- 공간 재사용 최적화

-- ANALYZE와 함께 실행:
VACUUM ANALYZE test_table; -- 쿼리 플래너 최적화

4. FILLFACTOR 활용

FILLFACTOR를 설정하면 테이블 페이지에 여유 공간을 남겨 UPDATE와 같은 연산에서 Dead Tuple 생성을 줄일 수 있습니다.

예제: FILLFACTOR 설정

ALTER TABLE test_table SET (FILLFACTOR = 70);

• FILLFACTOR는 페이지 공간의 최대 사용 비율(%)을 설정하며, 기본값은 100입니다.

5. 제약 조건 미리 검증

애플리케이션 로직에서 데이터 검증을 사전에 수행해 제약 조건 위반으로 Dead Tuple이 생성되는 상황을 줄입니다.

예제: INSERT 전에 값 검증

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM test_table WHERE id = 1
    ) THEN
        RAISE NOTICE 'Duplicate Key Detected!';
    ELSE
        INSERT INTO test_table (id, value) VALUES (1, 'Validated Data');
    END IF;
END;
$$;

6. HOT (Heap-Only Tuple) 최적화

HOT 업데이트를 활용하면 Dead Tuple을 줄일 수 있습니다. 이 기능은 테이블의 동일 페이지 내에서 수정이 이루어질 때 Dead Tuple 생성을 최소화합니다.

HOT 업데이트가 가능한 경우:

• 인덱스 필드를 수정하지 않을 때
• 충분한 페이지 여유 공간이 있을 때

7. Dead Tuple 모니터링

pg_stat_user_tables 뷰를 활용하여 Dead Tuple의 상태를 주기적으로 점검하세요.

예제: Dead Tuple 상태 확인

SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuple_count,
    n_live_tup AS live_tuple_count,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0
ORDER BY
    n_dead_tup DESC;

8. 분석 및 리포트

Dead Tuple이 많은 테이블은 주기적으로 분석하여 적절한 VACUUM 주기를 설정하거나 테이블 재구성을 고려합니다.

예제: Dead Tuple 비율 계산

SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuple_count,
    n_live_tup AS live_tuple_count,
    ROUND((n_dead_tup::NUMERIC / (n_live_tup + n_dead_tup)) * 100, 2) AS dead_tuple_ratio
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0;

결론 Dead Tuple 관리의 핵심은 사전 예방과 주기적 점검입니다. ON CONFLICT 구문과 VACUUM을 적극 활용하고, FILLFACTOR와 autovacuum 설정을 최적화하면 Dead Tuple을 효과적으로 줄일 수 있습니다. PostgreSQL의 MVCC 특성을 잘 이해하고 이를 활용해 최적화된 데이터베이스 환경을 구축해보세요! 🚀

8. 결론

이번 글에서는 PostgreSQL의 MVCC와 Dead Tuple에 대해 알아보며 Duplicate Key 에러와 자릿수 초과/도메인 제약 조건 위반의 차이를 상세히 분석해 보았습니다. 이 과정을 통해 PostgreSQL이 어떻게 데이터를 처리하고 제약 조건을 검증하며, 이러한 동작이 성능과 Dead Tuple 생성에 어떤 영향을 미치는지 이해할 수 있었습니다.

데이터베이스를 운영하다 보면 성능 최적화와 안정성 유지는 늘 중요한 과제입니다. PostgreSQL의 동작 원리를 깊이 이해한다면, 효율적인 트랜잭션 설계와 Dead Tuple 관리를 통해 시스템 성능을 한층 더 끌어올릴 수 있습니다.

다음에도 PostgreSQL의 흥미로운 주제를 다루며 실무와 이론을 연결할 수 있는 내용을 준비하겠습니다. PostgreSQL과 함께 더 나은 데이터베이스 운영 환경을 만들어 가세요! 🚀

궁금한 점이나 추가로 다뤄줬으면 하는 주제가 있다면 댓글로 남겨주세요. 감사합니다! 🙌

#PostgreSQL #MVCC #DeadTuple #DatabaseOptimization #DBA

PostgreSQL은 효율적인 데이터 검색을 위해 인덱스를 사용하며, 그 중 가장 많이 사용되는 인덱스 유형은 B-Tree 인덱스입니다. 이 글에서는 PostgreSQL B-Tree 인덱스의 기본 개념과 장단점, 그리고 이를 관리하는 방법을 초보자도 쉽게 이해할 수 있도록 설명하겠습니다. 또한, Oracle의 B-Tree 인덱스와 비교해 보겠습니다.

PostgreSQL B-Tree 인덱스란?

B-Tree 인덱스는 데이터를 정렬된 상태로 저장하여 빠른 검색을 가능하게 하는 데이터 구조입니다. PostgreSQL에서 B-Tree 인덱스는 기본적으로 데이터를 페이지라는 단위로 나누어 저장하며, 각 페이지는 데이터를 순차적으로 정렬합니다.

예시 책의 목차처럼 원하는 내용을 쉽게 찾을 수 있도록 정리된 것이 인덱스라고 생각할 수 있습니다. 예를 들어, user_id에 인덱스를 생성했다면, 이 인덱스를 사용하여 user_id를 기준으로 빠르게 데이터를 찾을 수 있습니다.

B-Tree의 분할 방식

PostgreSQL에서는 B-Tree 인덱스가 데이터가 추가될 때마다 50-50 분할 방식을 사용하여 페이지를 나눕니다. 이는 새 데이터가 추가되면 기존 데이터와 균등하게 분할하여 각 페이지에 저장된다는 의미입니다.

예시: 책의 목차가 너무 길어져서 두 페이지로 나누게 될 때, 기존의 내용 절반은 첫 번째 페이지에, 나머지 절반은 두 번째 페이지에 넣는 것과 비슷합니다.

PostgreSQL 50-50 분할의 장단점

장점:

• 빠른 범위 검색: B-Tree 인덱스는 범위 검색에 뛰어난 성능을 보입니다. 예를 들어, BETWEEN, >=, <= 조건의 쿼리에서 효율적인 검색을 할 수 있습니다.
• 자동 관리: 데이터 삽입 시 인덱스가 자동으로 갱신되므로 별도의 관리 없이도 기본적인 성능 최적화가 이루어집니다.

단점:

• 성능 저하 가능성: 연속적으로 데이터가 삽입될 때, 페이지 분할이 자주 발생하여 성능 저하가 발생할 수 있습니다. 예를 들어, 거래 기록처럼 데이터가 연속적으로 추가되는 경우입니다. 인덱스 크기 증가: 50-50 분할 방식에서는 빈 공간이 많이 남을 수 있으며, 이로 인해 인덱스 크기가 불필요하게 커질 수 있습니다.
• 경합 문제: 여러 사용자가 동시에 데이터를 삽입할 때 경합이 발생할 수 있으며, 이는 잠금 대기 시간을 늘려 성능에 영향을 미칩니다.
• 분할 작업 빈도 증가: 데이터가 연속적으로 추가되면 빈번한 분할 작업이 필요하게 되어 성능에 부정적인 영향을 미칩니다.
• 추가적인 관리 필요: VACUUM이나 REINDEX 명령어를 주기적으로 실행하여 인덱스를 최적화해야 합니다.

PostgreSQL B-Tree 인덱스 관리 방법

B-Tree 인덱스를 효과적으로 활용하기 위해 다음과 같은 관리 포인트를 고려해야 합니다.

정기적인 VACUUM 및 ANALYZE 실행

• VACUUM: 오래된 데이터나 공간을 청소하여 테이블과 인덱스의 크기를 최적화합니다. 이를 통해 인덱스의 성능을 유지할 수 있습니다.
• ANALYZE: 통계를 업데이트하여, 쿼리 최적화가 정확히 이루어지도록 돕습니다.

VACUUM ANALYZE users;

REINDEX 명령어 사용

인덱스가 비효율적으로 분할되거나 크기가 커질 경우, REINDEX를 통해 인덱스를 재생성해 성능을 높일 수 있습니다.

REINDEX INDEX idx_user_id;

인덱스를 재구성하여 효율성을 높이고, 페이지 공간 낭비를 줄일 수 있습니다.

연속적인 데이터 추가 시 고려사항

연속적으로 데이터가 추가되는 테이블에서는, 오른쪽으로만 데이터가 쌓여 특정 페이지에 경합이 발생할 수 있습니다. 이러한 경우 CLUSTER 명령어를 사용하여 테이블 데이터를 인덱스 순서대로 재정렬할 수 있습니다.

CLUSTER users USING idx_user_id;

성능 모니터링 및 인덱스 사용 검토:

pg_stat_user_indexes 뷰를 사용하여 인덱스가 얼마나 자주 사용되는지 확인할 수 있습니다. 자주 사용되지 않는 인덱스는 삭제하여 성능과 저장 공간을 최적화하는 것이 좋습니다.

SELECT indexrelid::regclass AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

문제 해결: B-Tree 인덱스 성능 저하 개선 방법

문제: 빈번한 페이지 분할 및 공간 낭비

연속된 데이터 추가로 인한 페이지 분할이 많아지면, 성능이 저하될 수 있습니다.

해결 방법

기존 인덱스를 주기적으로 재생성(REINDEX)

• 페이지 분할로 인해 크기가 커진 인덱스는 REINDEX를 통해 재생성하여 성능을 최적화할 수 있습니다.

Fillfactor 조정

인덱스 생성 시 FILLFACTOR를 설정하여, 페이지의 데이터 밀도를 조절할 수 있습니다. 예를 들어, FILLFACTOR를 80으로 설정하면, 각 페이지에 80%만 데이터를 채우게 되어 페이지 분할 빈도를 줄일 수 있습니다.

CREATE INDEX idx_user_id ON users(user_id) WITH (FILLFACTOR = 80);

CLUSTER 사용

데이터를 인덱스 순서에 맞춰 정렬하여, 검색 성능을 개선하고 페이지 분할을 줄입니다.

테이블 파티셔닝 고려

대규모 데이터 삽입이 자주 발생하는 테이블은 파티셔닝(partitioning)을 적용하여 데이터 경합을 줄일 수 있습니다. 예를 들어, 날짜를 기준으로 파티션을 나누면 특정 파티션에만 데이터가 추가되어 페이지 분할이 줄어들게 됩니다.

PostgreSQL과 Oracle의 B-Tree 인덱스 비교

PostgreSQL과 Oracle은 모두 B-Tree 인덱스를 지원하지만, 그 구현 방식에는 차이가 있습니다.

PostgreSQL과 Oracle의 B-Tree 인덱스 비교

PostgreSQL과 Oracle은 둘 다 B-Tree 인덱스를 사용하지만, 몇 가지 중요한 차이점이 있습니다. 각 데이터베이스는 고유한 방식으로 B-Tree 인덱스를 최적화하며, 특정 환경에서 성능 차이를 보일 수 있습니다.

예시 쿼리

-- 확장 설치 (슈퍼유저 권한 필요)
CREATE EXTENSION pageinspect;

-- 테이블 및 인덱스 생성
CREATE TABLE msg_send (
    msg_send_no BIGINT NOT NULL,
    msg_content TEXT,
    CONSTRAINT msg_send_pk PRIMARY KEY (msg_send_no)
);

-- 순차 삽입
INSERT INTO msg_send
SELECT i, repeat('ab', 50)
FROM generate_series(1, 1000000) a(i);

-- 순차 삽입 후 페이지 요약 데이터 가져오기
SELECT blkno, live_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('msg_send_pk', 100, 10);

-- 테이블 비우기 및 랜덤 삽입
TRUNCATE TABLE msg_send;

INSERT INTO msg_send
SELECT (mod(i, 10) || lpad(i::text, 8, '0'))::bigint, repeat('ab', 50)
FROM generate_series(1, 1000000) a(i);

-- 랜덤 삽입 후 페이지 상태 확인
SELECT blkno, live_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('msg_send_pk', 100, 10);

결론

PostgreSQL의 B-Tree 인덱스는 간단하고 효율적이지만, 대규모 데이터나 연속적인 데이터 삽입이 많은 경우에는 성능 저하와 인덱스 크기 증가와 같은 문제가 발생할 수 있습니다. 이를 해결하기 위해서는 정기적인 인덱스 관리나 VACUUM, REINDEX 명령어의 주기적인 실행이 필요합니다.

반면, Oracle은 자동 분할과 다양한 분할 전략을 제공하여, 복잡한 데이터 삽입 패턴에서도 성능을 최적화할 수 있습니다. 하지만 그만큼 관리가 복잡할 수 있으며, 시스템 자원도 많이 소모할 수 있습니다.

PostgreSQL은 비교적 간단한 사용 환경에서 유용하며, Oracle은 더 복잡한 데이터 환경에서 유리한 선택이 될 수 있습니다. 데이터베이스를 선택할 때는 시스템의 데이터 삽입 패턴과 관리 용이성을 고려하여 적합한 인덱스 전략을 선택하는 것이 중요합니다.

이렇게 PostgreSQL B-Tree 인덱스의 기본 개념부터 장단점, 관리 방법까지 정리해 보았습니다. 데이터베이스 성능을 최적화하고 관리하는 데 도움이 되길 바랍니다!

Oracle의 PL/SQL

PL/SQL은 Oracle 데이터베이스 내에서 비즈니스 로직을 구현하기 위한 강력한 언어입니다. SQL의 기능을 확장하여 오류 처리, 트랜잭션 관리 및 조건부 논리를 추가할 수 있도록 설계되었습니다.

PL/SQL의 주요 특징:

• 고급 오류 처리: NO_DATA_FOUND 및 TOO_MANY_ROWS와 같은 기본 제공 예외와 사용자 정의 예외를 통해 세밀한 오류 관리를 제공합니다.
• Oracle 도구와의 통합: SQL*Plus, Oracle Forms, APEX 등과의 깊은 통합을 통해 워크플로우를 쉽게 관리할 수 있습니다.

PostgreSQL의 PL/pgSQL

PL/pgSQL은 PostgreSQL에서 절차적 로직을 구현할 수 있게 해주는 언어입니다. PL/SQL과 비슷한 목표를 공유하지만 PostgreSQL의 오픈 소스 아키텍처와 긴밀하게 연계되어 있으며 간결한 구문이 특징입니다.

PL/pgSQL의 주요 기능:

• 간소화된 구문: 다른 프로그래밍 언어에 익숙한 개발자들이 쉽게 배울 수 있도록 직관적인 구문을 갖추고 있습니다.
• 확장성: PostGIS와 pg_stat_statements와 같은 PostgreSQL 확장과 원활하게 작동하여 고급 분석, 지리 정보 조회 및 성능 모니터링이 가능합니다.

2. 구문 및 구조적 차이점

변수 선언

변수 선언 구조는 유사하지만, PL/SQL과 PL/pgSQL의 구문은 다릅니다.

PL/SQL 예제:

DECLARE
    v_emp_id NUMBER := 1001;
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;

PL/pgSQL 예제:

DO $$
DECLARE
    v_emp_id INT := 1001;
    v_emp_name TEXT;
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
    RAISE NOTICE '%', v_emp_name;
END $$ LANGUAGE plpgsql;

차이점

• PL/pgSQL에서는 변수 유형을 명시적으로 지정해야 하며 PostgreSQL의 엄격한 타입 시스템을 따릅니다.
• Oracle의 NUMBER 유형은 PostgreSQL의 NUMERIC 또는 INTEGER로 매핑됩니다.

3. 문자열 처리: NVL vs COALESCE 및 DECODE vs CASE

문자열 처리에서 NVL과 DECODE 함수는 PostgreSQL에서는 각각 COALESCE와 CASE로 대체됩니다.

NVL vs COALESCE 사용

Oracle에서 null을 지정한 값으로 대체하는 데 NVL을 사용합니다. PostgreSQL에서는 COALESCE를 사용하여 여러 인수 중 첫 번째 null이 아닌 값을 반환합니다.

--PL/SQL :
SELECT NVL(employee_name, 'Unknown') AS emp_name FROM employees;

-- PL/pgSQL:
SELECT COALESCE(employee_name, 'Unknown') AS emp_name FROM employees;

DECODE vs CASE 사용

DECODE는 Oracle의 조건 함수로, 간단한 IF-THEN-ELSE 논리를 사용합니다. PostgreSQL에서는 보다 강력한 CASE를 사용합니다.

-- PL/SQL 예제:
SELECT DECODE(department_id, 10, 'Sales', 20, 'HR', 30, 'IT', 'Other') AS department_name FROM departments;

-- PL/pgSQL 예제:
SELECT CASE department_id WHEN 10 THEN 'Sales' WHEN 20 THEN 'HR' WHEN 30 THEN 'IT' ELSE 'Other' END AS department_name FROM departments;

4. 트랜잭션 관리: COMMIT, ROLLBACK, SAVEPOINT

PostgreSQL의 PL/pgSQL 함수 내에서는 독립 트랜잭션 관리가 불가능하지만, 트랜잭션 블록 외부에서 COMMIT 및 ROLLBACK을 사용할 수 있습니다. PostgreSQL의 트랜잭션 처리는 주로 함수 호출 외부에서 수행되며, 필요 시 함수 내 트랜잭션 관리는 EXCEPTION 절을 사용해 예외 처리와 함께 구현할 수 있습니다.

-- PL/SQL 예시:
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (1002, 'Jane');
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;

-- PL/pgSQL 예시:

DO $$
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (1002, 'Jane');
    -- 트랜잭션은 DO 블록 내에서 자동으로 커밋됨
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Transaction error occurred';
END $$ LANGUAGE plpgsql;

5. 고급 오류 처리 및 트랜잭션

PL/SQL에서는 EXCEPTION 블록을 통해 내장된 예외 및 사용자 정의 예외를 처리할 수 있습니다. PL/pgSQL에서는 다양한 메시지 수준으로 오류를 제어할 수 있습니다.

-- PL/SQL 예시:
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No record found.');

-- PL/pgSQL 예시:
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No record found';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'An error occurred';

• RAISE 구문은 PostgreSQL의 다양한 메시지 수준 (NOTICE, INFO, WARNING)을 통해 오류 메시지를 조정할 수 있습니다.

6. PL/pgSQL로 마이그레이션 팁: 모범 사례

코드 리팩토링

• PostgreSQL의 구조에 맞게 코드를 최적화하고, PL/SQL 코드를 그대로 옮기지 말고, 불필요한 패키지 의존성을 줄여야 합니다.

데이터 유형 매핑

• PostgreSQL의 타입 시스템을 충분히 이해하고, Oracle과의 차이점을 정확히 매핑하는 것이 중요합니다.

PostgreSQL의 강점 활용

• PostgreSQL의 JSON 데이터 타입, 외부 데이터 래퍼, 확장 모듈 등을 적극 활용하여 기능을 확장할 수 있습니다.

결론

Oracle의 PL/SQL에서 PostgreSQL의 PL/pgSQL로 마이그레이션하는 과정에서 두 언어의 주요 차이점과 PostgreSQL의 고유 기능을 이해하면 마이그레이션이 원활하게 이루어질 수 있습니다.

대량의 데이터를 다루는 현대의 데이터베이스 환경에서 쿼리 최적화는 성능 개선의 핵심 요소입니다. 이번 포스팅에서는 PostgreSQL의 EXPLAIN 명령어와 PEV(Execution Visualizer)를 활용하여 복잡한 JOIN 쿼리의 성능을 분석하고 최적화하는 방법을 깊이 있게 탐구해보겠습니다.

테이블 생성 및 데이터 삽입

우선, 대량 데이터 환경을 위해 두 개의 테이블을 생성하고, 각각 715 MB 및 287 MB의 데이터를 추가하겠습니다.

1. 테이블 생성

-- customer_data 테이블 생성
CREATE TABLE customer_data (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    account_balance NUMERIC(15, 2),
    transaction_date DATE
);

-- transaction_log 테이블 생성
CREATE TABLE transaction_log (
    transaction_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customer_data(customer_id),
    transaction_amount NUMERIC(10, 2),
    transaction_date DATE,
    transaction_type VARCHAR(50)
);

2. 대량의 더미 데이터 생성

각 테이블에 데이터를 삽입하여 10GB 및 5GB의 크기를 달성할 수 있습니다. 예를 들어, generate_series 함수를 활용하여 1천만 건 이상의 데이터를 삽입할 수 있습니다.

-- customer_data 테이블에 대량 데이터 삽입
INSERT INTO customer_data (name, age, account_balance, transaction_date)
SELECT 
    'Customer ' || i,
    (RANDOM() * 60 + 20)::INTEGER,
    (RANDOM() * 100000)::NUMERIC(15, 2),
    CURRENT_DATE - (RANDOM() * 365)::INTEGER
FROM generate_series(1, 10000000) AS i;

-- transaction_log 테이블에 대량 데이터 삽입
INSERT INTO transaction_log (customer_id, transaction_amount, transaction_date, transaction_type)
SELECT 
    (RANDOM() * 10000000)::INTEGER,
    (RANDOM() * 1000)::NUMERIC(10, 2),
    CURRENT_DATE - (RANDOM() * 365)::INTEGER,
    CASE WHEN RANDOM() > 0.5 THEN 'credit' ELSE 'debit' END
FROM generate_series(1, 5000000) AS i;

3. 복잡한 JOIN 쿼리 작성

대량 데이터를 효율적으로 분석하기 위해 고객의 평균 잔액이 특정 기준 이상이면서 거래 내역이 많은 고객을 조회하는 복잡한 쿼리를 작성해보겠습니다.

-- 고객의 평균 잔액이 특정 금액 이상이면서 거래 내역이 많은 고객 리스트 조회
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    c.customer_id,
    c.name,
    AVG(c.account_balance) AS avg_balance,
    COUNT(t.transaction_id) AS transaction_count,
    MAX(t.transaction_date) AS last_transaction_date
FROM 
    customer_data c
JOIN 
    transaction_log t ON c.customer_id = t.customer_id
WHERE 
    c.account_balance > 50000 -- 잔액 기준 설정
    AND t.transaction_date >= CURRENT_DATE - INTERVAL '1 year' -- 최근 1년 내 거래만 포함
GROUP BY 
    c.customer_id, c.name
HAVING 
    COUNT(t.transaction_id) > 100 -- 거래 횟수가 일정 수 이상인 고객 필터링
ORDER BY 
    avg_balance DESC, transaction_count DESC
LIMIT 50; -- 상위 50명 고객 조회

이 쿼리는 대량 데이터에서 고객의 평균 잔액과 최근 거래 횟수를 계산하여, 이를 기준으로 상위 고객을 추출하는 구조입니다.

쿼리 실행 계획 분석

쿼리의 성능을 개선하기 위해 EXPLAIN 명령어를 사용하여 쿼리 실행 계획을 분석합니다. 이를 통해 쿼리가 어떻게 실행되는지, 각 단계에서 소요되는 시간과 버퍼 사용량을 확인할 수 있습니다.

PEV를 활용한 실행 계획 시각화

PEV(Execution Visualizer)는 실행 계획을 시각적으로 표현하여, 복잡한 쿼리를 쉽게 이해하고 최적화 포인트를 찾아내는 데 도움을 줍니다. 이를 활용하여 실행 계획을 분석하면, 어떤 인덱스가 효과적으로 사용되고 있는지, 쿼리의 병목 현상이 발생하는지 등을 쉽게 파악할 수 있습니다.

pev(postgresql_explain_visualizer) : https://github.com/dalibo/pev2

이미지 사이트 : https://explain.dalibo.com/

결론

대량 데이터 처리에서 PostgreSQL의 쿼리 최적화는 성능 개선에 중요한 역할을 합니다. EXPLAIN 명령어와 PEV를 활용하여 복잡한 JOIN 쿼리의 실행 계획을 분석하고 최적화하는 방법을 살펴보았습니다.

psql에서 제공하는 \gset 명령어는 SQL 쿼리의 결과를 변수로 저장하여 후속 쿼리에서 활용할 수 있도록 하는 기능입니다. 이 기능은 특히 조건에 따라 여러 단계를 거치는 복잡한 쿼리나 일괄 작업(batch 작업)을 수행할 때 유용합니다. \gset을 사용하면 쿼리 결과에서 반환된 값을 변수로 받아서 다른 쿼리에서 동적으로 참조할 수 있습니다. 아래에서는 \gset 명령어를 활용해 고객의 계좌 정보 및 최근 거래 내역을 조회하고, 조건에 따라 추가 정보를 가져오는 시나리오를 예로 들어 보겠습니다.

\gset의 동작 및 처리 과정

psql에서 \gset이 어떻게 작동하는지 좀 더 깊이 이해하기 위해, \gset의 처리 흐름을 단계별로 살펴보겠습니다. 이 과정은 쿼리 실행, 결과 반환, 변수 설정, 그리고 변수 참조로 나누어 볼 수 있습니다.

1. 쿼리 실행

• \gset은 SQL 쿼리를 실행할 때 함께 사용됩니다. 예를 들어, SELECT 쿼리와 \gset을 조합하여 작성하면, psql은 먼저 쿼리를 실행하고 결과를 얻습니다.
• 이때, \gset을 실행한 쿼리는 반드시 한 줄의 결과만 반환해야 합니다. 즉, 여러 줄의 결과가 있을 경우에는 에러가 발생하거나 첫 번째 행만 처리됩니다.

2. 결과 반환 및 변수화

• 쿼리 실행 후 반환된 결과의 첫 번째 행의 각 열이 변수로 설정됩니다. \gset은 열 이름을 그대로 변수 이름으로 사용하고, 열에 담긴 데이터를 변수 값으로 할당합니다.
• 예를 들어 account_id, transaction_amount라는 열이 있는 쿼리 결과가 반환되면, 각각 :account_id와 :transaction_amount라는 변수로 할당됩니다.
• psql에서 변수는 :variable_name 형식으로 참조됩니다. 이는 SQL 쿼리에서 뿐만 아니라 psql 내부의 여러 작업에서도 활용할 수 있습니다.

3. 변수 참조

• 변수로 저장된 값을 참조하려면 :변수명 형태를 사용합니다. 예를 들어, SELECT * FROM account_details WHERE account_id = :account_id;와 같은 쿼리에서 :account_id는 앞서 \gset으로 설정된 변수 값을 동적으로 받아 실행됩니다.

• 이 변수는 psql 세션이 유지되는 동안 지속됩니다. 그러나 세션이 종료되면 변수도 소멸되므로, psql 외부의 프로그램이나 스크립트에서 사용하려면 파일에 변수 값을 저장하거나 별도로 전달해줘야 합니다.

4.조건부 데이터 처리의 유연성

• \gset의 가장 큰 장점 중 하나는 특정 조건에 따라 동적으로 쿼리 조건을 설정할 수 있다는 점입니다. 예를 들어, 쿼리 결과에 따라 특정 조건을 만족하면 다른 쿼리를 수행하거나, 특정 조건이 충족되지 않으면 해당 작업을 건너뛸 수 있습니다.

• 이 과정에서 변수 값을 조건부 처리에 사용하여 효율적으로 데이터를 조회할 수 있으며, 변수에 저장된 값을 활용해 후속 작업을 제어하는 데도 유용합니다.

예제 시나리오로 \gset의 처리 과정 요약

예제 시나리오: 고객 계좌 정보 조회 및 조건부 추가 정보 조회 예를 들어, 금융 서비스에서 특정 고객의 계좌 ID와 최근 거래 내역을 조회한 후, 그 거래 금액이 특정 기준 이상일 경우 추가적인 계좌 정보를 조회하는 조건부 로직을 구현해 보겠습니다. 이는 고객의 활동을 실시간으로 모니터링하거나 특정 조건을 충족하는 데이터에 대해 추가 조사를 수행하는 데 유용한 방식입니다.

단계 1: 특정 고객의 계좌 ID 및 최근 거래 금액 조회 및 변수 저장

먼저, 고객의 계좌 ID와 최근 거래 금액을 SQL 쿼리로 조회한 후, \gset을 사용하여 해당 결과를 변수에 저장합니다. 이 과정에서 고객의 최근 거래만 가져오도록 날짜 순서대로 정렬한 후 상위 한 건만을 조회합니다.

-- 고객의 계좌 ID와 최근 거래 금액을 변수에 저장
SELECT account_id, transaction_amount
FROM transactions
WHERE customer_id = 12345
ORDER BY transaction_date DESC
LIMIT 1 \gset

위의 쿼리를 실행하면 account_id와 transaction_amount 열의 값이 각각 :account_id와 :transaction_amount라는 변수에 저장됩니다. 이 변수를 통해 다음 쿼리에서 동적으로 활용할 수 있게 됩니다.

단계 2: 조건에 따른 추가 계좌 정보 조회

이제 저장된 변수 :transaction_amount를 사용하여 특정 금액 이상일 때 계좌의 추가 정보를 조회하는 쿼리를 실행할 수 있습니다. 예를 들어, 최근 거래 금액이 500만 원 이상인 경우에만 추가 정보를 조회하는 조건을 추가할 수 있습니다.

-- 최근 거래 금액이 500만 원 이상일 경우 계좌 정보 조회
SELECT *
FROM account_details
WHERE account_id = :account_id
AND :transaction_amount >= 5000000;

이렇게 하면 특정 고객의 최신 거래 금액이 기준을 초과하는 경우에만 해당 계좌의 추가적인 정보를 조회할 수 있습니다. 이러한 방식은 특정 조건을 충족하는 데이터만 추가적으로 조회하기 때문에 보안성과 성능 측면에서 유리합니다.

단계 3: 계좌 정보 조회 후 로그 남기기

마지막으로, 고객의 계좌 정보를 조회한 후, 해당 정보를 로그에 남기는 작업을 수행할 수 있습니다. 이는 고객의 계좌 정보가 정상적으로 조회되었는지 기록을 남기는 데 유용합니다. \echo 명령을 사용하여 psql 콘솔에서 해당 정보를 출력할 수 있습니다.

-- 조회된 계좌 정보 기반으로 고객 정보 로그 남기기
\echo "Customer ID: 12345, Account ID: :account_id, Last Transaction Amount: :transaction_amount"

이 출력은 로그 파일 또는 기록에 남길 수 있어 고객의 민감한 금융 데이터를 처리하는 시나리오에서 유용합니다. 예를 들어, 고객 계좌에 대한 조회 기록을 별도의 로깅 시스템에 남겨, 추후 보안 감사나 기록 확인 시 사용할 수 있습니다.

\gset을 통한 유연한 데이터 처리의 장점

이 예제처럼 \gset을 활용하면 데이터베이스 쿼리를 통한 변수 설정 및 조건부 처리를 효율적으로 수행할 수 있습니다. 특히 금융 서비스나 데이터 모니터링과 같이 조건부 데이터 조회가 필요한 업무에서는 \gset을 활용해 동적 SQL 처리를 유연하게 구성할 수 있습니다. 이를 통해 특정 조건을 만족하는 데이터만을 조회하거나 후속 작업에서 조건에 따라 다양한 처리를 할 수 있어, 보안 강화와 성능 최적화에 기여할 수 있습니다.

파티셔닝은 큰 데이터베이스 테이블을 더 작은 자식 테이블로 나누어 관리하는 과정입니다. 이 과정은 확장성, 쿼리 성능 개선 등 여러 이유로 수행됩니다.

올바른 파티셔닝 전략을 선택하는 것은 더 나은 성과를 달성하기 위한 중요한 결정입니다. 잘못된 파티셔닝 전략은 오히려 성능을 저하시킬 수 있습니다. 예를 들어, 파티셔닝 후에도 대부분의 데이터가 단일 파티션에 저장되고, 해당 파티션이 읽기 집중적인 경우라면 이러한 파티셔닝 전략은 효과적이지 않습니다.

파티셔닝된 테이블 자체는 가상 테이블로, 자체 저장소가 없으며, 저장소는 파티션에 속해 있습니다. 각 파티션은 일반적인 테이블처럼 동작하며, 파티셔닝된 테이블과 연결됩니다.

PostgreSQL에서 제공하는 파티셔닝 방식은 세 가지입니다

1. 리스트 파티셔닝
2. 범위 파티셔닝
3. 해시 파티셔닝

리스트 파티셔닝 (List Partitioning)

리스트 파티셔닝은 특정 열의 값이 불연속적인 범주형 값일 때 사용합니다. 이를 통해, 특정 값이 속하는 데이터를 별도의 파티션에 저장하여 특정 범주에 속하는 데이터를 효율적으로 조회할 수 있습니다.

특징

• 범주형 데이터에 적합: 특정 값들(예: 국가 코드, 지역 등)에 따라 데이터를 분리할 때 유용합니다.
• 파티션별로 분리된 관리 가능: 예를 들어, 국가별로 데이터를 나누어 지역별 조회 성능을 높이고 관리 편리성을 제공합니다.
• 제한된 값만 포함 가능: 리스트 파티셔닝에서는 주어진 값만 파티션에 포함되며, 그 외 값에 대한 파티션이 필요할 경우 기본 파티션(Default Partition)을 정의할 수 있습니다.

주의사항

리스트 파티셔닝을 잘못 사용하면 파티션 간 데이터 불균형이 발생할 수 있습니다. 예를 들어, 특정 국가에만 사용자가 집중되어 있다면 해당 파티션만 지나치게 커질 수 있습니다.

장점

• 지정된 특정 값에 대한 조회 성능이 뛰어납니다.
• 범주형 데이터를 다룰 때 직관적이고 관리가 용이합니다.

예를 들어, country 열을 기준으로 지역별 데이터를 나눌 수 있습니다.

CREATE TABLE employee (
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    country char(2) NOT NULL,
    PRIMARY KEY (first_name, country)
) PARTITION BY LIST(country);

위의 예시에서 country 값에 따라 직원 테이블을 여러 파티션으로 나누었습니다. 이제 각 나라별로 테이블을 생성할 수 있습니다.

CREATE TABLE emp_in PARTITION OF employee FOR VALUES IN ('IN');
CREATE TABLE emp_us PARTITION OF employee FOR VALUES IN ('US');

INSERT INTO employee VALUES ('foo', 'a', 'US');
INSERT INTO employee VALUES ('bar', 'b', 'IN');

이러한 방식으로 특정 지역의 데이터를 검색할 때는 해당 파티션만 조회하면 됩니다.

범위 파티셔닝 (Range Partitioning)

범위 파티셔닝은 연속적인 값, 주로 날짜나 숫자 범위를 기준으로 데이터를 분할합니다. 특정 범위에 해당하는 데이터를 각각의 파티션에 저장함으로써, 조회 시 해당 범위 내의 파티션만을 스캔하게 되어 성능을 크게 향상시킬 수 있습니다.

특징

• 시간 또는 연속 값에 적합: 날짜, 나이, 금액 등 연속된 값의 범위를 기준으로 데이터를 나누기에 유용합니다.
• 자동 확장 가능: 새로운 데이터 범위가 추가될 때마다 새 파티션을 쉽게 만들고 붙일 수 있습니다. 예를 들어, 매년 새로운 데이터를 저장하기 위해 연 단위 파티션을 만들 수 있습니다.

주의사항

범위 파티셔닝은 파티션을 너무 세분화하거나 잘못된 범위를 설정하면 데이터 불균형을 초래할 수 있습니다. 예를 들어, 특정 연도에만 데이터가 집중되는 경우 해당 연도의 파티션만 과도하게 커질 수 있습니다.

장점

• 주기적인 데이터 처리나 로그 데이터 관리에 매우 효과적입니다.
• 쿼리 시 불필요한 파티션을 스캔하지 않아도 되므로, 대량 데이터 처리 시 성능을 크게 향상시킬 수 있습니다.

예를 들어, 직원 테이블을 직원의 입사 연도에 따라 파티셔닝할 수 있습니다.

CREATE TABLE employee (
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    country char(2) NOT NULL,
    start_time timestamp without time zone NOT NULL
) PARTITION BY RANGE (start_time);

이제 특정 연도에 입사한 직원들을 저장할 파티션 테이블을 생성하고 이를 메인 테이블에 연결할 수 있습니다.

CREATE TABLE employee_2024 (LIKE employee INCLUDING ALL);
ALTER TABLE employee ATTACH PARTITION employee_2024
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

위와 같이 연도별로 파티션을 나누면 데이터가 유기적으로 증가하는 경우 성능을 최적화할 수 있습니다.

해시 파티셔닝 (Hash Partitioning)

해시 파티셔닝은 데이터를 균등하게 분배하기 위해 해시 함수와 모듈러 연산을 사용하여 파티션을 결정합니다. 데이터의 균등 분포가 중요한 경우, 또는 명확한 파티셔닝 기준이 없을 때 해시 파티셔닝이 유용합니다.

특징

• 데이터 균등 분배: 특정 값에 따라 데이터가 치우치지 않고, 모든 파티션에 고르게 분산되도록 보장합니다.
• 동일한 기준에 따라 분할: 해시 함수를 사용하여 일관된 방식으로 데이터를 분할합니다. 예를 들어, 직원 ID를 기준으로 파티션을 분할할 경우, 해당 해시 값에 따라 직원 데이터가 자동으로 균등하게 분배됩니다.

주의사항

해시 파티셔닝은 파티션의 개수가 고정되며, 파티션을 추가하거나 제거하려면 전체 데이터를 다시 해싱해야 하는 번거로움이 있을 수 있습니다. 또한, 자연스러운 데이터 그룹화가 아니라 단순히 해시 값에 따라 분할되므로 특정 범위의 데이터를 조회할 때 성능상 이점이 적을 수 있습니다.

장점

• 명확한 기준이 없거나 균등하게 데이터를 분배해야 할 때 매우 유용합니다.
• 불균형한 데이터 분포를 방지할 수 있습니다.

CREATE TABLE employee (
    emp_id bigint,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    country char(2) NOT NULL,
    start_time timestamp without time zone NOT NULL
) PARTITION BY HASH (emp_id);

이제 해시 값에 따라 파티션을 나누겠습니다.

CREATE TABLE employee_h0 PARTITION OF employee FOR VALUES WITH (modulus 5, remainder 0);
CREATE TABLE employee_h1 PARTITION OF employee FOR VALUES WITH (modulus 5, remainder 1);
CREATE TABLE employee_h2 PARTITION OF employee FOR VALUES WITH (modulus 5, remainder 2);
CREATE TABLE employee_h3 PARTITION OF employee FOR VALUES WITH (modulus 5, remainder 3);
CREATE TABLE employee_h4 PARTITION OF employee FOR VALUES WITH (modulus 5, remainder 4);

해시 파티셔닝을 통해 데이터가 고르게 분산되도록 할 수 있습니다.

파티셔닝 프루닝 (Partitioning Pruning)

PostgreSQL에서 파티셔닝된 테이블에 대한 쿼리 성능을 극대화하려면, 파티션 프루닝 기능을 활용해야 합니다. 파티션 프루닝은 쿼리 시 불필요한 파티션을 건너뛰고, 조건에 맞는 파티션만을 읽도록 쿼리 플래너에 지시하는 기능입니다. 이를 통해 쿼리 성능을 극대화할 수 있습니다

SET enable_partition_pruning = on;

이 설정을 통해 쿼리 플래너가 필요한 파티션만 스캔하도록 최적화할 수 있습니다.

결론

파티셔닝이 적용된 테이블은 데이터가 여러 파티션으로 나뉘어 저장되기 때문에, 쿼리 실행 시 필요한 파티션만을 읽음으로써 성능을 향상시킬 수 있습니다. 하지만 모든 경우에 파티셔닝이 유리한 것은 아니며, 잘못된 파티셔닝 전략은 성능을 저하시킬 수 있습니다. 예를 들어, 특정 파티션에 데이터가 몰리면 오히려 데이터 접근 속도가 느려질 수 있습니다.

최근에 PostgreSQL을 오랫동안 사용한 사람들이 "pg_dump는 백업 도구가 아니다"라는 말을 반복하는 것을 자주 듣습니다. 사실 문서도 최근에 수정되어 pg_dump를 백업 도구로 설명하지 않도록 변경되었고, 많은 사람들이 이에 안도하는 분위기입니다.

경험이 풍부한 PostgreSQL 사용자와 개발자가 pg_dump가 실제로 백업 도구라고 주장했다가 공개적으로 비난받는 사례도 있었습니다.

저는 이 이야기가 조금 아쉽게 느껴집니다.

이 주장이 사실이 아니기 때문입니다. 2024년 7월 31일 이전까지 문서에서는 오랫동안 pg_dump를 백업 도구로 설명해왔습니다. 이 문구는 2003년 4월 17일에 추가되었습니다.

물론, 문서가 20년 넘게 특정 내용을 담고 있었다고 해서 그것이 자동으로 사실이 되는 것은 아닙니다. 그러나 pg_dump와 pg_dumpall을 적절히 사용하면 데이터베이스의 모든 데이터를 저장하고 나중에 새로운 데이터베이스로 로드할 수 있습니다.

저는 몇 번의 프로젝트 동안 그렇게 해왔고, 그 방식이 저에게 잘 맞았습니다. 만약 데이터베이스가 다운되고 데이터 디렉토리의 내용을 잃어버린다면, 저장한 덤프에서 복구해 시스템을 다시 가동할 수 있었습니다.

재해가 발생했을 때 데이터를 복구하고 시스템을 다시 가동할 수 있게 해주는 도구를 뭐라고 부를까요? 저는 그것을 백업 도구라고 부릅니다.

물론, 저는 이 방법을 일반 사용자에게 권장하지는 않습니다. 당시 제가 작업하던 PostgreSQL 데이터베이스는 오늘날의 기준으로도 매우 작은 데이터베이스였고, 사용자도 적었습니다.

pg_dump를 사용해 큰 데이터베이스를 백업하면 시간이 매우 오래 걸릴 수 있으며, 복원하는 데도 시간이 오래 걸릴 수 있습니다.

pg_dump는 실행 중에 MVCC 스냅샷을 유지하기 때문에 백업 시간이 길어지면 리소스 사용 문제가 발생할 수 있으며, 이로 인해 데이터베이스가 팽창하여 이후 VACUUM 또는 심지어 VACUUM FULL을 수행해야 하는 상황이 발생할 수 있습니다.(상상하고 싶지 않은 일이죠.)

PostgreSQL을 경험한 사용자라면 문제를 수작업으로 해결하면 되고, 그런 문제를 직접 해결한 경우도 있습니다.

예를 들어, pg_dump를 사용해 덤프한 후 최신 버전의 데이터베이스로 복원하려고 하면 오류가 발생할 수 있습니다. pg_dump 버전이 서버 버전과 일치할 경우 이러한 문제가 발생하지 않아야 하지만, 구버전의 덤프를 최신 버전으로 복원하려다 보면 문제가 발생할 수 있고, 종종 덤프를 수동으로 수정해야 했습니다.

또한 pg_dump를 사용할 때는 각 데이터베이스마다 개별적으로 pg_dump를 실행하고, 전역 객체를 저장하기 위해 pg_dumpall -g도 실행해야 하며, pg_hba.conf나 postgresql.conf 같은 구성 파일도 백업해야 한다는 점을 잊지 말아야 합니다. (놓치기 쉬운 부분입니다.)

이 문제는 pg_dump의 또 다른 약점으로 이어집니다. pg_dump는 백업을 자동으로 관리해주지 않습니다. 예를 들어, 최근 N개의 백업을 유지하고 싶다면 pg_dump는 아무런 도움을 주지 않습니다.

전역 객체(pg_dumpall -g에서)와 각 데이터베이스 객체(pg_dump에서)를 적절한 순서로, 적절한 병렬성으로 복원하는 데도 아무런 도움을 주지 않습니다. 덤프를 tar 형식으로 할지, 커스텀 형식으로 할지도 전적으로 사용자가 알아서 결정해야 합니다.

이러한 이유와 다른 이유들로 인해 대부분의 경우 pg_dump보다는 barman이나 backrest 같은 전문적으로 작성된 백업 도구를 사용하는 것이 더 나을 것입니다.

이러한 도구들은 데이터베이스 파일의 물리적 복사본과 데이터베이스를 일관되게 만드는 데 필요한 WAL 파일을 함께 백업해줍니다. 이러한 방식으로 point-in-time 복구를 할 수 있으며, pg_dump를 사용해 하루에 한 번 백업하는 경우 재해가 발생하면 마지막 백업 시점으로 돌아가야 하고 그 이후의 모든 변경사항은 손실됩니다.

반면, 제대로 구성된 백업 도구를 사용하면 마지막 백업 이후 생성된 모든 WAL을 재생할 수 있어 몇 초 내지 거의 데이터 손실이 없을 수 있습니다.

하지만 이러한 점들이 pg_dump가 백업 도구가 아니라는 것을 의미하지는 않습니다. 그것은 단지 대부분의 사람들에게는 pg_dump가 적합한 백업 도구가 아니라는 것을 의미할 뿐입니다.

사용 사례에 따라 pg_dump가 더 적합하다면 주저하지 말고 사용하십시오.

PostgreSQL을 처음 사용하던 시절, 모든 데이터를 텍스트 형식으로 백업하는 방식이 마음에 들었습니다. 텍스트 파일을 읽고 이해할 수 있었기 때문에 어떤 일이 발생해도 데이터를 복구할 수 있다는 자신감이 있었습니다.

오늘날에도 여전히 데이터베이스의 논리적 복사본을 갖고 싶을 때가 있을 수 있으며, 데이터 크기가 크다면 논리적 복제를 사용하는 방법도 고려할 것입니다.

물리적 백업에만 의존하면 데이터 손상 시 그 손상이 모두 복제될 가능성이 있습니다. 논리적 복사본을 사용하면 손상된 데이터가 복제되지 않고 문제가 발생하면 이를 빠르게 감지하고 대응할 수 있습니다.

제 의견:

pg_dump는 작은 규모의 데이터베이스나 간단한 데이터 구조를 다룰 때 충분히 유용한 백업 도구가 될 수 있습니다. 하지만 대규모 데이터베이스나 복잡한 환경에서는 더 완전한 백업 및 복구 도구를 사용하는 것이 더 적합할 수 있습니다. 결국 중요한 것은 사용자의 요구 사항과 환경에 맞는 백업 전략을 선택하는 것이며, 그 과정에서 pg_dump가 좋은 선택이 될 수 있습니다.

나의 말:

Oracle와 PostgreSQL에서의 테이블스페이스는 데이터베이스 객체의 저장 및 관리에 있어 중요한 개념으로, 두 DBMS가 각각 다르게 구현하고 있기 때문에 이를 이해하는 것이 중요합니다. 이 글에서는 Oracle과 PostgreSQL에서 테이블스페이스가 어떻게 다른지, 그리고 그 차이가 데이터베이스 관리에 어떤 영향을 미치는지 심도 있게 설명하겠습니다.

0. 테이블스페이스

테이블스페이스는 데이터베이스 관리 시스템에서 중요한 역할을 하며, 데이터베이스 객체인 테이블과 인덱스가 저장되는 위치와 방식을 결정합니다. Oracle과 PostgreSQL 모두 테이블스페이스 개념을 가지고 있지만, 각 데이터베이스의 전체 아키텍처에 따라 이를 다르게 구현합니다.

Oracle의 테이블스페이스는 데이터 유형을 분리하고, 저장소를 관리하며, 성능을 최적화하는 등 다양한 기능을 제공하는 데이터베이스의 필수적인 부분입니다. 반면, PostgreSQL은 주로 데이터베이스의 물리적 파일이 저장되는 위치를 제어하기 위해 테이블스페이스를 사용하며, 접근 방식이 더 단순합니다.

1. Oracle의 테이블스페이스

1. Oracle과 PostgreSQL의 테이블스페이스 개요

Oracle의 테이블스페이스 접근법

그림1

Oracle에서 테이블스페이스는 하나 이상의 데이터 파일로 구성된 논리적 저장 단위입니다. 이 데이터 파일은 테이블, 인덱스 및 물리화된 뷰와 같은 실제 데이터를 저장합니다. Oracle은 특정 목적을 위해 다양한 유형의 테이블스페이스를 사용합니다.

• SYSTEM 및 SYSAUX: 시스템 메타데이터를 포함한 필수 관리 데이터를 포함합니다. SYSTEM 테이블스페이스는 데이터베이스 작동에 필수적이며, SYSAUX는 일부 작업 부하를 분산하는 데 도움을 줍니다.
• 사용자 테이블스페이스: 애플리케이션 전용 데이터를 저장하는 데 사용되며, 관리자가 다양한 데이터 세트를 조직하는 방법에 대한 유연성을 제공합니다.
• TEMP 테이블스페이스: 임시 데이터를 관리하는 데 사용됩니다. 예를 들어, 결과 정렬과 같은 임시 작업에 사용됩니다.
• UNDO 테이블스페이스: 트랜잭션 롤백 및 읽기 일관성을 유지하는 데 필수적인 UNDO 정보를 저장하는 데 사용됩니다.

Oracle의 접근 방식은 DBA가 저장소 성능을 관리하고 I/O를 최적화하며 데이터베이스 유지 관리를 용이하게 할 수 있도록 합니다.

PostgreSQL의 테이블스페이스 접근법

그림2

PostgreSQL 테이블스페이스는 Oracle에 비해 훨씬 간단하며, 주로 데이터가 저장되는 파일 시스템의 디렉토리에 대한 포인터로 작용합니다. 기본 저장소 위치는 데이터 디렉토리(pg_default)이며, 사용자 정의 테이블스페이스를 생성하여 다양한 물리적 디스크에 저장소를 관리할 수 있습니다.

PostgreSQL은 CREATE TABLESPACE 명령어를 사용하여 테이블스페이스를 생성하고 디렉토리 경로를 지정할 수 있습니다.

이러한 사용자 정의 테이블스페이스는 데이터베이스 객체를 다양한 저장소 위치에 저장하는 데 사용할 수 있으며, I/O 부하를 분산하여 성능을 개선합니다.

Oracle과 달리 PostgreSQL은 임시 또는 UNDO 데이터에 대한 전문 테이블스페이스를 포함하지 않으며, 대신 MVCC를 사용하여 읽기 일관성을 유지하고 파일 시스템 디렉토리를 저장소로 활용합니다.

2. 테이블스페이스의 생성 및 관리

Oracle 테이블스페이스 관리

Oracle에서 테이블스페이스를 생성하고 관리하는 것은 여러 고급 기능을 포함합니다.

테이블스페이스 생성: Oracle 테이블스페이스는 CREATE TABLESPACE 명령어로 생성됩니다. 관리자는 데이터 저장 방식을 관리하기 위해 DATAFILE, SIZE, AUTOEXTEND 및 익스텐트 관리와 같은 매개변수를 지정할 수 있습니다.

CREATE TABLESPACE user_data 
DATAFILE '/u01/app/oracle/oradata/orcl/user_data01.dbf' SIZE 100M 
AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

이 명령어는 필요에 따라 크기가 증가하는 데이터 파일과 함께 user_data 테이블스페이스를 생성합니다.

• Managing Growth: Oracle은 AUTOEXTEND 기능을 통해 유연성을 제공하며, 테이블스페이스 용량을 늘리기 위해 데이터 파일을 추가할 수 있습니다. 대형 데이터베이스를 위한 간편한 관리 기능을 제공하는 big file 및 small file 테이블스페이스 사용이 또한 도움이 됩니다.

• Specialized Tablespaces: TEMP 및 UNDO 테이블스페이스와 같은 전문 기능을 제공하여 임시 정렬 작업이나 트랜잭션 롤백을 관리할 수 있습니다. 이러한 세분화는 성능을 향상시키고 서로 다른 작업이 동일한 저장소 자원을 경쟁하지 않도록 보장합니다.

PostgreSQL 테이블스페이스 관리

PostgreSQL에서는 CREATE TABLESPACE 명령어를 사용하여 테이블스페이스를 생성하고 특정 디렉토리에 연결됩니다.

sql
CREATE TABLESPACE fast_storage LOCATION '/mnt/ssd/pg_fast_storage';

이 명령어는 /mnt/ssd/pg_fast_storage 디렉토리를 가리키는 fast_storage 테이블스페이스를 생성합니다.

• 테이블스페이스 할당: PostgreSQL은 테이블스페이스를 전체 데이터베이스, 개별 테이블 또는 인덱스에 할당할 수 있습니다. 이러한 유연성은 DBA가 다양한 물리적 장치에 데이터를 분산하여 성능을 최적화할 수 있도록 도와줍니다.

• 디렉토리 기반 관리: Oracle의 데이터 파일 시스템과 달리 PostgreSQL 테이블스페이스는 파일 시스템에 직접 의존합니다. 자동 크기 조정 또는 익스텐트 관리가 없습니다.

3. 아키텍처적 차이

데이터 파일 처리

• Oracle: Oracle에서 테이블스페이스는 하나 이상의 데이터 파일로 구성됩니다. 이러한 데이터 파일은 자동으로 성장하거나 수동으로 크기를 조정할 수 있습니다. Oracle은 extents 와 segments 를 사용하여 테이블스페이스 내에서 데이터 분배를 관리하여 저장소 최적화 및 성능을 개선합니다.

• PostgreSQL: PostgreSQL 테이블스페이스는 파일 시스템의 디렉토리로 표현되며, 해당 테이블스페이스와 관련된 모든 데이터가 저장됩니다. PostgreSQL은 명시적인 데이터 파일 없이 파일 시스템에 의존하여 데이터 관리를 수행합니다.

목적 및 복잡성

• Oracle: Oracle 테이블스페이스는 시스템, 사용자, 임시 및 UNDO 데이터를 분리하는 논리적 컨테이너 역할을 합니다. 이 계층화된 아키텍처는 더 나은 제어 및 성능 조정 기능을 제공합니다.

• PostgreSQL: PostgreSQL 테이블스페이스는 주로 데이터를 서로 다른 저장 장치에 배치하는 데 사용됩니다. PostgreSQL의 테이블스페이스 아키텍처의 단순성은 DBA에게 직관적인 저장소 솔루션을 제공하지만, Oracle에서 찾을 수 있는 Specialized Tablespaces 옵션이 부족합니다.

4. 실용적 사용 사례

Oracle 테이블스페이스는 다음과 같은 용도로 사용됩니다.

• I/O 최적화: 테이블과 인덱스를 서로 다른 테이블스페이스에 배치하여 경쟁을 줄이고 성능을 향상시킵니다.
• 스토리지 확장: 빅파일 테이블스페이스를 사용하여 대량 데이터 세트를 관리함으로써 처리해야 하는 물리적 파일 수를 줄입니다.

PostgreSQL 테이블스페이스는 종종 다음과 같은 용도로 사용됩니다.

• 데이터 분산: 데이터를 서로 다른 드라이브에 분산하여 성능을 개선하고 저장소 자원을 효율적으로 사용합니다.

• 사용자 정의 저장소 위치: 고사용량 테이블이나 인덱스를 더 빠른 저장소(예: SSD)에 할당하여 읽기/쓰기 성능을 개선합니다.

5. 백업, 복구 및 유지 관리

백업 전략

• Oracle: Oracle 테이블스페이스는 RMAN을 사용하여 개별적으로 백업할 수 있으며, 특정 테이블스페이스에 대해 시점 복구를 수행하는 데 매우 유용합니다.

• PostgreSQL: PostgreSQL은 pg_dump 및 pg_restore를 사용하여 데이터베이스의 백업과 복원을 수행하며, 각 테이블스페이스를 별도로 백업할 수 있습니다.

유지 관리 작업

• Oracle: 테이블스페이스의 정기적인 모니터링, 조정 및 최적화를 수행하여 데이터베이스 성능을 유지하고 효율성을 높입니다.

• PostgreSQL: PostgreSQL은 VACUUM 및 ANALYZE를 통해 데이터베이스의 성능을 유지하고, 각 테이블스페이스의 통계를 수집하여 쿼리 옵티마이저를 돕습니다.

6. 고가용성 및 성능 고려 사항

고가용성

• Oracle: Oracle 데이터베이스는 Real Application Clusters(RAC)를 통해 여러 인스턴스에서 테이블스페이스를 공유하여 고가용성을 지원합니다.

• PostgreSQL: PostgreSQL은 테이블스페이스를 복제하여 고가용성을 구현할 수 있지만, Oracle만큼 완전한 솔루션은 아닙니다.

성능 관리

• Oracle: 성능 조정은 테이블스페이스 내의 세그먼트와 익스텐트를 관리하여 수행됩니다.

• PostgreSQL: PostgreSQL은 테이블스페이스를 여러 드라이브에 분산하여 성능을 조정하며, 더 간단한 구조로 인해 DBA가 성능을 관리하는 데 집중할 수 있습니다.

마이그레이션 고려 사항

Oracle에서 PostgreSQL로 마이그레이션할 때, 테이블스페이스 구조와 이를 적절하게 반영하는 것이 중요합니다. DBA는 다음 사항을 고려해야 합니다.

• 테이블스페이스의 재구성: Oracle 테이블스페이스를 PostgreSQL의 기본 및 사용자 정의 테이블스페이스로 변환합니다. 필요한 경우 PostgreSQL의 데이터를 다시 분산합니다.
• 성능 테스트: 마이그레이션 후 성능 테스트를 통해 쿼리 성능을 보장하고, 문제를 조기에 식별합니다.

결론

Oracle과 PostgreSQL의 테이블스페이스는 데이터베이스 성능과 관리에 중요한 역할을 합니다. Oracle은 데이터 분할 및 성능 최적화에 필요한 다양한 고급 기능을 제공하지만, PostgreSQL은 직관적인 관리와 유연성을 제공합니다. DBA는 이러한 차이를 이해하고 각 데이터베이스의 요구에 맞게 최적화된 테이블스페이스 전략을 개발하여 데이터베이스 성능을 향상시킬 수 있습니다.

Oracle와 PostgreSQL에서의 스키마는 데이터베이스 객체의 저장 및 관리에 있어 중요한 개념으로, 두 DBMS가 각각 다르게 구현하고 있기 때문에 이를 이해하는 것이 중요합니다. 이 글에서는 Oracle과 PostgreSQL에서 스키마가 어떻게 다른지, 그리고 그 차이가 데이터베이스 관리에 어떤 영향을 미치는지 심도 있게 설명하겠습니다.

이번 글은 스키마에 대한 내용을 다루지만 다음 글에는 테이블스페이스에 대해서 다룰 것입니다.

1. 스키마 (Schema)

0. 스키마란?

Oracle과 PostgreSQL의 차이점을 탐구하기 전에, 스키마의 일반적인 정의를 간단히 살펴보겠습니다. 데이터베이스 시스템에서 스키마는 일반적으로 테이블, 뷰, 인덱스 및 프로시저와 같은 데이터베이스 객체의 집합을 나타냅니다. 스키마는 이러한 객체에 대한 네임스페이스를 제공하여 각 객체가 해당 공간 내에서 고유하게 식별되도록 합니다.

Oracle과 PostgreSQL은 많은 유사한 개념을 공유하지만, 근본적인 차이가 있습니다. 그 중 하나가 바로 스키마입니다.

Oracle과 PostgreSQL 모두 스키마를 객체를 구성하는 메커니즘으로 제공하지만, 사용자와의 상호작용, 접근 제어 및 객체 관리 방식에는 상당한 차이가 있습니다.

Oracle에서 "스키마"라는 용어는 사용자 개념과 밀접하게 연결되어 있지만, PostgreSQL에서는 스키마가 다소 다른 역할을 합니다. 사용자와 스키마 간의 일대일 관계에 익숙한 Oracle 사용자에게 이는 혼란을 줄 수 있습니다.

이 글에서는 Oracle과 PostgreSQL 간의 스키마 사용 차이점을 깊이 있게 살펴보고, 이들이 어떻게 구조화되고 사용되는지, PostgreSQL 스키마를 효과적으로 활용하여 데이터베이스 관리 능력을 향상시키는 방법에 대해 설명하겠습니다.

글의 순서는 Oracle에서 스키마가 어떻게 작동하는지 검토한 후, PostgreSQL이 스키마를 다르게 처리하는 방법을 살펴보겠습니다.

1. Oracle의 스키마: 사용자 중심 모델

그림1 출처

스키마는 사용자와 동의어

Oracle에서 스키마는 본질적으로 사용자 계정과 동등합니다. 사용자가 Oracle에서 생성될 때, 해당 사용자에 대한 스키마가 자동으로 생성됩니다. 이 스키마는 해당 사용자가 소유한 모든 객체(테이블, 뷰, 인덱스 등)를 포함합니다. 따라서 Oracle 데이터베이스에서는 사용자와 스키마 간에 일대일 관계가 있습니다.

이 구조는 각 사용자가 데이터베이스 내에서 자신의 작업 공간을 갖게 하며, 그들이 생성하는 모든 객체는 자신의 스키마 내에 저장됩니다. 사용자와 스키마 간의 긴밀한 결합은 접근 제어를 단순화하며, 스키마를 소유한 사용자만 해당 스키마의 객체에 접근하고 수정할 수 있습니다. 다른 사용자에게 명시적인 권한을 부여하지 않는 한, 객체에 접근할 수 없습니다.

예를 들어, SEHYUN이라는 사용자가 EMPLOYEES 테이블을 소유하고 있을 때, 이 테이블의 전체 이름은 SEHYUN.EMPLOYEES가 됩니다. 여기서 SEHYUN은 사용자이자 해당 스키마의 소유자이며, EMPLOYEES는 그 스키마 내의 테이블 객체입니다.

사용자-스키마 분리: 주요 제한사항

Oracle의 사용자-스키마 관계는 여러 사용자가 동일한 데이터베이스 객체 세트에서 작업해야 하는 환경에서 몇 가지 제한을 나타냅니다. 각 사용자에게 고유한 스키마가 있기 때문에, 사용자 간에 객체를 공유하는 것은 번거로울 수 있습니다. 예를 들어, 여러 사용자가 SEHYUN.EMPLOYEES 테이블에 접근해야 하는 경우, 각 사용자에게 명시적인 권한을 부여해야 하거나, 객체를 참조할 때 스키마를 포함하지 않고도 사용할 수 있도록 동의어를 사용해야 합니다.

이 제한은 사용자 수와 공유 객체가 증가할수록 보다 복잡한 접근 제어 메커니즘 및 객체 관리로 이어질 수 있습니다. 또한, Oracle에서의 스키마 설계 및 데이터베이스 조직은 사용자 계정을 중심으로 이루어지므로, 특정 사용자가 아닌 부서, 팀 또는 애플리케이션에 속하는 객체를 다루기에는 유연성이 떨어집니다.

2. PostgreSQL의 스키마: 유연한 네임스페이스 모델

그림2 출처

스키마는 네임스페이스

PostgreSQL에서 스키마는 데이터베이스 내의 논리적 네임스페이스로 작용하며, 특정 사용자와 직접 연결되지 않습니다. ** 이 구별은 중요합니다. Oracle에서는 각 사용자가 스키마를 소유하지만, PostgreSQL은 동일한 데이터베이스 내에 여러 스키마를 생성할 수 있으며, 이 스키마에는 서로 다른 사용자가 소유한 객체가 포함될 수 있습니다.**

PostgreSQL에서 스키마에 대한 이러한 유연한 접근 방식은 보다 확장 가능하고 모듈화된 조직 수준을 제공합니다. PostgreSQL의 스키마는 관련된 객체를 그룹화하는 폴더 또는 컨테이너로 생각할 수 있습니다. 이를 통해 같은 데이터베이스 내에서 기능, 부서 또는 애플리케이션별로 객체를 조직하기가 용이해집니다.

예를 들어, 인사 관리 애플리케이션을 위한 PostgreSQL 데이터베이스를 고려해 보십시오. hr이라는 스키마를 두고 그 안에 employees, departments, salaries와 같은 테이블을 포함할 수 있습니다. 이 테이블의 완전한 이름은 hr.employees가 됩니다. 여러 사용자가 이 스키마에 접근할 수 있으며, 그 안의 객체는 서로 다른 사용자가 소유할 수 있습니다.

사용자와 스키마의 분리

Oracle의 사용자와 스키마 간의 엄격한 결합과는 달리, PostgreSQL은 더 큰 유연성을 제공합니다. 사용자는 여러 스키마에 걸쳐 객체를 소유할 수 있으며, 스키마는 서로 다른 사용자가 소유한 객체를 포함할 수 있습니다. 이러한 분리는 데이터베이스 조직 및 접근에 대한 보다 세분화된 제어를 제공하며, 많은 사용자와 애플리케이션이 있는 환경에서 특히 유리할 수 있습니다.

예를 들어, PostgreSQL에서는 조직의 각 부서(hr, finance, sales)에 대한 스키마를 생성하고 각 스키마 내에서 서로 다른 사용자가 소유한 객체를 가질 수 있습니다. 이를 통해 보다 깔끔한 구조를 제공하고, 사용자가 자주 스키마 경계를 넘거나 스키마 특정 권한에 의존할 필요 없이 같은 데이터베이스 내에서 작업할 수 있게 합니다.

또한, PostgreSQL에서는 검색 경로(Search Path)를 설정하여 사용자가 스키마 이름을 명시하지 않고도 쉽게 객체에 접근할 수 있습니다. 이를 통해 쿼리의 간결성을 높이고, 스키마 이름을 반복적으로 입력해야 하는 불편함을 줄일 수 있습니다.

3. Oracle과 PostgreSQL 스키마 간의 비교

1. 스키마와 사용자 간의 관계

• Oracle: 스키마는 사용자 계정과 직접 연결되며, 사용자와 스키마 간의 일대일 관계가 존재합니다. 스키마는 본질적으로 사용자의 데이터베이스 작업 공간을 나타냅니다.
• PostgreSQL: 스키마는 사용자와 독립적입니다. 여러 사용자가 동일한 스키마 내에서 객체를 소유할 수 있으며, 하나의 사용자가 여러 스키마에서 객체를 소유할 수 있습니다. 이는 보다 유연하고 모듈화된 데이터베이스 조직 접근법을 가능하게 합니다.

2. 스키마 생성

• Oracle: 사용자가 생성될 때 자동으로 스키마가 생성됩니다. Oracle에서는 스키마를 명시적으로 생성하지 않고, 대신 사용자를 생성하면 해당 사용자에 대한 스키마가 생성됩니다.
• PostgreSQL: CREATE SCHEMA 명령어를 사용하여 명시적으로 스키마를 생성합니다. 단일 데이터베이스 내에 여러 스키마를 생성할 수 있으며, 이러한 스키마는 객체를 논리적으로 구성하는 데 사용될 수 있습니다.

3. Object 구성

• Oracle: 객체는 생성한 사용자와 연결된 스키마에 속하며, 이를 통해 각 사용자는 자신의 객체 집합을 관리합니다. 이는 접근 제어 및 객체 공유를 복잡하게 할 수 있습니다.
• PostgreSQL: 객체는 여러 스키마 간에 유연하게 분산될 수 있으며, 각 스키마는 관련 객체를 그룹화하여 데이터베이스를 보다 논리적으로 조직할 수 있도록 지원합니다

4. 접근 제어

• Oracle: 스키마는 사용자와 연결되어 있으므로 접근 제어는 사용자 권한과 밀접하게 관련되어 있습니다. 사용자가 다른 사용자의 스키마에 있는 객체에 접근해야 할 경우, 권한을 명시적으로 부여하거나 동의어를 사용하여 객체 접근을 간소화해야 합니다.
• PostgreSQL: 접근 제어는 더 유연합니다. 여러 사용자가 동일한 스키마 내에서 객체를 소유하고 공유할 수 있습니다. 권한을 스키마 수준에서 부여할 수 있어, 사용자는 매 쿼리에서 스키마 이름을 언급하지 않고도 객체에 접근할 수 있습니다.

5. 완전한 객체 이름

• Oracle: 객체는 schema_name.object_name 형식으로 참조됩니다. 각 스키마가 사용자와 일치하므로, 객체는 일반적으로 user_name.object_name 형식으로 참조됩니다.
• PostgreSQL: 객체는 schema_name.object_name 형식으로 참조되지만, 스키마는 사용자와 독립적이므로, 스키마 이름은 객체의 소유자가 아닌 데이터베이스의 논리적 조직을 반영합니다.

6. 대규모 환경에서의 스키마 관리

• Oracle: 대규모 환경에서 스키마 관리는 사용자와 스키마 간의 1:1 관계로 인해 더 복잡할 수 있습니다. 이 구조는 더 세부적인 접근 제어 메커니즘을 필요로 하며, 객체 공유를 더 번거롭게 만들 수 있습니다.
• PostgreSQL: 스키마와 사용자의 분리는 대규모 환경을 관리하는 것을 쉽게 만들어 줍니다. 스키마는 관련 객체를 그룹화하는 데 사용될 수 있으며, 사용자는 복잡한 접근 제어 메커니즘 없이 여러 스키마의 객체에 접근할 수 있습니다.

고려사항: Oracle에서 PostgreSQL로 전환

Oracle로 구성된 DBMS에서 PostgreSQL로 이동할 때 가장 큰 조정은 아마도 사용자와 스키마의 분리일 것입니다. Oracle에서는 사용자 중심의 스키마 접근 방식으로 인해 데이터베이스 조직이 개별 사용자 계정 주위를 형성합니다. 그러나 PostgreSQL에서는 스키마가 사용자와 독립적인 더 유연한 조직 도구로 간주됩니다.

필자의 경험: Oracle의 스키마 모델에서 PostgreSQL로 전환할 때 고려해야 할 몇 가지 주의사항

1. 새로운 시각에서 바라보는 데이터베이스 설계

Oracle에서는 각 애플리케이션이나 부서마다 새 사용자를 생성하고 사용자와 스키마 간의 1:1 관계에 의존하여 객체를 조직하는 데 익숙할 것입니다. PostgreSQL에서는 개별 사용자가 아닌 기능, 애플리케이션 모듈 또는 조직 부서를 기준으로 스키마를 생성하는 것을 고려해야 합니다.

예를 들어, HR 부서에 대한 별도의 사용자를 생성하는 대신 hr 스키마를 만들고 해당 스키마 내에 모든 관련 객체(예: 테이블, 뷰, 함수)를 조직할 수 있습니다. 여러 사용자는 필요에 따라 이 스키마에 대한 접근 권한을 부여받을 수 있습니다.

2. 검색 경로 활용하기

PostgreSQL은 검색 경로를 구성할 수 있으며, 이는 시스템이 완전한 이름이 지정되지 않은 객체 이름을 어떻게 해결하는지를 결정합니다. 검색 경로를 적절히 설정함으로써 쿼리를 간소화하고 매 쿼리마다 스키마 이름을 지정할 필요를 줄일 수 있습니다.

예를 들어, 대부분의 작업이 hr 스키마에서 이루어지는 경우, 검색 경로를 hr을 먼저 포함하도록 설정하여 employees와 같은 테이블을 참조할 수 있습니다. 이 경우 매 쿼리에서 스키마 이름을 접두어로 붙일 필요가 없습니다.

3. 접근 제어를 위한 역할 사용하기

PostgreSQL에서는 역할(사용자 또는 사용자 그룹을 나타낼 수 있음)을 사용하여 스키마 전반에 걸친 객체에 대한 접근을 관리합니다. 역할을 효과적으로 사용하면 어떤 사용자가 어떤 스키마에 접근할 수 있는지 및 해당 스키마 내의 객체에 대해 어떤 작업을 수행할 수 있는지를 제어할 수 있습니다.

예를 들어, hr 스키마의 테이블에서 SELECT 권한이 있는 read_only 역할을 생성하고, HR 데이터에 대한 읽기 전용 접근이 필요한 사용자에게 이 역할을 부여할 수 있습니다.

4. 스키마 마이그레이션 계획하기

Oracle 데이터베이스를 PostgreSQL로 마이그레이션할 경우, Oracle 사용자와 스키마를 PostgreSQL 스키마에 어떻게 매핑할지 신중하게 계획해야 합니다. Oracle은 스키마를 사용자와 직접 연결하기 때문에, 동일한 구조를 따르면 PostgreSQL에서 많은 스키마가 생성될 수 있습니다.

대신, 사용자 소유가 아닌 논리적 관계를 기반으로 관련 객체를 더 적은 스키마로 통합하는 것을 고려하세요. 이는 데이터베이스 구조를 단순화하고 장기적으로 관리하기 쉽게 만드는 데 도움이 될 수 있습니다.

결론: PostgreSQL의 유연성 활용하기

Oracle에서 PostgreSQL로 전환하려면 스키마 및 데이터베이스 조직에 대한 사고 방식을 전환해야 합니다. Oracle에서는 스키마가 사용자와 긴밀하게 결합되어 있어 직관적이지만 때때로 제한적인 환경을 형성합니다. 반면 PostgreSQL은 스키마와 사용자를 분리하여 데이터베이스 객체를 조직하고 관리하는 데 있어 더 유연하고 확장 가능한 시스템을 제공합니다.

Oracle과 PostgreSQL의 스키마 작업 방식의 차이를 이해함으로써, PostgreSQL에서 데이터베이스를 구조화하고 접근 제어를 관리하는 방법에 대해 더 정보에 기반한 결정을 내릴 수 있습니다. 유연한 네임스페이스 모델과 강력한 스키마 관리 도구를 제공하는 PostgreSQL은 복잡한 데이터베이스를 조직하는 데 robust한 플랫폼을 제공합니다. 협업과 확장성을 촉진하는 방식으로 구성할 수 있습니다.

서론

PostgreSQL은 다양한 테이블 접근 방법을 제공하여 데이터베이스의 성능을 극대화합니다. 이전 블로그 포스트에서 PostgreSQL의 테이블 접근 방법 API와 힙 튜플과 튜플 테이블 슬롯(TTS) 간의 차이를 살펴보았습니다. 이번 포스트에서는 순차 스캔을 구현하는 데 필요한 주요 API 호출들에 대해 자세히 분석하겠습니다.

1. 순차 스캔이란?

순차 스캔은 데이터베이스에서 테이블의 모든 행을 차례대로 읽는 방법입니다.

이 방법은 테이블이 작거나 모든 데이터를 읽어야 할 때 유용합니다.

PostgreSQL은 이 순차 스캔을 구현하기 위해 몇 가지 API를 사용합니다.

예를 들어, 당신이 도서관에서 모든 책을 살펴보는 상황을 상상해 보세요. 당신은 첫 번째 책부터 시작하여 마지막 책까지 한 권씩 확인하고 있습니다. 이것이 바로 순차 스캔의 기본 개념입니다.

2. 순차 스캔에 사용되는 API

PostgreSQL에서 순차 스캔을 수행하기 위해 호출되는 주요 테이블 접근 방법 API는 다음과 같습니다

• relation_size()
• slot_callbacks()
• scan_begin()
• scan_getnextslot()
• scan_end()

이 API들은 PostgreSQL의 힙 접근 방법을 기반으로 구현되어 있으며, 각각의 역할과 기능에 대해 깊이 살펴보겠습니다.

src/backend/access/heap/heapam_handler.c에 자세히 있습니다.

2-1. table_block_relation_size(Relation rel, ForkNumber forkNumber)

이 함수는 쿼리 계획 단계에서 가장 먼저 호출되는 함수로, 주어진 관계의 물리적 크기를 확인합니다.

입력 파라미터

• Relation rel: 테이블, 인덱스 또는 뷰를 나타내는 관계.
• ForkNumber forkNumber: 관계 내 데이터 섹션을 지정합니다.
  • MAIN_FORKNUM (0): 사용자 데이터를 저장.
  • FSM_FORKNUM (1): 자유 공간 맵 데이터를 저장.
  • VISIBILITY_FORKNUM (2): 가시성 데이터를 저장.
  • INIT_FORKNUM (3): WAL-로그된 테이블을 재설정하는 데 사용.

출력

총 블록 수(기본적으로 8KB 크기)를 반환하여 쿼리 최적화에 도움을 줍니다.

2-2. heapam_slot_callbacks()

이 함수는 적절한 TTS 작업을 반환하여 실행자가 힙 튜플을 TTS로 변환할 수 있도록 합니다.

const TupleTableSlotOps TTSOpsBufferHeapTuple = {
  .base_slot_size = sizeof(BufferHeapTupleTableSlot),
  .init = tts_buffer_heap_init,
  .release = tts_buffer_heap_release,
  .clear = tts_buffer_heap_clear,
  .getsomeattrs = tts_buffer_heap_getsomeattrs,
  .getsysattr = tts_buffer_heap_getsysattr,
  .materialize = tts_buffer_heap_materialize,
  .copyslot = tts_buffer_heap_copyslot,
  .get_heap_tuple = tts_buffer_heap_get_heap_tuple,

  /* A buffer heap tuple table slot can not "own" a minimal tuple. */
  .get_minimal_tuple = NULL,
  .copy_heap_tuple = tts_buffer_heap_copy_heap_tuple,
  .copy_minimal_tuple = tts_buffer_heap_copy_minimal_tuple
};

반환 값

• TupleTableSlotOps 구조체로, TTS에 대한 다양한 동작을 정의합니다.

주요 구성 요소

• .base_slot_size: 슬롯의 기본 크기.
• .init: 슬롯 초기화 함수.
• .release: 슬롯 자원 해제 함수.
• .clear: 슬롯을 지우는 함수.
• .getsomeattrs: 일부 속성을 가져오는 함수.
• .getsysattr: 시스템 속성을 가져오는 함수.
• .materialize: 슬롯을 물리화하는 함수.
• .copyslot: 슬롯을 복사하는 함수.
• .get_heap_tuple: 힙 튜플을 가져오는 함수.
• .copy_heap_tuple: 힙 튜플을 복사하는 함수.

2-3. scan_begin()

이 함수는 실제 순차 스캔을 시작하기 전에 호출되며, HeapScanDesc 구조체를 초기화합니다.

입력 파라미터

• Relation rel: 스캔할 관계.
• Snapshot snapshot: 현재 스냅샷 정보를 포함.

출력

스캔 상태에 대한 설명을 포함하는 HeapScanDesc 포인터를 반환합니다.

구성 요소

현재 블록 번호, 총 블록 수, 스캔 모드 등.

2-4. scan_getnextslot()

이 함수는 순차 스캔의 핵심으로, 저장 엔진에서 튜플을 검색하는 역할을 합니다.

입력 파라미터

• HeapScanDesc scanDesc: 현재 스캔 상태 정보.
• TupleTableSlot *slot: 검색된 튜플을 저장할 슬롯.

출력

• 데이터가 남아 있을 경우 true, 스캔이 종료된 경우 false를 반환합니다.

세부 사항

• 함수는 버퍼 관리자 모듈에 튜플을 요청하여 메모리 버퍼에서 튜플을 읽거나 물리적 저장소에서 읽을 수 있습니다. 이는 사용자가 자신의 데이터베이스 저장 엔진을 만들 수 있는 이유이기도 합니다. API를 통해 메모리에서 직접 튜플을 읽고 쓸 수 있어 인메모리 데이터베이스를 구성할 수 있습니다.

2-5. scan_end()

이 함수는 순차 스캔을 종료할 때 호출되며, scan_begin()에서 생성된 HeapScanDesc를 정리합니다.

입력 파라미터

• HeapScanDesc scanDesc: 종료할 스캔 상태 정보.

세부 사항

• 함수는 스캔 중에 할당된 자원을 해제하고, 메모리 누수를 방지하기 위해 모든 관련 구조체를 정리합니다.

결론

PostgreSQL의 순차 스캔은 데이터베이스 쿼리 성능에 중요한 역할을 합니다. 위에서 설명한 API들은 PostgreSQL이 데이터를 효과적으로 검색할 수 있도록 돕습니다. 다음 블로그에서는 삽입, 업데이트, 삭제, 트렁케이트와 같은 다른 DML 명령을 처리하는 API를 탐구할 예정입니다.

Reference

https://www.highgo.ca/2023/11/17/explore-table-access-method-capabilities-sequential-scan-analyzed/

PostgreSQL은 강력하고 확장 가능한 오픈 소스 데이터베이스로, 그 내부 아키텍처는 효율적인 데이터 저장과 검색을 위해 매우 정교하게 설계되어 있습니다. 그중에서도 테이블 액세스 메서드는 PostgreSQL의 핵심적인 요소로, 데이터가 디스크에 어떻게 저장되고 조회되는지를 결정합니다.

이 블로그에서는 테이블 액세스 메서드의 기본 개념부터 시작하여, PostgreSQL의 기본 저장 형식인 Heap을 이해하고, 내부적으로 데이터를 처리하는 Tuple Table Slot(TTS)과 Heap Tuple의 상호작용에 대해 심도 있게 다루고자 합니다. 이를 통해 PostgreSQL 아키텍처에 대한 명확한 이해를 돕고, 데이터 저장 방식의 핵심 개념을 파악할 수 있도록 하겠습니다.

본문

1. 테이블 액세스 메서드란 무엇인가?

테이블 액세스 메서드는 PostgreSQL이 데이터를 저장하고 읽는 방식을 결정하는 인터페이스입니다. PostgreSQL 12 이전까지는 이 부분이 고정된 형태였지만, 12부터는 사용자가 필요에 따라 커스터마이징할 수 있도록 확장되었습니다. 이를 통해 사용자는 PostgreSQL 코어의 기본 기능을 바탕으로 자신만의 테이블 액세스 메서드를 정의하고, 이를 통해 데이터를 고유한 방식으로 저장할 수 있게 되었습니다.

테이블 액세스 메서드를 통해 수행되는 작업에는 여러 가지가 있습니다. 예를 들어, 다음과 같은 작업이 테이블 액세스 메서드를 통해 이루어집니다

• 순차 스캔(Sequential Scan): 테이블의 모든 데이터를 순차적으로 읽어오는 작업입니다.

• 병렬 스캔(Parallel Scan): 여러 스레드를 활용하여 데이터를 병렬로 스캔하는 작업입니다.

• 인덱스 기반 데이터 가져오기(Index Fetch): 인덱스를 활용하여 데이터를 빠르게 조회하는 작업입니다.

• 쿼리 추정(Query Estimate): 쿼리의 실행 계획을 세울 때 필요한 데이터를 추정하는 작업입니다.

• 삽입, 업데이트, 삭제, 트렁케이트(Insert, Update, Delete, Truncate): 테이블에 데이터를 삽입하거나 수정, 삭제하는 작업들입니다.

• 테이블 생성 및 관리(Table Creation, Vacuum, Vacuum Full): 테이블을 생성하고 관리하며, VACUUM 작업을 통해 불필요한 공간을 정리하는 과정이 포함됩니다.

기본 테이블 액세스 메서드인 Heap

PostgreSQL에서 기본으로 제공되는 테이블 액세스 메서드는 Heap입니다. Heap은 PostgreSQL에서 사용하는 디스크 기반의 저장 엔진으로, 모든 데이터를 힙 구조로 저장하고 관리합니다. 이때, PostgreSQL의 기본 테이블 액세스 메서드는 Heap뿐이므로, 다른 테이블 액세스 메서드를 사용하려면 별도의 커스터마이징이 필요합니다.

기본적으로 PostgreSQL은 Heap이라는 저장 방식을 사용하여 데이터를 관리합니다. Heap은 디스크에 데이터를 저장할 때 기본적으로 사용하는 테이블 액세스 메서드이며, PostgreSQL의 Multi-Version Concurrency Control (MVCC) 시스템과 연동되어 각 트랜잭션이 일관된 데이터 스냅샷을 사용할 수 있게 합니다.(자세한 설명은 아래에서 진행합니다.)

테이블 액세스 메서드 인터페이스 API

테이블 액세스 메서드의 API는 PostgreSQL의 소스 코드 중 src/include/access/tableam.h 파일에 정의되어 있습니다. 이 파일은 테이블 액세스 메서드를 정의하기 위한 다양한 콜백 함수들을 포함하고 있으며, 기본 Heap 액세스 메서드의 구현은 src/backend/access/heap/heapam_handler.c 파일에 위치해 있습니다.

Defined in src/include/access/tableam.h, and heap access method’s implementation is located in src/backend/access/heap/heapam_handler.c.

이러한 작업은 PostgreSQL의 다양한 기능들을 지원하기 위한 복잡한 과정이 포함되어 있으며, 이를 제대로 구현하기 위해서는 PostgreSQL 내부 구조에 대한 깊은 이해가 필요합니다.

테이블 액세스 메서드에는 데이터를 삽입하거나 조회할 때, PostgreSQL 코어가 데이터를 처리하는 방식을 제어하는 함수들이 포함되어 있습니다. 예를 들어, PostgreSQL 코어는 데이터를 삽입하거나 조회할 때 Tuple Table Slot(TTS)라는 데이터 구조를 사용하여 데이터를 처리합니다. 테이블 액세스 메서드를 구현할 때, 이러한 TTS 구조를 이해하고 이를 적절히 변환하여 디스크에 데이터를 저장하거나, 디스크에서 데이터를 조회하여 다시 TTS로 변환하는 작업이 필요합니다.

2. Tuple Table Slot(TTS)란 무엇인가?

Tuple Table Slot(TTS)는 PostgreSQL 내부에서 사용하는 데이터 구조로, 데이터의 한 행을 처리할 때 사용됩니다. TTS는 PostgreSQL의 Executor(실행기) 모듈에서 사용되며, 쿼리 처리 과정에서 매우 중요한 역할을 합니다. 기본적으로 TTS는 다음과 같은 상황에서 사용됩니다.

• 쿼리 결과로 반환된 행을 저장
• 테이블에 삽입되거나 업데이트될 행을 저장

TTS는 PostgreSQL 실행기 모듈과 테이블 액세스 메서드 간에 데이터를 주고받기 위한 공통된 형식을 제공합니다. 이를 통해, 테이블 액세스 메서드는 데이터를 디스크에 저장하는 형식과 상관없이 TTS를 사용하여 데이터를 주고받을 수 있습니다.

TTS의 생명 주기는 쿼리 처리 과정과 밀접하게 연관되어 있으며, 쿼리가 실행될 때 TTS는 PostgreSQL 코어에서 테이블 액세스 메서드로 전달되어 데이터를 처리하게 됩니다. 이 과정에서 테이블 액세스 메서드는 TTS를 Heap 튜플 또는 다른 유형의 튜플 데이터 형식으로 변환하고, 다시 TTS로 변환하는 작업을 수행하게 됩니다. TTS와 관련된 작업은 PostgreSQL 소스 코드의 src/include/executor/tuptable.h 파일에 정의되어 있습니다.

3. Heap Tuple이란 무엇인가?

Heap Tuple은 PostgreSQL에서 데이터를 디스크에 저장하는 형식입니다. Heap 튜플은 각 행의 실제 데이터 값을 포함하며, 이 데이터를 저장할 때 추가적인 메타데이터도 함께 저장됩니다. Heap 튜플의 주요 구성 요소는 다음과 같습니다.

• 행 표현(Row Representation): Heap 튜플은 테이블의 각 행을 저장하는 물리적 표현입니다. 각 열의 데이터 값이 포함되어 있습니다.

• 가시성 정보(Visibility Information): Heap 튜플은 xmin, cid, hintbit flag 등의 메타데이터를 포함하여, PostgreSQL의 MVCC(다중 버전 동시성 제어) 시스템에서 데이터를 일관되게 관리할 수 있도록 지원합니다. 이를 통해 각 트랜잭션은 자신만의 일관된 데이터 스냅샷을 사용할 수 있습니다.

• 업데이트 지원(Support for Updates): PostgreSQL에서 행이 업데이트되면, 기존 행은 '죽은' 것으로 표시되고(xmax 값을 할당), 새로운 버전의 튜플이 생성됩니다. 이를 통해 PostgreSQL은 데이터의 일관성을 유지하면서도 다양한 버전의 데이터를 관리할 수 있습니다.

• 인덱싱을 통한 효율성(Indexed for Efficiency): Heap 튜플은 ctid라는 값을 포함하고 있으며, 이 값은 해당 튜플의 물리적 위치(페이지 번호와 오프셋)를 나타냅니다. 인덱스는 이러한 tid 값을 사용하여, 테이블을 전체 스캔하지 않고도 빠르게 해당 튜플을 조회할 수 있도록 돕습니다

4. Tuple Table Slot과 Heap Tuple의 차이점

Tuple Table Slot (TTS): TTS는 PostgreSQL의 실행기 모듈에서 사용하는 데이터 형식으로, 쿼리 결과를 처리하고 데이터베이스에 데이터를 삽입하거나 업데이트할 때 사용됩니다. TTS는 PostgreSQL 코어와 테이블 액세스 메서드 간에 데이터를 주고받기 위한 공통 형식입니다.

Heap Tuple: Heap 튜플은 PostgreSQL의 기본 테이블 액세스 메서드인 Heap에서 사용하는 데이터 형식으로, 데이터를 실제로 디스크에 저장할 때 사용됩니다. Heap 튜플은 각 행의 데이터 값뿐만 아니라 가시성 정보와 물리적 위치 정보도 함께 저장합니다.

테이블 액세스 메서드는 이러한 TTS와 Heap 튜플 간의 변환을 담당하며, PostgreSQL 코어와 데이터 저장소 간의 상호작용을 조정하는 역할을 합니다. 이 메서드는 PostgreSQL 시스템의 중요한 부분으로, 데이터를 효율적으로 저장하고 조회하는 데 필수적입니다.

결론

PostgreSQL의 테이블 액세스 메서드는 데이터베이스 시스템의 효율적인 데이터 저장과 처리를 위한 매우 중요한 개념입니다. 특히, Tuple Table Slot과 Heap Tuple 간의 상호작용을 이해하면, PostgreSQL이 데이터를 어떻게 처리하고 저장하는지에 대한 깊은 통찰을 얻을 수 있습니다. PostgreSQL 12 이후 도입된 사용자 정의 테이블 액세스 메서드를 통해 데이터 저장 방식을 보다 유연하게 관리할 수 있게 되었지만, 기본적인 Heap의 구조와 기능은 여전히 PostgreSQL에서 핵심적인 역할을 합니다.

이 블로그를 통해 PostgreSQL의 내부 구조를 깊이 있게 이해하고, 테이블 액세스 메서드가 데이터베이스 시스템의 성능과 확장성에 어떤 영향을 미치는지 알아보는 좋은 기회가 되었기를 바랍니다. PostgreSQL의 복잡한 내부 구조를 파악함으로써, 데이터베이스 성능 최적화와 커스터마이징에 더욱 유리한 포지션을 차지할 수 있을 것입니다.

Reference

https://www.highgo.ca/2023/10/20/quick-overview-of-postgresqls-table-access-method/

PostgreSQL에서 테이블의 성능을 유지하고, 비효율적인 공간을 회수하는 것은 매우 중요합니다. 이 작업을 위해 네 가지 대표적인 방법이 있는데, 각각의 특성에 따라 상황에 맞는 선택이 필요합니다. 이번 글에서는 PostgreSQL 초보자를 위해 VACUUM, VACUUM FULL, pg_repack, 그리고 pg_reorg의 차이점을 자세히 비교하고 어떤 상황에서 어떤 방법을 사용하는 것이 좋은지 설명해 드리겠습니다.

1. VACUUM

개요

VACUUM은 PostgreSQL에서 기본 제공되는 테이블 청소 작업으로, 테이블에서 삭제되거나 수정된 튜플을 "죽은 튜플(dead tuple)"로 인식하고 이 공간을 재사용 가능하도록 처리합니다. 그러나 이 공간은 실제로 반환되지 않고, 새 데이터가 들어올 때까지 예약된 상태로 남아 있습니다.

특징

• 장점:

  • 빠르고 성능에 큰 영향을 미치지 않습니다.
  • PostgreSQL에 기본 내장된 기능으로, 별도의 설치가 필요 없습니다.
  • 자동 VACUUM 설정이 가능하여 관리 부담이 적습니다.

• 단점:

  • 테이블에서 삭제된 행을 실제로 삭제하지 않고, 새로운 데이터로 교체할 수 있는 공간으로만 표시합니다.
  • 테이블의 물리적 크기를 줄이진 않으므로, 디스크 공간 절약에는 한계가 있습니다.

사용 사례

데이터가 많이 삭제되지 않은 테이블에서 정기적인 공간 최적화를 위해 사용. 기본적인 성능 유지 및 데이터베이스 관리를 위해 사용.

2. VACUUM FULL

개요

VACUUM FULL은 기본 VACUUM과 달리 테이블의 물리적 크기를 줄이고 디스크 공간을 회수하는 작업을 수행합니다. 테이블 내의 데이터를 재배치하여 실제로 사용되지 않는 공간을 반환하며, 이 작업을 위해 테이블을 잠그고 완전히 재구성합니다.

특징

• 장점:

  • 테이블의 물리적 크기를 줄여 실제 디스크 공간을 확보할 수 있습니다.
  • 테이블 데이터를 재구성하여 성능이 향상될 수 있습니다.

• 단점:

  • 긴 테이블 락이 발생하며, 테이블에 액세스하는 모든 쿼리가 중단될 수 있습니다.
  • 큰 테이블의 경우 시간이 오래 걸릴 수 있습니다.

사용 사례

디스크 공간이 부족한 경우, 즉각적인 디스크 공간 회수가 필요할 때 사용. 테이블 크기가 많이 커진 경우, 서비스 중단 시간이 허용될 때 사용.

3. pg_repack

개요

pg_repack은 PostgreSQL의 확장 모듈로, 테이블의 공간을 정리하면서도 테이블에 락을 거의 걸지 않고 작업을 수행할 수 있습니다. VACUUM FULL과 유사하게 테이블을 재구성하여 공간을 회수하지만, 서비스 중단 시간을 줄이는 데 최적화되어 있습니다.

특징

• 장점:

  • 락을 최소화하며 테이블의 공간을 최적화합니다.
  • 테이블과 인덱스를 모두 재구성하여 성능 향상에 도움을 줍니다.
  • VACUUM FULL보다 성능에 미치는 영향이 적습니다.

• 단점:

  • PostgreSQL의 외부 확장 모듈로, 별도의 설치가 필요합니다.
  • 트리거나 외래 키 제약 등이 있는 복잡한 테이블에서는 사용에 주의가 필요합니다.

사용 사례 트래픽이 많은 서비스에서 테이블을 잠그지 않고 최적화가 필요한 경우. 디스크 공간 회수와 성능 최적화를 동시에 해야 할 때.

4. pg_reorg

개요

pg_reorg는 PostgreSQL에서 테이블을 재구성하는 외부 도구로, VACUUM FULL과 pg_repack의 중간 단계에 위치한 도구입니다. 짧은 시간 동안 테이블에 락을 걸어 공간을 정리하면서도, 성능을 최대한 유지할 수 있도록 설계되었습니다.

특징

• 장점:

  • 락을 최소화하면서도 테이블과 인덱스를 재구성하여 공간을 최적화합니다.
  • pg_repack과 유사하게 큰 테이블에서도 사용할 수 있으며, 공간 효율을 높입니다.

• 단점:

  • 확장 모듈로, 별도의 설치가 필요합니다.
  • 최신 PostgreSQL 버전과 호환성이 떨어질 수 있어, 일부 환경에서는 설정에 주의가 필요합니다.

사용 사례

테이블을 완전히 재구성하면서도 잠금 시간을 줄이려는 경우. 서비스 중단 시간에 대한 여유가 있을 때 사용.

네 가지 방법의 비교

결론

VACUUM은 가볍고 빠르게 테이블의 공간을 최적화하지만, 디스크 공간을 직접적으로 회수하지는 않습니다. VACUUM FULL은 디스크 공간을 회수하고 성능 최적화를 도모할 수 있지만, 테이블에 락이 발생하여 큰 테이블에서는 성능 저하가 발생할 수 있습니다.

pg_repack은 테이블을 잠그지 않으면서도 공간을 최적화할 수 있어, 트래픽이 많은 환경에서 유용합니다. pg_reorg는 짧은 락을 사용해 공간을 회수할 수 있으며, pg_repack과 유사한 기능을 제공하되, 사용 환경에 따라 조금 더 유연하게 사용할 수 있습니다.

상황에 따라 이러한 도구를 적절히 조합하여 사용할 수 있으며, 테이블의 크기나 서비스 중단 시간에 민감한 환경에서는 pg_repack과 같은 도구가 적합할 수 있습니다. PostgreSQL 최적화 작업에 대한 이해를 바탕으로, 필요에 맞는 도구를 선택해 성능을 향상시킬 수 있기를 바랍니다! 🚀

PostgreSQL은 다른 데이터베이스 시스템들과 달리, 멀티 프로세스(Multi-Process) 구조를 사용합니다. 이 구조는 각 클라이언트 연결마다 독립적인 프로세스를 생성하여 데이터베이스 질의와 응답을 처리합니다.

이 방식은 격리성과 안정성을 보장하는데 탁월하지만, 오래 실행되는 쿼리(Long Query)로 인해 발생할 수 있는 몇 가지 문제가 있습니다.

특히, Autovacuum 프로세스가 이를 방해받아 제대로 동작하지 못할 수 있습니다. 이번 글에서는 PostgreSQL의 멀티 프로세스 구조가 왜 이러한 문제를 유발하는지, 그리고 이를 해결할 수 있는 다양한 방법에 대해 살펴보겠습니다.

1. PostgreSQL의 멀티 프로세스 구조

멀티 프로세스 아키텍처에서 PostgreSQL은 각 클라이언트 연결마다 별도의 프로세스를 생성하는 방식을 사용합니다. 이러한 구조의 동작 방식은 다음과 같습니다.

• 메인 프로세스(Postmaster): PostgreSQL 서버를 시작하면 메인 프로세스가 실행되며, 클라이언트 연결을 관리합니다.
• 클라이언트 연결: 클라이언트가 데이터베이스에 연결할 때마다 새로운 프로세스를 포크(fork)하여 해당 클라이언트를 위한 독립적인 프로세스를 생성합니다. 이 프로세스는 클라이언트의 질의와 응답을 처리합니다.
• 백그라운드 프로세스: 체크포인트 관리, 로그 쓰기, autovacuum 등의 작업을 처리하기 위한 백그라운드 프로세스들이 별도로 실행됩니다.

이 구조의 장점으로는 격리성이 있습니다. 하나의 프로세스에서 문제가 발생해도 다른 프로세스에 영향을 미치지 않기 때문에 안정성이 높습니다.** 하지만, 각 클라이언트 연결마다 별도의 프로세스가 생성되므로 리소스 사용량이 많아지고, 컨텍스트 스위칭 비용이 발생할 수 있다는 단점도 있습니다.**

2. Autovacuum과 MVCC

PostgreSQL은 MVCC(Multi-Version Concurrency Control)라는 동시성 제어 방식을 사용합니다. 트랜잭션이 데이터를 변경할 때 기존 데이터를 그대로 유지하고 새로운 데이터를 기록하여 동시성을 보장하는 방식입니다. 이 과정에서 데이터의 이전 버전, 즉 Old Tuple이 생성되며, 트랜잭션이 끝날 때까지 유지됩니다.

Autovacuum은 이러한 Old Tuple을 주기적으로 삭제하고, 테이블에 남아있는 불필요한 데이터를 정리하는 역할을 합니다. Autovacuum이 잘 동작해야만 데이터베이스의 디스크 공간을 효율적으로 사용할 수 있고, 성능 저하를 방지할 수 있습니다.

3. Long Query가 Autovacuum에 미치는 영향

오래 실행되는 쿼리(Long Query)는 트랜잭션이 열려 있는 상태로 데이터를 계속해서 참조하게 만듭니다. 이로 인해 Autovacuum은 트랜잭션이 끝나지 않는 한, 해당 데이터에 대한 정리 작업을 수행하지 못하게 됩니다. 이러한 상태가 지속되면 디스크 공간을 과도하게 사용할 뿐만 아니라, 성능에도 악영향을 미칠 수 있습니다.

주요 문제

• Old Tuple 유지: Long Query가 열려 있는 동안, Autovacuum은 데이터를 삭제하지 못해 디스크 공간이 계속 증가합니다.
• 트랜잭션 ID 랩어라운드 문제(Transaction ID Wraparound): PostgreSQL의 트랜잭션 ID가 한계를 넘으면 Wraparound 현상이 발생하여 데이터베이스의 일관성에 문제가 생길 수 있습니다. 이를 방지하려면 Autovacuum이 제때 실행되어야 합니다.

4. 문제 해결 방안

1) 트랜잭션을 짧게 유지

트랜잭션의 범위를 최소화하여 Long Query가 트랜잭션을 오래 잡아두지 않도록 개선하는 것이 가장 중요합니다.

• 쿼리 범위 최소화: 트랜잭션 내에서 불필요한 작업을 줄이고, 필요한 경우 즉시 커밋(commit)하거나 롤백(rollback)합니다.
• 배치 처리: 대량의 데이터를 처리해야 한다면, 이를 작은 트랜잭션으로 나누어 처리합니다.
• 백그라운드 작업 처리: 긴 쿼리를 별도의 백그라운드 작업으로 분리하여 처리하는 것도 방법입니다.

2) Autovacuum 설정 최적화 Autovacuum 설정을 조정하면 더 자주 실행되도록 하거나 오래된 트랜잭션을 처리하는 빈도를 늘릴 수 있습니다.

• autovacuum_vacuum_threshold: 테이블의 튜플 중 일정 비율이 변경되면 Autovacuum이 실행되도록 설정합니다.
• autovacuum_freeze_max_age: 오래된 트랜잭션을 처리하는 주기를 조정해 XID 랩어라운드를 방지할 수 있습니다.
• vacuum_cost_delay: Autovacuum이 시스템 자원을 지나치게 많이 사용하지 않도록 지연시간을 설정하여 자원 부담을 줄입니다.

예시 설정 (postgresql.conf)

autovacuum_vacuum_threshold = 50         # 더 자주 Autovacuum 실행
autovacuum_freeze_max_age = 100000000    # 트랜잭션 처리 주기 조정
vacuum_cost_delay = 10ms                 # 자원 소비 줄이기 위한 지연 설정

3) 수동 VACUUM 실행

Autovacuum이 제대로 동작하지 않는 경우, Long Query가 종료된 후 수동으로 VACUUM을 실행하여 데이터 정리를 할 수 있습니다.

VACUUM ANALYZE;

또는 디스크 공간을 회수해야 한다면, VACUUM FULL 명령을 사용할 수 있지만, 테이블이 독점 잠금(Lock) 상태가 되므로 신중히 사용해야 합니다.

4) Long Query 모니터링 및 최적화

Long Query는 PostgreSQL의 pg_stat_activity 뷰를 통해 모니터링할 수 있으며, 이를 기반으로 성능을 최적화해야 합니다.

Long Query 모니터링 쿼리

SELECT pid, usename, query, state, age(clock_timestamp(), query_start) AS runtime
FROM pg_stat_activity
WHERE state != 'idle' AND query != '<IDLE>' AND age(clock_timestamp(), query_start) > interval '5 minutes';

• 쿼리 리팩토링: 복잡한 쿼리는 쪼개서 성능을 최적화할 수 있습니다.
• 인덱스 추가: 빈번하게 사용되는 조건에 인덱스를 추가하여 쿼리 성능을 높입니다.
• 실행 계획 분석: EXPLAIN을 사용해 쿼리의 실행 계획을 분석하고 최적화 포인트를 찾습니다.

5) Statement Timeout 설정

Long Query를 강제로 제한하려면 쿼리 타임아웃을 설정할 수 있습니다. 일정 시간 동안 실행되지 않으면 쿼리를 중단하도록 설정하여 Autovacuum 작업에 방해가 되지 않도록 합니다.

예시 설정 (postgresql.conf)
statement_timeout = '5min';   # 5분 이상 실행되는 쿼리는 자동으로 중단

6) 모니터링 및 도구 활용

Autovacuum이 제대로 동작하는지 확인하기 위해 pg_stat_user_tables를 활용해 마지막으로 Autovacuum이 실행된 시점을 확인할 수 있습니다.

-- Autovacuum 실행 시점 확인
SELECT relname, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE last_autovacuum IS NOT NULL OR last_vacuum IS NOT NULL;

또한 pgAdmin이나 Percona Monitoring and Management(PMM)와 같은 외부 모니터링 도구를 통해 PostgreSQL의 성능 및 Autovacuum 동작을 시각적으로 모니터링할 수 있습니다.

결론

PostgreSQL의 멀티 프로세스 구조는 뛰어난 안정성을 제공하지만, Long Query로 인한 Autovacuum 문제는 성능에 큰 영향을 미칠 수 있습니다. 이를 해결하기 위해서는 트랜잭션을 짧게 유지하고, Autovacuum 설정을 최적화하며, Long Query를 모니터링하고 최적화하는 것이 중요합니다. 또한 수동으로 VACUUM을 실행하거나 쿼리 타임아웃을 설정하는 등의 방안도 고려할 수 있습니다.

이를 통해 PostgreSQL의 안정성과 성능을 극대화하면서도 시스템 자원을 효율적으로 관리할 수 있습니다.

PostgreSQL은 전 세계에서 널리 사용되는 강력한 데이터베이스 관리 시스템입니다. 데이터베이스를 운영하면서 발생하는 각종 이벤트나 오류를 기록하는 로그는 시스템 관리와 성능 튜닝에 중요한 역할을 합니다. 이 글에서는 PostgreSQL 로그 시스템의 기초부터 설정 방법, 커스텀 로그 생성, 분석 도구 활용까지 다루어 초보자들도 쉽게 이해할 수 있도록 설명하겠습니다.

1. 로그란 무엇인가?

로그(log)는 데이터베이스에서 발생한 일련의 활동이나 이벤트를 기록한 파일입니다. 시스템에서 무슨 일이 일어났는지 알 수 있도록 돕는 일종의 기록부라고 할 수 있습니다. 데이터베이스에 연결하거나 쿼리를 실행하는 등의 모든 활동은 로그에 기록될 수 있습니다. 이 기록을 통해 문제가 발생했을 때 원인을 분석하거나, 시스템 성능을 모니터링할 수 있습니다.

로그는 특히 대규모 데이터베이스 환경에서 중요합니다. 데이터베이스가 느려지거나, 장애가 발생했을 때, 로그는 문제를 파악하고 해결하는 데 있어 가장 중요한 도구 중 하나입니다.

2. PostgreSQL의 로그 레벨 (Message Severity Levels)

PostgreSQL은 각 로그 메시지에 대해 ‘심각도(severity)’를 설정할 수 있습니다. 이는 메시지가 얼마나 중요한지를 나타내며, 아래와 같은 여러 레벨이 있습니다.

• DEBUG5 - DEBUG1: 문제 해결을 위한 상세 정보를 기록합니다. DEBUG5가 가장 많은 정보를 기록하며, DEBUG1은 비교적 적은 정보를 기록합니다. 주로 개발 환경에서 사용됩니다.
• INFO: 사용자가 암시적으로 요청한 정보를 기록합니다. 예를 들어, 테이블을 수정하거나 데이터를 삽입한 결과와 같은 내용이 기록됩니다.
• NOTICE: 시스템이 사용자에게 알려야 할 유용한 정보를 기록합니다. 테이블 이름이 너무 길어서 자동으로 줄어드는 경우 등의 메시지를 확인할 수 있습니다.
• WARNING: 잠재적으로 문제가 될 수 있는 상황을 경고합니다. 예를 들어, 트랜잭션 외부에서 COMMIT을 시도하는 경우 경고 메시지가 남을 수 있습니다.
• ERROR: 쿼리가 실패했을 때 발생하는 오류를 기록합니다. ERROR 레벨 메시지가 발생하면 해당 명령어는 중단됩니다.
• LOG: 데이터베이스의 일반적인 활동을 기록합니다. 사용자가 직접 명시적으로 요청한 작업뿐 아니라 시스템에서 발생하는 주요 이벤트도 포함됩니다.
• FATAL: 세션을 중단시키는 심각한 오류를 기록합니다. FATAL 레벨 메시지가 발생하면 해당 세션은 종료됩니다.
• PANIC: 시스템 자체가 정상적으로 작동할 수 없는 치명적인 오류를 기록합니다. PANIC 오류가 발생하면 모든 세션이 중단되고 데이터베이스가 재시작됩니다.

이 로그 레벨은 PostgreSQL 서버의 log_min_messages 설정을 통해 조절할 수 있습니다. 기본값은 WARNING이며, 이는 경고 이상의 심각도를 가진 메시지만 기록한다는 뜻입니다.

/etc/postgresql/15/main/postgresql.conf

log_min_messages = warning
LOG: connection received: host=[local]
ERROR: relation "albu" does not exist at character 7
FATAL: role "root" does not exist
PANIC: database system shutdown requested

위와 같은 메시지는 PostgreSQL이 클라이언트 연결을 받아들이고, 쿼리 오류가 발생했으며, 심각한 오류로 인해 데이터베이스 시스템이 중단되었음을 보여줍니다.

3. 로그 메시지의 상세도 조정 (Reducing Log Verbosity)

PostgreSQL의 로그 설정을 통해 메시지의 상세도를 조절할 수 있습니다. log_error_verbosity 설정은 로그에 포함될 정보의 양을 결정합니다. 이 설정에는 세 가지 옵션이 있습니다

• terse: 간결한 로그 메시지만 남깁니다. 오류 메시지만 기록하고, 추가적인 세부 정보는 생략됩니다.
• default: 기본 설정으로, 메시지와 함께 추가적인 정보를 기록합니다. DETAIL, HINT, QUERY, CONTEXT 같은 유용한 정보가 포함됩니다.
• verbose: 가장 상세한 로그 메시지를 기록합니다. 메시지와 함께 소스 코드 파일 이름, 함수 이름, 줄 번호 등의 추가 정보도 기록됩니다.

예를 들어, DROP TABLE 쿼리가 실패한 경우 기본 로그 메시지는 다음과 같습니다.

LOG: statement: DROP table album;
ERROR: cannot drop table album because other objects depend on it
DETAIL: constraint fk_trackalbumid on table track depends on table album
HINT: Use DROP ... CASCADE to drop the dependent objects too.
STATEMENT: DROP table album;

만약 log_error_verbosity를 terse로 설정하면 DETAIL과 HINT 정보는 생략되고, 더 간결한 메시지만 남게 됩니다.

4. 로그 파일 위치와 관리

PostgreSQL 로그는 서버에서 지정된 디렉토리에 저장됩니다. 기본적으로 PostgreSQL은 표준 출력(stderr)이나 파일로 로그를 남기며, 로그 파일의 경로는 PostgreSQL 설정 파일(postgresql.conf)에서 log_directory와 log_filename을 통해 지정할 수 있습니다.

log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%a.log'

위의 설정은 PostgreSQL 로그가 /var/log/postgresql 디렉토리에 저장되고, 요일별로 다른 로그 파일이 생성됨을 의미합니다.

5. 연결 및 해제 로그 관리 (Logging Connections and Disconnections)

로그 설정에서 log_connections와 log_disconnections 옵션을 통해 클라이언트 연결 및 해제 로그를 관리할 수 있습니다. 기본적으로 이 옵션들은 비활성화되어 있으며, 이를 활성화하면 사용자가 언제 데이터베이스에 연결하고 해제했는지 기록할 수 있습니다.

log_connections = on
log_disconnections = on
LOG: connection received: host=[local]
LOG: disconnection: session time: 0:00:02.824 user=postgres database=chinook host=[local]

이 정보는 보안 관점에서 유용합니다. 누가, 언제, 어떤 IP에서 데이터베이스에 접속했는지 추적할 수 있기 때문입니다.

6. 커스텀 로그 메시지 생성 (Creating Custom Log Messages)

PostgreSQL에서 PL/pgSQL을 사용하면 쿼리나 트리거 함수에서 직접 로그 메시지를 생성할 수 있습니다. RAISE 구문을 사용하여 로그 메시지를 남길 수 있으며, 이는 개발 중 디버깅이나 운영 중 중요한 정보를 남기고자 할 때 유용합니다.

CREATE OR REPLACE FUNCTION custom_logs_func()
RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
    RAISE LOG 'This is an informational message';
    RAISE WARNING 'Something unexpected happened';
    RAISE EXCEPTION 'An unexpected error occurred';
END;
$$;

CREATE TRIGGER album_update
BEFORE UPDATE ON album
FOR EACH ROW EXECUTE FUNCTION custom_logs_func();

LOG: This is an informational message
WARNING: Something unexpected happened
ERROR: An unexpected error occurred

7. PgBadger를 이용한 로그 분석 (Analyzing Log Files with PgBadger)

PgBadger는 PostgreSQL 로그를 분석하고, 다양한 성능 지표를 시각화할 수 있는 도구입니다. PgBadger는 로그 파일을 읽어들여 데이터베이스 활동과 성능, 쿼리 효율성을 분석한 후 HTML 형식의 보고서를 생성합니다.

PgBadger는 특히 느린 쿼리를 찾아내거나, 데이터베이스 연결 빈도, 오류 메시지 발생 빈도 등을 파악할 때 매우 유용합니다.

pgbadger --prefix 'time=%m pid=%p user=%u db=%d app=%a ' /var/log/postgresql/postgresql-Wed.log -o report.html

PgBadger는 CSV나 stderr 포맷 로그와 호환되며, JSON 형식의 로그는 지원하지 않으므로 주의해야 합니다.

8. 로그 최적화 팁

• 불필요한 로그 줄이기: 너무 많은 로그는 분석을 복잡하게 만듭니다. log_min_duration_statement를 설정하여 일정 시간 이상 걸린 쿼리만 로그로 남기도록 조정할 수 있습니다.
• 로그 보관 설정: log_rotation_size와 log_rotation_age 설정을 통해 일정 용량이나 시간에 따라 로그 파일을 회전(rotating)시키면 로그 파일 크기를 적절히 유지할 수 있습니다.
• 로그 백업: 로그는 문제 해결을 위한 중요한 자료입니다. 일정 기간 동안 로그 파일을 보관하고 백업해두는 것이 좋습니다.

마무리

PostgreSQL의 로그는 데이터베이스를 모니터링하고, 문제를 진단하며, 성능을 최적화하는 데 매우 중요한 역할을 합니다. 이 글에서 설명한 로그 설정과 활용법을 잘 이해하고 적용하면 더 안정적이고 효율적인 데이터베이스 운영이 가능합니다. 초보자라도 천천히 따라해보며 자신의 데이터베이스 로그를 잘 관리해보세요. 문제가 발생했을 때 빠르게 원인을 찾을 수 있을 것입니다.

1.1 pg_stat_io 시스템 뷰

설명: 새로운 시스템 뷰로, 테이블, 인덱스, 기타 객체의 IO 통계를 제공합니다. 이를 통해 데이터베이스 관리자들은 IO 활동을 모니터링하고 성능 병목 현상을 파악할 수 있습니다.

SELECT * FROM pg_stat_io WHERE relname = 'my_table';

1.2 pg_checkpointer 백그라운드 작업 프로세스

설명: PostgreSQL 16에서 새롭게 도입된 백그라운드 프로세스로, 체크포인트 작업을 관리하여 데이터베이스 성능에 미치는 영향을 줄일 수 있습니다.

SELECT pg_stat_bgwriter();

1.3 SIMD 최적화 설명: 문자열 처리나 수치 연산 등 다양한 연산에서 SIMD 명령어를 활용하여 내부적으로 성능을 크게 향상시켰습니다.

1. 논리적 복제(Logical Replication)

PostgreSQL 16의 논리적 복제 기능은 매우 유연하게 개선되었습니다. 특히, 스탠바이 노드(복제본 노드)에서 논리적 복제를 실행할 수 있게 되면서, 다음과 같은 장점이 생겼습니다.

• 복제 인프라 유연성: 이전에는 논리적 복제를 위해 반드시 마스터 노드(원본 노드)에서만 설정할 수 있었는데, 이제는 스탠바이 노드에서도 설정이 가능해져 복제 인프라를 더 유연하게 구축할 수 있습니다. 이를 통해 고가용성(HA) 환경에서의 활용도가 높아졌습니다.

• 필터링 기능: 논리적 복제는 특정 테이블, 스키마, 또는 특정 조건에 맞는 데이터만 복제할 수 있도록 필터링할 수 있게 되었습니다. 예를 들어, 특정 스키마에 속한 테이블만 복제하거나, 특정 조건을 만족하는 레코드만 복제할 수 있습니다. 이를 통해 네트워크 트래픽과 스토리지 자원을 절약하면서도 필요한 데이터만을 선택적으로 복제할 수 있습니다.

-- 논리적 복제에서 필터링 모드를 설정할 수 있는 파라미터로, 
-- 특정 테이블이나 스키마만 복제할 수 있도록 도와
SET logical_replication_mode = 'per-table';

-- 기존의 publication에 테이블, 스키마 또는 필터링 조건을 추가하거나 변경
ALTER PUBLICATION my_publication ADD TABLE my_table;

• 비슈퍼유저 subscription 생성: 이전 버전에서는 논리적 복제를 설정할 때 슈퍼유저 권한이 필요했지만, 이제는 비슈퍼유저도 subscription을 생성할 수 있게 되었습니다. 이는 더 세밀한 권한 관리와 함께 보안을 강화하는 데 큰 도움이 됩니다.

ALTER SUBSCRIPTION my_subscription SET (enabled = false);

2. 스토리지(Storage) 개선

PostgreSQL 16은 스토리지 관리의 효율성을 크게 향상시키는 여러 개선을 도입했습니

• 병렬 VACUUM: VACUUM 작업이 병렬로 실행될 수 있어 대규모 데이터베이스의 유지보수 시간이 크게 단축됩니다. 이는 테이블이 커질수록 더욱 중요한데, 병렬로 VACUUM을 실행하면 여러 워커 프로세스가 동시에 작업을 수행하여, 데이터 청소 작업을 더 빠르고 효율적으로 진행할 수 있습니다.

SET parallel_vacuum_workers = 4;

• 페이지 단위 프리징: PostgreSQL의 프리징(freezing) 메커니즘은 오래된 트랜잭션 ID가 데이터베이스 내에서 오버플로되지 않도록 하는 중요한 작업입니다. 이제는 VACUUM 과정에서 전체 테이블을 프리징하는 대신, 각 페이지를 독립적으로 프리징할 수 있어, 성능이 크게 개선되고 작업의 신속성이 보장됩니다.

• IO 통계: 새로운 IO 통계 기능은 데이터베이스의 성능 병목 현상을 더 잘 파악할 수 있도록 도와줍니다. PostgreSQL은 이제 데이터베이스의 각 테이블과 인덱스에 대한 IO(입출력) 통계를 수집하여 관리자가 성능을 최적화할 수 있는 더 많은 정보를 제공합니다. 이를 통해 특정 쿼리 또는 작업이 데이터베이스 IO를 어떻게 사용하는지에 대한 깊은 인사이트를 얻을 수 있습니다.

SELECT * FROM pg_stat_io WHERE relname = 'my_table';

3. 새로운 SQL 기능 PostgreSQL 16은 개발자들이 더 많은 기능을 활용할 수 있도록 여러 가지 새로운 SQL 기능을 도입했습니다:

• JSON 기능 개선: JSON 데이터를 다루는 SQL 함수가 더 강력해졌습니다. 예를 들어, JSON 데이터를 필터링, 병합 또는 검색하는 데 사용되는 함수들이 개선되었고, 더 복잡한 JSON 데이터를 처리하는 것이 훨씬 쉬워졌습니다. 이는 특히 NoSQL과 같은 유연한 데이터 모델을 필요로 하는 애플리케이션에서 매우 유용합니다.

  SELECT jsonb_insert('{"a": 1, "b": 2}', '{b}', '"c": 3', false);

• 병렬 해시 조인: 해시 조인(Hash Join)은 대용량 데이터셋 간의 조인을 빠르게 처리하기 위해 자주 사용됩니다. PostgreSQL 16에서는 이 해시 조인 작업을 병렬로 수행할 수 있어, 큰 데이터셋을 다룰 때의 쿼리 성능이 더욱 향상되었습니다. 이 기능은 특히 데이터 웨어하우징 작업이나 분석 쿼리에서 유용합니다.

SET hash_index_build_concurrent = true;

• 새로운 텍스트 정렬 옵션: PostgreSQL 16은 다양한 텍스트 정렬(collation) 옵션을 추가하여, 다국어 환경에서 텍스트 데이터를 더 정확하게 정렬할 수 있도록 지원합니다. 예를 들어, 지역별 언어 규칙에 따라 정렬 순서를 맞출 수 있습니다.

4. 보안 및 권한 관리

PostgreSQL 16에서의 보안과 권한 관리 개선 사항은 데이터베이스 관리의 복잡성을 줄이면서도 보안을 강화했습니다

• 슈퍼유저 권한 불필요: PostgreSQL 16에서는 슈퍼유저 권한이 없어도 특정 관리 작업을 수행할 수 있습니다. 이는 데이터베이스의 보안을 유지하면서도 운영 효율성을 높이는 데 기여합니다. 예를 들어, 특정 기능이나 작업을 수행하는 데 더 이상 슈퍼유저 권한이 필요하지 않게 됨으로써, 조직 내에서 최소 권한 원칙을 더욱 철저히 적용할 수 있습니다.
• Kerberos 자격 증명 위임: PostgreSQL 16은 Kerberos 인증을 사용하는 경우, 자격 증명을 다른 시스템에 위임할 수 있도록 지원합니다. 이는 복잡한 인증 체계를 가진 대기업 환경에서 매우 유용하며, 보안을 강화하면서도 인증 프로세스를 간소화할 수 있습니다.

5. 기타 성능 개선

PostgreSQL 16은 다양한 성능 최적화 기능을 추가했습니다

• 메모리 관리 개선: 메모리 사용을 더 효율적으로 최적화하여, 특히 대규모 데이터베이스에서의 성능이 향상되었습니다. 더 나은 캐시 관리와 최적화된 메모리 할당 메커니즘을 통해, 쿼리 처리 속도가 빨라지고 메모리 낭비가 줄어듭니다.
• SIMD 활용: 최신 CPU의 SIMD(Single Instruction, Multiple Data) 명령어를 활용하여 데이터 처리 성능을 극대화했습니다. SIMD는 하나의 명령어로 여러 데이터 요소를 동시에 처리할 수 있어, 특히 수학적 연산이나 대규모 데이터 처리에서 성능을 크게 향상시킵니다.
• 해시 인덱스 빌드 성능 향상: 해시 인덱스는 데이터베이스에서 빠른 검색을 가능하게 하는 중요한 도구인데, PostgreSQL 16에서는 해시 인덱스를 더 빠르게 생성할 수 있도록 최적화되었습니다. 이는 인덱스 생성 시간이 줄어들어, 더 빠른 쿼리 응답 시간을 제공합니다.

이 외에도 PostgreSQL 16은 많은 개선사항이 있지만, 위의 기능들은 가장 주목할 만한 것들입니다. 이 모든 개선 사항은 PostgreSQL 16이 더욱 강력하고 유연한 데이터베이스 관리 시스템으로 발전하는 데 기여합니다.

서론: PostgreSQL은 강력한 데이터베이스 시스템으로, 적절한 로그 설정은 성능 모니터링과 문제 해결에 필수적입니다. 이 블로그에서는 PostgreSQL 로그 파일을 커스터마이징하는 다양한 방법에 대해 알아보겠습니다. 특히 로그 포맷팅, JSON 형식의 구조화된 로깅, 쿼리 로깅 방법, 메시지 심각도 수준, 로그 상세도 줄이기, 연결 및 연결 해제 로깅, 사용자 정의 로그 메시지 생성, 그리고 PgBadger를 활용한 로그 파일 분석 등을 다룰 것입니다.

1. 로그 파일 커스터마이징(Customizing the log files)

PostgreSQL에서는 로그 파일 이름을 log_filename 옵션을 통해 커스터마이징할 수 있습니다.

/etc/postgresql/15/main/postgresql.conf

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

이 설정은 파일 생성 시의 날짜와 시간이 포함된 로그 파일 이름을 생성합니다. %로 시작하는 escape 문자는 Open Group의 strftime 사양에 따라 다양한 포맷으로 대체됩니다.

/etc/postgresql/15/main/postgresql.conf

log_filename = 'postgresql-%a.log'

이 설정은 요일의 약어가 포함된 파일을 생성합니다(예: postgresql-Thu.log). 이때 로그 파일 이름이 .log로 끝나지만, JSON 또는 CSV 형식이 활성화된 경우 파일 이름은 각각 .json 또는 .csv로 끝납니다(예: postgresql-Thu.json).

로그 파일의 생성 권한을 제어하려면 log_file_mode 옵션을 변경할 수 있습니다

/etc/postgresql/15/main/postgresql.conf

log_file_mode = 0600 # 기본값

0600 권한은 민감한 파일에 자주 사용되며, 파일의 소유자만이 이 파일에 접근, 조회, 수정할 수 있습니다. 이 설정은 PostgreSQL 로그에 민감한 정보가 자주 포함되므로 보안 조치로 유용합니다.

로그 파일 rotation 설정

로그 파일에 데이터를 기록하면 적절한 로그 회전 정책이 없을 경우 디스크 공간이 빠르게 소모될 수 있습니다. 특히 쿼리 로깅이 활성화된 PostgreSQL에서는 더욱 그렇습니다.

PostgreSQL에서는 로그 파일 회전을 제어할 수 있는 몇 가지 설정을 제공

• log_rotation_age: 로그 파일이 rotation되기 전 최대 파일 age를 지정합니다. 기본값은 24시간(1일)입니다. 분(m), 일(d), 주(w)와 같은 단위를 사용할 수 있으며, 단위를 제공하지 않으면 분 단위가 기본값으로 사용됩니다. 시간 기반 로그 rotation을 비활성화하려면 0으로 설정할 수 있습니다.

• log_rotation_size: 새로운 로그 파일로 회전되기 전의 최대 파일 크기를 결정합니다. 기본값은 10메가바이트(10MB)입니다. 크기 기반 로그 rotation을 비활성화하려면 0으로 설정할 수 있습니다.

• log_truncate_on_rotation: 이 옵션은 시간 기반 rotation이 적용될 때만 유효합니다. 기존에 동일한 이름의 로그 파일이 있을 경우 덮어쓰기를 할지 여부를 결정합니다. 예를 들어, postgresql-%a.log와 같은 파일명을 사용할 때, 이 옵션을 활성화하고 log_rotation_age를 7일로 설정하면 매주 같은 파일명을 사용해 이전 주의 로그 파일을 덮어쓰게 됩니다.

PostgreSQL 로그 파일의 이름과 생성 권한을 사용자 정의할 수 있으며, 로그 파일 rotation을 통해 디스크 공간을 관리할 수 있습니다. 이 설정들은 보안 및 로그 관리에 있어 매우 유용하며, 필요에 따라 logrotate와 같은 추가 유틸리티를 활용 가능

2. 로그 포맷팅 (Log Formatting)

로그 파일의 포맷을 설정하여 분석과 모니터링을 더욱 효과적으로 할 수 있습니다. PostgreSQL은 다양한 로그 출력 포맷을 지원하며, 로그 라인의 구성 요소를 선택적으로 포함할 수 있습니다. 이 부분에서는 log_line_prefix 설정을 통해 로그에 추가할 수 있는 다양한 정보와 그 의미에 대해 설명할 것입니다.

PostgreSQL의 로그 형식은 기본적으로 log_destination 설정에 의해 제어되며, 기본값은 stderr로 설정되어 있습니다. 또한 로그를 JSON 또는 CSV 형식으로 출력하려면 각각 jsonlog 및 csvlog 옵션을 사용할 수 있습니다.

/etc/postgresql/15/main/postgresql.conf

log_destination = 'stderr'

stderr가 활성화되면, log_line_prefix 옵션을 통해 출력 형식을 수정할 수 있습니다. 이 옵션은 각 로그 기록의 시작 부분에 포함될 정보를 결정하는 printf 스타일의 문자열입니다.

SHOW log_line_prefix;

log_line_prefix
------------------
 %m [%p] %q%u@%d
(1 row)

이 값은 stderr로 전송되는 각 로그 기록에 다음과 같은 세부 정보를 포함하도록 설정

• %m: 밀리초 단위의 이벤트 발생 시간.
• %p: 로그를 생성한 특정 PostgreSQL 인스턴스의 프로세스 ID.
• %q: 이 토큰은 출력이 없지만 백그라운드 프로세스가 이 지점 이후의 모든 것을 무시하도록 합니다. 다른 토큰(%u@%d)은 세션 프로세스에서만 사용할 수 있습니다.
• %u: 이벤트를 트리거한 연결된 사용자.
• %d: 사용자가 연결된 데이터베이스.

위에서 설명한 형식을 사용하여 생성된 로그 항목은 다음과 같습니다.

2023-07-30 08:31:50.628 UTC [2176] postgres@chinook LOG:  statement: select albumid, title from album where artistid = 2;

postgres@chinook 이후의 모든 내용은 log_line_prefix에 의해 제어되지 않으며, 로그 수준과 이 경우 SELECT 문과 같은 로그 메시지로 구성됩니다

로그 포맷 커스터마이징

stderr 로그 형식을 수정하는 유일한 방법은 log_line_prefix 설정을 통해서만 가능합니다. 사용할 수 있는 이스케이프 시퀀스의 전체 표를 참조하세요. 다음 변수를 포함하는 것을 권장합니다.

/etc/postgresql/15/main/postgresql.conf

log_line_prefix = 'time=%m pid=%p error=%e sess_id=%c %qtag=%i usr=%u db=%d app=%a '

이 구성으로 PostgreSQL 서버를 다시 시작하면 다음과 같은 형식의 로그를 확인할 수 있습니다

time=2023-07-30 22:02:27.929 UTC pid=17678 error=00000 sess_id=64c6ddf3.450e LOG:  database system is ready to accept connections
time=2023-07-30 22:03:33.613 UTC pid=17769 error=00000 sess_id=64c6de20.4569 tag=idle usr=postgres db=chinook app=psql LOG:  statement: UPDATE album SET title = 'Audioslave' WHERE albumid = 10;

첫 번째 기록은 비세션 프로세스(백그라운드 프로세스)에 의해 생성되었습니다. 이러한 프로세스는 유지 관리 작업, 백그라운드 작업 및 데이터베이스 운영을 지원하기 위한 기타 내부 기능을 수행합니다.

%q 이스케이프의 포함은 이후의 모든 항목을 제외합니다. 이러한 시퀀스는 비세션 프로세스에서 무효하기 때문입니다. %q가 제외되면 태그, 사용자, 데이터베이스, 앱 키가 로그에 표시되지만 비어 있게 됩니다.

반면, 세션 프로세스(사용자 백엔드 프로세스)는 특정 사용자와 데이터베이스에 직접 연결되어 실제 쿼리와 명령을 처리합니다. 이러한 컨텍스트에서는 모든 이스케이프 시퀀스가 유효하므로 %q 이스케이프는 효과가 없습니다.

3. JSON 형식의 구조화된 로깅 (Structured Logging in JSON)

로그 데이터를 구조화된 JSON 형식으로 기록하면, 로그 분석 도구와의 통합이 쉬워지고, 로그 데이터의 검색과 분석이 용이해집니다. 이 섹션에서는 PostgreSQL에서 JSON 형식의 로그를 생성하는 방법과 그 장점에 대해 다룰 것입니다.

PostgreSQL은 2008년 2월에 출시된 v8.3 릴리스부터 CSV 형식의 로깅을 지원했지만 JSON은 최근 v15 릴리스에서 지원되었습니다. 이제 다음과 같이 log_destination 설정에 jsonlog를 추가하여 PostgreSQL 로그를 구조화된 JSON 형식으로 작성할 수 있습니다.

/etc/postgresql/15/main/postgresql.conf
log_destination = 'stderr,jsonlog'

서버를 다시 시작하고 로그 디렉토리를 확인하면 동일한 이름을 가진 두 개의 새로운 로그 파일(.log 및 .json)이 생성된 것을 확인할 수 있습니다. 이 파일들은 동일한 로그를 다른 형식으로 포함하고 있습니다. .log 파일은 이전과 같이 stderr 형식을 따르며, .json 파일은 예측 가능하게 JSON 로그를 포함합니다.

/var/lib/postgresql/15/main/log/postgresql-Sun.log

time=2023-07-30 22:48:01.817 UTC pid=18254 error=00000 sess_id=64c6e836.474e tag=idle usr=postgres db=chinook app=psql LOG:  statement: SHOW data_directory;
time=2023-07-30 22:49:21.808 UTC pid=18254 error=00000 sess_id=64c6e836.474e tag=idle usr=postgres db=chinook app=psql LOG:  statement: TABLE albu limit 10;
time=2023-07-30 22:49:21.808 UTC pid=18254 error=42P01 sess_id=64c6e836.474e tag=SELECT usr=postgres db=chinook app=psql ERROR:  relation "albu" does not exist at character 7
time=2023-07-30 22:49:21.808 UTC pid=18254 error=42P01 sess_id=64c6e836.474e tag=SELECT usr=postgres db=chinook app=psql STATEMENT:  TABLE albu limit 10;

이 로그는 앞서 설명한 log_line_prefix 설정에 따라 포맷된 것입니다. 반면, JSON 로그는 null 값을 제외한 모든 사용 가능한 필드를 포함합니다.

/var/lib/postgresql/15/main/log/postgresql-Sun.json

{"timestamp":"2023-07-30 22:48:01.817 UTC","user":"postgres","dbname":"chinook","pid":18254,"remote_host":"[local]","session_id":"64c6e836.474e","line_num":1,"ps":"idle","session_start":"2023-07-30 22:46:14 UTC","vxid":"4/3","txid":0,"error_severity":"LOG","message":"statement: SHOW data_directory;","application_name":"psql","backend_type":"client backend","query_id":0}
{"timestamp":"2023-07-30 22:49:21.808 UTC","user":"postgres","dbname":"chinook","pid":18254,"remote_host":"[local]","session_id":"64c6e836.474e","line_num":2,"ps":"idle","session_start":"2023-07-30 22:46:14 UTC","vxid":"4/4","txid":0,"error_severity":"LOG","message":"statement: TABLE albu limit 10;","application_name":"psql","backend_type":"client backend","query_id":0}
{"timestamp":"2023-07-30 22:49:21.808 UTC","user":"postgres","dbname":"chinook","pid":18254,"remote_host":"[local]","session_id":"64c6e836.474e","line_num":3,"ps":"SELECT","session_start":"2023-07-30 22:46:14 UTC","vxid":"4/4","txid":0,"error_severity":"ERROR","state_code":"42P01","message":"relation \"albu\" does not exist","statement":"TABLE albu limit 10;","cursor_position":7,"application_name":"psql","backend_type":"client backend","query_id":0}

4. PostgreSQL 쿼리 로깅 방법 (How to Log PostgreSQL Queries)

데이터베이스 쿼리를 로그에 기록하는 것은 성능 문제를 파악하고 최적화하는 데 중요한 역할을 합니다. 이 섹션에서는 쿼리 로그를 활성화하고 구성하는 방법, 그리고 특정 쿼리만 로그에 기록하도록 필터링하는 방법에 대해 설명할 것입니다.

log_statement 설정

log_statement 설정은 서버 로그 파일에 기록될 SQL 쿼리를 제어합니다. 유효한 옵션은 다음과 같습니다.

• none (기본값): SQL 쿼리를 기록하지 않습니다.
• ddl: 데이터 정의어(DDL) 문장만 기록합니다. 예를 들어 CREATE, ALTER, DROP 문장이 포함됩니다.
• mod: DDL 문장 외에 데이터 수정어(DML) 문장도 기록합니다. INSERT, UPDATE, DELETE 문장이 포함됩니다.
• all: 실행 단계에서 파싱 오류로 실패한 쿼리를 제외한 모든 SQL 쿼리를 기록합니다.

/etc/postgresql/15/main/postgresql.conf
log_statement = mod

주의: all 설정을 사용하면 생산 환경에서는 많은 양의 데이터를 생성하게 되어 성능 저하를 초래할 수 있기에 모든 쿼리를 기록하는 이유가 감사 목적이라면, pgAudit를 사용하는 것이 더 나은 제어를 제공합니다.

쿼리 실행 시간 기록

쿼리 로그의 주요 용도 중 하나는 성능 최적화를 위해 느리게 실행되는 쿼리를 식별하는 것입니다. 그러나 log_statement 옵션은 쿼리 실행 시간을 추적하지 않습니다. 이를 위해 log_duration 설정을 사용해야 합니다. 이 설정은 불리언 값으로, off (0) 또는 on (1)으로 설정할 수 있습니다.

/etc/postgresql/15/main/postgresql.conf
log_duration = 1

서버를 재시작하고 로그를 다시 확인하면 밀리초 단위의 실행 시간 값이 포함된 로그 항목을 확인할 수 있습니다.

LOG:  statement: UPDATE album SET title = 'Audioslave' WHERE albumid = 10;
LOG:  duration: 18.498 ms

log_duration이 켜져 있으면, 서버에서 실행된 모든 명령에 대해 실행 시간 로그가 생성됩니다(심지어 간단한 SELECT 1도 포함). 이로 인해 엄청난 양의 로그가 생성될 수 있으며, 이는 성능 저하와 디스크 공간 소모를 초래할 수 있습니다.

또 다른 문제는 log_duration을 사용하면 쿼리 실행 시간이 어떤 쿼리에서 발생했는지 알 수 없다는 점입니다. 이를 테스트하기 위해 log_statement을 ddl로 설정하고 log_duration을 계속 켜 둡니다. 서버를 재시작하고 다음 쿼리를 psql에서 실행해보겠습니다.

UPDATE album SET title = 'Audioslave' WHERE albumid = 10;
쿼리가 성공적으로 완료되면 다음과 같은 로그를 확인할 수 있습니다
LOG:  duration: 13.492 ms

중요한 점은 이 로그 항목이 생성된 쿼리 자체는 기록되지 않으므로 사실상 유용성이 떨어집니다. log_duration을 반드시 사용해야 한다면, log_statement도 all로 설정하여 각 실행 시간 로그가 생성된 쿼리와 연관될 수 있도록 해야 합니다.

대안: log_min_duration_statement

log_min_duration_statement는 정수 값을 받아, 지정된 시간(밀리초)을 초과하는 쿼리만 로그에 기록합니다. 0으로 설정하면 모든 완료된 쿼리의 지속 시간이 기록됩니다.

/etc/postgresql/15/main/postgresql.conf
log_duration = 0
log_min_duration_statement = 0

log_min_duration_statement 설정은 불리언 값이 아니며, 밀리초 단위의 정수 값을 허용합니다. 지정된 값보다 실행 시간이 긴 쿼리만 로그로 기록됩니다. 위 예제에서는 0으로 설정하여 모든 완료된 쿼리의 실행 시간이 기록됩니다.

위의 업데이트된 설정을 적용하고 PostgreSQL 서버를 재시작한 후, log_statement을 ddl 모드로 유지합니다. 이후 UPDATE 쿼리를 반복해 실행해 보세요. 다음과 같은 실행 시간 로그를 확인할 수 있습니다

LOG:  duration: 13.504 ms  statement: UPDATE album SET title = 'Audioslave' WHERE albumid = 10;

이 설정은 log_duration 출력보다 개선된 점이 있으며, 쿼리와 실행 시간 로그가 모두 표시됩니다.

여전히 한 가지 약점이 남아 있습니다.

쿼리가 완료되기 전에 로그 파일에서 쿼리를 볼 수 없다는 점입니다. 서버가 쿼리 완료 전에 크래시가 발생하면, 크래시 전에 실행 중이었던 쿼리를 알 수 없습니다.

그러나 쿼리가 자체적으로 로그를 생성하는 경우에는 로그 동작이 변경됩니다. 이를 관찰하려면 log_statement을 all로 변경한 후 서버를 재시작합니다. psql에서 SELECT pg_sleep(5)를 실행하고 로그를 관찰하겠습니다.

LOG:  statement: SELECT pg_sleep(5);
LOG:  duration: 5005.975 ms

이 설정을 사용하면, 다섯 초가 지나기 전에 문제가 발생하더라도 어떤 쿼리가 사건에 영향을 미쳤을 가능성이 있는지 알 수 있습니다.

PostgreSQL 문서에서는 log_line_prefix에 프로세스 ID와 세션 ID를 포함시킬 것을 권장합니다. 이렇게 하면 로그를 읽고 분석할 때 두 항목을 쉽게 연관시킬 수 있습니다.

느리게 실행되는 쿼리 기록

모든 쿼리의 지속 시간을 기록하면 로그 파일이 불필요한 항목으로 가득 차게 됩니다. 따라서 느리게 실행되는 쿼리만 기록하는 것이 더 유용합니다. 이는 PostgreSQL 서버의 부담을 줄이는 데도 도움이 됩니다.

느리게 실행되는 쿼리를 정의하는 기준은 애플리케이션 유형과 특정 워크로드에 따라 달라집니다. 일반적으로 250ms 이상의 쿼리를 기록하는 것이 좋습니다

/etc/postgresql/15/main/postgresql.conf
log_min_duration_statement = 250

SELECT pg_sleep(1);

LOG:  statement: SELECT pg_sleep(1);
LOG:  duration: 1013.675 ms

결론

PostgreSQL 로그 파일을 효과적으로 관리하고 분석하는 것은 데이터베이스의 성능을 최적화하고 문제를 신속하게 해결하는 데 필수적입니다. 이 블로그에서 소개한 다양한 로그 커스터마이징 방법을 활용하여, 여러분의 PostgreSQL 로그 관리를 한층 더 향상시킬 수 있을 것입니다.

Reference

https://betterstack.com/community/guides/logging/how-to-start-logging-with-postgresql/#customizing-the-log-files

PostgreSQL는 오랜 기간 동안 신뢰성과 성능으로 많은 개발자와 기업들 사이에서 널리 사용되고 있는 데이터베이스 관리 시스템(DBMS)입니다. 특히 트랜잭션 처리에서 강력한 기능을 제공하는데, 이는 여러 사용자가 동시에 데이터베이스에 접근하고 수정하는 상황에서 매우 중요합니다.

하지만 이와 같은 트랜잭션 처리로 인해 시간이 지남에 따라 데이터베이스에 불필요한 데이터가 쌓이고, 성능 저하가 발생할 수 있습니다. 이를 해결하기 위한 주요 도구 중 하나가 바로 VACUUM입니다. 이 블로그에서는 VACUUM의 기본 개념, 왜 필요한지, 그리고 이를 최적화하는 방법에 대해 깊이 있게 다뤄보겠습니다.

다음 블로그엔 pg_squeeze이용하여 Vacuum 최적화 진행할 것입니다.

1. VACUUM이란 무엇인가?

PostgreSQL에서 VACUUM은 삭제되거나 업데이트된 행들(레코드)을 정리하는 작업을 의미합니다. 트랜잭션 처리 중 발생하는 죽은 튜플(dead tuples)을 제거하여 데이터베이스 파일의 공간을 회수하고 성능을 유지하는 데 필수적인 역할을 합니다.

VACUUM은 일반적으로 데이터베이스의 테이블에서 삭제되거나 무효화된 레코드들을 제거하고, 그 공간을 다시 사용할 수 있도록 관리합니다.

트랜잭션과 VACUUM의 관계

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식을 사용하여 트랜잭션 간의 동시성을 보장합니다. 트랜잭션이 진행되면서 새로운 행이 삽입되거나 기존 행이 삭제, 수정되면 실제로 데이터를 즉시 삭제하거나 수정하지 않고, 새로운 버전을 생성합니다.

이러한 방식 덕분에 여러 트랜잭션이 동시에 동일한 데이터를 접근하더라도 충돌 없이 작업을 수행할 수 있습니다.

예를 들어, 두 개의 트랜잭션이 동시에 동일한 테이블에 접근하고 각각 데이터를 처리한다고 가정해보겠습니다.

위의 예시에서, 중요하게 이해해야 할 점은 두 세션에서 서로 다른 데이터를 볼 수 있다는 것입니다. 왼쪽 세션은 실제로 3개의 행을 보겠지만, 동시에 실행되는 오른쪽 세션에서는 해당 트랜잭션이 아직 커밋되지 않았기 때문에 어떤 행도 보지 못합니다.

ROLLBACK의 대상이 된 3개의 행을 어떻게 제거할 수 있을까요? MSSQL과 Oracle과는 달리, PostgreSQL에서는 ROLLBACK 시 디스크에서 이러한 행을 제거하지 않으므로 PostgreSQL에서 ROLLBACK이 매우 빠르고, MSSQL에서 큰 트랜잭션의 경우 매우 느린 이유입니다.

이때, Vacuum의 역할은 더 이상 어떤 활성 트랜잭션에서도 볼 수 없는 행들을 제거하는 것입니다.

주요 포인트

• 긴 트랜잭션은 VACUUM의 동작을 지연시킴.
• VACUUM이 실행되더라도 아직 볼 수 있는 데이터는 삭제되지 않음.
• VACUUM의 효과를 보기 위해서는 트랜잭션을 신속하게 종료하는 것이 중요함.

2. 왜 VACUUM은 디스크 공간을 바로 반환하지 않는가?

많은 PostgreSQL 사용자들이 종종 질문하는 내용 중 하나는, 왜 VACUUM을 실행했는데도 디스크 공간이 줄어들지 않나요? 입니다. 이는 PostgreSQL의 내부 구조와 VACUUM 작업이 어떻게 수행되는지에 대한 이해가 필요합니다.

VACUUM은 삭제된 행을 디스크에서 물리적으로 제거하지 않고, 단순히 "죽은 튜플"로 마킹하고, 이후 삽입되는 데이터가 이 공간을 재사용하도록 설계되어 있습니다. 이를 통해 디스크에서 데이터를 물리적으로 이동시키는 작업을 줄이고, 성능을 유지할 수 있습니다.

간단한 테이블을 생성하고 VACUUM이 어떻게 동작하는지 살펴보는 예시를 통해서 Vacuum이 공간을 관리를 어떻게 하는지 보겠습니다.

/*
이 테이블에 데이터를 삽입하고 각 행이 어느 블록에 저장되었는지 확인
각 행은 ctid를 통해 데이터가 저장된 블록을 확인할 수 있으며, VACUUM 실행 전후의 블록 상태를 관찰할 수 있습니다. 
pg_freespacemap 확장을 사용하면 각 블록의 남은 공간을 보기 가능*/


postgres=# CREATE TABLE t_vacuum (
          id    int,
          x     char(1500) DEFAULT 'pglover'
);
CREATE TABLE
postgres=# INSERT INTO t_vacuum
     SELECT id FROM generate_series(1, 300) AS id;
INSERT 0 300

postgres=# SELECT ctid, id FROM t_vacuum limit 20;
 ctid  | id
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
 (0,5) |  5
 (1,1) |  6
 (1,2) |  7
 (1,3) |  8
 (1,4) |  9
 (1,5) | 10
 (2,1) | 11
 (2,2) | 12
 (2,3) | 13
 (2,4) | 14
 (2,5) | 15
 (3,1) | 16
 (3,2) | 17
 (3,3) | 18
 (3,4) | 19
 (3,5) | 20
(20 rows)

PostgreSQL에서 1,500바이트가 넘는 300개의 큰 행이 있는 테이블을 생성하면, 8k 블록에 5개의 행만 저장됩니다. 각 행이 어느 블록에 있는지 확인하기 위해 숨겨진 열 "ctid"를 사용할 수 있습니다. 예를 들어, ctid = '(2, 4)'는 두 번째 블록의 네 번째 행을 의미합니다.

행을 삭제하고 VACUUM을 실행하면 어떤 일이 일어나는지 확인하기 위해, 테이블 내부의 변화를 살펴보기 위한 확장 기능을 활성화할 수 있습니다.

삭제 작업 후 VACUUM을 실행하면, 공간이 회수되어 향후 데이터 삽입 시 재사용될 수 있는 것을 확인할 수 있습니다. 하지만 이 과정에서 디스크 자체의 공간이 줄어드는 것은 아닙니다. VACUUM이 테이블 간 데이터 이동을 수행하지 않기 때문입니다.

pg_fresspace extension설치

pg_freespacemap 확장은 PostgreSQL에서 자유 공간 맵(FSM, Free Space Map) 정보를 조회할 수 있게 해주는 도구입니다. 이 맵은 PostgreSQL이 각 테이블의 페이지에서 얼마나 많은 여유 공간이 있는지를 추적하는 데 사용됩니다. 이러한 정보를 통해 쓸 수 있는 빈 공간을 찾아서 데이터가 효율적으로 저장될 수 있도록 도와줍니다.

주요 기능

• 공간 관리: pg_freespacemap은 PostgreSQL 테이블 페이지의 여유 공간을 추적하는 메커니즘입니다. 테이블에 데이터를 삽입할 때, 이미 할당된 페이지에서 빈 공간을 찾아 데이터를 추가하는 방식으로 사용됩니다.
• 성능 최적화: 여유 공간이 있는 페이지를 효율적으로 사용하면 새로운 페이지를 할당하지 않아도 되기 때문에 성능이 개선될 수 있습니다.
• VACUUM과 연계: VACUUM 작업은 삭제된 데이터가 있는 공간을 해제하고, 이 해제된 공간을 추적하는 데 자유 공간 맵을 사용합니다. 이로써 PostgreSQL이 효율적으로 디스크 공간을 재사용할 수 있게 합니다

sudo yum install postgresql16-contrib
sudo systemctl restart postgresql-16

postgres=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION
postgres=# SELECT * FROM pg_freespace('t_vacuum');
 blkno | avail
-------+-------
     0 |   448
     1 |   448
     2 |   448
     3 |   448
     4 |   448
     5 |   448
     6 |   448
     7 |   448
     8 |   448
     9 |   448
    10 |   448
    11 |   448
'''
중략
'''

    59 |     0
(60 rows)

1. 데이터를 삭제하고 상황을 확인

postgres=# DELETE FROM t_vacuum
           WHERE  id = 7 OR id = 8
           RETURNING ctid;
 ctid
-------
 (1,2)
 (1,3)
(2 rows)

2. 데이터 파일에 갭이 생긴

DELETE 2
postgres=# SELECT       ctid, id
        FROM    t_vacuum
        WHERE   id BETWEEN 5 AND 10;
 ctid  | id
-------+----
 (0,5) |  5
 (1,1) |  6
 (1,4) |  9
 (1,5) | 10
(4 rows)

3. ctid 값이 (1,2)와 (1,3)인 행이 삭제되었으며, 이 행들은 이제 "죽은" 상태로 표시됩니다. 데이터는 실제로는 디스크에서 죽어 있지만, freespace map(자유 공간 맵)을 확인해 보면 내용이 변하지 않았습니다

postgres=# select * from pg_freespace('t_vacuum');
 blkno | avail
-------+-------
     0 |   448
     1 |  448
     2 |   448
     3 |   448
     4 |   448
     5 |   448
     6 |   448
     7 |   448
     8 |   448
     9 |   448
    10 |   448
    11 |   448
'''
중략
'''

    59 |     0
(60 rows)

중요한 점은 데이터가 삭제되더라도 공간이 즉시 회수되지 않는다는 것입니다. 공간은 VACUUM 명령어를 통해 회수

4. vacuum 회수

postgres=# VACUUM t_vacuum;
VACUUM

5. VACUUM 명령어가 실행된 후(긴 실행 트랜잭션이 작업을 방해하지 않는 경우), freespace map(FSM)은 디스크의 새로운 현실을 반영

postgres=# SELECT * FROM pg_freespace('t_vacuum');
 blkno | avail 
-------+-------
     0 |   448
     1 |  3520
     2 |   448
     3 |   448
     4 |   448
     5 |   448
     6 |   448
     7 |   448
     8 |   448
     9 |   448
    10 |   448
    11 |   448
'''
중략
'''

    59 |     448
(60 rows)

Point1

여전히 60개의 블록이 있지만, 이제 두 번째 블록에 훨씬 더 많은 여유 공간이 있습니다. 일반적인 VACUUM은 블록 간에 데이터를 이동하지 않으며, 이는 기본 동작 방식입니다. 따라서 공간은 파일 시스템으로 반환되지 않습니다. 그러나 대부분의 경우 나중에 사용할 수 있기 때문에 큰 문제는 없습니다.

Point2

이와 같이 VACUUM은 공간을 다시 사용할 수 있도록 블록 내에 여유 공간을 확보하지만, 디스크 공간을 즉시 반환하지는 않습니다. VACUUM FULL을 실행해야만 물리적으로 공간을 반환할 수 있지만, 이 작업은 성능 저하를 유발할 수 있습니다.

결론

VACUUM은 PostgreSQL 데이터베이스에서 공간을 효율적으로 관리하고 성능을 유지하는 데 중요한 역할을 합니다. 그러나 VACUUM이 디스크 공간을 즉시 반환하지 않는 이유와 VACUUM FULL의 필요성에 대한 이해는 중요합니다. 일반적인 VACUUM은 공간을 재사용할 수 있도록 하지만, 디스크에서 물리적으로 공간을 반환하기 위해서는 VACUUM FULL과 같은 추가 작업이 필요합니다. 이를 통해 데이터베이스의 성능을 최적화하고, 데이터의 효율적인 저장과 관리를 할 수 있습니다.

Reference

https://www.cybertec-postgresql.com/en/vacuum-managing-and-detecting-bloat/