PostgreSQL 18에서 새로 추가된 통계 덤프/복원 기능을 이용해 프로덕션의 옵티마이저 통계를 어디서든 주입하고, CI·로컬에서도 프로덕션과 같은 쿼리 플랜을 재현하는 방법을 설명한다.
이전 글(previous article)에서는 PostgreSQL 플래너가 pg_class와 pg_statistic을 읽어 행 수를 추정하고, 조인 전략을 고르며, 인덱스 스캔이 가치 있는지 결정하는 방식을 다뤘습니다. 메시지는 명확했습니다. 통계가 틀리면, 나머지도 전부 같이 틀어집니다.
스트리밍 복제는 비트 단위 그대로 복제를 제공하므로, 모든 리플리카는 기본(프라이머리) 서버와 동일한 통계를 공유합니다.
하지만 우리가 다루지 않은 한 가지가 있습니다. 통계는 그것을 생성한 데이터베이스 클러스터에 종속적입니다. 통계를 채우는 주된 방법은 실제 데이터가 필요로 하는 ANALYZE입니다.
PostgreSQL 18이 이를 바꿨습니다. pg_restore_relation_stats와 pg_restore_attribute_stats라는 두 개의 새 함수가 카탈로그 테이블에 숫자를 직접 기록합니다. pg_dump --statistics-only와 결합하면, 옵티마이저 통계를 배포 가능한 아티팩트로 취급할 수 있습니다. 작고, 이식 가능하며, 순수 SQL입니다.
이 기능은 업그레이드 사용 사례에 의해 추진되었습니다. 과거에는 메이저 버전 업그레이드를 하면 pg_statistic이 비어 버려 ANALYZE를 돌려야 했습니다. 대형 클러스터에서는 몇 시간이 걸릴 수도 있습니다. PostgreSQL 18에서는 업그레이드 시 통계를 자동으로 이전합니다. 하지만 이건 시작일 뿐입니다. 같은 논리를 통해 프로덕션에서 통계를 내보내어 어디에든 주입할 수 있습니다. 테스트 DB, 로컬 디버깅, 또는 CI 파이프라인의 일부로도요.
CI 데이터베이스에는 1,000행이 있습니다. 프로덕션에는 5천만 행이 있습니다. 플래너는 각 경우에 완전히 다른 결정을 내립니다. CI에서 EXPLAIN을 실행해 봐야 프로덕션 플랜에 대해서는 아무것도 알려주지 못합니다. 이것이 RegreSQL의 핵심 전제입니다. 플래너가 프로덕션 규모의 통계를 보게 만들면, CI에서 쿼리 플랜 회귀를 잡아내는 일이 훨씬 더 신뢰할 만해집니다.
디버깅에도 동일하게 적용됩니다. 프로덕션에서 쿼리가 느려서 로컬에서 플랜을 재현하고 싶은데, 내 데이터베이스는 통계가 다르고 플래너는 예상 가능한 경로를 선택합니다. 프로덕션 통계를 옮겨오면 실제로 프로덕션에 접속하지 않고도, 프로덕션에서 플래너가 해야 하는 사고의 스냅샷을 제공받을 수 있습니다.
이식 가능한 PostgreSQL 통계를 위한 첫 번째 함수는 pg_restore_relation_stats입니다. 이 함수는 테이블 수준의 데이터를 가변 인자(name/value) 쌍 형태로 pg_class에 직접 기록합니다.
SELECT pg_restore_relation_stats(
'schemaname', 'public',
'relname', 'orders',
'relpages', 123513::integer,
'reltuples', 50000000::real,
'relallvisible', 123513::integer,
'relallfrozen', 120000::integer
);
하지만 이건 그냥 예시일 뿐입니다. 실제 통계를 조금 바꿔서 전체 가치를 확인해 봅시다. 작은 테이블을 만들고, 가짜 프로덕션 유사 통계를 주입한 뒤, 플래너가 마음을 바꾸는 것을 관찰하겠습니다.
CREATE TABLE test_orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
amount numeric(10,2) NOT NULL,
status text NOT NULL DEFAULT 'pending',
created_at date NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO test_orders (customer_id, amount, status, created_at)
SELECT
(random() * 9999 + 1)::int,
(random() * 5000 + 5)::numeric(10,2),
(ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)::int],
'2024-01-01'::date + (random() * 365)::int
FROM generate_series(1, 10000);
CREATE INDEX ON test_orders (created_at);
CREATE INDEX ON test_orders (status);
ANALYZE test_orders;
현재 통계를 확인하면, 예상 가능한 데이터가 들어 있습니다.
SELECT relname, relpages, reltuples
FROM pg_class WHERE relname = 'test_orders';
relname | relpages | reltuples
-------------+----------+-----------
test_orders | 74 | 10000
(1 row)
74페이지에 10,000행이면, 플래너는 순차 스캔을 선택합니다.
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on test_orders (cost=0.00..199.00 rows=5891 width=26)
Filter: (created_at > '2024-06-01'::date)
(2 rows)
이제 프로덕션 규모의 테이블 통계를 주입해 봅시다.
SELECT pg_restore_relation_stats(
'schemaname', 'public',
'relname', 'test_orders',
'relpages', 123513::integer,
'reltuples', 50000000::real,
'relallvisible', 123513::integer
);
그리고 결과에 놀랄 수도 있습니다.
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01';
QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_orders (cost=0.00..448.45 rows=17649 width=26)
Filter: (created_at > '2024-06-01'::date)
플래너는 여전히 순차 플랜을 사용합니다. 바뀐 것은 추정 행 수뿐입니다. 왜 그럴까요? 이전 글을 기억한다면, 여기서 컬럼 수준 통계가 중요해지는 지점입니다. 히스토그램 경계가 우리가 삽입한 원래 10,000행에 맞춰져 있기 때문입니다.
이 함수는 컬럼 수준 통계를 pg_statistic에 기록합니다. pg_statistic은 ANALYZE가 채우는 카탈로그로, MCV, 히스토그램, 상관관계 등을 담습니다.
이전 섹션에서는 플래너가 테이블에 5천만 행이 있다고 믿으면서도 순차 스캔에 머물러 있었습니다. 빠진 조각은 컬럼 수준 통계입니다. 이어서 created_at에 대한 히스토그램 경계를 주입해 봅시다.
SELECT pg_restore_attribute_stats(
'schemaname', 'public',
'relname', 'test_orders',
'attname', 'created_at',
'inherited', false::boolean,
'null_frac', 0.0::real,
'avg_width', 4::integer,
'n_distinct', -0.05::real,
'histogram_bounds', '{2019-01-01,2019-07-01,2020-01-01,2020-07-01,2021-01-01,2021-07-01,2022-01-01,2022-07-01,2023-01-01,2023-07-01,2024-01-01}'::text,
'correlation', 0.98::real
);
이제 플래너는 데이터가 5년 범위에 걸쳐 있다는 것을 압니다. 2024년 마지막 6개월을 필터링하는 쿼리는 좁은 구간만을 다룹니다.
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Index Scan using test_orders_created_at_idx on test_orders (cost=0.29..153.21 rows=6340 width=26)
Index Cond: (created_at > '2024-06-01'::date)
히스토그램 경계는 데이터의 비-MCV 부분을 동일한 개체 수를 가진 버킷으로 나눕니다. most_common_vals가 데이터 대부분을 차지한다면, 히스토그램은 남은 꼬리(tail) 부분만을 덮습니다. 버킷 수는 default_statistics_target으로 제어됩니다(기본값 100, 즉 경계 101개).
그리고 이게 바로 플랜 뒤집기입니다. 히스토그램은 플래너에게 데이터가 2019–2024에 걸쳐 있음을 알려주므로 > '2024-06-01'은 좁은 꼬리 구간에 매칭됩니다. 5천만 행 중 작은 일부죠. 이전에는 무시되던 인덱스 스캔이 이제는 명백한 선택이 됩니다. 테이블 수준 통계가 스케일을 정했고, 컬럼 수준 통계가 선택도를 형성했으며, 둘이 함께 플랜을 바꿨습니다.
correlation 통계는 물리적 행 순서가 컬럼의 정렬 순서와 얼마나 밀접하게 맞는지를 플래너에게 알려줍니다. 1.0에 가까운 값은 순차적인 접근 패턴을 의미하며, 다음 행이 같은 페이지 또는 인접 페이지에 있을 가능성이 크기 때문에 인덱스 스캔을 더 싸게 만듭니다. created_at처럼 행이 시간 순으로 삽입되는 시계열 데이터에서는 상관관계가 보통 매우 높습니다.
같은 함수로 MCV 리스트도 다룰 수 있습니다. 프로덕션에서 status 컬럼은 균등하지 않습니다. 주문의 95%는 delivered이고, 1.5%는 pending일 수 있습니다.
SELECT pg_restore_attribute_stats(
'schemaname', 'public',
'relname', 'test_orders',
'attname', 'status',
'inherited', false::boolean,
'null_frac', 0.0::real,
'avg_width', 9::integer,
'n_distinct', 5::real,
'most_common_vals', '{delivered,shipped,cancelled,pending,returned}'::text,
'most_common_freqs', '{0.95,0.015,0.015,0.015,0.005}'::real[]
);
다음을 보면
EXPLAIN SELECT * FROM test_orders WHERE status = 'pending';
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on test_orders (cost=8.93..90.42 rows=599 width=27)
Recheck Cond: (status = 'pending'::text)
-> Bitmap Index Scan on test_orders_status_idx (cost=0.00..8.78 rows=599 width=0)
Index Cond: (status = 'pending'::text)
(4 rows)
다음과 비교할 수 있습니다.
EXPLAIN SELECT * FROM test_orders WHERE status = 'delivered';
QUERY PLAN
------------------------------------------------------------------
Seq Scan on test_orders (cost=0.00..448.45 rows=28458 width=27)
Filter: (status = 'delivered'::text)
(2 rows)
같은 컬럼, 같은 연산자, 다른 플랜입니다. 플래너는 pending에 대해서는 비트맵 인덱스 스캔을 사용합니다(1.5%는 인덱스를 정당화할 만큼 충분히 희귀). 그리고 delivered에 대해서는 순차 스캔을 사용합니다(95%가 테이블 대부분이기 때문). MCV 리스트에서 나온 선택도 비율이 플랜 선택을 좌우합니다.
5천만 행 테이블치고는 행 추정치(599와 28,458)가 기대보다 낮다는 것을 눈치챘을 수도 있습니다. 플래너는 실제 물리 파일 크기를 확인합니다. 우리 테이블은 디스크에 74페이지뿐이며, 우리가 주입한 123,513페이지가 아닙니다. 따라서 플래너는 reltuples를 비례해서 축소합니다. 절대값은 줄어들지만, 그 사이의 _비율_은 올바르게 유지되고, 플랜의 형태를 결정하는 것은 바로 그 비율입니다. 실제로 pg_dump --statistics-only를 사용할 때는 보통 비슷한 데이터 볼륨을 가진 데이터베이스로 복원하게 되므로, 추정치도 자연스럽게 맞아떨어집니다.
앞에서 다룬 함수들은 메커니즘입니다. 운영 관점에서는 pg_dump가 필요한 모든 것을 제공합니다. PostgreSQL 18에는 세 가지 플래그가 추가되었습니다.
| Flag | Effect |
|---|---|
--statistics | 통계를 덤프합니다(명시적으로 요청해야 함) |
--statistics-only | 스키마나 데이터가 아니라 통계만 덤프합니다 |
--no-statistics | 통계를 덤프하지 않습니다 |
프로덕션 데이터베이스의 통계를 내보낼 때
pg_dump --statistics-only -d production_db > stats.sql
출력은 SELECT pg_restore_relation_stats(...)와 SELECT pg_restore_attribute_stats(...) 호출들의 연속이라는 것을 보게 될 것입니다. 위에서 설명한 그대로입니다.
프로덕션 데이터를 테스트 가능한 플랜으로 바꾸는 전체 워크플로는 대략 이렇게 생겼을 수 있습니다.
# 1. dump schema from production
pg_dump --schema-only -d production_db > schema.sql
# 2. dump statistics from production
pg_dump --statistics-only -d production_db > stats.sql
# 3. create test database with schema
createdb test_db
psql -d test_db -f schema.sql
# 4. load fixture data (optional; masked, minimal)
psql -d test_db -f fixtures.sql
# 5. inject production statistics
psql -d test_db -f stats.sql
# 6. query plans now match production
psql -d test_db -c "EXPLAIN SELECT * FROM test_orders WHERE status = 'pending'"
통계 덤프는 매우 작습니다. 수백 개의 테이블과 수천 개의 컬럼이 있는 데이터베이스라도 통계 덤프는 1MB 미만으로 나옵니다. 프로덕션 데이터는 수백 GB일 수 있지만, 그것을 설명하는 통계는 텍스트 파일 하나에 들어갑니다.
이제 “함정은 어디 있지?”라고 생각할 수 있습니다. 큰 함정이 하나 있습니다. 오토바큐움이 언젠가 동작해서 ANALYZE를 실행합니다. 그러면 주입한 통계를 실제 숫자로 덮어써 버리고, 다시 원점으로 돌아갑니다.
이를 막으려면 주입한 테이블에서 autovacuum analyze를 비활성화하세요.
-- disable autovacuum
ALTER TABLE test_orders SET (autovacuum_enabled = false);
-- or set analyze threshold so high it nevers kicks-in
ALTER TABLE test_orders SET (autovacuum_analyze_threshold = 2147483647);
여기서는 주의가 필요합니다.
개발 환경에서 이 테이블들에 데이터를 쓰기도 한다면(마이그레이션 실행, 픽스처 로드, 삽입 테스트 등), 주입된 통계는 쓰기 작업이 일어날 때마다 현실과 점점 더 멀어집니다. 플래너는 더 이상 로컬 데이터와 맞지 않는 프로덕션 분포를 기반으로 계획을 세우게 됩니다.
읽기 전용 쿼리 플랜 테스트에서는 이것이 바로 원하는 동작입니다. 데이터를 수정하는 통합 테스트에서는, 각 테스트 실행 후 통계를 다시 주입해야 할 수도 있습니다.
그리고 제발, 절대로 프로덕션에서 이런 일을 하지 마세요!
앞서 봤듯이 relpages를 주입하려고 애쓸 가치는 없습니다. 플래너가 실제 파일 크기를 확인하고 비례해서 스케일링하기 때문입니다. 이는 플래너가 추정할 수 있는 절대 행 수의 상한을 제한합니다. 즉, 프로덕션 환경과 비슷한 숫자를 얻으려면 여전히 비슷한 데이터 볼륨을 만들어야 합니다(이 기능의 주요 사용 사례인 백업 복원 얘기라면 문제되지 않습니다).
또한 다변량 상관관계, 컬럼 그룹을 가로지르는 distinct count, 컬럼 조합에 대한 MCV 리스트를 위해 사용하는 CREATE STATISTICS는 PostgreSQL 18 범위에 포함되지 않는다는 점도 알아둘 만합니다. 이것들은 복원 후에도 ANALYZE가 필요합니다. PostgreSQL 19에서는 pg_restore_extended_stats()로 이 격차를 메울 예정입니다.
복원 함수들은 대상 테이블에 대한 MAINTAIN 권한이 필요합니다. 이는 PostgreSQL 17에서 도입된 ANALYZE, VACUUM, REINDEX, CLUSTER에 필요한 권한과 동일합니다.
자동화를 위해 이를 부여하는 가장 쉬운 방법은 다음과 같습니다.
GRANT pg_maintain TO ci_service_account;
이 권한은 데이터베이스의 모든 테이블에 대해 MAINTAIN을 부여합니다. CI 파이프라인이 슈퍼유저 없이도 통계를 주입하기에 충분합니다.