[PostgreSQL] BRIN 인덱스로 200만 행 시계열을 24kB로 색인한다

설치·접속: PostgreSQL 설치와 접속

부제: 센서 로그처럼 시간순으로 계속 쌓이기만 하는 대용량 테이블에서, 특정 날짜 구간만 뽑을 때

-- 시간순 append-only 테이블에 BRIN
CREATE INDEX idx_events_ts_brin ON demo_events USING brin(ts);

SELECT count(*), avg(temperature) FROM demo_events
  WHERE ts >= '2025-01-10' AND ts < '2025-01-11';

-- 인덱스 크기: 같은 컬럼 btree 43MB  vs  BRIN 24kB (약 1800배 차이)
 Aggregate (actual time=12.359..12.360 rows=1 loops=1)
   Buffers: shared hit=5 read=640
   ->  Bitmap Heap Scan on demo_events (actual time=1.172..7.977 rows=86400 loops=1)
         Heap Blocks: lossy=640
         ->  Bitmap Index Scan on idx_events_ts_brin (actual time=0.028..0.028 rows=6400 loops=1)
 Execution Time: 12.38 ms
-- 인덱스 없이 같은 쿼리: Parallel Seq Scan, 12800 buffers, 42.3 ms

BRIN(Block Range INdex)은 각 행이 아니라 블록 묶음마다 min/max 값만 저장한다. 그래서 200만 행짜리 btree가 43MB일 때 BRIN은 24kB로 끝난다. 대신 조회 시 "이 블록 범위에 답이 있을 수 있다"까지만 좁히고 실제 필터는 heap에서 하므로(Heap Blocks: lossy) 정밀도는 낮다. 이게 통하는 전제는 물리적 저장 순서와 컬럼 값의 순서가 일치하는 것 — 시간순으로 append되는 로그/이벤트/주문 이력이 딱 그렇다. 값이 뒤죽박죽 섞여 저장되면 블록마다 min/max 폭이 넓어져 무용지물이 된다. 대용량 append-only 테이블에서 색인 크기를 극단적으로 아끼고 싶을 때 쓴다.

이렇게도 쓴다

블록 묶음 크기를 줄여 정밀도를 올린다. (기본 128 → 32)

CREATE INDEX idx_ts_brin32 ON demo_events USING brin(ts) WITH (pages_per_range=32);

 

여러 컬럼을 한 BRIN에 묶는다. 모두 물리 순서와 상관관계가 있어야 유효.

CREATE INDEX idx_multi_brin ON demo_events USING brin(ts, device_id);

 

대량 적재 후 요약본을 다시 계산해 인덱스를 최신화한다. (조합: brin_summarize_new_values)

SELECT brin_summarize_new_values('idx_events_ts_brin');

 

같은 컬럼 btree와 크기를 나란히 비교한다. (조합: pg_relation_size)

SELECT pg_size_pretty(pg_relation_size('idx_events_ts_brin'));

 

자동 요약을 켜서 새로 쌓인 블록이 바로 색인되게 한다. (조합: autosummarize)

CREATE INDEX idx_auto ON demo_events USING brin(ts) WITH (autosummarize=on);

 

BRIN이 실제로 선택되는지, lossy 블록 수가 얼마인지 본다. (조합: EXPLAIN ANALYZE)

EXPLAIN (ANALYZE) SELECT count(*) FROM demo_events
  WHERE ts >= '2025-02-01' AND ts < '2025-02-02';