- Saat mengoperasikan job queue di Postgres, masalah klasik berupa akumulasi dead tuple dan table bloat yang ditimbulkannya dapat menurunkan kinerja; artikel ini merangkum penyebab dan cara mengatasinya
- Tabel queue umumnya memiliki sebagian besar baris yang berputar singkat dalam pola insert-read-delete, sehingga ukurannya tampak tetap tetapi throughput kumulatifnya sangat besar
- Dalam struktur MVCC Postgres, baris yang dihapus tidak langsung dibuang, melainkan tersisa sebagai dead tuple yang harus dibersihkan; tugas ini ditangani oleh autovacuum
- Jika transaksi yang berjalan lama atau query analitik yang saling overlap menahan MVCC horizon, autovacuum tidak bisa membersihkan dead tuple sehingga performa queue menurun
- Fitur Traffic Control milik PlanetScale (bagian dari ekstensi Insights) ditawarkan sebagai cara praktis untuk mengatasi masalah ini lewat pembatasan resource per kelas query
Karakteristik workload queue
- Ciri khas tabel queue adalah sebagian besar barisnya bersifat sementara (transient) — diinsert, dibaca satu kali, lalu dihapus
- Ukuran tabel nyaris tetap, tetapi throughput kumulatifnya sangat besar
- Keuntungan utama menaruh job queue di Postgres adalah memungkinkan sinkronisasi status job dan logika DB lain dalam transaksi yang sama
- jika job gagal, seluruh transaksi akan di-rollback
- jika memakai layanan queue eksternal, sinkronisasi dengan status transaksi aplikasi menjadi lebih rumit
Contoh tabel queue dan cara kerja worker
- Skema dasar yang ditunjukkan dalam artikel
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
run_at TIMESTAMPTZ DEFAULT now(),
status TEXT DEFAULT 'pending',
payload JSONB
);
CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = 'pending';
- Worker membuka transaksi dan mengunci pending job tertua dengan
FOR UPDATE SKIP LOCKED untuk mencegah pemrosesan ganda
- Jika pekerjaan berhasil, lakukan
DELETE lalu COMMIT; jika gagal, transaksi di-rollback sehingga baris itu kembali terlihat bagi worker lain
- Transaksi ini harus dijaga sesingkat mungkin — makin lama terbuka, makin besar hambatannya terhadap vacuum (contoh di artikel memakai worker sub-milidetik)
Bukan performa dasarnya yang jadi masalah
- Sudah terdokumentasi bahwa Postgres mampu menangani job queue berskala besar, jadi kapabilitas dasarnya bukan masalah
- Masalah sesungguhnya adalah hidup berdampingan dengan workload lain yang bersaing di DB yang sama
- Kesehatan tabel queue tidak hanya ditentukan oleh konfigurasinya sendiri, tetapi juga oleh perilaku semua transaksi yang berjalan di instance Postgres yang sama
- Artikel berfokus pada trafik query yang saling berebut di primary (dampak replica dan replication slot dibahas terpisah)
Masalah sebenarnya: pembersihan dead tuple
- Postgres memakai MVCC untuk mempertahankan beberapa versi dari baris yang sama — baris yang dihapus tidak langsung dibuang, tetapi hanya ditandai terhapus dan menjadi tidak terlihat bagi transaksi baru
- Baris yang tersisa seperti ini disebut dead tuple dan dibersihkan lewat operasi vacuum
- Dead tuple tidak muncul di hasil
SELECT, tetapi tetap menimbulkan biaya
- Sequential scan: executor membaca dead tuple dari heap page, mengecek visibilitasnya, lalu membuangnya
- Index scan (seperti pada queue dengan
ORDER BY run_at LIMIT 1): indeks B-tree menumpuk referensi ke dead tuple, sehingga pemindaian ikut melewati entri yang menunjuk ke baris yang sudah tak terlihat
- Setiap dead index entry memicu I/O tambahan; tidak terlihat oleh aplikasi, tetapi biayanya bisa meningkat tajam seiring jumlah dead tuple
- Siklus pembersihan ditentukan oleh
autovacuum_naptime (default 1 menit), sedangkan apakah vacuum dijalankan bergantung pada autovacuum_vacuum_threshold dan autovacuum_vacuum_scale_factor
Mekanisme internal dead tuple
- Ada 3 metadata baris yang penting
ctid: lokasi fisik tuple di heap (page, offset)xmin: ID transaksi (XID) yang menginsert baris inixmax: ID transaksi yang menghapus/mengunci baris ini; 0 berarti belum ditandai terhapus
- Saat mengambil 3 pending job, executor bisa saja harus memindai 6 dead tuple yang sebelumnya sudah dihapus lalu hanya mengembalikan 3 baris
- Pada indeks juga sama: jika leaf entry menunjuk ke heap tuple yang sudah dead, kerja sia-sia saat scan akan terus menumpuk
- Jika DB lebih lambat membersihkan daripada menghasilkan dead tuple, sistem masuk ke lintasan kegagalan
- Cluster Postgres yang dituning dengan baik dapat menangani throughput queue hingga puluhan ribu per detik
Saat autovacuum menjadi tidak berdaya
- Penyebab utama autovacuum gagal membersihkan dead tuple
- table lock tertentu menghalangi cleanup
- konfigurasi autovacuum yang tidak tepat
- paling sering, transaksi aktif mencegah reclaim dead tuple
- Postgres tidak akan mem-vacuum dead tuple yang masih mungkin terlihat oleh transaksi aktif
- transaksi aktif tertua menentukan cutoff → MVCC horizon
- sampai transaksi itu selesai, semua dead tuple setelah snapshot tersebut harus dipertahankan
- Satu transaksi tunggal berdurasi 2 menit bisa mengunci horizon selama 2 menit
- Pola kegagalan yang sama juga bisa timbul dari query berdurasi menengah yang saling overlap
- contoh: 3 query analitik masing-masing 40 detik dijalankan selang-seling tiap 20 detik; tiap query memang tidak timeout, tetapi selalu ada satu yang aktif sehingga horizon tidak pernah maju
- Dalam filosofi “Just use Postgres” yang menaruh banyak workload di satu DB, masalahnya bukan pemrosesan job cepat itu sendiri, melainkan query lambat yang overlap sehingga pembersihan dead tuple tertunda
Alat yang ada dan keterbatasannya
- Opsi tuning autovacuum:
autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
- Timeout untuk membatasi query lama
statement_timeout (Postgres 7.3): menghentikan statement SQL individual yang melewati batas waktuidle_in_transaction_session_timeout (9.6): menghentikan sesi yang terlalu lama idle di dalam transaksitransaction_timeout (17.0): menghentikan transaksi aktif maupun nonaktif yang melampaui waktu yang ditentukan
- Timeout ini hanya menargetkan durasi query tunggal, bukan concurrency atau biaya eksekusi, sehingga tidak cocok untuk menghentikan workload yang terus-menerus menahan MVCC horizon
- Yang dibutuhkan adalah pemisahan per kelas trafik, sehingga workload prioritas tinggi tetap berjalan sementara tingkat penggunaan resource workload prioritas rendah dapat diatur
Database Traffic Control™
- Fitur ini merupakan bagian dari ekstensi Insights yang dikembangkan PlanetScale dan khusus untuk PlanetScale Postgres
- Digunakan untuk mengontrol performa dan penggunaan resource query individual secara rinci
- Dengan Resource Budget, query target diberi batas resource → dapat diblokir saat melampaui batas
- Strategi penyelesaiannya adalah membatasi jumlah dan frekuensi query lambat yang overlap, agar autovacuum punya ruang untuk membersihkan dead tuple dengan laju yang memadai
- Query yang diblokir tidak ditolak permanen, tetapi harus dicoba ulang, sehingga logika retry di aplikasi menjadi penting
- Pendekatannya adalah meratakan kecepatan eksekusi sambil mempertahankan total volume pekerjaan
Konfigurasi demo dan latar belakang
- Ide artikel ini berasal dari blog Brandur Leach tahun 2015 berjudul "Postgres Job Queues & Failure By MVCC"
- mencatat failure mode fatal pada job queue berbasis Postgres
- menyertakan test bench yang membuktikan bagaimana transaksi yang tidak ditutup menahan MVCC horizon dan mencegah cleanup
- Test bench aslinya tersedia di
brandur/que-degradation-test
Mereproduksi masalah (berdasarkan Postgres 18)
- Pengujian asli berbasis Ruby + gem Que v0.x + Postgres 9.4
- Penulis menulis ulang dengan TypeScript + Bun untuk mengisolasi dan memverifikasi perilaku pada level SQL
- Mempertahankan pola recursive CTE yang sama seperti Que, schema yang sama, producer rate, durasi kerja, jumlah worker, dan pola long-runner yang sama
- Dijalankan di cluster PlanetScale PS-5 (mulai dari $5/bulan)
- Hasilnya: penurunan performa yang terlihat, tetapi masih terkendali
- pengujian asli mendorong DB ke death spiral dalam 15 menit, tetapi pada PS-5 worker queue tetap mendekati 0 selama 15 menit
- namun dead tuple terus meningkat secara linear, menandakan masalah yang sama akan muncul lagi jika durasinya diperpanjang
- berkat perbaikan pembersihan indeks B-tree (bottom-up deletion untuk version churn, penghapusan dead index tuple berbasis scan, dan lain-lain), masalah ini berkurang tetapi belum hilang
Upaya perbaikan: SKIP LOCKED + batch processing
- Ada 2 perbaikan modern yang belum ada pada 2015
FOR UPDATE SKIP LOCKED — mengganti seluruh recursive CTE dengan satu SELECT, sambil melewati baris yang sudah dikunci worker lain- Batch processing (10 job per transaksi) — memproses 10 job dalam sekali lock acquisition, sehingga biaya index scan bisa tersebar
- Kondisi tetap sama: worker 8, producer 50 job/detik, work 10ms, long-runner mulai setelah 45 detik
- Hasil utama
| Metrik |
original (recursive CTE) |
enhanced (SKIP LOCKED + batch) |
| Baseline lock time |
2–3ms |
1.3–3.0ms |
| End lock time (typical) |
10–34ms |
9–29ms |
| Worst spike |
84.5ms (dead tuple 33k) |
180ms (dead tuple 24k) |
| Queue depth |
0–100 (oscillating) |
0 (sebagian besar waktu) |
| Dead tuples at end |
42,400 |
42,450 |
| Throughput |
~89/s |
~50/s |
- Kurva degradasinya hampir sama — keduanya memindai indeks B-tree yang sama dan menemui dead tuple yang sama
- Perbedaan throughput bukan karena strategi lock, melainkan karena desain pengujian (worker CTE mengambil job lebih cepat dari producer, sedangkan worker batch mengosongkan queue lalu tidur backoff)
- Kesimpulannya: desain queue yang 10 tahun lalu bisa mematikan DB dalam 15 menit sekarang bisa bertahan lebih lama, tetapi masalah dasarnya tetap ada — jika dinaikkan ke 500 job/detik, masalah akan muncul lebih cepat
Menyelesaikannya dengan Traffic Control
- Resource Budget menyediakan kontrol berikut
- Server share & burst limit: proporsi resource server dan laju konsumsinya
- Per-query limit: batas waktu query berdasarkan penggunaan server dalam satuan detik
- Maximum concurrent workers: rasio terhadap worker process yang tersedia
- Penargetan query umumnya dilakukan lewat metadata pada tag SQLCommenter (misalnya
action=analytics)
- Alih-alih long-runner yang akan tertangkap
idle_in_transaction_session_timeout, artikel memicu degradasi dengan skenario yang lebih realistis, yaitu query analitik aktif yang saling overlap (kasus yang tidak bisa ditangkap session timeout)
- Maximum concurrent workers untuk query
action=analytics dibatasi ke 1 worker (max_worker_processes 25%) → hanya 1 query analitik yang boleh berjalan bersamaan
- Untuk memicu death spiral dalam jendela 15 menit, producer dinaikkan menjadi 800 job/detik
- Dari EC2 ke DB PlanetScale yang sama, workload "enhanced" dijalankan dua kali
- 800 job/detik
- 3 query analitik berdurasi 120 detik dijalankan bersamaan dan disusun agar terus overlap
- berlangsung selama 15 menit
- Perbandingan hasil
| Metrik |
Traffic Control nonaktif |
Traffic Control aktif |
| Queue backlog |
155,000 jobs |
0 jobs |
| Lock time |
300ms+ |
2ms |
| Dead tuples at end |
383,000 |
0–23,000 (cycling) |
| Analytics queries |
3 concurrent, overlapping |
1 at a time, 2 retrying |
| VACUUM effectiveness |
Blocked (horizon tertahan) |
Normal (ada jeda cleanup di antara query) |
| Outcome |
Death spiral |
Completely stable |
- Traffic Control secara langsung membatasi concurrency workload tertentu, memberikan kontrol yang tidak bisa dicapai hanya dengan tuning autovacuum atau timeout
- Laporan analitik tetap berjalan selama kapasitas memungkinkan dan 15 pekerjaan selesai dalam 15 menit, sementara queue tetap sehat sepanjang waktu
Ringkasan
- Masalah MVCC dead tuple pada queue berbasis Postgres bukan peninggalan 2015 semata
- Postgres modern memberi ruang napas lebih besar lewat perbaikan B-tree dan
SKIP LOCKED, tetapi mekanisme dasarnya tetap sama
- jika VACUUM gagal membersihkan dead tuple, jumlahnya akan terus menumpuk
- jika transaksi yang lama berjalan atau saling overlap menahan MVCC horizon, VACUUM tidak bisa membersihkannya
- Dalam lingkungan “Just use Postgres” yang menaruh queue, analitik, dan logika aplikasi di satu DB, ini bukan risiko teoritis, melainkan kondisi operasional sehari-hari
- Bentuk berbahayanya bukan crash dramatis, melainkan keseimbangan yang diam-diam memburuk — lock time perlahan naik, job makin lambat, dan alarm tidak berbunyi
- Alat timeout di Postgres tidak bisa membedakan kelas workload atau membatasi concurrency
- Jika queue dijalankan berdampingan dengan workload lain, tindakan paling efektif adalah memastikan VACUUM dapat tetap mengejar laju perubahan, dan Traffic Control menyederhanakan hal itu
1 komentar
Komentar Hacker News
Postgres masih memiliki masalah vacuum horizon. Ini adalah situasi ketika query yang berjalan lama menghalangi vacuum pada tabel yang berubah cepat. Masalah ini sudah dikenal luas sejak 2015. Postgres bawaan tidak punya alat yang bagus untuk mengatasinya, tetapi versi kustom perusahaan penulis memiliki fitur untuk menyelesaikannya. Kesimpulannya, tetap bukan ide yang bijak untuk mencampur pekerjaan panjang bergaya OLAP dan pekerjaan cepat bergaya queue dalam instance Postgres yang sama. Bergantung pada kebutuhan, memakai message queue seperti 0MQ atau RMQ bisa menjadi solusi yang lebih mudah
Artikelnya cukup bagus, tetapi ada beberapa hal yang perlu dikritisi.
SELECT * FROM jobs WHERE status='pending' ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED;memang nyata, tetapi bisa dikurangi dengan menambahkan kolom yang naik secara monoton dan membuat indeks di atasnya. Dengan begitu, dead tuple tidak perlu dipertimbangkan, dan dampaknya lebih ke pemborosan ruang saja, sementara performa baca tidak terlalu menurun. Namun, bagaimana menjamin kenaikan monoton saat ada penulisan paralel bergantung pada desain aplikasiSaya penulisnya. Silakan tanya apa saja
Ini memang terdengar seperti iklan, tetapi akan menyenangkan jika ada sedikit penjelasan tentang pendekatan teknis penyelesaiannya
Postgres benar-benar bisa melakukan banyak hal. Orang-orang memilih Kafka atau SQS, tetapi sebenarnya cukup banyak pekerjaan yang bisa ditangani dengan Graphile Worker saja
Di Postgres, masalahnya menjadi jauh lebih serius saat melakukan update pada baris. Jika hanya memakai insert dan delete, sistem bisa bertahan cukup lama