Bayangkan tabel log transaksi kasir Toko Maju Jaya yang tumbuh sepuluh ribu baris setiap hari: setiap menit satu baris, tanpa pernah diubah, hanya ditambah. Tabel sebesar itu sangat sering dikueri dengan rentang waktu — “transaksi hari ini”, “transaksi minggu lalu” — dan Seq Scan yang membaca seluruh tabel menjadi semakin mahal setiap bulan. Index B-tree bisa mempercepat query seperti itu, tetapi B-tree menyimpan satu entri per baris: untuk 100 ribu baris timestamp, index-nya sendiri membengkak lebih dari 2 MB. PostgreSQL menawarkan alternatif yang jauh lebih hemat: BRIN (Block Range Index) tidak mengindeks setiap baris, melainkan menyimpan ringkasan nilai minimum dan maksimum untuk setiap rentang blok tabel. Hasilnya index yang ukurannya puluhan sampai ratusan kali lebih kecil — dengan syarat yang akan kita buktikan sendiri di chapter ini.

Cara Kerja BRIN: Ringkasan per Rentang Blok #

PostgreSQL menyimpan tabel dalam halaman 8 kB; satu blok berisi puluhan baris tergantung lebar barisnya. BRIN bekerja pada kelompok halaman tersebut: untuk setiap rentang blok (bawaannya 128 halaman), BRIN menyimpan dua nilai saja — nilai terkecil dan terbesar kolom yang diindeks pada rentang itu. Saat query mencari waktu BETWEEN 1 Feb AND 2 Feb, BRIN memeriksa ringkasan tiap rentang: rentang yang minimum-atau-maksimumnya tidak menyentuh rentang waktu yang dicari pasti tidak memuat baris jawaban, sehingga seluruh bloknya di-skip. Rentang yang mungkin memuat jawaban ditandai pada sebuah bitmap, lalu baris-barisnya dibaca dan diperiksa ulang (recheck).

Konsekuensi desain ini ada dua. Pertama, BRIN kasar: satu entri ringkasan mewakili ribuan baris, bukan satu baris seperti pada B-tree, sehingga baris yang ditemukan harus selalu diverifikasi ulang. Kedua, BRIN hanya efektif bila urutan nilai kolom mengikuti urutan fisik baris di disk — persis sifat tabel log yang di-insert berurutan waktu. Bila baris-barisnya tersebar acak di seluruh tabel, setiap rentang blok memuat hampir seluruh rentang nilai, ringkasan min/max menjadi tidak selektif, dan BRIN tidak lagi berguna.

Menyiapkan Tabel Log 100 Ribu Baris #

Kita pakai domain yang sama dengan chapter-chapter sebelumnya: log transaksi kasir Toko Maju Jaya. generate_series menghasilkan 100 ribu baris dengan waktu yang naik satu menit tiap baris, sehingga urutan fisik penyimpanan mengikuti urutan waktu secara alami:

sql buat-tabel.sql 
-- Log transaksi kasir Toko Maju Jaya: satu baris per menit,
-- di-insert berurutan dari 2026-01-01 sehingga kolom waktu
-- terkorelasi alami dengan urutan fisik di disk.
CREATE TABLE log_kasir (
    id       bigint NOT NULL,
    waktu    timestamp NOT NULL,
    kasir_id smallint NOT NULL,
    total    numeric(12,2) NOT NULL
);

INSERT INTO log_kasir (id, waktu, kasir_id, total)
SELECT g,
       TIMESTAMP '2026-01-01 06:00:00' + (g * interval '1 minute'),
       (g % 8) + 1,
       ((g * 13) % 500 + 1) * 500.00
FROM generate_series(1, 100000) AS g;

SELECT count(*) AS jumlah_baris,
       min(waktu) AS mulai,
       max(waktu) AS selesai
FROM log_kasir;

-- Statistik tabel diisi sekarang agar rencana eksekusi stabil.
ANALYZE log_kasir;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 100000
 jumlah_baris |        mulai        |       selesai       
--------------+---------------------+---------------------
       100000 | 2026-01-01 06:01:00 | 2026-03-11 16:40:00
(1 row)

ANALYZE

Tabel log_kasir memiliki empat kolom dan tidak diberi PRIMARY KEY — sengaja, karena tabel log murni tambah-only memang jarang butuh index unik, dan kita ingin mengukur dampak tiap index secara terisolasi. Baris terakhir menegaskan cakupan data: 100.000 transaksi mulai 1 Januari pukul 06:01 sampai 11 Maret pukul 16:40 — sekitar sepuluh minggu operasional toko. ANALYZE di akhir file mengisi statistik tabel agar rencana eksekusi yang kita lihat nanti stabil.

Index BRIN Pertama: 24 kB untuk 100 Ribu Baris #

sql index-brin.sql 
-- Index BRIN pada kolom waktu: hanya menyimpan ringkasan min/max
-- per rentang blok, bukan satu entri per baris seperti B-tree.
CREATE INDEX idx_brin_waktu ON log_kasir USING brin (waktu);

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_index_brin,
       pg_size_pretty(pg_relation_size('log_kasir')) AS ukuran_tabel;

-- Query rentang: seluruh transaksi 1 Februari 2026.
SELECT count(*) AS transaksi_sehari
FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

Output yang Dihasilkan:

text 
CREATE INDEX
 ukuran_index_brin | ukuran_tabel 
-------------------+--------------
 24 kB             | 5840 kB
(1 row)

 transaksi_sehari 
------------------
             1440
(1 row)

Tiga hasil penting muncul sekali jalan. CREATE INDEX ... USING brin (waktu) membangun index BRIN pada kolom waktu — prosesnya hanya membaca tabel sekali sambil mencatat min/max tiap rentang blok. Ukurannya 24 kB untuk tabel 5.840 kB — index BRIN hanya 0,4% dari ukuran tabel. Query rentang “seluruh transaksi 1 Februari” mengembalikan 1.440 baris, tepat satu transaksi per menit selama satu hari penuh.

Rencana Eksekusi: Bitmap dan Recheck #

Mari kita intip bagaimana planner benar-benar memakai index BRIN. Karena EXPLAIN ANALYZE mencantumkan waktu eksekusi yang berbeda-beda di setiap mesin, output berikut disalin dari eksekusi nyata dan ditampilkan secara manual — angka Planning Time, Execution Time, dan biaya bisa berbeda di lingkungan Anda, tetapi bentuk rencananya akan sama:

sql explain-analyze.sql 
-- Rencana eksekusi nyata untuk index BRIN dengan pengaturan bawaan
-- (pages_per_range = 128).
EXPLAIN ANALYZE SELECT count(*) FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

Output yang Dihasilkan:

text 
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=996.09..996.10 rows=1 width=8) (actual time=1.627..1.628 rows=1 loops=1)
   ->  Bitmap Heap Scan on log_kasir  (cost=12.40..992.41 rows=1474 width=0) (actual time=0.788..1.562 rows=1440 loops=1)
         Recheck Cond: ((waktu >= '2026-02-01 00:00:00'::timestamp without time zone) AND (waktu <= '2026-02-01 23:59:00'::timestamp without time zone))
         Rows Removed by Index Recheck: 16096
         Heap Blocks: lossy=128
         ->  Bitmap Index Scan on idx_brin_waktu  (cost=0.00..12.03 rows=16667 width=0) (actual time=0.047..0.048 rows=1280 loops=1)
               Index Cond: ((waktu >= '2026-02-01 00:00:00'::timestamp without time zone) AND (waktu <= '2026-02-01 23:59:00'::timestamp without time zone))
 Planning Time: 0.206 ms
 Execution Time: 1.669 ms
(9 rows)

Empat baris rencana ini merangkum seluruh mekanisme BRIN:

  1. Bitmap Index Scan on idx_brin_waktu: BRIN menandai rentang-rentang blok yang min/max-nya berpotongan dengan 1 Februari. Bitmap ini lossy — ia mencatat halaman, bukan baris.
  2. Heap Blocks: lossy=128: hanya 128 halaman yang perlu dibaca dari 5.840 kB tabel — sisanya di-skip karena ringkasan min/max-nya tidak menyentuh rentang query.
  3. Rows Removed by Index Recheck: 16096: dari ±17.500 baris di 128 halaman itu, 16.096 baris ternyata bukan anggota 1 Februari dan dibuang saat recheck. Inilah harga kekasaran BRIN: blok dibaca seluruhnya, baris disaring satu per satu.
  4. Aggregate: count(*) menjumlahkan 1.440 baris yang lolos — hasil identik dengan tanpa index.

BRIN vs B-tree: 92 Kali Lebih Kecil #

Sekarang bandingkan dengan B-tree pada kolom dan data yang persis sama:

sql ukuran-vs-btree.sql 
-- Bandingkan dengan B-tree pada kolom dan data yang sama.
CREATE INDEX idx_btree_waktu ON log_kasir (waktu);

SELECT pg_size_pretty(pg_relation_size('log_kasir')) AS ukuran_tabel,
       pg_size_pretty(pg_relation_size('idx_btree_waktu')) AS ukuran_btree,
       pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_brin,
       pg_relation_size('idx_btree_waktu') / pg_relation_size('idx_brin_waktu') AS rasio;

-- Jawaban query tetap sama persis; yang berbeda hanyalah jalur pencarian.
SELECT count(*) AS transaksi_sehari
FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

Output yang Dihasilkan:

text 
CREATE INDEX
 ukuran_tabel | ukuran_btree | ukuran_brin | rasio 
--------------+--------------+-------------+-------
 5840 kB      | 2208 kB      | 24 kB       |    92
(1 row)

 transaksi_sehari 
------------------
             1440
(1 row)

B-tree memakan 2.208 kB, BRIN hanya 24 kB — rasio 92 kali. Untuk tabel yang tumbuh miliaran baris, selisih ini berarti gigabyte lawan megabyte. Query count(*) yang sama mengembalikan 1.440 baris pada kedua index; kebenaran hasil tidak pernah dikorbankan, yang berbeda hanya jalur pencarian. Perbandingan lengkapnya:

AspekB-treeBRIN
Ukuran untuk 100 ribu baris2.208 kB24 kB
Entri per barissatu entri per barissatu ringkasan per rentang blok
Presisi pencarianpersis ke bariskasar ke blok, wajib recheck
Syarat datatidak adakorelasi urutan nilai dengan urutan fisik
Kecepatan buildlebih lambat, tulis semua entrisangat cepat, satu pass ringkasan
Cocok untukquery umum, ORDER BYtabel besar tambah-only dengan urutan alami

Menyetel pages_per_range #

Kekasaran BRIN bisa diperhalus lewat parameter pages_per_range, yaitu berapa halaman yang dicakup satu ringkasan. Bawaannya 128; nilai lebih kecil membuat ringkasan lebih sering dibuat sehingga kandidat blok lebih presisi:

sql pages-per-range.sql 
-- Rentang blok bawaan BRIN adalah 128 halaman.
-- Dengan pages_per_range = 16, ringkasan dibuat lebih sering:
-- index sedikit membesar, tetapi kandidat blok jauh lebih presisi.
DROP INDEX idx_btree_waktu;
DROP INDEX idx_brin_waktu;

CREATE INDEX idx_brin_waktu_16 ON log_kasir USING brin (waktu)
    WITH (pages_per_range = 16);

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu_16')) AS ukuran_brin_16;

Output yang Dihasilkan:

text 
DROP INDEX
DROP INDEX
CREATE INDEX
 ukuran_brin_16 
----------------
 24 kB
(1 row)

Ukuran index tidak berubah (tetap 24 kB) karena jumlah ringkasan masih sedikit dibanding skala tabel, tetapi presisinya naik drastis. File berikut membandingkan rencana eksekusi varian pages_per_range = 16 dengan kasus data yang urutannya diacak; output disalin manual dari eksekusi nyata karena memuat waktu eksekusi yang bervariasi antar mesin:

sql explain-perbandingan.sql 
-- Perbandingan dua pengaturan pages_per_range dan kasus data acak.
-- Bagian 1: pages_per_range = 16 (index dibuat di file pages-per-range.sql).
EXPLAIN ANALYZE SELECT count(*) FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

-- Bagian 2: data yang sama, tetapi urutan fisik barisnya diacak
-- sehingga korelasi alami antara waktu dan blok hilang.
SELECT setseed(0.42);
CREATE TABLE log_acak AS
SELECT waktu, kasir_id, total
FROM log_kasir
ORDER BY random();

CREATE INDEX idx_brin_acak ON log_acak USING brin (waktu);
ANALYZE log_acak;

-- Query yang sama: jawabannya tetap benar, tetapi rencana eksekusinya berubah.
EXPLAIN ANALYZE SELECT count(*) FROM log_acak
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

Output yang Dihasilkan:

text 
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=778.67..778.68 rows=1 width=8) (actual time=0.334..0.334 rows=1 loops=1)
   ->  Bitmap Heap Scan on log_kasir  (cost=12.37..774.98 rows=1474 width=0) (actual time=0.084..0.269 rows=1440 loops=1)
         Recheck Cond: ((waktu >= '2026-02-01 00:00:00'::timestamp without time zone) AND (waktu <= '2026-02-01 23:59:00'::timestamp without time zone))
         Rows Removed by Index Recheck: 752
         Heap Blocks: lossy=16
         ->  Bitmap Index Scan on idx_brin_waktu_16  (cost=0.00..12.00 rows=2174 width=0) (actual time=0.033..0.034 rows=160 loops=1)
               Index Cond: ((waktu >= '2026-02-01 00:00:00'::timestamp without time zone) AND (waktu <= '2026-02-01 23:59:00'::timestamp without time zone))
 Planning Time: 0.254 ms
 Execution Time: 0.379 ms
(9 rows)

 setseed 
---------
 
(1 row)

SELECT 100000
CREATE INDEX
ANALYZE
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2143.54..2143.55 rows=1 width=8) (actual time=6.591..6.593 rows=1 loops=1)
   ->  Seq Scan on log_acak  (cost=0.00..2140.00 rows=1416 width=0) (actual time=0.015..6.521 rows=1440 loops=1)
         Filter: ((waktu >= '2026-02-01 00:00:00'::timestamp without time zone) AND (waktu <= '2026-02-01 23:59:00'::timestamp without time zone))
         Rows Removed by Filter: 98560
 Planning Time: 0.087 ms
 Execution Time: 6.614 ms
(6 rows)

Bagian pertama file memakai index idx_brin_waktu_16: blok yang dibaca menyusut dari 128 menjadi 16 halaman (Heap Blocks: lossy=16), dan baris yang dibuang saat recheck turun dari 16.096 menjadi hanya 752. Nilai pages_per_range yang lebih kecil menggandakan jumlah entri ringkasan, jadi aturannya klasik: turunkan bila query Anda sangat selektif dan ukuran index masih jauh di bawah tabel; naikkan bila tabelnya raksasa dan query-nya selalu menyapu rentang besar.

Saat Korelasi Hilang, BRIN Tidak Lagi Berguna #

Bagian kedua file membangun log_acak: tabel dengan baris dan nilai yang sama, tetapi urutan fisiknya diacak lewat ORDER BY random() dengan benih tetap agar demonstrasi dapat direproduksi. Ringkasan min/max kini memuat nilai dari seluruh spektrum waktu di hampir setiap rentang blok, sehingga hampir semua rentang “mungkin memuat” jawaban. Planner membaca situasi ini dan memilih Seq Scan biasa: Rows Removed by Filter: 98560 — seluruh tabel dibaca untuk menemukan 1.440 baris. Bandingkan dengan tabel terurut di mana 99% halaman berhasil di-skip.

Kapan BRIN Menjadi Pilihan Tepat #

BRIN unggul pada profil tabel berikut: berukuran sangat besar (puluhan juta baris ke atas), tambah-only seperti log, telemetri sensor, atau riwayat peristiwa, dengan kolom kueri yang nilainya bertambah seiring waktu insert. Di profil itu BRIN memberi pemfilteran blok yang murah — index 24 kB tadi membantu query yang sebelumnya harus membaca seluruh tabel. Ringkasan rentang blok baru juga otomatis dipelihara saat baris baru masuk, dan autovacuum menyegarkan ringkasan rentang lama secara berkala, sehingga tidak ada proses manual khusus untuk operasional harian.

Referensi #

Setelah BRIN yang membagi tabel menjadi rentang blok, kita berpindah ke keluarga index yang membagi ruang nilai: SP-GiST dengan struktur quadtree dan radix tree untuk alamat IP dan pencarian prefix — chapter berikutnya.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel.sql

Unduh berkas
sql 
-- Log transaksi kasir Toko Maju Jaya: satu baris per menit,
-- di-insert berurutan dari 2026-01-01 sehingga kolom waktu
-- terkorelasi alami dengan urutan fisik di disk.
CREATE TABLE log_kasir (
    id       bigint NOT NULL,
    waktu    timestamp NOT NULL,
    kasir_id smallint NOT NULL,
    total    numeric(12,2) NOT NULL
);

INSERT INTO log_kasir (id, waktu, kasir_id, total)
SELECT g,
       TIMESTAMP '2026-01-01 06:00:00' + (g * interval '1 minute'),
       (g % 8) + 1,
       ((g * 13) % 500 + 1) * 500.00
FROM generate_series(1, 100000) AS g;

SELECT count(*) AS jumlah_baris,
       min(waktu) AS mulai,
       max(waktu) AS selesai
FROM log_kasir;

-- Statistik tabel diisi sekarang agar rencana eksekusi stabil.
ANALYZE log_kasir;

explain-analyze.sql

Unduh berkas
sql 
-- Rencana eksekusi nyata untuk index BRIN dengan pengaturan bawaan
-- (pages_per_range = 128).
EXPLAIN ANALYZE SELECT count(*) FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

explain-perbandingan.sql

Unduh berkas
sql 
-- Perbandingan dua pengaturan pages_per_range dan kasus data acak.
-- Bagian 1: pages_per_range = 16 (index dibuat di file pages-per-range.sql).
EXPLAIN ANALYZE SELECT count(*) FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

-- Bagian 2: data yang sama, tetapi urutan fisik barisnya diacak
-- sehingga korelasi alami antara waktu dan blok hilang.
SELECT setseed(0.42);
CREATE TABLE log_acak AS
SELECT waktu, kasir_id, total
FROM log_kasir
ORDER BY random();

CREATE INDEX idx_brin_acak ON log_acak USING brin (waktu);
ANALYZE log_acak;

-- Query yang sama: jawabannya tetap benar, tetapi rencana eksekusinya berubah.
EXPLAIN ANALYZE SELECT count(*) FROM log_acak
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

index-brin.sql

Unduh berkas
sql 
-- Index BRIN pada kolom waktu: hanya menyimpan ringkasan min/max
-- per rentang blok, bukan satu entri per baris seperti B-tree.
CREATE INDEX idx_brin_waktu ON log_kasir USING brin (waktu);

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_index_brin,
       pg_size_pretty(pg_relation_size('log_kasir')) AS ukuran_tabel;

-- Query rentang: seluruh transaksi 1 Februari 2026.
SELECT count(*) AS transaksi_sehari
FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';

pages-per-range.sql

Unduh berkas
sql 
-- Rentang blok bawaan BRIN adalah 128 halaman.
-- Dengan pages_per_range = 16, ringkasan dibuat lebih sering:
-- index sedikit membesar, tetapi kandidat blok jauh lebih presisi.
DROP INDEX idx_btree_waktu;
DROP INDEX idx_brin_waktu;

CREATE INDEX idx_brin_waktu_16 ON log_kasir USING brin (waktu)
    WITH (pages_per_range = 16);

SELECT pg_size_pretty(pg_relation_size('idx_brin_waktu_16')) AS ukuran_brin_16;

ukuran-vs-btree.sql

Unduh berkas
sql 
-- Bandingkan dengan B-tree pada kolom dan data yang sama.
CREATE INDEX idx_btree_waktu ON log_kasir (waktu);

SELECT pg_size_pretty(pg_relation_size('log_kasir')) AS ukuran_tabel,
       pg_size_pretty(pg_relation_size('idx_btree_waktu')) AS ukuran_btree,
       pg_size_pretty(pg_relation_size('idx_brin_waktu')) AS ukuran_brin,
       pg_relation_size('idx_btree_waktu') / pg_relation_size('idx_brin_waktu') AS rasio;

-- Jawaban query tetap sama persis; yang berbeda hanyalah jalur pencarian.
SELECT count(*) AS transaksi_sehari
FROM log_kasir
WHERE waktu BETWEEN TIMESTAMP '2026-02-01 00:00:00'
                AND TIMESTAMP '2026-02-01 23:59:00';