Optimasi Query PostgreSQL Skala Jutaan Baris: Indexing, Table Partitioning, dan Connection Pooling

Pada tahap awal pengembangan aplikasi web, performa database PostgreSQL jarang menimbulkan masalah karena volume data masih dalam kisaran puluhan ribu baris. Namun, ketika platform bertransformasi melayani transaksi B2B, log aktivitas audit, atau katalog multi-entitas dengan jutaan SKU, query pencarian yang sebelumnya berjalan dalam 5 milidetik dapat membengkak menjadi 4 detik akibat sequential scan dan disk I/O thrashing.

Mengatasi degradasi performa pada skala data besar membutuhkan pemahaman mendalam tentang arsitektur penyimpanan PostgreSQL: pemilihan jenis indeks yang tepat, pemecahan tabel fisik (table partitioning), dan manajemen koneksi menggunakan connection pooler terpisah.

Anatomi Indeks PostgreSQL: Kapan Menggunakan B-Tree, BRIN, dan GIN? #

Kesalahan paling umum adalah membuat indeks B-Tree default pada setiap kolom yang muncul di klausa WHERE. B-Tree sangat serbaguna untuk data dengan kardinalitas tinggi dan query kesetaraan titik tunggal (equality lookup). Namun, pada tabel dengan ratusan juta baris, indeks B-Tree menghabiskan memori RAM yang sangat besar dan memperlambat operasi INSERT secara signifikan.

Implementasi BRIN untuk Data Time-Series dan Log Audit #

Jika sebuah tabel log transaksi atau audit memiliki 50 juta baris yang selalu dimasukkan secara berurutan berdasarkan waktu, indeks BRIN (Block Range Index) menawarkan penghematan memori dramatis:

-- Membuat indeks BRIN pada kolom timestamp transaksi
-- Menyusutkan ukuran indeks dari 1.2 GB (B-Tree) menjadi hanya 450 KB
CREATE INDEX idx_transaksi_created_at_brin 
ON audit_transaksi_log USING brin (created_at) 
WITH (pages_per_range = 128);

Indeks BRIN hanya membaca rentang blok disk yang relevan dengan filter tanggal. Query WHERE created_at >= '2026-11-01' dapat melompati 95% blok tabel tanpa perlu memuat pohon indeks raksasa ke dalam shared_buffers.

Optimasi GIN untuk Kolom Dokumen JSONB #

Pada katalog produk B2B di mana spesifikasi teknis disimpan dalam format JSONB dinamis, indeks GIN dengan operator jsonb_path_ops memberikan kecepatan pencarian optimal:

-- Menggunakan jsonb_path_ops untuk pencarian containment (@>)
CREATE INDEX idx_katalog_spesifikasi_gin 
ON produk_b2b USING gin (spesifikasi jsonb_path_ops);

-- Query yang memanfaatkan indeks di atas secara instan
EXPLAIN ANALYZE
SELECT id, nama_produk 
FROM produk_b2b 
WHERE spesifikasi @> '{"standar_industri": "ISO-9001", "grade": "marine"}';

Partisi Tabel Deklaratif (Declarative Table Partitioning) #

Ketika sebuah tabel tunggal melebihi kapasitas memori RAM server (misalnya lebih dari 50 GB), kinerja query analitik dan pembersihan data lama (data pruning) akan anjlok. PostgreSQL menyediakan mekanisme Declarative Partitioning yang membagi tabel logis menjadi beberapa tabel fisik yang lebih kecil.

               Tabel Induk (orders_master)
                       │
         ┌─────────────┼─────────────┐
         ▼             ▼             ▼
  Partisi Q1 2026  Partisi Q2 2026  Partisi Q3 2026
  (orders_2026_q1) (orders_2026_q2) (orders_2026_q3)
  [Rentang Waktu]   [Rentang Waktu]  [Rentang Waktu]

Konfigurasi Partisi Berdasarkan Rentang Waktu (Range Partitioning) #

-- 1. Membuat tabel master dengan skema partisi
CREATE TABLE transaksi_perusahaan (
    id BIGSERIAL,
    organisasi_id INT NOT NULL,
    total_nilai NUMERIC(15, 2) NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. Membuat partisi spesifik per kuartal
CREATE TABLE transaksi_2026_q1 PARTITION OF transaksi_perusahaan
    FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE transaksi_2026_q2 PARTITION OF transaksi_perusahaan
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-07-01 00:00:00+00');

CREATE TABLE transaksi_2026_q3 PARTITION OF transaksi_perusahaan
    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');

Dengan partisi deklaratif, fitur Partition Pruning pada engine query planner akan secara otomatis mengabaikan partisi yang tidak sesuai dengan filter tanggal pada query pengguna. Selain itu, penghapusan data lama berumur 5 tahun cukup dilakukan dengan perintah DROP TABLE transaksi_2021_q1; yang membebaskan ruang disk dalam milidetik tanpa menimbulkan lock contention seperti DELETE FROM.

Mengatasi Masalah Connection Starvation dengan PgBouncer #

Arsitektur web modern yang menjalankan ratusan instance container (seperti Kubernetes pods atau Node.js worker threads) sering kali membuka koneksi langsung ke PostgreSQL. Setiap koneksi PostgreSQL di sisi server mengalokasikan proses prosesor mandiri (forked process) yang memakan sekitar 10 MB RAM dan memicu persaingan memori CPU (context switching).

Jika aplikasi membuka 500 koneksi bersamaan, server database akan mengalami degradasi drastis (connection saturation).

Solusi standarnya adalah menempatkan PgBouncer di depan PostgreSQL dengan mode Transaction Pooling:

; Konfigurasi pgbouncer.ini untuk throughput tinggi
[databases]
puluhlabs_db = host=127.0.0.1 port=5432 dbname=puluhlabs

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Mode transaction pooling mendaur ulang koneksi server segera setelah transaksi selesai
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 30
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 50

Dalam mode transaction pooling, 2.000 klien web dapat terhubung secara bersamaan ke PgBouncer pada port 6432, sementara PgBouncer hanya meneruskan beban transaksi melalui 30 hingga 50 koneksi persisten ke server PostgreSQL pada port 5432. Hal ini menstabilkan penggunaan CPU database dan mencegah insiden Out-Of-Memory (OOM).

Parameter Penyetelan Kinerja Kritis pada postgresql.conf #

Pengaturan bawaan (default) PostgreSQL dirancang sangat konservatif agar dapat berjalan pada komputer berspesifikasi rendah. Untuk server produksi B2B dengan RAM 16 GB dan SSD NVMe, parameter berikut wajib disesuaikan:

# postgresql.conf untuk server produksi RAM 16 GB, 4 vCPU SSD NVMe

# Alokasi buffer memori utama (25% dari total RAM sistem)
shared_buffers = 4GB

# Estimasi memori yang tersedia untuk caching OS dan kernel (75% dari RAM)
effective_cache_size = 12GB

# Memori per operasi sorting dan hash table per query
work_mem = 64MB

# Memori untuk operasi VACUUM dan penambahan indeks
maintenance_work_mem = 1GB

# Estimasi biaya akses disk acak untuk storage SSD NVMe (default HDD = 4.0)
random_page_cost = 1.1

# Paralelisasi query untuk tabel raksasa
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Setelah menerapkan parameter di atas dan me-restart PostgreSQL, jalankan VACUUM ANALYZE secara menyeluruh agar statistik perencana kueri (query planner statistics) terbarui secara akurat sesuai distribusi data terkini.