Optimasi PostgreSQL: Teknik Non-Konvensional untuk Query yang Lebih Cepat

PostgreSQL adalah database relational yang powerful dan andal, namun performanya sering menurun ketika volume data bertambah besar atau query menjadi semakin kompleks. Banyak developer langsung beralih ke solusi hardware seperti menambah RAM, upgrade ke SSD yang lebih cepat, atau bahkan scaling ke read replica. Padahal, ada banyak teknik optimasi non-konvensional yang bisa diterapkan di level aplikasi, query, dan schema tanpa biaya infrastruktur tambahan sama sekali.

Artikel ini merangkum teknik-teknik optimasi PostgreSQL yang jarang dibahas di tutorial standar atau dokumentasi dasar. Semua contoh menggunakan syntax PostgreSQL 15+ dan diasumsikan kamu sudah familiar dengan konsep dasar indeks, EXPLAIN ANALYZE, dan tuning parameter konfigurasi. Penerapannya bisa langsung di production dengan risiko minimal karena semua teknik ini adalah standard PostgreSQL feature.

Partial Indexing untuk Query yang Spesifik

Kebanyakan developer menggunakan indeks B-tree pada seluruh kolom tanpa mempertimbangkan pola query yang sebenarnya dijalankan aplikasi. Padahal, jika query kamu hanya mengakses subset data tertentu, partial index jauh lebih efisien dalam hal ukuran, kecepatan, dan maintenance overhead. Partial index memungkinkan kamu membuat indeks hanya untuk baris yang memenuhi kondisi tertentu.

Contoh kasus nyata: tabel orders dengan kolom status dan created_at. Kamu sering query order yang berstatus pending dalam 7 hari terakhir untuk dashboard admin atau notification worker:

CREATE INDEX idx_recent_pending_orders ON orders (created_at)
WHERE status = 'pending' AND created_at > CURRENT_DATE - INTERVAL '7 days';

Indeks ini jauh lebih kecil dan lebih cepat dibanding indeks penuh pada created_at. PostgreSQL juga lebih efisien dalam maintenance karena hanya memperbarui indeks ketika baris memenuhi kondisi WHERE. Hasilnya adalah write throughput yang lebih baik dan disk space yang lebih hemat. Untuk tabel dengan 100 juta baris di mana hanya 5% yang pending, partial index bisa menghemat 95% disk space dibanding indeks penuh.

Expression Index untuk Query yang Mengandung Fungsi

Query yang menggunakan fungsi pada kolom sering tidak bisa memanfaatkan indeks biasa karena fungsi mengubah nilai yang diindeks. Solusinya adalah expression index yang menyimpan hasil evaluasi ekspresi, bukan nilai kolom mentah. Ini adalah fitur yang sangat powerful namun sering terlupakan.

Misalnya, aplikasi kamu sering mencari email tanpa memperhatikan case sensitivity:

CREATE INDEX idx_email_lower ON users (LOWER(email));

Dengan indeks ini, query berikut bisa menggunakan index scan yang sangat cepat:

SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');

Tips penting: hindari membuat expression index pada fungsi yang tidak stabil (volatile) seperti RANDOM() atau NOW(), karena hasilnya berubah setiap kali dipanggil dan PostgreSQL tidak bisa mengoptimalkannya dengan baik. Selalu periksa volatility category fungsi di dokumentasi PostgreSQL sebelum membuat expression index.

Covering Index untuk Eliminate Table Access

Covering index atau index-only scan adalah teknik canggih di mana semua kolom yang dibutuhkan query tersedia dalam indeks. PostgreSQL tidak perlu mengakses heap table sama sekali, mengurangi I/O secara drastis terutama untuk tabel dengan banyak kolom atau row yang lebar. Ini adalah optimasi paling efektif untuk query OLAP atau reporting.

Untuk membuat covering index, gunakan clause INCLUDE yang tersedia sejak PostgreSQL 11:

CREATE INDEX idx_users_covering ON users (department)
INCLUDE (name, email, salary);

Query berikut bisa melakukan index-only scan tanpa touching heap sama sekali:

SELECT name, email, salary FROM users WHERE department = 'Engineering';

Perlu diingat, INCLUDE column hanya disimpan di leaf node indeks, sehingga ukuran indeks tetap terjaga. Namun, jika terlalu banyak kolom disertakan, overhead maintenance write bisa meningkat secara signifikan. Gunakan covering index untuk query yang sering dijalankan dengan pola select yang konsisten dan kolom yang relatif statis.

Partitioning untuk Query Time-Series

Untuk tabel dengan volume data besar yang terus tumbuh, seperti log, metrics, atau event data, declarative partitioning adalah solusi ideal yang sudah native di PostgreSQL. Fitur ini tersedia sejak versi 10 dan terus diperbaiki di setiap rilis. Partitioning sangat cocok untuk data time-series di mana query selalu memiliki filter waktu.

Contoh setup partitioning untuk tabel logs dengan range berdasarkan waktu:

CREATE TABLE logs (
    id BIGSERIAL,
    event_time TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (event_time);

CREATE TABLE logs_2026_01 PARTITION OF logs
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Keuntungan utama: query dengan filter pada event_time hanya akan membaca partition yang relevan. PostgreSQL melakukan partition pruning secara otomatis saat query planning. Maintenance juga lebih mudah karena kamu bisa drop partition lama tanpa DELETE yang mahal dan menyebabkan bloat. Untuk data archival, kamu bisa detach partition lama dan attach ke tabel archive dengan downtime minimal.

VACUUM Strategy yang Tepat

PostgreSQL menggunakan MVCC yang menyimpan multiple versi baris untuk concurrency control. Tanpa VACUUM yang efektif, table bloat akan meningkat pesat dan performa query menurun drastis karena PostgreSQL harus scan dead tuples yang tidak perlu. Namun, VACUUM yang terlalu agresif bisa mengganggu workload production dengan lock contention dan CPU spikes.

Strategi terbaik untuk workload write-heavy adalah menyesuaikan threshold autovacuum per tabel:

ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

Nilai default autovacuum_vacuum_scale_factor adalah 0.2 (20%). Untuk tabel dengan miliaran baris, ini berarti bloat harus mencapai ratusan juta baris sebelum VACUUM aktif. Mengurangi ke 5% atau bahkan menggunakan threshold absolute dengan autovacuum_vacuum_threshold lebih responsif dan mencegah bloat membesar.

Untuk tabel yang sudah sangat bloat, pertimbangkan menggunakan pg_repack atau pg_squeeze untuk rebuild secara online tanpa exclusive lock yang mengganggu operasional. Kedua tool ini membuat tabel baru di background dan melakukan switchover secara atomik. pg_repack adalah yang paling umum digunakan dan didukung oleh komunitas.

Conclusion dan Referensi

Optimasi PostgreSQL tidak selalu berarti menambah resource atau melakukan refactoring besar. Dengan teknik partial indexing, expression index, covering index, declarative partitioning, dan VACUUM tuning yang tepat, kamu bisa mendapatkan peningkatan performa signifikan dari sisi software engineering. Semua teknik ini menggunakan fitur native PostgreSQL yang sudah well-tested di production di berbagai skala.

Sebelum menerapkan teknik ini di production, selalu gunakan EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) untuk membandingkan execution plan sebelum dan sesudah optimasi. Lakukan benchmark dengan data yang representative. Dokumentasi lengkap PostgreSQL tersedia di official PostgreSQL documentation.