Optimasi AllocSet PostgreSQL: Cegah Spill to Disk pada Query Agregasi

Mekanisme AllocSet dan HashAggregate Memory Spill

PostgreSQL mengelola alokasi memori dinamis menggunakan hirarki MemoryContext. Implementasi standar untuk eksekusi query adalah AllocSet (Allocation Set). AllocSet mengalokasikan memori dalam blok-blok besar bertahap (mulai dari 8 KB hingga kelipatan 8 MB) dan membaginya ke dalam chunk-chunk kecil menggunakan prinsip bump allocator—mirip pola alokasi linier pada arena memory LLVM—dengan tambahan freelist untuk pemakaian ulang memori yang dibebaskan.

Ketika query menjalankan operasi HashAggregate, PostgreSQL menyusun hashtable di dalam satu konteks AllocSet untuk melacak status akumulasi setiap grup. Batas atas konsumsi memori untuk satu node eksekutor ini dikendalikan oleh parameter work_mem. Masalah muncul karena AllocSet memiliki overhead metadata per-chunk serta fragmentasi internal. Ketika estimasi ukuran hashtable melebihi alokasi work_mem, eksekutor PostgreSQL terpaksa beralih dari operasi murni in-memory menjadi eksekusi multi-batch. Data partisi yang tidak muat akan ditumpahkan (spill) ke disk menggunakan berkas temporer (temp files), memicu latensi I/O disk yang masif.

Diagnosis Query: Membedah EXPLAIN (ANALYZE, BUFFERS, MEMORY)

Berikut adalah skenario query agregasi lambat pada tabel transaksi e-commerce bervolume tinggi:

SELECT 
    customer_id, 
    DATE_TRUNC('day', transaction_date) AS tx_date,
    COUNT(order_id) AS total_orders,
    SUM(amount) AS total_spent
FROM transactions
GROUP BY customer_id, DATE_TRUNC('day', transaction_date);

Mulai PostgreSQL 13, parameter MEMORY dapat ditambahkan ke perintah EXPLAIN untuk menginspeksi alokasi memori eksekutor secara langsung:

EXPLAIN (ANALYZE, BUFFERS, MEMORY) 
SELECT 
    customer_id, 
    DATE_TRUNC('day', transaction_date) AS tx_date,
    COUNT(order_id) AS total_orders,
    SUM(amount) AS total_spent
FROM transactions
GROUP BY customer_id, DATE_TRUNC('day', transaction_date);

Output rencana eksekusi menunjukkan degradasi performa berikut:

HashAggregate  (cost=85420.00..98210.00 rows=450000 width=36) (actual time=1120.450..2480.320 rows=450000 loops=1)
  Group Key: customer_id, DATE_TRUNC('day', transaction_date)
  Batches: 8  Memory Usage: 4161kB  Disk Usage: 38912kB
  Buffers: shared hit=12540 read=32460, temp read=4864 written=4864
  ->  Seq Scan on transactions  (cost=0.00..52400.00 rows=1500000 width=24) (actual time=0.045..280.120 rows=1500000 loops=1)
        Buffers: shared hit=12540 read=32460
Planning Time: 0.182 ms
Execution Time: 2510.420 ms

Poin kritis pada output di atas:

• Batches: 8: HashAggregate tidak dapat menyelesaikan seluruh hashing dalam satu siklus memori. Data dibagi menjadi 8 batch terpisah.
• Disk Usage: 38912kB: Data intermediate sebesar ~38 MB ditulis ke media penyimpanan lokal.
• temp read=4864 written=4864: Operasi I/O temp file membakar siklus CPU dan latency throughput disk.

Strategi Optimasi 1: Isolasi work_mem pada Tingkat Transaksi/Sesi

Meningkatkan work_mem secara global di postgresql.conf sangat berisiko karena setiap node query independen dalam satu koneksi konkuren dapat mengalokasikan nilai tersebut, berpotensi memicu Linux OOM Killer. Naikkan alokasi hanya pada sesi yang mengeksekusi batch agregasi:

BEGIN;
-- Alokasikan memori cukup agar seluruh hashtable muat di RAM
SET LOCAL work_mem = '128MB';

SELECT 
    customer_id, 
    DATE_TRUNC('day', transaction_date) AS tx_date,
    COUNT(order_id) AS total_orders,
    SUM(amount) AS total_spent
FROM transactions
GROUP BY customer_id, DATE_TRUNC('day', transaction_date);

COMMIT;

Dampaknya, metrik rencana eksekusi berubah menjadi Batches: 1, menghilangkan Disk Usage dan temp read/written sepenuhnya.

Strategi Optimasi 2: Eliminasi AllocSet Hash Churn via GroupAggregate Streaming

Meskipun menaikkan work_mem menyelesaikan spill to disk, HashAggregate tetap memiliki kompleksitas memori $O(K)$ di mana $K$ adalah jumlah entri grup unik dalam hashtable. Alternatif paling efisien adalah mengubah metode agregasi menjadi GroupAggregate streaming dengan memori $O(1)$.

GroupAggregate hanya membutuhkan satu baris/grup aktif di memori AllocSet jika data masukan sudah terurut. Begitu kunci grup berganti, PostgreSQL mengosongkan konteks memori baris via MemoryContextReset(), mencegah memory footprint membesar tanpa peduli berapa ratus juta baris yang dihitung.

Buat index penutup (covering index) yang mengevaluasi ekspresi pengelompokan:

CREATE INDEX idx_transactions_group_covering 
ON transactions (customer_id, (DATE_TRUNC('day', transaction_date))) 
INCLUDE (order_id, amount);

Rencana eksekusi kini berubah secara fundamental:

GroupAggregate  (cost=0.43..48120.10 rows=450000 width=36) (actual time=0.082..480.110 rows=450000 loops=1)
  Group Key: customer_id, (DATE_TRUNC('day', transaction_date))
  Buffers: shared hit=42100
  ->  Index Only Scan using idx_transactions_group_covering on transactions  (cost=0.43..32150.20 rows=1500000 width=24) (actual time=0.065..210.450 rows=1500000 loops=1)
        Buffers: shared hit=42100
Planning Time: 0.210 ms
Execution Time: 512.310 ms

Pada rencana di atas, node HashAggregate digantikan oleh streaming GroupAggregate. Seluruh I/O disk temporer lenyap dan overhead AllocSet ditekan ke batas minimum.

Strategi Optimasi 3: Reduksi Kardinalitas Melalui Pre-Agregasi

Jika query melibatkan relasi multi-tabel (JOIN), jangan lakukan agregasi setelah operasi join selesai. Lakukan agregasi parsial di subquery terlebih dahulu untuk memangkas baris yang masuk ke AllocSet memory builder:

-- Pola Sub-Optimal (Hashtable memuat hasil ekspansi baris JOIN)
SELECT c.name, COUNT(t.order_id), SUM(t.amount)
FROM customers c
JOIN transactions t ON c.id = t.customer_id
GROUP BY c.id, c.name;

-- Pola Optimal (Hashtable diisolasi pada kolom numerik agregat terlebih dahulu)
SELECT 
    c.name, 
    agg.total_orders, 
    agg.total_spent
FROM customers c
JOIN (
    SELECT 
        customer_id,
        COUNT(order_id) AS total_orders,
        SUM(amount) AS total_spent
    FROM transactions
    GROUP BY customer_id
) agg ON c.id = agg.customer_id;

Verifikasi Metrik: Sebelum vs Sesudah Optimasi

Prioritaskan penggunaan index penutup untuk mengubah alur ke GroupAggregate jika beban kerja agregasi berjalan periodik dalam konkurensi tinggi. Gunakan peningkatan work_mem tingkat transaksi hanya pada pipeline reporting atau analytical load ad-hoc yang tidak memungkinkan pembuatan indeks baru.