PostgreSQL 고급 인덱싱: 부분, 포함, BRIN 및 GIN
1열 B-Tree 인덱스는 성능 문제의 80%에 대한 솔루션입니다. 하지만 20% 나머지는 더 정교한 도구가 필요합니다. "활성" 행만 인덱싱하는 부분 인덱스 인덱스를 95% 줄이고, 포함 인덱스는 힙 테이블에 대한 액세스를 완전히 제거합니다. 시계열 테이블의 BRIN은 B-Tree보다 100배 적은 공간을 차지하며 GIN은 B-트리로는 수행할 수 없는 전체 텍스트 및 배열 검색입니다. 이 가이드에서는 네 가지 유형을 모두 다룹니다.
무엇을 배울 것인가
- 부분 인덱스: 가장 빈번한 쿼리에 중요한 행만 인덱스합니다.
- INCLUDE로 인덱스 커버링: SELECT 쿼리에서 인덱스 전용 스캔 활성화
- BRIN 인덱스: 수백만 개의 행이 있는 시계열 테이블용
- GIN 인덱스: 전체 텍스트 검색, 배열 포함 및 JSONB 쿼리
- 각 시나리오에 맞는 올바른 인덱스 유형을 선택하는 방법
부분 인덱스: 중요한 것만 인덱스
부분 인덱스는 WHERE 조건을 만족하는 행만 인덱스합니다. 그리고 기술은 소수의 행이 대부분의 행을 수신하는 시나리오에 가장 강력합니다. 쿼리: "보류 중인" 주문, "읽지 않은" 메시지, "진행 중인" 작업.
-- Scenario: tabella ordini con 50 milioni di righe
-- 99% degli ordini ha stato 'completato' o 'cancellato'
-- Le query operative riguardano solo gli ordini 'pendenti' (50.000 righe)
-- Indice normale: 50 milioni di righe -> grande, lento da mantenere
CREATE INDEX idx_ordini_stato ON ordini (stato);
-- Dimensione: ~2GB, aggiornato ad ogni INSERT/UPDATE/DELETE
-- Partial index: solo gli ordini pendenti (50.000 righe)
CREATE INDEX idx_ordini_pendenti ON ordini (creato_il)
WHERE stato = 'pendente';
-- Dimensione: ~2MB (1000x piu piccolo!)
-- PostgreSQL lo usa automaticamente per query con WHERE stato = 'pendente'
-- Query che usa il partial index
EXPLAIN ANALYZE
SELECT id, utente_id, totale
FROM ordini
WHERE stato = 'pendente'
AND creato_il < NOW() - INTERVAL '1 hour';
-- -> Index Scan using idx_ordini_pendenti (2MB di indice vs 2GB!)
부분 고유 값에 대한 부분 인덱스
-- Caso d'uso: un utente puo avere al massimo un indirizzo "primario"
-- ma puo avere infiniti indirizzi non primari
-- Indice UNIQUE parziale: vincola l'unicita solo dove e_primario = true
CREATE UNIQUE INDEX idx_indirizzi_primary_unico
ON indirizzi (utente_id)
WHERE e_primario = true;
-- Ora questo INSERT fallisce (utente 42 ha gia un indirizzo primario)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Roma 1', true); -- ERROR: duplicate key value
-- Ma questo e permesso (indirizzi non primari non hanno il vincolo)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Milano 5', false); -- OK
-- Partial index per soft delete
CREATE INDEX idx_prodotti_attivi ON prodotti (categoria_id, prezzo)
WHERE deleted_at IS NULL;
-- Il 95% delle query filtra su deleted_at IS NULL
-- L'indice e 20x piu piccolo di uno su tutte le righe
INCLUDE로 인덱스 덮기: 힙 액세스 제거
인덱스 전용 스캔은 힙 테이블에 액세스하지 않고 인덱스에서 직접 데이터를 읽습니다.
가능한 가장 빠른 스캔 유형입니다. 이를 활성화하려면 인덱스에 다음이 모두 포함되어야 합니다.
SELECT에 나타나는 열입니다. 조항 INCLUDE "추가" 열 추가
B-Tree 구조에 포함하지 않고 인덱스에 포함(따라서 성능에 영향을 주지 않음)
인덱스 자체에 대한 INSERT/UPDATE).
-- Query frequente: lista ordini per utente con totale e data
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = $1
ORDER BY creato_il DESC;
-- Indice normale: solo la colonna di filtro
CREATE INDEX idx_ordini_utente ON ordini (utente_id, creato_il DESC);
-- Piano: Index Scan -> poi Heap Fetch per ogni riga (per leggere 'totale')
-- Performance: O(k * log N) dove k = righe per utente
-- Covering index: aggiunge 'totale' con INCLUDE
CREATE INDEX idx_ordini_utente_covering ON ordini (utente_id, creato_il DESC)
INCLUDE (totale);
-- Piano: Index Only Scan -> nessun Heap Fetch!
-- Performance: O(log N) + legge solo le pagine dell'indice
-- Verificare che usi Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = 42
ORDER BY creato_il DESC
LIMIT 50;
-- Output: "Index Only Scan using idx_ordini_utente_covering"
-- "Heap Fetches: 0" <- ottimo!
포함하면 도움이 되지 않습니다.
-- INCLUDE non aiuta se:
-- 1. La visibility map non e aggiornata (heap fetches necessari per MVCC)
-- Soluzione: VACUUM regolare
VACUUM VERBOSE ordini;
-- Verifica: "Index Only Scans: N" in pg_stat_user_indexes
-- 2. La tabella ha molti UPDATE non vacuumati
SELECT
relname,
n_dead_tup,
n_live_tup,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'ordini';
-- 3. Le colonne in INCLUDE sono troppo grandi (testo lungo, array, JSONB)
-- Il indice diventa enorme, peggio che un heap fetch
-- Regola pratica per INCLUDE:
-- - Colonne numeriche (int, bigint, numeric): OK
-- - Colonne brevi (varchar < 50 char): OK
-- - Colonne lunghe, JSONB, testo: meglio evitare
BRIN 지수: 시계열 테이블용
BRIN(Block Range INdex)은 데이터가 물리적으로 정렬되는 테이블을 위해 설계되었습니다. 열(일반적으로 삽입 타임스탬프) 각 값을 색인화하는 대신 BRIN은 각 페이지 블록의 최소/최대값만 저장합니다. 에 비해 엄청나게 작습니다 B-트리이지만 덜 정확함: 날짜 범위 스캔에 이상적입니다.
-- Tabella eventi IoT: 500 milioni di righe, inserite in ordine temporale
-- B-Tree su ts_evento: 15GB di indice
-- BRIN su ts_evento: 150KB di indice (100.000x piu piccolo!)
CREATE TABLE eventi_iot (
id BIGSERIAL,
device_id INT NOT NULL,
ts_evento TIMESTAMPTZ NOT NULL DEFAULT NOW(),
valore FLOAT8 NOT NULL,
metadata JSONB
) PARTITION BY RANGE (ts_evento);
-- Creare BRIN invece di B-Tree per la colonna temporale
CREATE INDEX idx_eventi_ts_brin ON eventi_iot USING BRIN (ts_evento)
WITH (pages_per_range = 128); -- default: 128 pagine per range
-- Query che sfrutta il BRIN
EXPLAIN ANALYZE
SELECT device_id, AVG(valore)
FROM eventi_iot
WHERE ts_evento BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY device_id;
-- "Bitmap Index Scan on idx_eventi_ts_brin"
-- "Recheck Cond: (ts_evento BETWEEN ...)"
-- BRIN su colonne fisicamente correlate (non necessariamente timestamp)
-- Esempio: log con ID auto-incrementale
CREATE INDEX idx_log_id_brin ON application_logs USING BRIN (log_id)
WITH (pages_per_range = 64);
BRIN 대 B-Tree: 선택 시기
-- Confronto pratico su tabella da 500M righe:
--
-- | B-Tree | BRIN
-- ---------|--------------|--------
-- Dimensione| 15 GB | 150 KB
-- Build time| 45 min | 2 min
-- Scan (1 settimana su 1 anno) | 0.1s | 0.8s
-- INSERT overhead | alto | minimo
-- UPDATE overhead | alto | minimo
-- BRIN e ottimo quando:
-- 1. La tabella cresce per append (log, eventi, transazioni finanziarie)
-- 2. I dati sono fisicamente correlati con la colonna indicizzata
-- 3. Lo spazio disco e un vincolo
-- 4. Le query sono sempre su range (non su valori singoli)
-- BRIN NON funziona bene quando:
-- 1. Gli INSERT non sono in ordine (rows sparse = BRIN inutile)
-- 2. Le query cercano valori singoli precisi
-- 3. La tabella ha molti UPDATE/DELETE che rovinano la correlazione fisica
-- Misurare la correlazione fisica (valore tra -1 e 1, vicino a 1 = BRIN ottimo)
SELECT
attname,
correlation
FROM pg_stats
WHERE tablename = 'eventi_iot'
AND attname = 'ts_evento';
-- Se correlation > 0.9: BRIN e molto efficiente
-- Se correlation < 0.5: BRIN poco utile, meglio B-Tree
GIN 인덱스: 전체 텍스트 검색, 배열 및 JSONB
GIN(Generalized Inverted INdex)은 PostgreSQL의 가장 다양한 인덱스 유형입니다. 이를 통해 다음을 수행할 수 있습니다. 색인 복합 콘텐츠 — 배열, tsVectors, JSONB — 콘텐츠의 각 요소 별도로 인덱싱됩니다. 전체 텍스트 검색 및 배열 쿼리에 필수적입니다.
전체 텍스트 검색을 위한 GIN
-- Full-text search su una tabella di articoli
-- Aggiungere una colonna tsvector calcolata (PostgreSQL 12+)
ALTER TABLE articoli ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('italian',
coalesce(titolo, '') || ' ' ||
coalesce(contenuto, '') || ' ' ||
coalesce(tags::text, '')
)
) STORED;
-- Creare l'indice GIN sulla colonna search_vector
CREATE INDEX idx_articoli_fts ON articoli USING GIN (search_vector);
-- Query full-text che usa il GIN
SELECT id, titolo, ts_rank(search_vector, query) AS rank
FROM articoli,
to_tsquery('italian', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- Con highlighting
SELECT
id,
titolo,
ts_headline('italian', contenuto, query,
'MaxFragments=3, MaxWords=30, MinWords=10') AS snippet
FROM articoli,
to_tsquery('italian', 'postgresql:* & (performance | ottimizzazione)') AS query
WHERE search_vector @@ query;
어레이 격리를 위한 GIN
-- Tabella prodotti con array di tag
CREATE TABLE prodotti (
id BIGSERIAL PRIMARY KEY,
nome TEXT NOT NULL,
tags TEXT[] NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_prodotti_tags ON prodotti USING GIN (tags);
-- Query con operatori array su GIN (molto piu veloci senza GIN)
-- @>: "contiene tutti questi tag"
SELECT id, nome
FROM prodotti
WHERE tags @> ARRAY['postgresql', 'performance'];
-- &&: "ha almeno uno di questi tag"
SELECT id, nome
FROM prodotti
WHERE tags && ARRAY['database', 'sql', 'nosql'];
-- Verifica che il GIN sia usato
EXPLAIN ANALYZE
SELECT id, nome FROM prodotti
WHERE tags @> ARRAY['postgresql'];
-- "Bitmap Index Scan on idx_prodotti_tags"
JSONB용 GIN
-- Indice GIN su colonna JSONB (indicizza ogni chiave/valore)
CREATE TABLE eventi (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
);
-- GIN con gin_trgm_ops (trigrammi): per LIKE e ricerche simili
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN con jsonb_path_ops (piu compatto ma solo per @> e @?)
CREATE INDEX idx_eventi_payload_path ON eventi
USING GIN (payload jsonb_path_ops);
-- GIN standard (supporta tutti gli operatori JSONB)
CREATE INDEX idx_eventi_payload ON eventi USING GIN (payload);
-- Query che usa il GIN JSONB
-- Trovare eventi con specifico tipo
EXPLAIN ANALYZE
SELECT id, payload->>'timestamp'
FROM eventi
WHERE payload @> '{"tipo": "acquisto", "canale": "mobile"}';
-- Differenza tra i due tipi GIN per JSONB:
-- jsonb_path_ops: 30-40% piu compatto, solo @> e jsonpath
-- default: supporta anche ?, ?|, ?&, @?, @@
-- Scegliere default se usi operatori di existence (?)
-- Scegliere jsonb_path_ops se usi solo @> (containment)
의사결정 지도: 선택할 지수
| 대본 | 지수 유형 | 이유 |
|---|---|---|
| PK 또는 FK에 대한 쿼리(동등) | B-트리(기본값) | =, <, >, 사이에 가장 적합 |
| 몇 개의 "활성" 행(예: 보류 중) | 부분 B-트리 | 100배 더 작은 인덱스 |
| 인덱스의 모든 열을 사용하여 SELECT | INCLUDES가 포함된 B-트리 | 인덱스 전용 스캔, 힙 액세스 없음 |
| 타임스탬프가 있는 추가 전용 테이블 | 브린 | B-트리보다 1000배 작음 |
| 이탈리아어/영어로 전체 텍스트 검색 | TSVector의 GIN | @@ 연산자를 위한 유일한 방법 |
| 배열 포함(@>, &&) | TEXT[]의 GIN | 배열 연산자를 위한 유일한 방법 |
| JSONB 포함(@>) | 진 jsonb_path_ops | 기본보다 30% 더 컴팩트함 |
| 텍스트의 '패턴%'처럼 | B-트리(varchar_pattern_ops) | LIKE 접두사만 |
| 텍스트의 '%pattern%'처럼 | pg_trgm을 사용한 GIN | 매체에서 LIKE를 할 수 있는 유일한 방법 |
지수 유지관리
-- Identificare indici non usati (candidati alla rimozione)
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scansioni,
pg_size_pretty(pg_relation_size(indexrelid)) AS dimensione
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Identificare indici duplicati (stesso set di colonne)
SELECT
t.relname AS tabella,
array_agg(i.relname) AS indici_duplicati,
pg_size_pretty(sum(pg_relation_size(ix.indexrelid))) AS spazio_totale
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
GROUP BY t.relname, ix.indkey
HAVING count(*) > 1;
-- Rebuild di un indice senza bloccare (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_ordini_stato;
-- Monitorare lo spazio degli indici
SELECT
relname,
pg_size_pretty(pg_total_relation_size(oid)) AS totale,
pg_size_pretty(pg_relation_size(oid)) AS tabella,
pg_size_pretty(pg_indexes_size(oid)) AS indici
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;
결론
올바른 인덱스 유형을 선택하면 쿼리가 10초 동안 지속되는 차이가 생길 수 있습니다. 그리고 10밀리초 동안 지속되는 것입니다. 부분 인덱스와 커버링 인덱스가 최적화되었습니다. 운영 쿼리에 더 영향을 미칩니다. BRIN은 로그 및 시계열 데이터에 있어 혁신적입니다. GIN은 다른 유형의 인덱스가 허용하지 않는 검색 가능성을 열어줍니다.
시리즈의 다음 기사에서는 테이블 파티셔닝: 관리 기술을 다룹니다. 파티션 정리 및 DROP을 사용하여 성능 저하 없이 수십억 개의 행이 있는 테이블 데이터 수명주기 관리를 위한 즉각적인 PARTITION.
시리즈: 데이터베이스 엔지니어링 및 최적화
- 기사 1: PostgreSQL 17/18 - 뉴스 및 성능
- 기사 2: 분석 설명 - 쿼리 계획 읽기
- 제3조(본): 고급 인덱싱 - 부분, 포함, BRIN, GIN
- 기사 4: 테이블 분할 - 수십억 개의 행 관리
- 기사 5: 연결 풀링 - PgBouncer 대 Pgpool-II