BRIN Index di Nuxt 3 Nitro: Solusi Slow Query Tabel Append-Only

Masalah B-Tree pada Tabel Append-Only Skala Besar

Tabel append-only seperti audit log, riwayat transaksi, dan analytic events memiliki karakteristik data yang di-insert berurutan berdasarkan waktu (monotonically increasing). Pendekatan default PostgreSQL membuat B-Tree index pada kolom created_at.

Seiring bertambahnya data hingga puluhan juta baris, B-Tree menimbulkan dua masalah kritis:

• RAM Starvation (Buffer Cache Churn): Ukuran file B-Tree index membengkak sebanding dengan jumlah baris (O(N)). Ketika ukuran index melampaui alokasi shared_buffers, database terpaksa membaca disk (I/O thrashing) untuk memperbarui dan membaca leaf node B-Tree.
• Degradasi Write Throughput: Setiap operasi INSERT membutuhkan traversal B-Tree tree path dan penyeimbangan leaf page (page splits), yang menurunkan throughput penulisan log berkecepatan tinggi.

Arsitektur PostgreSQL BRIN (Block Range Index)

BRIN bekerja dengan memanfaatkan urutan fisik data di dalam disk heap. Alih-alih menyimpan pointer untuk setiap baris data, BRIN hanya menyimpan metadata ringkasan berupa nilai minimum dan maksimum (summary tuple) untuk sekumpulan page disk (disebut block range).

Ketika query melakukan filter range waktu, PostgreSQL membaca ringkasan BRIN untuk menentukan blok disk mana yang mungkin memuat data tersebut, lalu melewatkan (skip) blok disk yang berada di luar range. Syarat mutlak efektivitas BRIN adalah korelasi fisik (physical clustering) data mendekati 1.0 atau -1.0 pada pg_stats.correlation, karakteristik alami tabel append-only.

Ukuran Index dan Resource Footprint

Tuning pages_per_range dan Analisis EXPLAIN ANALYZE

Parameter pages_per_range menentukan berapa banyak disk pages (standar 8KB per page) yang diwakili oleh satu pasangan min/max. Default PostgreSQL adalah 128 pages (1 MB data disk per summary entry).

-- Cek korelasi fisik data pada tabel logs
SELECT tablename, attname, correlation 
FROM pg_stats 
WHERE tablename = 'audit_logs' AND attname = 'created_at';

-- Drop index B-Tree lama
DROP INDEX CONCURRENTLY IF EXISTS idx_audit_logs_created_at;

-- Buat BRIN index dengan tuning range
CREATE INDEX idx_audit_logs_created_at_brin 
ON audit_logs USING brin (created_at) 
WITH (pages_per_range = 64);

Nilai pages_per_range = 64 menghasilkan selektivitas lebih granular dibanding 128, mengurangi pembacaan blok data yang tidak relevan (lossy scan overhead) dengan penambahan ukuran index yang masih sangat kecil (relatif ratusan kilobyte).

Profil Eksekusi EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM audit_logs 
WHERE created_at BETWEEN '2026-03-01 00:00:00' AND '2026-03-01 23:59:59';

-- Hasil Execution Plan:
-- Bitmap Heap Scan on audit_logs (cost=14.20..1250.40 rows=8500 width=128) (actual time=1.120..14.850 rows=8420 loops=1)
--   Recheck Cond: ((created_at >= '2026-03-01 00:00:00'::timestamp) AND (created_at <= '2026-03-01 23:59:59'::timestamp))
--   Rows Removed by Index Recheck: 312
--   Buffers: shared hit=428
--   ->  Bitmap Index Scan on idx_audit_logs_created_at_brin (cost=0.00..12.08 rows=8500 width=0) (actual time=0.045..0.045 loops=1)
--         Index Cond: ((created_at >= '2026-03-01 00:00:00'::timestamp) AND (created_at <= '2026-03-01 23:59:59'::timestamp))
--         Buffers: shared hit=2

Perhatikan parameter Buffers: shared hit=2 pada Bitmap Index Scan. BRIN hanya menyentuh 2 block memori untuk menemukan blok heap target, jauh lebih efisien dibanding B-Tree yang membutuhkan puluhan atau ratusan block hit.

Implementasi Server Route di Nuxt 3 Nitro

Gunakan driver PostgreSQL berbobot ringan seperti postgres (postgres.js) di dalam server route Nitro.

1. Inisialisasi Database Client

// server/utils/db.ts
import postgres from 'postgres';

const config = useRuntimeConfig();

// Single instance connection pool
export const sql = postgres(config.databaseUrl, {
  max: 15,
  idle_timeout: 20,
  connect_timeout: 10,
});

2. Server API Route: server/api/logs.get.ts

// server/api/logs.get.ts
import { z } from 'zod';
import { sql } from '../utils/db';

const querySchema = z.object({
  from: z.string().datetime(),
  to: z.string().datetime(),
  limit: z.coerce.number().min(1).max(500).default(100),
});

export default defineEventHandler(async (event) => {
  const query = await getValidatedQuery(event, (data) => querySchema.parse(data));

  // BRIN digunakan secara optimal saat filter range created_at diterapkan
  const logs = await sql`
    SELECT id, event_name, payload, created_at
    FROM audit_logs
    WHERE created_at >= ${query.from} 
      AND created_at <= ${query.to}
    ORDER BY created_at ASC
    LIMIT ${query.limit};
  `;

  return {
    success: true,
    count: logs.length,
    data: logs,
  };
});

3. Konsumsi Melalui useFetch di Frontend Nuxt 3

<script setup lang="ts">
const dateRange = ref({
  from: new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString(),
  to: new Date().toISOString(),
});

const { data: logResponse, pending, error } = await useFetch('/api/logs', {
  query: dateRange,
  lazy: true,
});
</script>

<template>
  <div>
    <div v-if="pending">Memuat audit logs...</div>
    <div v-else-if="error">Gagal: {{ error.message }}</div>
    <ul v-else>
      <li v-for="log in logResponse?.data" :key="log.id">
        [{{ log.created_at }}] - {{ log.event_name }}
      </li>
    </ul>
  </div>
</template>

Limitasi dan Maintenance BRIN

• Lossy Scan: BRIN mengembalikan seluruh block range. PostgreSQL harus melakukan recheck kondisi (Rows Removed by Index Recheck) pada setiap baris data di blok tersebut. BRIN tidak cocok untuk query point-lookup (mencari tepat 1 baris berdasarkan UUID/ID).
• Unsummarized Pages: Insert baru pada heap tidak otomatis langsung terangkum dalam range BRIN hingga range tersebut penuh atau dijalankan maintenance rutin:

-- Jalankan periodik via pg_cron atau task scheduler untuk range aktif terbaru
SELECT brin_summarize_new_values('idx_audit_logs_created_at_brin');

Rekomendasi: Pertahankan B-Tree untuk kolom primary key (ID) atau kolom dengan nilai acak (UUID). Gunakan BRIN murni untuk kolom timestamp atau sequential ID pada tabel append-only yang berukuran puluhan gigabyte ke atas.