PostgreSQL bloat 재기와 pg_repack 온라인 정리
협력사 상품 가격을 한 시간마다 받아 와서 상품별로 한 행씩 덮어쓰는 스냅숏 테이블이 있었다. 행 수는 200만 건 언저리에서 거의 늘지 않았는데, 반년쯤 지나 백업 파일이 이상하게 커져서 보니 그 테이블 하나가 처음 크기의 네 배 가까이 되어 있었다. autovacuum은 꼬박꼬박 돌고 있었다. 죽은 행은 치워지는데 파일은 줄지 않았다.
PostgreSQL의 UPDATE는 기존 행을 고치지 않고 새 버전을 하나 더 쓴다. 옛 버전은 VACUUM이 치우지만 그 자리는 테이블 안의 빈칸으로 남을 뿐 운영체제에 돌려주지 않는다. 이렇게 쌓인 빈칸이 bloat다. 아래는 그 테이블의 bloat를 재 보고, VACUUM FULL 대신 pg_repack으로 줄이다가 한 번 실패한 기록이다.
n_dead_tup만 봐서는 bloat가 안 보인다
처음 본 것은 통계 뷰였다.
SELECT relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
pg_size_pretty(pg_table_size(relid)) AS table_size,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;dead_pct는 2%도 안 돼서 문제없다고 보고 하루를 넘겼다. 그런데 이 숫자로는 bloat가 보이지 않는다. VACUUM이 죽은 행을 치우면 n_dead_tup은 0 가까이 내려가고, 파일을 부풀린 것은 치운 뒤 남은 빈 공간이기 때문이다. 이 뷰는 autovacuum이 밀리는지 볼 때 쓰고, 부푼 정도는 다른 도구로 잰다.
pgstattuple로 정확하게 재기
정확한 숫자는 contrib 확장인 pgstattuple이 준다.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT pg_size_pretty(table_len) AS table_len,
tuple_percent,
dead_tuple_percent,
free_percent
FROM pgstattuple('public.price_snapshots');tuple_percent는 살아 있는 행, dead_tuple_percent는 아직 치우지 않은 죽은 행, free_percent는 빈 공간의 비율이다. 셋을 더해도 100이 안 되는 건 페이지 헤더와 행 포인터, 정렬용 패딩이 빠지기 때문이다. 내 테이블은 free_percent가 70을 넘겼다. 파일의 3분의 2 이상이 빈칸이었다.
대신 pgstattuple은 테이블 전체를 읽는다. 읽기 잠금만 잡아 쓰기는 막지 않지만 I/O가 만만치 않다. 40GB짜리 로그 테이블에 낮 시간에 돌렸다가 디스크 읽기 그래프가 치솟아 급히 취소한 뒤로, 큰 테이블에는 pgstattuple_approx를 쓴다. visibility map에서 all-visible인 페이지는 건너뛰고 빈 공간은 free space map에서 가져오므로, 죽은 행 수치만 정확하고 나머지는 추정값이다.
SELECT pg_size_pretty(table_len) AS table_len,
scanned_percent,
approx_free_percent,
dead_tuple_percent
FROM pgstattuple_approx('public.price_snapshots'::regclass);두 함수는 기본으로 pg_stat_scan_tables 역할만 실행할 수 있어서 모니터링 계정에는 GRANT pg_stat_scan_tables TO monitoring_user;로 권한을 준다.
통계 기반 추정 쿼리는 어디까지 믿을까
ioguix가 공개한 pgsql-bloat-estimation 쿼리는 테이블을 읽지 않고 pg_stats 통계로 원래 차지해야 할 크기를 계산해 실제 크기와의 차이를 보여 준다. 데이터베이스 전체를 몇 초 만에 훑을 수 있어 후보를 고를 때 편하다.
한계는 그 저장소의 README에 적혀 있다. 정렬 패딩을 계산하지 못해 늘 bloat에 섞이고, TOAST로 빠진 긴 값은 통계에 보이지 않으며, 페이지 수가 적은 작은 테이블은 비율이 부풀어 보인다. jsonb 컬럼이 큰 테이블에 돌렸더니 bloat가 음수로 나와 한참 고개를 갸웃했다.
| 방법 | 테이블 스캔 | 정확도 | 쓰는 때 |
|---|---|---|---|
pg_stat_user_tables | 없음 | bloat는 안 보임 | autovacuum 밀림 확인 |
| 통계 기반 추정 쿼리 | 없음 | 대략, 오차 큼 | 전체 후보 고르기 |
pgstattuple_approx | 일부 | 죽은 행은 정확, 나머지 추정 | 큰 테이블 확인 |
pgstattuple | 전체 | 정확 | 정리 직전 최종 확인 |
나는 추정 쿼리로 후보를 고르고 pgstattuple로 확인한 뒤에야 정리 작업을 잡는다.
VACUUM FULL이 막는 것
빈 공간을 운영체제에 돌려주는 가장 간단한 길은 VACUUM FULL이다. 테이블을 새 파일에 다시 쓰는데, 공식 문서대로 작업 내내 ACCESS EXCLUSIVE 잠금을 잡아 SELECT까지 기다리게 하고 디스크도 테이블 크기만큼 더 쓴다.
전에 2GB쯤 되는 테이블이라 금방 끝나리라 보고 새벽에 돌린 적이 있다. 하필 그 테이블을 읽는 긴 통계 쿼리가 있었고, VACUUM FULL이 그걸 기다리는 동안 뒤에 선 평범한 API 조회까지 줄줄이 막혀 몇 분 동안 타임아웃 알림이 쏟아졌다. 테이블을 다시 쓰는 DDL의 잠금 이야기는 ALTER TABLE을 다룬 글에도 적었는데, 같은 실수를 다른 명령으로 되풀이한 셈이다.
pg_repack이 하는 일과 디스크 계산
pg_repack은 같은 결과를 거의 온라인으로 얻는다.
- 잠깐 ACCESS EXCLUSIVE를 걸고
repack_trigger트리거와repack.log_<oid>로그 테이블을 만든다. - SHARE UPDATE EXCLUSIVE만 쥔 채 새 테이블로 행을 복사하고 인덱스를 만든다. 그동안 쓰기는 그대로 돌고, 바뀐 내용은 트리거가 로그 테이블에 적는다.
- 쌓인 로그를 새 테이블에 반영한다.
- 다시 잠깐 ACCESS EXCLUSIVE를 걸고 두 테이블을 바꿔치기한 뒤 옛 테이블을 지운다.
PostgreSQL 9.5 이상(문서 기준 18까지 지원)이어야 하고, 대상 테이블에 기본키나 NOT NULL 컬럼의 유니크 인덱스가 있어야 한다. 슈퍼유저나 소유자만 실행할 수 있고, 서버 확장과 클라이언트 버전이 다르면 시작부터 거부한다. 디스크는 테이블과 인덱스를 합친 크기의 두 배쯤 비어 있어야 하며, 쓰기가 많으면 로그 테이블도 자라니 여유를 더 둔다.
SELECT pg_size_pretty(pg_total_relation_size('public.price_snapshots')) AS total_with_indexes;# 무엇을 할지 먼저 확인
pg_repack --dry-run -t public.price_snapshots -d shop
# 실제 실행: 잠금을 60초 안에 못 잡으면 다른 쿼리를 죽이지 않고 건너뜀
pg_repack -t public.price_snapshots --no-order --wait-timeout=60 --no-kill-backend -d shop--wait-timeout 기본값은 60초이고, 그 시간이 지나면 잠금을 막는 쿼리를 강제로 취소한다. 남의 쿼리를 죽이느니 건너뛰는 편이 마음 편해서 --no-kill-backend를 붙인다. --no-order는 정렬 없이 VACUUM FULL처럼 다시 쓰라는 옵션이다.
중간에 끊겼을 때 치우는 법
첫 pg_repack은 실패했다. 디스크 여유를 테이블 크기만큼만 보고 시작했다가 인덱스를 만드는 도중에 디스크 경보가 울려 Ctrl+C를 눌렀다. 공간은 돌아왔지만 원본에 붙은 repack_trigger가 그대로 남아, 모든 쓰기가 아무도 읽지 않는 로그 테이블에 계속 쌓이고 있었다. 다음 날 다시 돌리자 트리거가 이미 있다는 경고와 함께 작업이 멈췄다. bloat를 줄이려다 쓰기 비용을 하나 늘려 놓은 셈이었다.
남은 것부터 확인한다.
-- 원본에 남은 트리거
SELECT tgname
FROM pg_trigger
WHERE tgrelid = 'public.price_snapshots'::regclass
AND tgname LIKE 'repack%';
-- repack 스키마에 남은 작업용 테이블
SELECT tablename FROM pg_tables WHERE schemaname = 'repack';
-- 만들다 만 인덱스
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;문서가 권하는 정리법은 확장을 지우고 다시 만드는 것이다.
DROP EXTENSION pg_repack CASCADE;
CREATE EXTENSION pg_repack;CASCADE는 repack 스키마의 로그 테이블과 원본의 트리거까지 걷어 내니, 실행 전에 위 조회 결과로 무엇이 지워질지 눈으로 확인한다. 유효하지 않은 인덱스는 직접 DROP INDEX로 지워야 한다. 두 번째 실행은 디스크를 두 배 넘게 비워 두고 시작했고 30분쯤 걸려 끝났다.
분기 점검 목록에 넣은 순서
그 일 뒤로 분기마다 하는 DB 점검 목록에 bloat 항목을 넣었다. 지난 9월 점검에서도 추정 쿼리로 상위 후보를 뽑고 큰 테이블만 pgstattuple_approx로 확인했다. free_percent가 높아도 행 수가 일정한 테이블은 빈칸을 재사용하며 일정 크기에서 멈추는 경우가 많아 그냥 둔다. 크기가 계속 늘거나 크게 지운 뒤 다시 채워질 일이 없는 테이블만 pg_repack 대상으로 잡는다. 실행 전에는 pg_total_relation_size의 두 배가 비었는지와 --dry-run 결과를 보고, 끝나면 트리거 조회를 한 번 더 한다.
그래도 pg_repack은 증상을 지우는 도구다. 가격 스냅숏 테이블은 가격이 그대로여도 매시간 모든 행을 덮어쓰고 있었다. UPDATE에 WHERE price IS DISTINCT FROM EXCLUDED.price 조건을 붙이자 실제로 바뀌는 행이 몇 퍼센트로 줄었고, 그 뒤로 테이블은 다시 부풀지 않았다. 정리 도구보다 죽은 행을 덜 만드는 쿼리에 시간을 더 쓰게 된 이유다.