Aplikasi yang dibangun cepat menggunakan bantuan AI (vibecoding) sering kali lolos uji fungsional di lingkungan lokal, namun kolaps begitu masuk lingkungan produksi dengan volume data riil. Generator kode berbasis LLM cenderung memproduksi query ORM yang terlihat bersih secara sintaksis, tetapi mengabaikan cara kerja PostgreSQL query planner dalam membaca disk dan memori.

Masalah utama dari kode vibecoding terletak pada tiga pola: ketiadaan composite index untuk kombinasi filtering dan ordering, foreign key tanpa index yang memicu lock saat operasi write, dan implementasi paginasi berbasis limit-offset pada tabel besar. Tanpa intervensi indeks yang tepat, database dipaksa melakukan Sequential Scan (Seq Scan) berulang kali.

Diagnostik Bottleneck: pg_stat_statements dan EXPLAIN BUFFERS

Langkah pertama bukan menebak kolom mana yang perlu diindeks, melainkan mengukur query terberat langsung dari shared buffer database.

1. Menemukan Query Terburuk via pg_stat_statements

Ekstensi pg_stat_statements mencatat performa agregat eksekusi query. Aktifkan dan jalankan query berikut untuk mencari query dengan konsumsi waktu total tertinggi:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT 
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

2. Analisis Execution Plan dengan EXPLAIN (ANALYZE, BUFFERS)

Saat query bermasalah teridentifikasi, evaluasi menggunakan parameter ANALYZE dan BUFFERS. Flag BUFFERS krusial untuk melihat apakah data dibaca langsung dari RAM (shared hit) atau ditarik dari disk (shared read).

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

Keluaran yang mengindikasikan masalah:

Limit  (cost=18542.10..18542.15 rows=20 width=36) (actual time=84.120..84.125 rows=20 loops=1)
  Buffers: shared hit=1200 read=14500
  ->  Sort  (cost=18542.10..18612.30 rows=28080 width=36) (actual time=84.118..84.122 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=1200 read=14500
        ->  Seq Scan on orders  (cost=0.00..17650.00 rows=28080 width=36) (actual time=0.045..78.900 rows=28500 loops=1)
              Filter: ((tenant_id = 42) AND (status = 'COMPLETED'::text))
              Rows Removed by Filter: 971500
              Buffers: shared hit=1200 read=14500
Planning Time: 0.150 ms
Execution Time: 84.210 ms

Plan di atas menunjukkan pembacaan disk tinggi (read=14500 blok data) dan 971.500 baris diperiksa via Sequential Scan hanya untuk membuangnya kembali.

Optimasi 1: Composite Index (Equality-Then-Range Rule)

Kode vibecoding sering kali menambahkan single-column index secara terpisah pada tenant_id, status, dan created_at. PostgreSQL jarang menggabungkan tiga B-Tree index individual secara efisien untuk query semacam ini.

Gunakan aturan Equality-Then-Range/Sort: letakkan kolom dengan kondisi kesetaraan persis (=) di urutan awal, diikuti oleh kolom rentang (>, <, BETWEEN) atau kolom sorting (ORDER BY).

-- Hapus indeks individual redundant jika ada
DROP INDEX IF EXISTS idx_orders_tenant;
DROP INDEX IF EXISTS idx_orders_status;

-- Buat composite index yang sesuai pola query
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created 
ON orders (tenant_id, status, created_at DESC);

Setelah indeks diterapkan, PostgreSQL dapat langsung melompat ke node B-tree target dan mengambil 20 record pertama tanpa sorting di memori:

Limit  (cost=0.42..15.10 rows=20 width=36) (actual time=0.038..0.055 rows=20 loops=1)
  Buffers: shared hit=4 read=0
  ->  Index Scan using idx_orders_tenant_status_created on orders
        Index Cond: ((tenant_id = 42) AND (status = 'COMPLETED'::text))
        Buffers: shared hit=4 read=0
Execution Time: 0.075 ms

Hasil: I/O buffer turun drastis dari 15.700 blok menjadi 4 blok. Execution time terpangkas dari 84 ms ke 0.075 ms.

Optimasi 2: Unindexed Foreign Keys

Secara default, deklarasi FOREIGN KEY di PostgreSQL tidak otomatis membuat indeks pada kolom referensi tersebut. AI generator sering melewatkan ini karena DDL migration ORM hanya menyematkan relasi.

Ketiadaan indeks pada foreign key memicu dua masalah fatal:

  1. Operasi JOIN antara tabel induk dan anak harus melakukan Sequential Scan pada tabel anak.
  2. Operasi DELETE atau UPDATE pada primary key tabel induk akan mengunci atau memindai tabel anak secara menyeluruh untuk memastikan integritas referensial.
-- Identifikasi foreign key yang belum memiliki indeks pendukung
SELECT 
    c.conrelid::regclass AS table_with_fk,
    pg_get_constraintdef(c.oid) AS fk_definition
FROM pg_constraint c
LEFT JOIN pg_index i 
    ON i.indrelid = c.conrelid 
    AND ARRAY[c.conkey] <@ ARRAY[i.indkey]
WHERE c.contype = 'f' 
  AND i.indexrelid IS NULL;

Solusi: Selalu pasang B-Tree index pada kolom foreign key.

CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items (order_id);

Optimasi 3: Partial Index untuk Kolom Status / Soft Delete

Jika tabel memiliki kolom dengan distribusi data timpang (skewed distribution)—misalnya 95% record berstatus PROCESSED dan hanya 5% berstatus PENDING—membuat indeks standar pada kolom status membuang ukuran disk dan RAM buffer pool.

Gunakan Partial Index dengan klausul WHERE untuk hanya mengindeks data yang relevan dicari:

-- Buat indeks hanya untuk record yang membutuhkan pemrosesan aktif
CREATE INDEX CONCURRENTLY idx_orders_pending_processing 
ON orders (created_at ASC) 
WHERE status = 'PENDING';
Catatan: Query SQL harus menyertakan predikat yang sama persis (WHERE status = 'PENDING') agar query planner dapat menggunakan partial index tersebut.

Optimasi 4: Transisi dari OFFSET ke Keyset Pagination

Kode boilerplate vibecoding hampir selalu menggunakan LIMIT n OFFSET m untuk tabel dashboard. Pada offset tinggi (misal: OFFSET 500000), database harus memproses 500.020 baris, mengurutkannya, lalu membuang 500.000 baris pertama.

Query Lama (Limit-Offset):

-- Lambat: Menghabiskan resource I/O untuk memindai data yang dilewati
SELECT id, created_at, total_amount 
FROM orders 
ORDER BY id DESC 
LIMIT 20 OFFSET 100000;

Query Baru (Keyset Pagination):

Gunakan pointer record terakhir (cursor) yang diambil oleh client:

-- Cepat: Langsung lompat ke record spesifik via index seek
SELECT id, created_at, total_amount 
FROM orders 
WHERE id < 985230  -- ID terakhir dari halaman sebelumnya
ORDER BY id DESC 
LIMIT 20;

Pendekatan ini mempertahankan kompleksitas waktu konstan O(log N) terlepas dari seberapa dalam pengguna menelusuri halaman.

Checklist Audit Indeks Sebelum Deploy ke Produksi

  • Cek Ukuran Indeks vs Tabel: Jangan sampai total ukuran index melebihi tabelnya sendiri tanpa alasan teknis yang valid. Pantau via pg_relation_size().
  • Identifikasi Unused Index: Indeks yang tidak pernah terpakai memperlambat operasi INSERT, UPDATE, dan DELETE. Monitor via pg_stat_user_indexes.
  • Gunakan CONCURRENTLY: Di database produksi yang aktif, selalu gunakan CREATE INDEX CONCURRENTLY untuk mencegah locking eksklusif pada tabel yang sedang berjalan.