JSONB·파티션·유지보수

목표

jsonb 연산자와 경로 질의, 생성 열, 범위 파티션(가지치기·붙이기/떼기·DEFAULT), 죽은 튜플과 VACUUM, 그리고 pg_stat_statements 로 상위 질의 뽑기까지 — 개발자가 매주 만나는 심화 주제를 한 실습에서 다룹니다.

왜 중요한가

jsonb 는 유연하지만 꺼내는 법을 모르면 순차 스캔만 하게 되고, 큰 시계열 표는 파티션 없이는 오래된 데이터를 지우는 데도 하루가 걸립니다. 갱신이 쌓이면 표가 부푸는데 그 회수를 이해하지 못하면 원인을 표 안에서만 찾다 놓칩니다. 이 도구들은 대용량·문서 데이터를 다루는 현장의 기본기입니다.

환경

이 파드는 postgres 계정으로 돕니다. psql 만 치면 labdb 에 붙습니다.

export PATH=/usr/lib/postgresql/16/bin:$PATHexport PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdbpsql

실습 표(orders_doc·sensor_readings_src)는 1단계에서 /opt/lab/fixtures/pg/jsonb_partition.sql 을 적재해 만듭니다. 8단계의 pg_stat_statements 는 서버 재시작이 필요한데, 이 파드에서는 postgres 사용자가 직접 재시작할 수 있습니다: pg_ctl -D /var/lib/postgresql/data restart. 산출물은 /root/jp/ 아래에 둡니다.

단계

1. 픽스처(orders_doc·sensor_readings_src)를 적재하고 행 수를 확인합니다.
2. jsonb 연산자로 세 가지를 세어 /root/jp/02-jsonb.txt 에 남깁니다.
3. doc 에서 tier 를 뽑는 생성 열을 만듭니다.
4. sensor_readings 를 ts 범위로 파티션하고 원본을 적재합니다.
5. DEFAULT 에 쌓인 5월 데이터에 전용 파티션을 붙입니다.
6. 2월 범위 질의로 파티션 가지치기를 확인합니다.
7. 한 파티션을 갱신해 부풀린 뒤 VACUUM 으로 회수하고 /root/jp/07-bloat.txt 에 남깁니다.
8. pg_stat_statements 를 켜고 상위 질의를 뽑아 /root/jp/08-top.txt 에 남깁니다.

참고

• jsonb 는 ->(jsonb)·->>(텍스트)·@>(담김)·?(키/원소 존재)·jsonb_array_length 를 씁니다.
• 파티션 키(ts)로 거르는 질의만 가지치기됩니다. 파티션 키가 아닌 열로 거르면 모든 파티션을 훑습니다.
• 8단계 재시작은 모든 연결을 끊습니다. 앞 단계의 백그라운드 세션이 없을 때 하세요.

단계 8개

1. 픽스처를 적재하고 행 수를 본다
2. jsonb 연산자와 경로 질의
3. 생성 열로 jsonb 값 뽑기
4. ts 범위 파티션 표 만들기
5. DEFAULT 에서 5월을 떼어 전용 파티션으로
6. 파티션 가지치기를 EXPLAIN 으로 확인
7. 부풀림과 VACUUM 회수
8. pg_stat_statements 로 상위 질의