Masalah Degradasi B-Tree pada Tabel Append-Only

Tabel log, event stream, dan audit trail memiliki pola tulis append-only. Data masuk berurutan berdasarkan waktu (kolom created_at) tanpa modifikasi record lama. Pendekatan standar menggunakan indeks B-Tree default pada kolom timestamp menimbulkan masalah arsitektur saat volume data menembus puluhan juta baris:

  • Index Bloat: B-Tree menyimpan pointer untuk setiap baris data (tuple). Pada 50 juta baris, indeks B-Tree timestamp rata-rata menyita 1 GB hingga 2 GB storage.
  • Memory Pressure: Indeks B-Tree yang membengkak harus dimuat ke dalam shared_buffers RAM PostgreSQL. Ini menyingkirkan cache data tabel dan memicu I/O disk tinggi.
  • Write Overhead: Setiap operasi INSERT membutuhkan penyeimbangan ulang struktur tree (leaf page split), menurunkan throughput write pipeline API.

Mekanisme BRIN (Block Range Index)

BRIN (Block Range Index) dirancang spesifik untuk kolom yang memiliki korelasi fisik tinggi dengan urutan penyimpanan blok di disk (korelasi mendekati 1.0 atau -1.0). Alih-alih mencatat pointer setiap baris, BRIN membagi blok penyimpanan tabel ke dalam rentang (page ranges) dan hanya mencatat dua nilai metadata per rentang: nilai minimum dan maksimum.

Ketika query melakukan filter rentang tanggal, PostgreSQL memeriksa metadata BRIN untuk melompati (skip) blok disk yang nilainya berada di luar kriteria pencarian. Blok yang lolos kualifikasi kemudian dibaca menggunakan Bitmap Heap Scan dengan recheck condition.

Konfigurasi DDL dan Parameter pages_per_range

Parameter pages_per_range menentukan berapa banyak disk page (blok) 8 KB yang dirangkum dalam satu slot metadata indeks. Nilai default PostgreSQL adalah 128 (setara 1 MB heap data per rentang).

-- Skema tabel log audit
CREATE TABLE application_logs (
    id BIGSERIAL PRIMARY KEY,
    service_name VARCHAR(64) NOT NULL,
    severity VARCHAR(16) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CLOCK_TIMESTAMP()
);

-- BRIN Index dengan tuning pages_per_range
CREATE INDEX idx_logs_created_at_brin 
ON application_logs 
USING brin (created_at) 
WITH (pages_per_range = 64);

Catatan: Menurunkan pages_per_range dari 128 ke 64 meningkatkan presisi filter blok dan mengurangi pembacaan data yang tidak relevan (lossiness scan), dengan kompensasi kenaikan kecil pada footprint ukuran indeks.

Implementasi Handler Rentang Waktu di Go Fiber

Handler Go Fiber berikut menerima filter timestamp melalui query parameter, memvalidasi input RFC3339, dan mengeksekusi parameterized query menggunakan driver jackc/pgx/v5.

package main

import (
	"context"
	"database/sql"
	"net/http"
	"time"

	"github.com/gofiber/fiber/v2"
	_ "github.com/jackc/pgx/v5/stdlib"
)

type LogEntry struct {
	ID          int64     `json:"id"`
	ServiceName string    `json:"service_name"`
	Severity    string    `json:"severity"`
	Payload     string    `json:"payload"`
	CreatedAt   time.Time `json:"created_at"`
}

func RegisterLogRoutes(app *fiber.App, db *sql.DB) {
	app.Get("/api/v1/logs", func(c *fiber.Ctx) error {
		startQuery := c.Query("start")
		endQuery := c.Query("end")

		if startQuery == "" || endQuery == "" {
			return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{
				"error": "Parameter 'start' dan 'end' (RFC3339) wajib diisi",
			})
		}

		startTime, err := time.Parse(time.RFC3339, startQuery)
		if err != nil {
			return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "Format start time salah"})
		}

		endTime, err := time.Parse(time.RFC3339, endQuery)
		if err != nil {
			return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "Format end time salah"})
		}

		ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
		defer cancel()

		// BRIN optimal untuk filter BETWEEN pada kolom terkorelasi
		query := `
			SELECT id, service_name, severity, payload::text, created_at
			FROM application_logs
			WHERE created_at BETWEEN $1 AND $2
			ORDER BY created_at ASC
			LIMIT 500;
		`

		rows, err := db.QueryContext(ctx, query, startTime, endTime)
		if err != nil {
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
		}
		defer rows.Close()

		logs := make([]LogEntry, 0)
		for rows.Next() {
			var l LogEntry
			if err := rows.Scan(&l.ID, &l.ServiceName, &l.Severity, &l.Payload, &l.CreatedAt); err != nil {
				return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
			}
			logs = append(logs, l)
		}

		return c.Status(http.StatusOK).JSON(logs)
	})
}

Analisis Ukuran Storage dan Profil I/O Query

Eksekusi query berikut di PostgreSQL untuk membandingkan footprint penyimpanan antara B-Tree dan BRIN pada dataset tabel identik sebesar 25.000.000 baris:

SELECT 
    pg_size_pretty(pg_relation_size('idx_logs_created_at_btree')) AS btree_size,
    pg_size_pretty(pg_relation_size('idx_logs_created_at_brin')) AS brin_size;

Hasil perbandingan fisik storage:

  • B-Tree: ~540 MB
  • BRIN (pages_per_range = 64): ~360 KB

Ukuran indeks BRIN menyusut lebih dari 99%, memungkinkan seluruh struktur indeks menetap permanen dalam RAM cache.

Eksekusi EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM application_logs
WHERE created_at BETWEEN '2026-03-01 00:00:00+00' AND '2026-03-01 06:00:00+00';

Keluaran execution engine menunjukkan cara kerja BRIN:

Bitmap Heap Scan on application_logs (cost=24.12..1450.40 rows=28500 width=180) (actual time=1.120..14.350 rows=28410 loops=1)
  Recheck Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamptz) AND (created_at <= '2026-03-01 06:00:00+00'::timestamptz))
  Rows Removed by Index Recheck: 120
  Buffers: shared hit=421
  ->  Bitmap Index Scan on idx_logs_created_at_brin (cost=0.00..20.50 rows=28500 width=0) (actual time=0.082..0.082 rows=14 loops=1)
Planning Time: 0.155 ms
Execution Time: 15.820 ms

Bitmap Index Scan hanya membutuhkan 14 blok index metadata. Rows Removed by Index Recheck menunjukkan overhead pemindaian tuple di perbatasan range yang tereliminasi cepat pada fase filter CPU.

Batasan Teknis dan Kapan Menghindari BRIN

Meskipun efisien, BRIN tidak dapat menggantikan B-Tree pada semua use case:

  • Out-of-Order Ingestion: Jika log dikirimkan asinkron dengan timestamp masa lalu yang acak, korelasi nilai minimum/maksimum per blok rusak. Nilai korelasi kolom di pg_stats yang jatuh di bawah 0.8 menyebabkan BRIN membaca hampir seluruh isi tabel (degradasi menjadi Full Table Scan).
  • Update dan Delete: Operasi UPDATE yang memindahkan data ke page baru memperlebar interval nilai min-max range blok tersebut, mematikan kemampuan skip blok.
  • Point Queries: Menemukan log spesifik berdasarkan timestamp unik (mencari tepat 1 baris) lebih lambat di BRIN daripada B-Tree karena BRIN tetap harus memindai seluruh page di dalam range blok terkait (1 range = 64-128 page).

Gunakan BRIN khusus untuk tabel historis terurut yang diakses berbasis rentang interval waktu (range queries).