Anatomi Compute Burn pada Beban Kerja PostgreSQL

Lonjakan biaya compute (vCPU) dan storage I/O pada instance cloud terkelola (AWS RDS, GCP Cloud SQL, atau Azure Database) hampir selalu berakar pada eksekusi query yang membaca lebih banyak halaman data (data blocks) daripada yang sebenarnya dibutuhkan. Pola eksekusi yang tidak efisien memaksa PostgreSQL memuat blok heap disk ke dalam shared_buffers, membakar siklus CPU untuk pemindaian baris, dan mempercepat eviksi buffer pool.

Ketika indeks standar (B-Tree) digunakan pada filter pencarian, database tetap harus mengunjungi heap tabel untuk mengambil kolom-kolom yang ada di klausa SELECT. Pola ini dikenal sebagai random heap access. Melalui Covering Index dengan klausa INCLUDE, seluruh kolom yang dibutuhkan query dimuat langsung di daun B-Tree, memungkinkan engine menjalankan Index Only Scan dengan zero heap fetch.

Identifikasi Query Boros via pg_stat_statements

Langkah pertama mitigasi adalah mengidentifikasi query dengan konsumsi I/O dan eksekusi kumulatif tertinggi. Ekstensi pg_stat_statements mencatat telemetri eksekusi secara real-time.

-- Pastikan ekstensi aktif
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Deteksi 5 query teratas berdasarkan disk I/O dan konsumsi CPU
SELECT 
    queryid,
    substring(query, 1, 60) AS query_truncated,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    shared_blks_read,
    shared_blks_hit,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS buffer_hit_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC, total_exec_time DESC
LIMIT 5;

Metrik kritis yang harus dianalisis:

  • shared_blks_read: Jumlah blok 8KB yang dibaca langsung dari storage engine/disk OS. Tingginya nilai ini memicu compute burn pada disk I/O dan latency.
  • shared_blks_hit: Jumlah blok yang ditemukan di RAM (shared_buffers).
  • buffer_hit_ratio: Rasio ketersediaan data di memori. Jika rasio query jatuh di bawah 99% pada beban kerja OLTP, query tersebut kandidat utama optimasi indexing.

Analisis Kasus: Sequential Scan & Random Heap Fetch

Ambil contoh tabel transaksi berskala besar berikut:

CREATE TABLE customer_orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    order_total NUMERIC(12,2) NOT NULL,
    payment_method VARCHAR(30) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Dummy data volume tinggi
INSERT INTO customer_orders (customer_id, order_status, order_total, payment_method, created_at)
SELECT 
    (random() * 100000)::int,
    (ARRAY['PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED'])[floor(random()*4)+1],
    (random() * 500)::numeric(12,2),
    'CREDIT_CARD',
    NOW() - (random() * interval '90 days')
FROM generate_series(1, 2000000);

Query aplikasi yang sering dieksekusi:

SELECT customer_id, order_total 
FROM customer_orders 
WHERE customer_id = 45210 AND order_status = 'COMPLETED';

Kondisi 1: Index B-Tree Standar

Indeks komposit standar dibuat hanya untuk kolom filter:

CREATE INDEX idx_orders_filter ON customer_orders (customer_id, order_status);

Eksekusi EXPLAIN (ANALYZE, BUFFERS):

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using idx_orders_filter on customer_orders  (cost=0.43..16.48 rows=4 width=12) (actual time=0.041..0.052 rows=5 loops=1)
  Index Cond: ((customer_id = 45210) AND ((order_status)::text = 'COMPLETED'::text))
  Buffers: shared hit=3 read=4
Planning Time: 0.125 ms
Execution Time: 0.071 ms

Pada Index Scan, engine membaca indeks untuk menemukan pointer baris (tuple ID/TID), kemudian melakukan random fetch ke heap tabel untuk mengambil nilai order_total. Bila baris hasil bernilai ratusan atau ribuan, shared_blks_read dan CPU context switching melonjak drastis.

Optimasi Menggunakan Covering Index dengan Klausa INCLUDE

Sejak PostgreSQL 11, fitur Covering Index diimplementasikan secara elegan lewat klausa INCLUDE. Klausa ini membedakan kolom pencarian (search key) dengan kolom muatan (payload/non-search key).

-- Hapus indeks lama
DROP INDEX idx_orders_filter;

-- Buat covering index dengan INCLUDE
CREATE INDEX idx_orders_covering ON customer_orders (customer_id, order_status) INCLUDE (order_total);

Kenapa Bukan B-Tree Komposit (customer_id, order_status, order_total)?

  • Ukuran B-Tree Root & Branch: Kolom di dalam INCLUDE hanya disimpan pada level daun (leaf nodes). Struktur internal B-Tree tetap ramping, muat lebih banyak di RAM, dan proses transversing pohon lebih cepat.
  • Bypass Batasan B-Tree Key Size: PostgreSQL memiliki batasan kapasitas index tuple size (sekitar sepertiga ukuran buffer page 8KB). Menyimpan data di INCLUDE menghindari evaluasi perbandingan (collation parsing overhead) saat navigasi pohon.
  • Semantik Intent: Menunjukkan secara eksplisit kepada perencana query bahwa order_total tidak digunakan untuk operasi sorting (ORDER BY) atau filtering (WHERE).

Kondisi 2: Pasca Penerapan Covering Index

Uji ulang rencana eksekusi:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_orders_covering on customer_orders  (cost=0.43..8.47 rows=4 width=12) (actual time=0.021..0.024 rows=5 loops=1)
  Index Cond: ((customer_id = 45210) AND ((order_status)::text = 'COMPLETED'::text))
  Heap Fetches: 0
  Buffers: shared hit=3
Planning Time: 0.110 ms
Execution Time: 0.038 ms

Perubahan signifikan:

  • Node berubah menjadi Index Only Scan.
  • Heap Fetches: 0: PostgreSQL sama sekali tidak menyentuh blok heap tabel.
  • shared_blks_read: 0: Semua data yang dibutuhkan selesai diselesaikan langsung dari buffer index, mengeliminasi I/O storage sekunder.

Penting: Dependensi Visibility Map
Index Only Scan tetap membutuhkan akses heap jika halaman data belum ditandai sebagai all-visible dalam Visibility Map (VM). Pastikan proses VACUUM (Autovacuum) berjalan sehat agar flag VM selalu diperbarui. Jika autovacuum tertinggal, nilai Heap Fetches akan meningkat meskipun query menggunakan Index Only Scan.

Trade-off dan Biaya Arsitektural

Menerapkan covering index memerlukan pertimbangan kompromi teknis:

  1. Write Amplification: Setiap operasi INSERT atau DELETE harus memperbarui indeks tambahan. Volume storage index bertambah sebanding dengan lebar payload kolom.
  2. Disfungsi HOT (Heap-Only Tuples) Update: Jika nilai kolom yang berada di dalam klausa INCLUDE di-update, PostgreSQL tidak dapat memanfaatkan optimasi HOT update pada tuple tersebut. Indeks baru wajib dialokasikan, meningkatkan frekuensi bloat. Jangan memasukkan kolom yang sering berubah nilainya ke dalam INCLUDE.

Guardrail Konfigurasi Database Melawan Query Anomali

Selain optimasi struktural, database harus memiliki pertahanan (circuit breaker) terhadap query tak terduga yang lolos ke staging/production.

1. statement_timeout

Membatalkan query yang berjalan melewati batas ambang waktu CPU untuk menghindari pemadaman sumber daya:

-- Konfigurasi global di postgresql.conf (misal 5 detik)
statement_timeout = 5000

-- Atau konfigurasi per-role aplikasi pengguna
ALTER ROLE app_user SET statement_timeout = '3s';

2. work_mem Isolation

Pengaturan work_mem yang terlalu tinggi secara global memicu OOM (Out Of Memory) crash ketika banyak query melakukan sorting atau hashing bersamaan. Pertahankan work_mem global pada angka konservatif, naikkan hanya di level session untuk job pelaporan batch.

-- postgresql.conf (default aman untuk konkurensi tinggi)
work_mem = 8MB

-- Naikkan hanya di level worker backend saat batch ETL berjalan
SET LOCAL work_mem = '64MB';

3. idle_in_transaction_session_timeout

Transaksi yang terbuka terlalu lama menahan pembaharuan Visibility Map dan memblokir proses Autovacuum, yang pada akhirnya merusak reliabilitas Index Only Scan.

-- Putus koneksi yang idle dalam transaksi lebih dari 15 detik
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15s';
SELECT pg_reload_conf();

Kesimpulan

Menurunkan tagihan compute PostgreSQL tidak harus diselesaikan dengan scaling instance (vertikal). Identifikasi query pemakan resource melalui pg_stat_statements, identifikasi random access heap, dan konversi ke Index Only Scan via klausa INCLUDE. Gabungkan strategi indeks ini dengan guardrail statement_timeout dan alokasi memori yang terisolasi untuk mengamankan stabilitas sistem produksi.