Postgres index bloat detection + REINDEX

Postgres “kok lambat ya?” padahal dulu cepat. Bisa jadi index bloat — UPDATE/DELETE banyak bikin index gendut, query latency naik diam-diam. Snippet ini query deteksi bloat (yang akurat, bukan estimate kasar) + REINDEX CONCURRENTLY untuk fix tanpa lock table.

Kode

-- ==========================================
-- Query 1: list top index dengan estimated bloat
-- Pakai formula bloat-check yang sudah well-known
-- ==========================================
WITH btree_index_atts AS (
  SELECT
    nspname, relname, reltuples, relpages, indrelid, relam,
    regexp_split_to_table(indkey::TEXT, ' ')::SMALLINT AS attnum,
    indexrelid AS index_oid
  FROM pg_index
    JOIN pg_class ON pg_class.oid = pg_index.indexrelid
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    JOIN pg_am ON pg_class.relam = pg_am.oid
  WHERE pg_am.amname = 'btree' AND nspname NOT IN ('pg_catalog', 'information_schema')
),
index_item_sizes AS (
  SELECT
    ind_atts.nspname,
    ind_atts.relname AS index_name,
    ind_atts.reltuples,
    ind_atts.relpages,
    ind_atts.relam,
    indrelid AS table_oid,
    index_oid,
    current_setting('block_size')::NUMERIC AS bs,
    8 AS maxalign,
    24 AS pagehdr,
    /* per-tuple header size */
    CASE WHEN MAX(COALESCE(pg_stats.null_frac, 0)) = 0
         THEN 2 ELSE 6 END AS index_tuple_hdr,
    SUM((1 - COALESCE(pg_stats.null_frac, 0))
        * COALESCE(pg_stats.avg_width, 1024))::INT AS nulldatawidth
  FROM pg_attribute
  JOIN btree_index_atts AS ind_atts
    ON pg_attribute.attrelid = ind_atts.indexrelid
    AND pg_attribute.attnum = ind_atts.attnum
  LEFT JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
    AND ((pg_stats.tablename = ind_atts.relname AND pg_stats.attname = pg_get_indexdef(ind_atts.index_oid, ind_atts.attnum, TRUE))
      OR (pg_stats.tablename = ind_atts.relname AND pg_stats.attname = ind_atts.attnum::TEXT))
  WHERE pg_attribute.attnum > 0
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned AS (
  SELECT
    maxalign, bs, nspname, index_name, reltuples, relpages,
    relam, table_oid, index_oid,
    (5 + index_tuple_hdr + nulldatawidth +
     CASE WHEN (index_tuple_hdr + nulldatawidth) % maxalign = 0 THEN 0
     ELSE maxalign - (index_tuple_hdr + nulldatawidth) % maxalign END
    )::NUMERIC AS nulldatahdrwidth
  FROM index_item_sizes
),
otta_calc AS (
  SELECT
    bs, nspname, table_oid, index_oid, index_name, relpages,
    COALESCE(CEIL((reltuples * (4 + nulldatahdrwidth)) / (bs - 24::FLOAT)), 0) AS otta
  FROM index_aligned
)
SELECT
  nspname AS schema,
  index_name,
  pg_size_pretty(bs * relpages::BIGINT) AS index_size,
  pg_size_pretty(bs * otta::BIGINT) AS estimated_optimal,
  CASE WHEN relpages::BIGINT = 0 THEN 0
       ELSE ROUND((100 * (relpages - otta) / relpages)::NUMERIC, 2)
  END AS bloat_pct,
  CASE WHEN relpages < otta THEN '0 bytes'
       ELSE pg_size_pretty(bs * (relpages - otta)::BIGINT)
  END AS waste_bytes
FROM otta_calc
WHERE relpages > 100  -- skip index kecil
ORDER BY bs * (relpages - otta)::BIGINT DESC
LIMIT 20;

-- ==========================================
-- Query 2: pgstattuple — AKURAT (butuh extension)
-- Lebih lama tapi exact, recommended untuk decision REINDEX
-- ==========================================
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  schemaname AS schema,
  tablename AS table_name,
  indexname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  ROUND((1 - leaf_fragmentation/100.0)::NUMERIC, 2) AS leaf_density,
  ROUND(leaf_fragmentation::NUMERIC, 2) AS bloat_pct,
  pg_size_pretty(
    (pg_relation_size(indexrelid) * leaf_fragmentation / 100)::BIGINT
  ) AS estimated_bloat_bytes
FROM (
  SELECT
    s.schemaname,
    s.tablename,
    i.indexrelname AS indexname,
    i.indexrelid,
    (pgstatindex(i.indexrelid::regclass::text)).leaf_fragmentation
  FROM pg_stat_user_indexes i
  JOIN pg_stat_user_tables s ON s.relid = i.relid
  WHERE pg_relation_size(i.indexrelid) > 10 * 1024 * 1024  -- > 10MB
) sub
WHERE leaf_fragmentation > 20
ORDER BY pg_relation_size(indexrelid) * leaf_fragmentation DESC
LIMIT 20;

-- ==========================================
-- Query 3: dead tuple ratio (untuk VACUUM kebutuhan)
-- ==========================================
SELECT
  schemaname AS schema,
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  CASE WHEN n_live_tup + n_dead_tup = 0 THEN 0
       ELSE ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2)
  END AS dead_pct,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- ==========================================
-- Action: REINDEX CONCURRENTLY (tanpa lock)
-- ==========================================
-- Single index — pakai CONCURRENTLY supaya tidak block write
REINDEX INDEX CONCURRENTLY public.idx_pesanan_status_kota_created;

-- Whole table (semua index pada table)
REINDEX TABLE CONCURRENTLY public.pesanan;

-- Whole database (long, hati-hati di prod)
-- REINDEX DATABASE CONCURRENTLY tokopedia_prod;

-- Cek progress (PG 12+)
SELECT
  pid, phase, command,
  blocks_done, blocks_total,
  ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 2) AS pct,
  tuples_done, tuples_total
FROM pg_stat_progress_create_index;

-- ==========================================
-- Cleanup invalid index (kalau REINDEX CONCURRENTLY interrupt)
-- ==========================================
-- Cek invalid index dulu
SELECT
  n.nspname AS schema, c.relname AS index_name,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;

-- Hapus invalid index (biasanya ada nama "_ccnew" atau "_ccold")
DROP INDEX CONCURRENTLY IF EXISTS public.idx_pesanan_status_kota_created_ccnew;

Pemakaian

# Cron script — cek bloat mingguan
psql -d tokopedia_prod -f /opt/maintenance/check_bloat.sql > /var/log/bloat_report.log

# Alert via Slack kalau ada index > 50% bloat
THRESHOLD=50
RESULT=$(psql -t -d tokopedia_prod -c "
  SELECT index_name FROM ... WHERE bloat_pct > $THRESHOLD;
")

if [ -n "$RESULT" ]; then
  curl -X POST -H 'Content-Type: application/json' \
    -d "{\"text\":\":warning: Index bloat: $RESULT\"}" \
    "$SLACK_WEBHOOK"
fi

-- Workflow REINDEX safe di production:

-- 1. Verify pakai pgstattuple (akurat)
SELECT * FROM pgstatindex('public.idx_pesanan_status_kota_created');
-- leaf_fragmentation > 30% → REINDEX

-- 2. Cek koneksi yang akan kena block
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle' AND query LIKE '%idx_pesanan_status_kota_created%';

-- 3. REINDEX CONCURRENTLY
\timing
REINDEX INDEX CONCURRENTLY public.idx_pesanan_status_kota_created;

-- 4. Verify ukuran turun + masih valid
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS new_size,
  indisvalid AS valid
FROM pg_index
WHERE indexrelid = 'public.idx_pesanan_status_kota_created'::regclass;

-- 5. ANALYZE table supaya planner update
ANALYZE pesanan;

# Untuk database besar — REINDEX semua index bloated dengan delay
psql -d tokopedia_prod <<'SQL' | while read idx; do
  echo "REINDEX $idx ..."
  psql -d tokopedia_prod -c "REINDEX INDEX CONCURRENTLY $idx;" \
    || echo "  FAIL: $idx"
  sleep 30  # rest antara reindex supaya tidak overload
done
SELECT format('%I.%I', n.nspname, c.relname)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE (pgstatindex(c.oid::regclass::text)).leaf_fragmentation > 30
  AND pg_relation_size(i.indexrelid) > 100 * 1024 * 1024;
SQL

Kapan dipakai

• Postgres database yang sudah beroperasi > 6 bulan dengan banyak UPDATE/DELETE.
• Query latency naik tanpa ada perubahan schema atau data growth signifikan.
• Setelah migration besar (bulk update jutaan row).
• Maintenance window quarterly untuk healthy DB.

Catatan

• REINDEX CONCURRENTLY Postgres 12+. Tidak block write tapi butuh disk space sementara 2x ukuran index. Pastikan disk free cukup.
• Versi lama harus pakai pattern: CREATE INDEX CONCURRENTLY ix_new + DROP INDEX ix_old + RENAME. Manual tapi works di PG 11.
• pgstattuple akurat tapi lambat — locking ringan, tapi full scan index. Jalankan di off-peak.
• autovacuum settings — bloat = symptom autovacuum kurang aggressive. Tune autovacuum_vacuum_scale_factor dari 0.2 ke 0.05 untuk tabel hot.
• HOT update — kalau index tidak include kolom yang di-UPDATE, Postgres bisa HOT update tanpa update index. Design schema dengan ini in mind.
• invalid index dari REINDEX CONCURRENTLY interrupt — drop manual, jangan dibiarkan (tidak dipakai planner tapi makan disk).
• bloat di TOAST — tabel besar dengan kolom TEXT/JSONB juga bisa bloat di TOAST. Pakai pg_total_relation_size untuk full picture.

REINDEX bukan silver bullet. Kalau aplikasi pattern-nya banyak UPDATE kolom yang ada di index, bloat akan come back. Refactor schema lebih impactful — separate hot field ke tabel terpisah.