Anatomi Masalah: Reconnect Storm dan Bottleneck Catch-Up Query

Ketika platform pesan skala besar (seperti WhatsApp atau Telegram) pulih dari downtime sistemik, jutaan klien TCP/WebSocket tersambung kembali secara serentak. Fenomena thundering herd ini memicu apa yang dikenal sebagai reconnect storm. Setiap instans klien yang aktif langsung mengeksekusi catch-up query untuk menyinkronkan pesan tertunda yang belum sempat terkirim selama periode pemadaman.

Kueri tipikal yang dikirimkan ke layer basis data umumnya berbentuk:

SELECT id, sender_id, payload, created_at 
FROM messages 
WHERE recipient_id = $1 AND is_delivered = FALSE 
ORDER BY id ASC 
LIMIT 50;

Dalam volume puluhan ribu request per detik, kueri tersebut memicu lonjakan penggunaan CPU hingga 100% dan I/O latency ratusan milidetik. Basis data mengalami I/O thrashing akibat pembacaan disk acak secara masif, menurunkan throughput, hingga akhirnya koneksi ke database exhausted.

Kegagalan Indeks Standar dan Low-Selectivity Boolean

Penyebab utama runtuhnya performa kueri catch-up terletak pada karakteristik data dan desain indeks konvensional. Pada platform pesan, 95% hingga 99% data di dalam tabel messages berstatus is_delivered = TRUE. Hanya sebagian kecil pesan (biasanya kurang dari 1-5%) yang berstatus is_delivered = FALSE.

Pembuatan indeks B-Tree reguler seperti berikut tidak efisien:

-- Indeks konvensional yang tidak efisien
CREATE INDEX idx_messages_user_delivered ON messages (recipient_id, is_delivered);

Pendekatan ini memiliki kelemahan struktural mendasar:

  • Index Bloat: Indeks mencakup miliaran baris pesan lama yang sudah terkirim, mengakibatkan ukuran indeks di disk membesar hingga puluhan gigabyte.
  • Cache Eviction: Ukuran indeks yang jauh melampaui alokasi shared_buffers memaksa Postgres melakukan pembacaan berulang dari storage (disk reads). Halaman memori aktif (working set) yang sering diakses terlempar keluar dari RAM.
  • Low Selectivity: Kolom boolean memiliki kardinalitas sangat rendah (hanya dua kemungkinan nilai). B-Tree engine harus melintasi banyak leaf page indeks hanya untuk memfilter baris yang relevan jika filter selektivitas tidak optimal.

Implementasi Solusi: Partial Index dengan Keyset Pagination

PostgreSQL menyediakan fitur Partial Index, yaitu indeks yang dibangun dengan menyertakan klausul WHERE (predikat). Indeks ini hanya memetakan baris yang memenuhi kondisi logis tertentu ke dalam struktur pohon B-Tree.

Dikombinasikan dengan composite keyset (recipient_id, id), indeks parsial mengeliminasi kebutuhan memindai data pesan yang sudah terkirim, sekaligus menyediakan data terurut secara deterministik untuk paginasi kursor (keyset pagination).

-- Eksekusi pembuatan partial index secara non-blocking
CREATE INDEX CONCURRENTLY idx_messages_undelivered_keyset 
ON messages (recipient_id, id) 
WHERE is_delivered = FALSE;

Mengapa Pola Ini Bekerja?

  1. Ukuran Minimalis: Indeks hanya mencatat pointer baris yang memiliki is_delivered = FALSE. Ukuran indeks mengecil drastis hingga >95% lebih hemat ruang dibanding indeks penuh.
  2. 100% Fit di RAM: Karena ukuran indeks kecil (hanya beberapa megabyte hingga ratusan megabyte, bukan gigabyte), seluruh pohon indeks dapat menetap permanen di dalam shared_buffers memori utama.
  3. Eliminasi Sort Step: Penambahan kolom id pada komposit indeks membuat data sudah terurut di level B-Tree node, menghilangkan operasi Sort yang rakus memori atau temp disk I/O.

Validasi Kinerja: Evaluasi EXPLAIN (ANALYZE, BUFFERS)

Berikut perbandingan metrik eksekusi kueri pada tabel messages dengan volume 50 juta baris, di mana 100 ribu baris berstatus is_delivered = FALSE.

Sebelum Menggunakan Partial Index (Indeks Penuh pada recipient_id)

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..1245.80 rows=50 width=84) (actual time=14.210..89.412 rows=50 loops=1)
  Buffers: shared hit=12 read=410
  ->  Index Scan using idx_messages_recipient on messages (cost=0.56..24890.11 rows=998 width=84) (actual time=14.208..89.398 rows=50 loops=1)
        Index Cond: (recipient_id = 482910)
        Filter: (NOT is_delivered)
        Rows Removed by Filter: 1420
        Buffers: shared hit=12 read=410
Planning Time: 0.184 ms
Execution Time: 89.468 ms

Metrik di atas menunjukkan Postgres harus membaca 410 buffer pages dari disk (read=410) karena halaman tidak ada di memori, membuang 1.420 baris via filter di heap, dengan waktu eksekusi ~89 ms untuk satu kueri klien saja.

Sesudah Menggunakan Partial Index Komposit

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..4.81 rows=50 width=84) (actual time=0.034..0.061 rows=50 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_messages_undelivered_keyset on messages (cost=0.29..8.42 rows=92 width=84) (actual time=0.032..0.054 rows=50 loops=1)
        Index Cond: (recipient_id = 482910)
        Buffers: shared hit=4
Planning Time: 0.098 ms
Execution Time: 0.082 ms

Hasil optimasi menunjukkan:

  • Zero Disk Read: shared read=0, seluruhnya dilayani dari memori (shared hit=4).
  • Execution Time: Turun dari 89.468 ms menjadi 0.082 ms (peningkatan kecepatan lebih dari 1.000x lipat).
  • Eliminasi Filter Overhead: Tidak ada baris yang dipindai lalu dibuang (Rows Removed by Filter: 0).

Paginasi Keyset untuk Penarikan Data Masif

Jika klien memiliki ratusan pesan tertunda, penarikan harus berlanjut menggunakan kursor berbasis kolom id terakhir yang diterima, bukan menggunakan OFFSET:

-- Pola kueri keyset pagination catch-up lanjutan
SELECT id, sender_id, payload, created_at 
FROM messages 
WHERE recipient_id = $1 
  AND is_delivered = FALSE 
  AND id > $2 
ORDER BY id ASC 
LIMIT 50;

Perencana kueri PostgreSQL akan melakukan Index Seek langsung ke posisi leaf node (recipient_id, $last_seen_id) tanpa memindai ulang item yang telah terkirim sebelumnya.

Menjaga Working Set di Buffer Cache: Partisi dan Archival

Partial index menyelesaikan masalah pencarian, namun performa jangka panjang bergantung pada pemeliharaan tabel induk agar terbebas dari table bloat.

1. Declarative Table Partitioning

Pisahkan tabel messages menggunakan partisi rentang waktu (Range Partitioning) berdasarkan kolom created_at per hari atau per minggu:

CREATE TABLE messages (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    recipient_id BIGINT NOT NULL,
    sender_id BIGINT NOT NULL,
    payload TEXT NOT NULL,
    is_delivered BOOLEAN DEFAULT FALSE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
) PARTITION BY RANGE (created_at);

Ketika partisi diterapkan, partial index dibuat secara otomatis di setiap anak partisi. Keuntungannya, data aktif hanya berada pada partisi terbaru, memperkecil cakupan vacuum dan autovacuum.

2. Archival Pipeline

Pesan lama yang sudah sukses terkirim (is_delivered = TRUE) dan berumur lebih dari 30 hari harus dipindahkan dari sistem OLTP ke cold storage (misalnya AWS S3 via Parquet atau tabel OLAP seperti ClickHouse/BigQuery):

  • Detach Partition: Jalankan ALTER TABLE messages DETACH PARTITION messages_y2023m12; secara terjadwal. Operasi ini instan dan tidak memblokir tabel utama.
  • Dump & Drop: Ekspor data partisi yang terlepas ke cold storage, kemudian jalankan DROP TABLE messages_y2023m12; untuk mengembalikan disk space ke OS tanpa fragmentasi storage.

3. Menjaga Kebersihan Indeks dengan Tuning Autovacuum

Karena baris dengan is_delivered = FALSE sering mengalami update menjadi TRUE setelah terkirim, pointer indeks harus dihapus dari partial index. Lakukan tuning autovacuum khusus pada tabel antrean:

ALTER TABLE messages SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_cost_limit = 1000
);

Pengaturan ini memicu autovacuum lebih dini ketika 5% baris mengalami perubahan, mencegah akumulasi dead tuple pada index leaf nodes dan menjamin footprint memory tetap rendah.