Endpoint daftar data sering melambat bukan karena query untuk mengambil satu halaman data, melainkan karena aplikasi juga menjalankan SELECT COUNT(*) untuk menampilkan total halaman. Pada tabel PostgreSQL besar, count eksak dapat membutuhkan pembacaan banyak halaman dan pemeriksaan visibility setiap tuple. Karena itu, solusi yang tepat tidak selalu berupa menambahkan indeks.

Langkah pertama adalah mengukur query yang benar-benar dijalankan, termasuk filter, join, dan kondisi soft delete. Setelah itu, pilih apakah API benar-benar membutuhkan angka total yang eksak dan real-time, atau cukup dengan estimasi, ringkasan yang sedikit tertunda, atau indikator has more.

Mengapa COUNT(*) pada PostgreSQL bisa mahal

PostgreSQL memakai MVCC (Multi-Version Concurrency Control). Saat baris diubah atau dihapus, versi lama baris tidak selalu langsung hilang secara fisik. PostgreSQL harus menentukan apakah setiap tuple terlihat oleh snapshot transaksi yang sedang membaca data.

Akibatnya, COUNT(*) tidak dapat sekadar membaca metadata jumlah baris yang selalu presisi. Untuk count eksak, executor perlu memeriksa baris yang cocok dengan kondisi query dan memastikan setiap baris visible bagi snapshot tersebut.

Index bukan jaminan count instan

Indeks tetap berguna jika query mempunyai filter selektif, misalnya per tenant, status, atau rentang waktu. Namun indeks tidak otomatis membuat COUNT(*) menjadi operasi konstan. PostgreSQL dapat memilih:

  • Sequential scan, jika sebagian besar tabel diperkirakan cocok atau membaca tabel dinilai lebih murah.
  • Index scan atau bitmap scan, jika indeks membantu menemukan kandidat baris.
  • Index-only scan, jika indeks mencakup kebutuhan query dan banyak halaman heap ditandai all-visible pada visibility map.

Pada index-only scan, PostgreSQL masih dapat harus mengunjungi heap untuk halaman yang belum ditandai all-visible. Tabel dengan banyak update/delete, vacuum yang tertinggal, atau transaksi lama dapat memiliki lebih banyak pemeriksaan heap. Jadi, indeks yang benar dapat mengurangi kerja, tetapi tidak menjamin count selalu cepat.

Filter, I/O, dan pola akses

Perhatikan contoh tabel pesanan berikut.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE INDEX orders_active_tenant_status_idx
  ON orders (tenant_id, status)
  WHERE deleted_at IS NULL;

Indeks parsial tersebut relevan untuk query aktif berikut karena predikat query sesuai dengan predikat indeks.

SELECT count(*)
FROM orders
WHERE tenant_id = $1
  AND status = 'open'
  AND deleted_at IS NULL;

Namun jika satu tenant memiliki sebagian besar baris pada tabel, atau status open sangat umum, query masih dapat membaca banyak entri indeks dan/atau halaman heap. Filter rentang waktu juga perlu masuk ke desain indeks jika memang menjadi pola akses utama; jangan membuat indeks hanya untuk count tanpa memeriksa seluruh workload write dan read.

Ukur query COUNT(*) dengan EXPLAIN (ANALYZE, BUFFERS)

Jalankan analisis pada lingkungan yang representatif. Hindari menjalankan query berat sembarangan di production saat beban tinggi, karena ANALYZE benar-benar mengeksekusi query.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
  AND deleted_at IS NULL;

Fokus pada beberapa bagian berikut dalam hasilnya:

  • Node scan: Seq Scan, Index Scan, Bitmap Heap Scan, atau Index Only Scan. Ini menjelaskan strategi akses yang dipilih planner.
  • actual rows dan rows: perbedaan besar antara estimasi planner dan jumlah aktual menandakan statistik kurang representatif atau korelasi data sulit diperkirakan.
  • Rows Removed by Filter: nilai tinggi pada sequential scan menunjukkan banyak baris dibaca lalu dibuang oleh filter.
  • Buffers: shared hit berarti halaman tersedia di shared buffers; shared read menunjukkan halaman perlu dibaca dari storage. Banyak read biasanya lebih sensitif terhadap I/O dan cache dingin.
  • Heap Fetches pada Index Only Scan: angka besar berarti executor masih sering memeriksa heap karena visibility map belum cukup membantu.
  • Planning Time dan Execution Time: pada count besar, masalah umumnya berada pada execution, bukan planning.

Bandingkan rencana dan buffer sebelum serta sesudah perubahan dengan parameter yang sama. Satu eksekusi pada cache hangat tidak cukup untuk menyimpulkan bahwa masalah telah selesai.

Verifikasi statistik, vacuum, dan autovacuum

Planner memilih rencana berdasarkan statistik. Statistik yang basi dapat membuat PostgreSQL mengira filter sangat selektif padahal tidak, atau sebaliknya. Mulailah dengan inspeksi statistik tabel.

SELECT
  c.reltuples::bigint AS estimated_rows,
  c.relpages,
  s.n_live_tup,
  s.n_dead_tup,
  s.last_analyze,
  s.last_autoanalyze,
  s.last_vacuum,
  s.last_autovacuum
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_all_tables AS s ON s.relid = c.oid
WHERE n.nspname = 'public'
  AND c.relname = 'orders';

reltuples dan n_live_tup adalah estimasi, bukan sumber count eksak. Nilai tersebut berguna untuk diagnosis dan estimasi kasar, terutama bila dibaca bersama waktu analyze/vacuum terakhir serta jumlah dead tuple.

Langkah perbaikan yang aman

  1. Perbarui statistik setelah perubahan data besar atau setelah membuat indeks: ANALYZE orders;
  2. Pastikan autovacuum tidak tertinggal. Dead tuple yang menumpuk meningkatkan pekerjaan vacuum dan dapat mengurangi peluang index-only scan efektif.
  3. Periksa transaksi yang berjalan sangat lama. Transaksi lama dapat menahan horizon cleanup dan menghambat pembersihan versi tuple lama.
  4. Jika distribusi kolom filter sangat tidak merata, pertimbangkan menaikkan statistik kolom secara terukur, lalu analyze ulang.
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders;

Untuk tabel yang churn-nya tinggi, parameter autovacuum dapat disetel per tabel setelah diukur, misalnya ambang vacuum/analyze berbasis skala yang lebih rendah. Jangan menyalin angka konfigurasi dari tabel lain tanpa melihat ukuran tabel, laju update/delete, kapasitas I/O, dan dampaknya pada workload.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.02
);

Pengaturan ini membuat ambang relatif lebih cepat tercapai dibanding nilai yang lebih besar, tetapi juga dapat meningkatkan frekuensi kerja autovacuum. Pantau hasilnya melalui statistik tabel dan metrik database, bukan hanya berdasarkan satu query count.

Pilih strategi sesuai kontrak API dan kebutuhan konsistensi

1. Count eksak saat dibutuhkan

Pertahankan COUNT(*) eksak bila total benar-benar bagian dari kontrak bisnis, filter cukup selektif, dan frekuensi endpoint masih dapat ditanggung. Perbaiki dahulu indeks yang sesuai dengan predikat, statistik, serta kesehatan vacuum.

Untuk pagination, pertimbangkan apakah total perlu dihitung pada setiap request. Total dapat dipisahkan ke endpoint lain, dihitung hanya ketika pengguna membukanya, atau di-cache untuk kombinasi filter yang populer. Jika UI hanya perlu mengetahui adanya halaman berikutnya, ambil limit + 1 baris dan kirimkan has_more, tanpa count total.

2. Estimasi jumlah baris

Untuk jumlah total seluruh tabel, pg_class.reltuples adalah estimasi murah yang biasanya cukup untuk label seperti “sekitar 1,2 juta data”. Nilainya dapat tertinggal sampai statistik diperbarui.

SELECT reltuples::bigint AS estimated_total
FROM pg_class
WHERE oid = 'public.orders'::regclass;

Untuk filter kompleks, planner juga menghasilkan estimasi melalui EXPLAIN, tetapi kualitasnya bergantung pada statistik, distribusi data, join, dan korelasi antar-kolom. Estimasi planner cocok untuk observabilitas atau UX yang secara eksplisit menerima angka perkiraan; jangan gunakan sebagai dasar keputusan bisnis, kuota, atau otorisasi.

3. Counter cache yang diperbarui secara transaksional

Jika aplikasi sangat sering membutuhkan count per tenant dan status, simpan ringkasan pada tabel counter. Pembaruan data utama dan counter harus berada dalam transaksi yang sama agar setelah commit keduanya konsisten.

CREATE TABLE tenant_order_counts (
  tenant_id bigint PRIMARY KEY,
  open_count bigint NOT NULL DEFAULT 0,
  CHECK (open_count >= 0)
);

BEGIN;

INSERT INTO orders (tenant_id, status)
VALUES ($1, 'open');

INSERT INTO tenant_order_counts (tenant_id, open_count)
VALUES ($1, 1)
ON CONFLICT (tenant_id)
DO UPDATE SET open_count = tenant_order_counts.open_count + EXCLUDED.open_count;

COMMIT;

Untuk perubahan status atau delete, hitung delta berdasarkan transisi yang benar, misalnya open -> closed mengurangi open_count. Semua jalur mutasi harus menerapkan aturan yang sama: endpoint aplikasi, worker, job impor, skrip administrasi, dan proses backfill.

Trade-off: operasi UPDATE pada satu baris counter mengunci baris tersebut. Tenant yang sangat aktif dapat menjadi titik kontensi write. Counter juga menambah write amplification dan kompleksitas recovery. Bila diperlukan, gunakan counter yang lebih terpartisi, misalnya per tenant dan hari, lalu jumlahkan beberapa bucket saat membaca.

Counter transaksional mencegah race condition lost update yang umum terjadi bila aplikasi menjalankan pola “baca nilai, tambah di aplikasi, lalu simpan”. Gunakan operasi atomik SET count = count + delta di database. Tetap siapkan proses rekonsiliasi periodik dengan count eksak, terutama jika ada jalur data historis atau kegagalan operasional yang mungkin melewati mekanisme counter.

4. Tabel agregat atau materialized view

Untuk dashboard dan laporan dengan dimensi stabil, agregat per hari, tenant, atau status sering lebih efisien daripada menghitung tabel mentah berulang kali. Materialized view cocok bila keterlambatan data dapat diterima.

CREATE MATERIALIZED VIEW order_counts_by_day AS
SELECT
  tenant_id,
  status,
  created_at::date AS order_date,
  count(*) AS total
FROM orders
WHERE deleted_at IS NULL
GROUP BY tenant_id, status, created_at::date;

CREATE UNIQUE INDEX order_counts_by_day_key
  ON order_counts_by_day (tenant_id, status, order_date);

View tersebut dapat di-refresh sesuai kebutuhan operasional. REFRESH MATERIALIZED VIEW CONCURRENTLY dapat mengurangi gangguan pembaca, tetapi memerlukan unique index yang sesuai pada materialized view yang sudah terisi dan tetap memiliki biaya refresh. Refresh bukan inkremental secara otomatis; pada tabel sumber yang sangat besar, biaya refresh penuh dapat menjadi masalah tersendiri.

Alternatifnya adalah tabel summary yang diperbarui inkremental saat write. Ini memberi latensi baca rendah, tetapi memiliki trade-off yang mirip dengan counter cache: lebih banyak write, aturan transisi lebih rumit, serta kebutuhan rekonsiliasi.

Konsistensi: total dan daftar dapat berbeda

Bahkan count eksak tidak otomatis menjamin daftar dan total selalu berasal dari snapshot yang sama. Dalam mode Read Committed, dua statement terpisah dapat melihat commit yang berbeda di antara keduanya. Jika kontrak API menuntut snapshot konsisten, jalankan pembacaan dalam transaksi dengan isolation level yang sesuai atau rancang query/API agar semantiknya jelas.

Untuk sebagian besar endpoint daftar, kontrak yang lebih realistis adalah: daftar mencerminkan snapshot query, total adalah estimasi atau total yang mungkin berubah selama pengguna membuka halaman. Nyatakan semantik ini pada respons API, misalnya total_estimate, total_as_of, atau has_more.

Checklist sebelum dan sesudah perubahan

  1. Catat query count asli, parameter representatif, frekuensi request, p95/p99 latensi, dan dampaknya pada endpoint.
  2. Jalankan EXPLAIN (ANALYZE, BUFFERS) untuk kasus tenant kecil, sedang, dan besar; jangan hanya menguji satu parameter.
  3. Periksa jenis scan, buffer read/hit, heap fetches, estimasi versus actual rows, serta Rows Removed by Filter.
  4. Periksa last_autoanalyze, last_autovacuum, live/dead tuple, dan transaksi lama sebelum menyimpulkan indeks adalah masalahnya.
  5. Pastikan indeks baru cocok dengan predikat nyata dan ukur dampak tambahan terhadap insert, update, storage, dan maintenance.
  6. Tentukan kontrak total: eksak, estimasi, tertunda, atau tidak diperlukan karena memakai has_more.
  7. Jika memakai counter atau summary, pastikan seluruh jalur mutasi berada dalam transaksi yang sama, gunakan update atomik, dan buat job rekonsiliasi.
  8. Sesudah rilis, bandingkan latensi, buffer reads, beban CPU/I/O, error rate, serta throughput write sebelum dan sesudah perubahan.

Dengan pengukuran yang benar, keputusan biasanya menjadi jelas: optimalkan count eksak bila memang layak, atau ubah kontrak pembacaan agar endpoint tidak memaksa PostgreSQL menghitung seluruh populasi pada setiap request.