Amazon Aurora 및 Amazon RDS의 PostgreSQL 18: 성능 향상

이 글은 AWS Blog의 “PostgreSQL 18 on Amazon Aurora and Amazon RDS: Security, monitoring, and developer enhancements” by Nazneen Jafri, Sukhpreet Kaur Bedi, Ranjan Burman, and Baji Shaik 게시글을 번역한 글 입니다.

복합 인덱스 전반의 쿼리 성능 관리, 구체화(materialized)된 CTE에서의 메모리 스필(spill) 진단, 그리고 실행 계획의 성능저하(plan regression) 없이 메이저 버전 업그레이드 수행은 PostgreSQL 사용자라면 흔히 겪게 되는 과제입니다. PostgreSQL 18은 중복 인덱스를 제거하는 스킵 스캔(skip scan) 최적화, 스토리지 동작을 확인할 수 있는 EXPLAIN 기능 향상, 메이저 버전 업그레이드 후에도 유지되는 옵티마이저 통계를 통해 이러한 문제들을 직접적으로 해결합니다. Amazon Aurora PostgreSQL 호환 버전과 PostgreSQL용 Amazon Relational Database Service(Amazon RDS)에서 바로 해당 기능을 사용해 볼 수 있습니다.

이 글은 PostgreSQL 18의 주요 기능을 다룬 2부작 시리즈 중의 첫번째 부분이며, 이 글에서는 성능 향상에 중점을 둡니다. 다중 컬럼 인덱스를 위한 스킵 스캔 최적화, 향상된 EXPLAIN 출력, 불필요한 셀프 조인의 자동 제거, 데이터베이스를 효율적으로 실행하는 데 도움이 되는 여러 vacuum 및 autovacuum 개선 사항을 다룹니다. 2부에서는 보안, 모니터링, 개발자, 논리적 복제 향상에 대한 부분을 다룹니다. 자세한 내용은 PostgreSQL 18 릴리스 노트를 참조해주시길 바랍니다.

사전 조건

이 게시물은 별도의 명령어를 실행하지 않고도 읽을 수 있지만, 예제를 직접 시도해 보고 싶다면 다음과 같은 사전 조건들을 충족해야 합니다.

1. Aurora PostgreSQL 클러스터 또는 PostgreSQL용 RDS 인스턴스가 없다면 새로 생성합니다. 자세한 내용은 Amazon Aurora PostgreSQL 호환 DB 클러스터 생성 또는 PostgreSQL DB 인스턴스 생성을 각각 참조합니다.
2. Aurora PostgreSQL 또는 PostgreSQL용 RDS 인스턴스에 액세스하기 위해 PostgreSQL 클라이언트를 설치할 Amazon Elastic Compute Cloud(Amazon EC2) 인스턴스를 생성합니다. 자세한 내용은 EC2 리소스 생성 및 EC2 인스턴스 시작을 참조합니다. 또는 RDS 데이터베이스와 EC2 컴퓨팅 인스턴스 간 연결을 원클릭으로 설정할 수도 있습니다.
3. PostgreSQL 클라이언트를 설치합니다. Amazon Linux 2023에서는 다음 명령을 사용하여 psql 명령줄 도구를 다운로드할 수 있습니다.필요한 모든 빌드 종속 항목을 설치합니다.
```
sudo dnf install -y gcc readline-devel libicu-devel zlib-devel openssl-devel make bison flex perl-core
```
  PostgreSQL 18 소스 코드를 다운로드합니다. 아래 명령에서 18.x를 현재 마이너 버전(예: 18.4)으로 바꿉니다.
```
wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz
```
  소스 파일의 압축을 풉니다.
```
tar -xvzf postgresql-18.x.tar.gz
```
  소스 디렉터리로 이동합니다.
```
cd postgresql-18.x
```
  빌드를 구성합니다. (시스템 전체 설치를 위해 bindir을 /usr/bin으로 설정)
```
./configure --bindir=/usr/bin --with-openssl
```
  클라이언트 바이너리, 라이브러리 및 헤더만 빌드하고 설치합니다.
```
sudo make -C src/bin install
sudo make -C src/include install
sudo make -C src/interfaces install
```
  설치를 확인합니다.
```
psql --version
```

다중 컬럼 인덱스를 위한 스킵 스캔(skip scan) 최적화

PostgreSQL 18은 스킵 스캔 최적화를 도입하여, 선행 컬럼이 WHERE절에 지정되지 않은 경우에도 쿼리 플래너가 다중 컬럼 B-트리 인덱스를 더 효율적으로 사용할 수 있게 해줍니다. 이 최적화는 복합 인덱스가 있지만 쿼리가 항상 선행 컬럼을 필터링 조건으로 사용하지 않는 시나리오에서 쿼리 성능을 향상시킬 수 있습니다.

스킵 스캔(skip scan) 최적화 이해하기

이전 PostgreSQL 버전에서는 쿼리가 WHERE절에 선행 컬럼을 포함한 경우에만 다중 컬럼 인덱스가 효율적으로 사용되었습니다. 예를 들어, (status, created_date)에 대한 인덱스의 경우, created_date만으로 필터링하는 쿼리는 해당 인덱스를 사용할 수 없었습니다.

스킵 스캔(skip scan)을 사용하면 PostgreSQL이 선행 컬럼의 고유 값들을 건너뛰며 이후 컬럼에 대해 인덱스를 사용할 수 있어, 다중 컬럼 인덱스를 여러 개의 단일 컬럼 인덱스처럼 처리할 수 있습니다.

예제 시나리오

다중 컬럼 인덱스가 있는 테이블을 생성하여 스킵 스캔 최적화가 어떻게 작동하는지 살펴보겠습니다.

-- Create a table for customer orders
CREATE TABLE customer_orders (
    order_id serial PRIMARY KEY,
    status varchar(20),
    created_date date,
    customer_id integer,
    amount numeric(10,2)
);

-- Create a multicolumn index
CREATE INDEX idx_orders_status_date ON customer_orders(status, created_date);

-- Insert sample data with a few distinct status values
INSERT INTO customer_orders (status, created_date, customer_id, amount)
SELECT
    CASE (random() * 4)::integer
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'processing'
        WHEN 2 THEN 'shipped'
        WHEN 3 THEN 'delivered'
        ELSE 'cancelled'
    END,
    current_date - (random() * 365)::integer,
    (random() * 10000)::integer,
    (random() * 1000)::numeric(10,2)
FROM generate_series(1, 1000000);

-- Analyze the table
ANALYZE customer_orders;

이제 (선행 컬럼이 아닌) created_date로만 필터링하는 쿼리를 실행해 보겠습니다.

-- Query filtering on created_date only
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customer_orders
WHERE created_date = '2026-03-15';

PostgreSQL 18 출력 (스킵 스캔 사용):

Bitmap Heap Scan on customer_orders  (cost=47.66..5422.68 rows=2726 width=27) (actual time=0.581..3.223 rows=2659 loops=1)
   Recheck Cond: (created_date = '2026-03-15'::date)
   Heap Blocks: exact=2229
   Buffers: shared hit=2266
   ->  Bitmap Index Scan on idx_orders_status_date  (cost=0.00..46.98 rows=2726 width=0) (actual time=0.305..0.305 rows=2659 loops=1)
         Index Cond: (created_date = '2026-03-15'::date)
         Index Searches: 11
         Buffers: shared hit=37
Planning Time: 0.065 ms
Execution Time: 3.374 ms

PostgreSQL 17 및 이전 버전에서는 이 쿼리가 순차 스캔을 사용하거나 created_date에 대한 별도의 인덱스를 필요로 할 가능성이 높습니다.

PostgreSQL 17 출력 (스킵 스캔 미사용):

Gather  (cost=1000.00..13833.43 rows=2721 width=27) (actual time=0.995..72.942 rows=2700 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=7353
   ->  Parallel Seq Scan on customer_orders  (cost=0.00..12561.33 rows=1134 width=27) (actual time=0.213..61.421 rows=900 loops=3)
         Filter: (created_date = '2026-03-15'::date)
         Rows Removed by Filter: 332433
Planning Time: 0.091 ms
Execution Time: 73.200 ms

스킵 스캔(skip scan)이 유용한 경우

1. - 선행 컬럼의 카디널리티가 낮은 경우. (고유 값이 적은 경우)
  - 쿼리가 선행 컬럼이 아닌 컬럼을 필터링하는 경우.
  - 선행 컬럼이 아닌 컬럼에 대한 필터의 선택도가 높은 경우.

성능 비교

Aurora PostgreSQL 17.10과 Aurora PostgreSQL 18.4를 사용한 테스트에서는 두 환경 모두 db.r6g.large에서 100만 개의 행과 선행 status 컬럼에 5개의 고유 값(웜 버퍼 캐시)으로 구성하였습니다.

스킵 스캔이 없는 경우(Aurora PostgreSQL 17), 선행 컬럼이 WHERE절에 없기 때문에 쿼리 플래너가 (status, created_date)에 대한 복합 인덱스를 사용할 수 없습니다. 대신 쿼리 플래너는 병렬 순차 스캔(Parallel Seq Scan)을 수행하여 총 7,353개의 블록을 읽고 73.2ms 만에 작업을 완료합니다.

스킵 스캔을 사용하는 경우(Aurora PostgreSQL 18), 플래너는 11회의 인덱스 검색으로 기존 복합 인덱스를 활용하여 2,266개 블록만을 읽어들이고 3.4ms 만에 작업을 완료합니다. 이는 별도의 인덱스를 추가하지 않고 21배의 성능 향상을 의미합니다.

스킵 스캔은 저장 공간 오버헤드와 유지 관리 비용 없이, 전용 단일 컬럼 인덱스를 사용하는 것과 유사한 성능을 제공합니다.

향상된 EXPLAIN

PostgreSQL 18은 EXPLAIN 명령을 크게 개선하여 쿼리 성능 분석을 더 직관적이고 포괄적으로 만듭니다. 이러한 개선 사항은 쿼리 실행, 리소스 사용량, 최적화 기회에 대한 더 깊은 인사이트를 제공하여 개발자와 데이터베이스 관리자가 성능 병목 지점을 더 효과적으로 식별하는 데 도움이 됩니다.

EXPLAIN ANALYZE의 자동 버퍼 통계

PostgreSQL 18의 중요한 사용성 개선 사항 중 하나는 EXPLAIN ANALYZE가 이제 기본적으로 버퍼 통계를 포함하여, BUFFERS 옵션을 명시적으로 지정할 필요가 없어졌다는 점입니다. 이 변경은 BUFFERS 플래그를 추가하지 않으면 중요한 I/O 정보가 숨겨지던 오래된 불편을 해결합니다.

PostgreSQL 18 이전에는 쿼리 성능을 분석하기 위해 다음과 같은 구문이 필요했습니다.

-- PostgreSQL 17 and earlier: BUFFERS option required
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customer_orders
WHERE created_date >= '2025-06-01'
  AND created_date < '2025-07-01';

BUFFERS 옵션이 없으면 실행 시간과 행 수는 확인할 수 있지만 캐시 적중, 디스크 읽기, 그리고 I/O 패턴에 대한 정보는 얻을 수 없습니다. PostgreSQL 18에서는 버퍼 통계가 자동으로 포함됩니다.

-- PostgreSQL 18: Buffer statistics included by default
EXPLAIN ANALYZE
SELECT * FROM customer_orders
WHERE created_date >= '2025-06-01'
  AND created_date < '2025-07-01';

이제 출력은 다음과 같이 표시됩니다.

Seq Scan on customer_orders  (cost=0.00..22353.00 rows=84546 width=27)
  (actual time=0.009..53.400 rows=82676 loops=1)
  Filter: ((created_date >= '2025-06-01'::date) AND (created_date < '2025-07-01'::date))
  Rows Removed by Filter: 917324
  Buffers: shared hit=18234 read=1245
Planning Time: 0.073 ms
Execution Time: 56.664 ms

버퍼 통계는 대부분의 데이터가 캐시에서 제공되었으며(18,234 공유 버퍼 적중), 디스크에서 읽어온 데이터의 양은 1,245 블록에 불과했습니다. 이는 성능 문제가 I/O 병목에서 비롯된 것인지 아니면 다른 요인에서 비롯된 것인지 파악하는 데 도움이 됩니다.

구체화된 노드의 메모리 및 디스크 사용량

PostgreSQL 18은 실행 계획에서 구체화된 노드에 대한 스토리지 가시성을 도입했습니다. executor가 반복 액세스를 위해 CTE를 구체화하면, 이제 실행 계획은 중간 결과가 메모리에 보관되었는지 아니면 디스크로 스필되었는지를 최대 스토리지 사용량과 함께 보고합니다. 이 정보는 PostgreSQL 17 및 이전 버전에서는 보이지 않았으며, work_mem 설정과 행 너비 추정치를 기반으로 추측할 수밖에 없었습니다.

예제 1: 메모리 스토리지 (기본값)

EXPLAIN (ANALYZE, BUFFERS)
WITH top_products AS MATERIALIZED (
    SELECT product_id, product_name, price
    FROM products
    WHERE price > 500
)
SELECT * FROM top_products
ORDER BY price DESC
LIMIT 10;

출력:

 Limit  (cost=394.97..394.99 rows=10 width=238) (actual time=3.028..3.031 rows=10.00 loops=1)
   Buffers: shared hit=64
   CTE top_products
     ->  Seq Scan on products  (cost=0.00..189.00 rows=4950 width=22) (actual time=0.012..1.535 rows=4948.00 loops=1)
           Filter: (price > '500'::numeric)
           Rows Removed by Filter: 5052
           Buffers: shared hit=64
   ->  Sort  (cost=205.97..218.34 rows=4950 width=238) (actual time=3.027..3.028 rows=10.00 loops=1)
         Sort Key: top_products.price DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=64
         ->  CTE Scan on top_products  (cost=0.00..99.00 rows=4950 width=238) (actual time=0.013..2.273 rows=4948.00 loops=1)
               Storage: Memory  Maximum Storage: 296kB
               Buffers: shared hit=64
Planning:
   Buffers: shared hit=36
Planning Time: 0.193 ms
Execution Time: 3.057 ms
(18 rows)

주목해야 할 부분은 CTE Scan 노드의 Storage: Memory Maximum Storage: 296kB입니다. 이는 4,948개 행 전체로 이루어진 구체화된 결과 집합이 저장되었으며, 최대 296kB의 메모리를 사용했음을 의미합니다. 임시 파일은 생성되지 않았으며, 공유 버퍼에서 원본 테이블을 읽는 것 외에는 디스크 I/O가 발생하지 않았습니다.

예제 2: 디스크 스토리지 (낮은 `work_mem`)

이제 구체화된 CTE가 더 이상 메모리에 맞지 않도록 work_mem을 인위적으로 제한하면 어떤 일이 일어나는지 살펴보겠습니다.

-- Set low work_mem to force disk storage
SET work_mem = '64kB';

EXPLAIN (ANALYZE, BUFFERS)
WITH top_products AS MATERIALIZED (
    SELECT product_id, product_name, price
    FROM products
    WHERE price > 500
)
SELECT * FROM top_products
ORDER BY price DESC
LIMIT 10;

출력:

 Limit  (cost=394.97..394.99 rows=10 width=238) (actual time=3.184..3.187 rows=10.00 loops=1)
   Buffers: shared hit=64, temp written=20
   I/O Timings: temp write=0.127
   CTE top_products
     ->  Seq Scan on products  (cost=0.00..189.00 rows=4950 width=22) (actual time=0.013..1.391 rows=4948.00 loops=1)
           Filter: (price > '500'::numeric)
           Rows Removed by Filter: 5052
           Buffers: shared hit=64
   ->  Sort  (cost=205.97..218.34 rows=4950 width=238) (actual time=3.183..3.184 rows=10.00 loops=1)
         Sort Key: top_products.price DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=64, temp written=20
         I/O Timings: temp write=0.127
         ->  CTE Scan on top_products  (cost=0.00..99.00 rows=4950 width=238) (actual time=0.015..2.411 rows=4948.00 loops=1)
               Storage: Disk  Maximum Storage: 160kB
               Buffers: shared hit=64, temp written=20
               I/O Timings: temp write=0.127
Planning Time: 0.102 ms
Execution Time: 3.268 ms
(19 rows)

차이점을 주목해보겠습니다. Storage: Memory가 Storage: Disk로 바뀌었고, 몇가지의 새로운 지표들이 나타났습니다. temp written=20은 임시 파일 I/O가 발생했음을 나타내며, I/O Timings: temp write=0.127은 해당 임시 파일에 쓰는 데 소요된 시간을 수치로 보여줍니다. 실행 시간은 3.057ms에서 3.268ms로 증가했는데, 데이터셋 규모가 작기 때문에 증가폭은 크지 않았습니다. 하지만 데이터셋의 크기가 더 클 경우, 디스크 스필로 인한 성능저하가 훨씬 심각해집니다.

실제 상황에서 중요한 이유

이 스토리지 보고 기능은 구체화된 CTE를 진단하고 튜닝하는 방식을 변화시킵니다. 평소 밀리초 단위로 실행되는 쿼리가 갑자기 느려지는 경우, 이전에 메모리에 있던 CTE가 Storage: Disk로전환되었는지를 확인하면 즉시 원인을 파악할 수 있습니다. 데이터셋이 메모리 임계값을 초과한 것 입니다. Maximum Storage값은 구체화에 필요한 정확한 공간을 알려줌으로써 추측이 아닌 정확한 work_mem 조정을 가능하게 합니다.

다만, 항상 work_mem을 늘리는 것이 올바른 해결책은 아닙니다. work_mem은 각 세션 내에서 작업별, 쿼리별로 할당되므로, 동시 연결이 많은 시스템에서 너무 높은 값을 설정하면 메모리 압박을 초래할 수 있습니다. 더 나은 접근 방식은 Maximum Storage 값을 기준으로 사용하는 것입니다. 예를 들어, CTE에 296kB가 필요한데 work_mem이 4MB라면 충분한 여유가 있는 것입니다. 반면 4MB 설정에서 3.9MB가 필요한 경우, 8MB 정도로 적절히 늘려 무리 없이 메모리 내에서 처리가 가능하도록 할 수 있습니다. 구체화 오버헤드가 병목인 쿼리의 경우에는 MATERIALIZED 힌트가 정말 필요한지 검토해 볼 필요가 있습니다. 때로는 쿼리 플래너가 CTE를 인라인하도록 두는 것이 전반적으로 더 나은 실행 계획을 만들어내기도 합니다.

Storage, Maximum Storage, temp written 그리고 I/O Timings 지표를 종합하면 PostgreSQL 18 이전에는 알 수 없었던 구체화 비용을 확인할 수 있습니다. 이를 통해 과거에는 경험에 기반한 추측에 의존했던 튜닝 결정을 데이터에 기반한 합리적인 결정으로 내릴 수 있게 됩니다.

EXPLAIN ANALYZE의 인덱스 검색 지표

PostgreSQL 18은 EXPLAIN ANALYZE 출력에 새로운 지표인 Index Searches를 도입했습니다. 이 지표는 쿼리 실행 중 데이터베이스가 인덱스 트리를 몇 번 탐색했는지를 보여주어 인덱스 사용 패턴에 대한 유용한 정보를 제공합니다.

Index Searches 카운터란?

Index Searches 카운터는 Index Scan, Bitmap Index Scan, Index-Only Scan 노드에 표시됩니다. 이 카운터는 모든 노드의 실행 및 반복 과정에서 총 몇 번의 인덱스 트리 탐색이 이루어졌는지를 나타냅니다.

예제: 여러 값을 가진 IN 절

특정 가격의 제품을 검색하는 쿼리를 살펴보겠습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE price IN (100.50, 500.75, 750.25, 999.99);

출력:

Bitmap Heap Scan on products  (cost=17.17..30.51 rows=4 width=29)
                              (actual time=0.046..0.047 rows=1.00 loops=1)
  Recheck Cond: (price = ANY ('{100.50,500.75,750.25,999.99}'::numeric[]))
  Heap Blocks: exact=1
  Buffers: shared hit=4 read=5
  ->  Bitmap Index Scan on idx_products_price  (cost=0.00..17.17 rows=4 width=0)
                                                (actual time=0.039..0.039 rows=1.00 loops=1)
        Index Cond: (price = ANY ('{100.50,500.75,750.25,999.99}'::numeric[]))
        Index Searches: 4
        Buffers: shared hit=3 read=5
Planning Time: 1.907 ms
Execution Time: 0.064 ms

출력 결과에서 Index Searches: 4라는 항목을 확인해 봅니다. 쿼리는 4개의 서로 다른 가격 값을 검색하며, PostgreSQL은 IN절의 각 값에 대해 별도로 인덱스 트리를 탐색하는 작업을 4회 수행했습니다. 각 검색은 인덱스 루트에서 시작하여 적절한 리프 페이지로 이동하는 방식으로 진행됩니다.

이것이 중요한 이유

PostgreSQL 18 이전에는 인덱스가 사용되었다는 것은 알 수 있었지만 executor가 트리를 몇 번 탐색했는지 알 수 없었습니다. 이제는 확인할 수 있습니다. IN절에서 높은 탐색 횟수가 나타난다면, 각 값이 별도의 루트-투-리프(root-to-leaf) 탐색을 유발했음을 의미합니다. 만약 해당 값들이 연속적인 범위에 있다면, 조건절을 BETWEEN 범위 조건으로 변경하여 탐색 횟수를 줄일 수 있습니다. 중첩 루프 조인(nested loop join)의 경우, 이 지표가 전체 루프 반복 과정에서 수행된 총 탐색 횟수를 보여줍니다. 이를 통해 내부 인덱스가 과도하게 사용되고 있는지, 혹은 다른 조인 전략을 사용하는 것이 비용 측면에서 더 효율적일지를 빠르게 파악할 수 있습니다.

여기서 다룬 세 가지 향상 사항(자동 버퍼 통계, 구체화된 CTE 스토리지 및 메모리 사용량 보고, 인덱스 탐색 횟수)은 PostgreSQL 18에 포함된 EXPLAIN 개선 사항의 일부에 불과합니다. EXPLAIN 관련 향상 사항의 전체 목록은 PostgreSQL 18 릴리스 노트를 참조해주시길 바랍니다.

불필요한 셀프 조인의 자동 제거

PostgreSQL 18은 셀프 조인 제거(Self-Join Elimination, SJE) 기능을 도입합니다. 이는 테이블을 자기 자신과 조인하는 이너 조인(inner join)이 불필요하다고 판단될 경우, 이를 자동으로 감지해 제거하는 쿼리 플래너 최적화 기술입니다. 이 기능은 SELECT 쿼리에 적용되며, 쿼리 플래너가 셀프 조인의 한쪽이 결과에 추가적인 행이나 컬럼을 기여하지 않는다고 안전하게 판단할 수 있을 때 해당 조인을 완전히 제거합니다.

셀프 조인은 두 가지 조건이 충족될 때 제거될 수 있습니다.

조인 조건이 고유 키(unique key)또는 기본 키(primary key) 컬럼에 있는 경우. 이는 외부 행당 일치하는 행이 최대 하나뿐임을 의미하므로, 조인이 중복되거나 행을 필터링할 수 없습니다.
외부 별칭만으로 충족될 수 없는 내부 테이블 별칭의 컬럼이 SELECT 목록이나 WHERE절에서 참조되지 않는 경우.

이러한 조건이 충족되면, 쿼리 플래너는 테이블을 한 번만 스캔하도록 쿼리를 다시 작성하여 조인을 완전히 제거합니다.

예제: ORM이 생성한 셀프 조인

기본 키에 대한 셀프 조인은 중복을 인식하지 않고 재사용 가능한 조각들을 조합하여 쿼리를 구성하는 ORM과 쿼리 빌더에 의해 흔히 발생합니다. id를 기본 키로 하는 employees 테이블을 예로 들어보겠습니다.

CREATE TABLE employees (
    id     serial PRIMARY KEY,
    name   text,
    dept   text,
    salary numeric
);

기본 키를 기준으로 테이블을 자기 자신과 조인하는 쿼리:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT e1.id, e1.name, e1.salary
FROM employees e1
JOIN employees e2 ON e1.id = e2.id;

PostgreSQL 17 및 이전 버전 (실제 조인 실행):

Hash Join
  Hash Cond: (e1.id = e2.id)
  ->  Seq Scan on employees e1
  ->  Hash
        ->  Seq Scan on employees e2

PostgreSQL 18 (조인이 제거됨, 단일 스캔):

Seq Scan on employees e2  (actual time=0.017..8.749 rows=100000 loops=1)
  Buffers: shared hit=770
Planning Time: 0.318 ms
Execution Time: 12.768 ms

쿼리 플래너는 기본 키 id를 기준으로 employees와의 조인이 불필요하다는 것을 인식합니다. 각 행은 다른 별칭으로 표시된 테이블에서도 정확히 하나의 행과 대응하며, 그 행은 실제로 동일한 물리적 행입니다. 따라서 조인은 단순한 스캔으로 재작성됩니다.

WHERE 절이 있는 경우

SJE는 추가 필터가 있는 경우에도 작동합니다.

EXPLAIN (ANALYZE, COSTS OFF)
SELECT e1.id, e1.name, e1.dept, e1.salary
FROM employees e1
JOIN employees e2 ON e1.id = e2.id
WHERE e1.dept = 'Engineering';

Seq Scan on employees e2  (actual time=0.031..6.985 rows=33333 loops=1)
  Filter: (dept = 'Engineering'::text)
  Rows Removed by Filter: 66667
  Buffers: shared hit=770
Planning Time: 0.357 ms
Execution Time: 8.486 ms

마찬가지로, 단일 스캔으로 처리됩니다. 셀프 조인이 제거되고 필터가 직접 적용됩니다.

셀프 조인 제거가 적용되지 않는 경우

SJE는 고유 키 또는 기본 키 조인 조건을 필요로 합니다. 같은 부서의 동료를 찾는 것과 같이 고유하지 않은 컬럼에 대한 셀프 조인은 여러 행이 일치할 수 있기 때문에 제거될 수 없습니다.

EXPLAIN (ANALYZE, COSTS OFF)
SELECT e1.id, e1.name, e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e1.dept = e2.dept
WHERE e1.id = 1
LIMIT 5;

Nested Loop
  Join Filter: (e1.dept = e2.dept)
  ->  Index Scan using employees_pkey on employees e1
        Index Cond: (id = 1)
  ->  Seq Scan on employees e2

여기서는 조인이 유지됩니다. dept는 고유하지 않으므로 한 명의 직원이 여러 동료와 일치할 수 있으며, 조인이 결과셋을 실제로 변경합니다.

적용 범위: SELECT 전용

PostgreSQL 18 구현은 SJE를 SELECT쿼리에만 적용합니다. UPDATE, DELETE 그리고 MERGE 문의 셀프 조인은 제거되지 않습니다. 이는 의도적인 설계입니다. DML 쿼리는 동시 행 업데이트를 위한 EvalPlanQual(EPQ) 재평가 과정을 거치는데, 이 경우 두 테이블 별칭이 서로 다른 행 버전(스냅샷과 최신 버전)을 참조해야 할 수 있습니다. 이러한 상황에서 조인을 제거하면 잘못된 결과가 발생합니다. PostgreSQL 커뮤니티는 초기 구현을 보수적으로 범위를 한정했으며, DML 지원은 향후 릴리스에서 제공될 예정입니다.

autovacuum_vacuum_max_threshold를 통한 세밀한 autovacuum 제어

PostgreSQL 18은 새로운 파라미터인 autovacuum_vacuum_max_threshold를 도입했습니다. 이 파라미터는 테이블 크기와 관계없이 autovacuum이 VACUUM을 트리거하기 전에 누적될 수 있는 데드 튜플수에 상한을 설정합니다. 이는 스케일 팩터 기반(scale-factor) 공식이 autovacuum을 지나치게 오래 지연시킬 수 있는 대용량 테이블에서의 오랜 문제를 해결합니다.

대규모 테이블의 문제점

Autovacuum은 업데이트되거나 삭제된 튜플 수를 다음과 같이 계산된 임계값과 비교하여 테이블을 vacuum할 시점을 결정합니다.

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table_rows

기본값(threshold = 50, scale_factor = 0.2 )을 사용할 경우, 중소형 테이블에는 잘 작동합니다. 그러나 대용량 테이블의 경우, 이 공식은 autovacuum이 실행되기 전에 엄청난 양의 팽창(bloat)이 누적되도록 허용하는 임계값을 만듭니다.

테이블 크기	autovacuum 트리거에 필요한 데드 튜플 (PG18 이전)
100만 행	200,050
1억 행	20,000,050
5억 행	100,000,050
10억 행	200,000,050

10억 행 테이블에서는 autovacuum이 실행되기 전에 2억 개의 데드 튜플이 생성되기를 기다립니다. 이는 상당한 테이블 팽창, 쿼리 성능 저하, 트랜잭션 ID 랩어라운드 압박의 위험이 증가합니다.

PG18 이전의 해결 방법은 테이블별로 autovacuum_vacuum_scale_factor = 0 및 autovacuum_vacuum_threshold = N을 수동으로 설정하는 것이었지만, 이는 모든 대규모 테이블을 식별하고 테이블이 커짐에 따라 해당 설정을 유지 관리해야 했습니다.

autovacuum_vacuum_max_threshold 작동 방식

PostgreSQL 18은 공식을 다음과 같이 변경합니다.

vacuum threshold = MIN(autovacuum_vacuum_max_threshold,
                       autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table_rows)

새로운 파라미터는 상한선 역할을 합니다. 스케일 팩터 계산이 최대 임계값을 초과하면, autovacuum은 그 상한값에 도달했을 때 실행됩니다. 기본값은 1억 개 튜플(100,000,000 tuples)이며, 이는 테이블이 아무리 크더라도 autovacuum이 실행되기 전까지 어떤 테이블도 1억 개를 초과하는 데드 튜플을 누적하지 않는다는 의미입니다.

SHOW autovacuum_vacuum_max_threshold;

 autovacuum_vacuum_max_threshold
---------------------------------
 100000000
(1 row)

10억 행 테이블 예시의 경우, 이제 유효 임계값이 2억이 아닌 1억으로 제한됩니다.

PostgreSQL용 Amazon RDS 및 Aurora PostgreSQL에서 구성하기

autovacuum_vacuum_max_threshold는 동적 파라미터(sighup 수준)입니다. 파라미터 그룹에서 변경할 수 있으며 재부팅하지 않고도 즉시 적용됩니다.

PostgreSQL용 Amazon RDS: DB 인스턴스 파라미터 그룹을 수정합니다.

Aurora PostgreSQL: DB 클러스터 파라미터 그룹을 수정합니다. (클러스터의 모든 인스턴스에 적용됨)

두 경우 모두 파라미터 변경은 재부팅 없이 적용됩니다. 연결된 세션에서 SHOW autovacuum_vacuum_max_threshold;를 사용하여 확인할 수 있습니다.

모든 테이블에 적용되는 전역 상한값 낮추기:

-- In parameter group: set to 10 million dead tuples max
autovacuum_vacuum_max_threshold = 10000000

테이블별 재정의:

-- Cap a specific high-churn table at 500,000 dead tuples
ALTER TABLE orders SET (autovacuum_vacuum_max_threshold = 500000);

-- Disable the cap for a specific table (use scale factor only)
ALTER TABLE archive_data SET (autovacuum_vacuum_max_threshold = -1);

전역 상한값 비활성화하기:

-- In parameter group: revert to pre-PG18 behavior
autovacuum_vacuum_max_threshold = -1

올바른 값 선택

기본값인 1억은 대부분의 워크로드에 합리적인 안전망입니다. 오래된 행과 새 행 양쪽에 걸쳐 변경이 많은 대규모 테이블이 있는 데이터베이스의 경우, 이를 1천만으로 낮추면 freeze 활동과의 균형을 맞추면서 autovacuum이 데드 튜플 누적을 앞서 처리하는 데 도움이 될 수 있습니다. 데드 튜플이 천천히 누적되는 추가 전용(append-only) 또는 읽기 위주의 테이블의 경우에는 기본값이나 -1(비활성화)이 적절할 수 있습니다.

pg_class와 함께 pg_stat_user_tables를 쿼리하여 모든 테이블의 현재 유효 임계값을 확인할 수 있습니다.

SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    LEAST(
        current_setting('autovacuum_vacuum_max_threshold')::bigint,
        current_setting('autovacuum_vacuum_threshold')::bigint +
        (current_setting('autovacuum_vacuum_scale_factor')::float * reltuples)::bigint
    ) AS effective_vacuum_threshold
FROM pg_stat_user_tables
JOIN pg_class ON relname = pg_class.relname
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 10;

vacuum_truncate 서버 변수

PostgreSQL 18은 vacuum_truncate를 서버 전역 GUC(Grand Unified Configuration) 파라미터로 도입했습니다. 이를 통해 데이터베이스 관리자는 개별 테이블 스토리지 옵션을 수정하지 않고도 모든 테이블에 걸쳐 VACUUM의 파일 truncation 동작을 비활성화할 수 있는 단일 제어 지점을 갖게 됩니다.

VACUUM truncation 이해하기

VACUUM이 실행되면 heap 스캔, index 및 heap vacuum, 최종적으로 heap truncation을 포함한 여러 단계를 거칩니다. truncation 단계는 테이블 데이터 파일 끝부분의 빈 페이지를 제거하고 해당 디스크 공간을 운영 체제에 반환합니다. 이는 유익해 보이지만, 간과할 수 없는 점도 있습니다. truncation 작업에는 테이블에 대한 AccessExclusiveLock이 필요한데, 이는 ALTER TABLE, DROP TABLE 및 유사한 DDL 작업에서 사용하는 것과 동일한 잠금 수준입니다.

처음 세 단계 동안 VACUUM은 동시 읽기와 쓰기를 허용하는 ShareUpdateExclusiveLock만 보유합니다. 그러나 truncation 단계에 진입하면 AccessExclusiveLock으로 격상되어 테이블에 대한 다른 모든 액세스를 일시적으로 차단합니다. 대용량 테이블을 다루는 OLTP 시스템에서는 이러한 잠금 처리로 인해 쿼리 처리 속도가 현저히 느려질 수 있습니다.

읽기 전용 복제본에 미치는 영향

truncation 잠금은 Aurora PostgreSQL과 PostgreSQL용 RDS 읽기 전용 복제본에 특히 큰 영향을 미칩니다. 기본 인스턴스가 테이블을 truncation하면 WAL 스트림에 XLOG_STANDBY_LOCK 레코드를 기록합니다. 복제본의 WAL 재생 프로세스는 이 레코드를 적용하기 전에 해당 릴레이션에 대해 동일한 AccessExclusiveLock을 획득해야 합니다. 만약 복제본에서 장기 실행 중인 쿼리가 충돌하는 잠금을 보유하고 있는 경우, 복제본은 WAL 재생을 진행하기 위해 30초 대기 후 해당 쿼리를 취소합니다. 읽기 전용 복제본의 사용자들은 아래와 같은 상황을 경험하게 됩니다

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.

이는 읽기 전용 복제본에서 예기치 않은 쿼리 취소가 발생하는 흔한 원인이며, 특히 기본 인스턴스에서 autovacuum이 대용량 테이블을 활발하게 truncation하고 있을 때 자주 나타납니다.

PostgreSQL 18 이전: 테이블별 제어만 가능

PostgreSQL 18 이전에는 vacuum_truncate가 테이블별로 설정되는 스토리지 수준 파라미터로만 존재했습니다.

-- Disable truncation for a specific table (PostgreSQL 12+)
ALTER TABLE orders SET (vacuum_truncate = off);

이 접근 방식은 truncation으로 인해 문제를 일으키는 모든 테이블을 식별하고 각각을 개별적으로 수정해야 했으며, 수백 개의 테이블이 있는 데이터베이스에서는 이러한 작업이 상당한 유지보수 부담이 되었습니다.

PostgreSQL 18: 서버 전체 GUC

PostgreSQL 18은 vacuum_truncate를 서버 전역 GUC로 승격시켜, 파라미터 그룹을 통해 truncation 동작을 전역적으로 제어할 수 있게 했습니다. 기본값은 on(truncation 활성화)으로 유지되어 하위 버전과의 호환성을 보존합니다.

현재 설정을 확인합니다.

SHOW vacuum_truncate;

 vacuum_truncate
-----------------
 on
(1 row)

PostgreSQL용 Amazon RDS에서 vacuum_truncate 구성하기

truncation을 전역적으로 비활성화하려면 vacuum_truncate = off로 사용자 지정 파라미터 그룹을 생성하고 인스턴스에 적용합니다. 파라미터는 동적 파라미터이므로 재부팅이 필요하지 않습니다. 다만, 새로운 파라미터 그룹을 인스턴스에 적용하기 위하여 재부팅 후 설정을 확인할 수 있습니다.

SHOW vacuum_truncate;

 vacuum_truncate
-----------------
 off
(1 row)

동작 차이 시연하기

VACUUM verbose 출력에서 vacuum_truncate의 효과를 직접 관찰할 수 있습니다. 구체적으로는 얼마나 많은 페이지가 제거되었는지, 몇 개의 페이지가 남았는지, truncation 관련 행이 표시되는지의 여부를 확인할 수 있습니다.

vacuum_truncate = on(기본값)인 경우:

VACUUM VERBOSE vacuum_demo;

INFO:  vacuuming "postgres.public.vacuum_demo"
INFO:  table "vacuum_demo": truncated 4425 to 45 pages
INFO:  finished vacuuming "postgres.public.vacuum_demo": index scans: 0
pages: 4380 removed, 45 remain, 4425 scanned (100.00% of total)
tuples: 990000 removed, 10000 remain, 0 are dead but not yet removable

truncated 4425 to 45 pages 행은 VACUUM이 4,380개의 빈 페이지를 제거하고, 그 공간을 OS에 반환했음을 확인해 줍니다. 이 truncation에서는 AccessExclusiveLock이 필요합니다.

vacuum_truncate = off인 경우:

VACUUM VERBOSE vacuum_demo;

INFO:  vacuuming "postgres.public.vacuum_demo"
INFO:  finished vacuuming "postgres.public.vacuum_demo": index scans: 0
pages: 0 removed, 4425 remain, 4425 scanned (100.00% of total)
tuples: 990000 removed, 10000 remain, 0 are dead but not yet removable

truncation 행이 완전히 사라졌습니다. 데드 튜플은 여전히 제거되지만(990,000개 행 정리), 빈 페이지는 파일에 그대로 남아 있습니다. AccessExclusiveLock이 획득되지 않으며, WAL truncation 레코드가 복제본에 전송되지 않습니다.

vacuum_truncate = off를 사용해야 하는 경우

truncation 비활성화하는 것은 다음과 같은 경우에 적절합니다.

1. - 읽기 전용 복제본에서 빈번한 쿼리 취소가 발생하는 경우, 기본 인스턴스의 VACUUM truncation으로 인한 canceling statement due to conflict with recovery 오류가 발생하는 경우.
  - 테이블의 쓰기 변경(write churn)이 빈번한 경우. 삭제된 공간이 새로운 삽입으로 금방 재사용 되는 상황에서는, 파일을 truncation했다가 다시 확장하는 것은 I/O를 낭비합니다.
  - 바쁜 OLTP 시스템의 대규모 테이블에서, 짧은 AccessExclusiveLock 조차 눈에 띄는 지연 시간 급증을 유발하는 경우.

truncation을 활성화 상태(기본값)로 유지하는것은 다음과 같은 경우에 적절합니다.

1. - 테이블이 주기적으로 대량 삭제되고 공간이 실제로 재사용되지 않는 경우, truncation은 디스크 공간을 OS에 반환할 수 있습니다.
  - 스토리지 비용이 중요하고, VACUUM이 공간을 적극적으로 회수하기를 원하는 경우.
  - 읽기 전용 복제본이 없어 WAL 충돌 우려가 없는 경우.

테이블별 재정의

서버 전역 GUC는 여전히 테이블 수준에서 재정의할 수 있습니다. vacuum_truncate = off를 전역적으로 설정했지만 특정 테이블에 대해서만 truncation을 활성화하려는 경우:

ALTER TABLE archive_data SET (vacuum_truncate = on);

반대로, 전역 설정이 on이지만 트래픽이 많은 특정 테이블에서만 truncation을 비활성화해야 하는 경우:

ALTER TABLE orders SET (vacuum_truncate = off);

이러한 계층화된 제어(파라미터 그룹을 사용한 서버 기본값, 스토리지 옵션을 사용한 테이블별 재정의)는 데이터베이스 전반의 truncation 동작을 정밀하게 제어할 수 있습니다.

autovacuum_worker_slots

PostgreSQL 18은 새로운 파라미터인 autovacuum_worker_slots를 도입했습니다. 이 파라미터는 autovacuum 워커 슬롯을 할당하는 개념과 실제 그 슬롯들을 사용하는 개념을 분리합니다. 이러한 분리를 통해 autovacuum_max_workers를 서버 재시작 없이 런타임에 조정할 수 있게 되었으며, 이는 변화하는 워크로드에서 autovacuum을 튜닝하는 데 있어 운영상 중요한 개선점입니다.

PostgreSQL 18 이전의 문제

PostgreSQL 18 이전에는 autovacuum_max_workers가 autovacuum 동시성을 제어하는 유일한 파라미터였습니다. 이 파라미터가 시작 시 예약되는 백엔드 슬롯 수를 직접 제어했기 때문에 postmaster 컨텍스트를 가졌으며, 이는 변경 시 전체 서버 재시작이 필요함을 의미했습니다. 쓰기가 많은 기간 동안 테이블 팽창을 따라잡기 위해 autovacuum 워커를 일시적으로 늘려야 하는 경우, 인스턴스를 재시작하는 수 밖에 없었습니다.

PostgreSQL 18에서의 작동 방식

PostgreSQL 18은 2단계 모델을 도입했습니다.

1. - autovacuum_worker_slots는 시작 시 autovacuum 워커를 위해 예약되는 백엔드 슬롯의 최대 수를 설정합니다. 이는 postmaster 수준의 파라미터로, 값 변경 시 여전히 재시작이 필요합니다. 이는 하드 상한선 역할을 합니다.
  - autovacuum_max_workers는 특정 시점에 그 슬롯들 중 몇 개가 실제로 사용되고 있는지를 제어합니다. 해당 파라미터는 동적 파라미터이므로, 구성을 리로드하면 실행 중에도 값을 변경할 수 있으며, 별도의 재시작이 필요하지 않습니다.

SELECT name, setting, context,
       CASE context
           WHEN 'postmaster' THEN 'Requires restart'
           WHEN 'sighup'     THEN 'Runtime (no restart)'
       END AS change_method
FROM pg_settings
WHERE name IN ('autovacuum_worker_slots', 'autovacuum_max_workers')
ORDER BY name;

          name           | setting |  context   |    change_method
-------------------------+---------+------------+----------------------
 autovacuum_max_workers  | 3       | sighup     | Runtime (no restart)
 autovacuum_worker_slots | 16      | postmaster | Requires restart
(2 rows)

기본적으로 autovacuum_worker_slots는 16이고 autovacuum_max_workers는 3입니다. 인스턴스를 재시작하지 않고도 언제든지 autovacuum_max_workers를 최대 16까지 늘릴 수 있습니다.

PostgreSQL용 Amazon RDS에서 구성하기

RDS 및 Aurora PostgreSQL에서는 파라미터 그룹의 autovacuum_max_workers를 즉시 적용(Apply immediately)옵션으로 업데이트할 수 있으며, 재부팅이 필요하지 않습니다. 상한선(autovacuum_worker_slots)을 늘리려면 파라미터 그룹을 업데이트하고 인스턴스를 재부팅해야 합니다.

예제: 쓰기가 많은 기간 동안 autovacuum 워커 일시적으로 늘리기

파라미터 그룹에서 autovacuum_max_workers를 3에서 6으로 업데이트한 후, 즉시 적용하고 결과를 확인합니다.

SHOW autovacuum_max_workers;

 autovacuum_max_workers
------------------------
 6
(1 row)

워크로드가 정상화되면 다시 되돌립니다.

중요: autovacuum_max_workers는 autovacuum_worker_slots를 초과할 수 없습니다. 더 높게 설정하려고 하면 PostgreSQL이 슬롯 값으로 제한합니다. autovacuum_max_workers를 더 높게 허용하려면, 먼저 autovacuum_worker_slots를 늘린 다음(재시작 필요), 새로운 상한 내에서 autovacuum_max_workers를 자유롭게 조정할 수 있습니다.

테이블별 vacuum 및 analyze 타이밍

PostgreSQL 18은 pg_stat_all_tables(및 그 변형인 pg_stat_user_tables, pg_stat_sys_tables)에 테이블별 유지 관리 작업에 소요된 누적 시간을 추적하는 네 개의 새로운 컬럼을 추가했습니다.

컬럼	설명
`total_vacuum_time`	수동 VACUUM 작업에 소요된 누적 시간 (밀리초)
`total_autovacuum_time`	autovacuum 작업에 소요된 누적 시간 (밀리초)
`total_analyze_time`	수동 ANALYZE 작업에 소요된 누적 시간 (밀리초)
`total_autoanalyze_time`	autoanalyze 작업에 소요된 누적 시간 (밀리초)

PostgreSQL 18 이전에는 pg_stat_user_tables가 마지막 vacuum 또는 analyze가 언제 실행되었는지(last_vacuum, last_autovacuum)는 보여주었지만, 얼마나 오래 걸렸는지에 대한 정보는 제공하지 않았습니다. 어떤 테이블이 가장 많은 유지 관리 시간을 소비했는지 식별하려면 로그 파일을 파싱하거나 작업 진행 중에 pg_stat_progress_vacuum을 사용해야 했습니다. 이 새로운 컬럼은 해당 정보를 영구적이고 누적된 형태로 제공합니다.

예제

500만 행이 있는 테이블을 생성하고 그 중 절반을 삭제한 다음, VACUUM VERBOSE과 ANALYZE VERBOSE를 실행합니다.

CREATE TABLE timing_demo (id serial, val text);
INSERT INTO timing_demo SELECT i, md5(i::text) FROM generate_series(1, 5000000) i;
DELETE FROM timing_demo WHERE id % 2 = 0;

VACUUM VERBOSE timing_demo;

INFO:  vacuuming "postgres.public.timing_demo"
INFO:  finished vacuuming "postgres.public.timing_demo": index scans: 0
pages: 0 removed, 41667 remain, 41667 scanned (100.00% of total)
tuples: 2500000 removed, 2500000 remain, 0 are dead but not yet removable
...
system usage: CPU: user: 0.18 s, system: 0.00 s, elapsed: 0.18 s

ANALYZE VERBOSE timing_demo;

INFO:  analyzing "public.timing_demo"
INFO:  "timing_demo": scanned 30000 of 41667 pages, containing 1799980 live rows and 0 dead rows
INFO:  finished analyzing table "postgres.public.timing_demo"
...
system usage: CPU: user: 0.10 s, system: 0.00 s, elapsed: 0.10 s

이제 새로운 타이밍 컬럼을 쿼리합니다.

SELECT relname,
       round(total_vacuum_time::numeric, 2)     AS total_vacuum_ms,
       round(total_analyze_time::numeric, 2)    AS total_analyze_ms
FROM pg_stat_user_tables
WHERE relname = 'timing_demo';

   relname   | total_vacuum_ms | total_analyze_ms
-------------+-----------------+------------------
 timing_demo |          189.00 |           107.00
(1 row)

VACUUM VERBOSE의 elapsed: 0.18 s는 total_vacuum_time의 189ms에 직접 매핑되며, ANALYZE VERBOSE의 elapsed: 0.10 s는 total_analyze_time의 107ms에 매핑됩니다. 이 뷰는 모든 수동 및 자동 작업에 걸쳐 이러한 값을 누적하여, 테이블별 유지 관리 시간의 누계를 제공합니다.

이 컬럼들은 누적되기 때문에, VACUUM과 ANALYZE를 다시 실행하면 합계에 추가되어 테이블의 생애 주기동안 발생하는 유지 관리 오버헤드의 전체 현황을 파악할 수 있습니다.

실용적인 사용 사례

유지 관리 오버헤드가 높은 테이블 식별

SELECT relname,
       round(total_vacuum_time::numeric, 2)      AS vacuum_ms,
       round(total_autovacuum_time::numeric, 2)  AS autovacuum_ms,
       round(total_analyze_time::numeric, 2)     AS analyze_ms,
       round(total_autoanalyze_time::numeric, 2) AS autoanalyze_ms
FROM pg_stat_user_tables
ORDER BY (total_vacuum_time + total_autovacuum_time) DESC
LIMIT 10;

수동 및 자동 유지 관리 시간 비교

total_vacuum_time에 비해 total_autovacuum_time이 매우 높은 테이블은 autovacuum 임계값을 튜닝하면 도움이 될 수 있습니다. 둘 다 높은 테이블은 팽창, 인덱스 상태, 쓰기 패턴을 점검해볼 필요가 있습니다.

리소스 정리

이 글을 따라하면서 데이터베이스 객체를 생성한 경우, 다음 명령을 실행하여 제거합니다.

-- Remove tables created in this post
DROP TABLE IF EXISTS customer_orders;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS vacuum_demo;
DROP TABLE IF EXISTS timing_demo;

-- Reset any session-level parameter changes
RESET work_mem;

이 안내를 위해 특별히 Aurora PostgreSQL 클러스터 또는 PostgreSQL용 RDS 인스턴스를 생성했고 더 이상 필요하지 않은 경우, 지속적인 요금이 발생하지 않도록 인스턴스를 삭제합니다. 자세한 내용은 DB 인스턴스 삭제 또는 Aurora DB 클러스터 삭제를 참조하세요. 마찬가지로, PostgreSQL 클라이언트 연결만을 위해 생성한 Amazon EC2 인스턴스가 있다면 종료합니다.

결론

스킵 스캔(skip scan)을 사용하면, 기존 복합 인덱스가 추가 인덱스를 유지 관리하지 않고도 이전에는 처리할 수 없었던 쿼리를 처리할 수 있게 되었습니다. 향상된 EXPLAIN은 CTE 튜닝을 추측에서 데이터 기반 결정으로 바꿔줍니다. 셀프 조인 제거는 ORM이 생성하는 비효율성을 자동으로 해결합니다. 그리고 새로운 autovacuum 제어 기능은 테이블별 수동 구성 없이도 가장 큰 테이블을 효과적으로 유지하는 데 도움이 됩니다. 이러한 개선 사항은 현재 Aurora PostgreSQL 호환 버전과 PostgreSQL용 Amazon RDS에서 모두 사용할 수 있습니다.

2부에서는 MD5 암호 지원과 같은 보안 향상 사항, 병렬 워커 및 복제에 대한 모니터링 개선 사항, 개발자를 위한 새로운 uuidv7() 함수, 업그레이드 중 옵티마이저 통계 보존, 논리적 복제 향상 사항을 다룹니다. 시작하려면 PostgreSQL 18에서 Aurora PostgreSQL DB 클러스터 또는 PostgreSQL용 RDS DB 인스턴스를 생성하고 오늘 바로 이러한 기능을 사용해 보세요.

AWS 기술 블로그