Optimasi Query PostgreSQL: Strategi Indexing & Partitioning Skala Besar

Mengapa Query Menjadi Lambat?

Ketika volume tabel melampaui 1 juta baris, Sequential Scan (Full Table Scan) akan membebani memory dan I/O disk server secara drastis.

1. Diagnosa dengan EXPLAIN ANALYZE

Jalankan query dengan profiling eksekusi aktual:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions
WHERE status = 'PAID' AND created_at >= '2026-01-01';

Perhatikan metrik:

• Cost: Perkiraan beban komputasi.
• Actual Time: Waktu riil eksekusi milidetik.
• Buffers (Shared Hit/Read): Akses RAM cache vs Disk I/O.

2. Implementasi Partial & Composite Index

Jika 90% transaksi berstatus 'COMPLETED', mengindeks seluruh tabel adalah pemborosan storage. Gunakan Partial Index:

-- Hanya membuat index untuk status aktif yang sering difilter
CREATE INDEX idx_trans_pending_created 
ON transactions (created_at DESC) 
WHERE status = 'PENDING';

3. Table Partitioning Berdasarkan Waktu

Untuk tabel riwayat log atau transaksi finansial bertaraf multi-juta baris, partisi deklaratif PostgreSQL adalah solusi ideal:

CREATE TABLE transaction_logs (
    id UUID NOT NULL,
    user_id UUID NOT NULL,
    amount NUMERIC(15, 2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Partisi per Kuartal
CREATE TABLE logs_2026_q1 PARTITION OF transaction_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');