Performa PostgreSQL pada Skala — Index, VACUUM, dan Optimasi Query

PostgreSQL pada Skala

PostgreSQL bisa menangani tabel dengan miliaran baris — tetapi hanya jika dikonfigurasi dan dimaintain dengan benar. Artikel ini membahas teknik-teknik kunci untuk menjaga performa PostgreSQL pada skala besar.

Jenis Index dan Kapan Menggunakannya

B-Tree (Default)

CREATE INDEX idx_articles_published_at ON articles(published_at DESC);

Cocok untuk: equality, range, sorting. Ini adalah index default dan paling serbaguna.

GIN (Generalized Inverted Index)

CREATE INDEX idx_articles_tags ON articles USING gin(tags);
CREATE INDEX idx_articles_content ON articles USING gin(to_tsvector('indonesian', content));

Cocok untuk: full-text search, JSONB, array. Lambat untuk build/update, cepat untuk query.

GiST

CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

Cocok untuk: spatial data, range type, nearest-neighbor search.

Partial Index

CREATE INDEX idx_articles_published ON articles(published_at DESC)
    WHERE published = true;

Hanya meng-index baris yang memenuhi kondisi. Ukuran index jauh lebih kecil.

Covering Index (INCLUDE)

CREATE INDEX idx_articles_lookup ON articles(locale, slug)
    INCLUDE (title, excerpt);

Menghindari “index-only scan” harus kembali ke heap untuk kolom tambahan.

VACUUM: Kunci Performa Jangka Panjang

PostgreSQL menggunakan MVCC (Multi-Version Concurrency Control). Setiap UPDATE membuat versi baru baris; versi lama menjadi “dead tuple”. VACUUM membersihkan dead tuple ini.

Autovacuum

-- Konfigurasi autovacuum per tabel
ALTER TABLE articles SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% dead tuples
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.02
);

Monitor Dead Tuples

SELECT
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Jika dead_pct > 20%, autovacuum tidak berjalan cukup cepat.

Optimasi Query

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT a.*, c.name
FROM articles a
JOIN categories c ON c.id = a.category_id
WHERE a.locale = 'id' AND a.published = true
ORDER BY a.published_at DESC
LIMIT 20;

Perhatikan:

• Seq Scan pada tabel besar = missing index
• Nested Loop dengan banyak iterasi = pertimbangkan Hash Join
• Buffers: shared hit = data dari cache (baik)
• Buffers: shared read = data dari disk (potensi bottleneck)

Common Table Expressions (CTE)

-- PostgreSQL 12+ mengoptimasi CTE secara otomatis
WITH recent_articles AS (
    SELECT * FROM articles
    WHERE locale = 'id' AND published = true
    ORDER BY published_at DESC
    LIMIT 20
)
SELECT ra.*, array_agg(t.name) AS tag_names
FROM recent_articles ra
JOIN article_tags at ON at.article_id = ra.id
JOIN tags t ON t.id = at.tag_id
GROUP BY ra.id;

Keyset Pagination (vs OFFSET)

-- BURUK: OFFSET melambat seiring halaman bertambah
SELECT * FROM articles ORDER BY published_at DESC OFFSET 10000 LIMIT 20;

-- BAIK: Keyset pagination — konstan
SELECT * FROM articles
WHERE published_at < $1  -- last_seen_published_at
ORDER BY published_at DESC
LIMIT 20;

Konfigurasi Tuning

# postgresql.conf
shared_buffers = 4GB          # 25% RAM
effective_cache_size = 12GB   # 75% RAM
work_mem = 64MB               # Per-query sort/hash memory
maintenance_work_mem = 1GB    # VACUUM, CREATE INDEX
wal_buffers = 64MB
max_connections = 200
random_page_cost = 1.1        # SSD (default 4.0 untuk HDD)
effective_io_concurrency = 200 # SSD

Monitoring

-- Query paling lambat
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Kesimpulan

Performa PostgreSQL pada skala memerlukan tiga pilar: index yang tepat, VACUUM yang terjaga, dan query yang teroptimasi. Monitoring aktif dengan pg_stat_statements dan pg_stat_user_tables memberikan visibilitas ke masalah sebelum pengguna merasakannya.