Postgres BRIN vs. B-tree · mikor melyik nyer

A BRIN (Block Range INdex) 9.5 óta van a Postgresben. A magyar csapatok, akiket auditálunk, nagy része sosem használta · B-tree-t pakolnak mindenre, és aztán panaszkodnak a WAL-méretre meg a bloatra. Pár csapat egyszer rátette egy rossz oszlopra a BRIN-t, lassúnak ítélte, és sosem ment vissza. Ugyanazon a táblán mindkettő rossz.

A szabály, amit alkalmazunk, számokkal alább: BRIN append-mostly, időrendezett vagy egyébként fizikailag klaszterezett oszlopra (event, telemetry, audit log, IoT-mérés). B-tree mindenre másra. Ami egyik kategóriába sem fér, oda csak mérés után kerül index.

Mi az a BRIN

A B-tree soronként egy bejegyzést tárol. A BRIN tárolja az összegzést (min, max) page-tartományonként · alapértelmezésben 128 page-enként. Tehát 200M index-bejegyzés helyett kb. 200M / (128 * sor-per-page) bejegyzést tárol. A keresés: 'mely page-tartományok tartalmazhatnak [a,b]-ben értéket?', majd azokat sequential scan-eli. Csak akkor jó, ha a tartományon belül az értékek tényleg klaszterezettek · ez gyakorlatban azt jelenti, hogy sorrendben szúrsz be és sosem update-elsz.

A benchmark · 200M sor telemetria

Valódi tábla egy ügyfél-megbízásból (számok anonimizálva, alak megőrizve). `metrics` tábla, 200M sor, naponta partícionált · az index-tesztre egy 30 napos partícióra fókuszáltunk, kb. 60M sor. Érdekes oszlopok: `created_at` (insert-idő, monoton), `user_id` (random UUID), `metric` (alacsony cardinality string), `value` (double).

-- B-tree a created_at oszlopon
CREATE INDEX btree_created ON metrics_p USING btree (created_at);

-- BRIN a created_at oszlopon, default pages_per_range = 128
CREATE INDEX brin_created ON metrics_p USING brin (created_at)
  WITH (pages_per_range = 128);

-- BRIN user_id-ra (bukni fog, őszinteségből)
CREATE INDEX brin_user ON metrics_p USING brin (user_id)
  WITH (pages_per_range = 128);

Méretek · a nyilvánvaló BRIN-nyereség

btree_created      ~1.7 GB
brin_created       ~560 KB    (3000x kisebb)
brin_user          ~560 KB    (ugyanaz az alak, eltérő történet alább)

Önmagában a méretkülönbség az, amiért BRIN létezik. Egy hot append-only partíción a `created_at`-re tett B-tree kb. 3%-a volt a táblának. A BRIN ugyanezen az oszlopon 0,001%. Az insert-eken a WAL-volumen arányosan csökkent · ami egy elfoglalt ingest-pipeline-on a tényleges produkciós nyereség, nem a query-sebesség.

Range-query · amire a BRIN-t építették

Tipikus query · 'add az utolsó 6 óra metrikáit egy dashboardnak'.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), avg(value) FROM metrics_p
WHERE created_at >= now() - interval '6 hours';

B-tree:  Bitmap heap scan, 28 ms, ~2.1M sor scannel
BRIN:    Bitmap heap scan, 31 ms, ~2.3M sor (kicsit szélesebb page-tartomány)

~10%-on belül walltime-ban. Elhanyagolható különbség az érintett sorokon. Egy 3000-szer kisebb, fenntartásra alig kerülő indexért ez az alku, amit akarunk.

Szélesebb range · a BRIN tovább zárkózik

Ha a tartomány szélesebb (pl. 7 napos ablak, ~14M sor), a költséget a heap-scan dominálja, nem az index-lookup. BRIN és B-tree pár százalékon belülre konvergál. A B-tree 'precíz' bejegyzései felesleges munka · úgyis le kell olvasni a page-eket.

Ahol a BRIN bukik · alacsony korrelációjú oszlop

Ugyanaz a query, de `user_id`-ra (random UUID) szűrve.

EXPLAIN ANALYZE
SELECT * FROM metrics_p WHERE user_id = '...';

B-tree user_id:  ~5 ms, 60 sor
BRIN user_id:    ~3.4 s, 60 sor  (a tábla nagy részét kellett scannelnie)

A BRIN nem segít, ha az index oszlop nem korrelál a fizikai sorrenddel. Az összegző tartományok mind átfedik a predikátumot, így az index azt javasolja, hogy mindent scanneljen. Ez az eset, amit az emberek kipróbálnak, leírják a BRIN-t, és sosem mennek vissza. Ne tegyél BRIN-t UUID-ra, hash-re, vagy bármi olyanra, ahol az érték a heap-en szétszórva ül.

Ingest-költség · a csendes BRIN-nyereség

60M soros insert egy napi partícióba: B-tree-vel kb. 11 perc; BRIN-nel ugyanez a load 8 perc alatt, főleg azért, mert a BRIN-nek nem kell minden insertnél page-eket egyensúlyba raknia. A WAL-volumen 40%-kal esett. Egy percenként futó ingest-pipeline-on ez érzékelhetően csökkenti a replikációs lag-et és a backup-méretet, nem csak microbenchmark-trofea.

Mikor melyik

• BRIN: append-mostly, időrendezett vagy egyébként fizikailag klaszterezett oszlop. Audit log, IoT-mérés, event-tábla, partíció-idő oszlop.
• BRIN: bármi, amire range-query-zel, nem point-lookup-ot.
• B-tree: amit point-lookup-olsz · primary key, foreign key, user_id, email.
• B-tree: amit `ORDER BY`-jal használsz (BRIN nem szolgál sortot).
• B-tree: nagy update-churn-ű oszlop, ami töri a fizikai rendet.

Üzemeltetési jegyzetek

1. Állítsd a `pages_per_range`-t megfontoltan. A 128-as default OLTP-vegyesen jó; tiszta ingest-táblán nagyon szűk range-ekkel menj le 32-re.
2. Futtasd a `VACUUM`-ot rendszeresen. A BRIN-összegzéseket az autovacuum frissíti; ha kikapcsolod, a BRIN elöregszik és a planner nem bízik benne.
3. Párosítsd a BRIN-t deklaratív partícionálással. Constraint exclusion plusz BRIN a partíción belül a legolcsóbb time-series kombináció.
4. Bulk reload után újra-összegezz: `SELECT brin_summarize_new_values('brin_created');`
5. Ne számíts BRIN + `INCLUDE` index-only scan-re · a BRIN nem támogatja úgy, mint a B-tree.

A BRIN nem B-tree helyettesítő. Másik eszköz másik adatalakra. A megfelelő oszlopon verhetetlen lemezen, WAL-ban, ingest-költségben, miközben pár százalékon belül marad olvasási latenciában. A rossz oszlopon kínos. A döntés mechanikus · 'fizikailag rendezettek-e az értékek?', ha igen BRIN, ha nem B-tree.