Index Maintenance — Write Overhead

Write overhead của index: vì sao mỗi CREATE INDEX là một thuế đánh lên mọi INSERT/UPDATE/DELETE

Một index trong Postgres không phải "miễn phí cho write" — nó là một cấu trúc song song trên đĩa mà engine phải duy trì đồng bộ với heap cho mọi thay đổi dữ liệu. Mỗi INSERT chèn entry vào tất cả index của bảng; mỗi UPDATE có thể nhân đôi việc đó nếu phá HOT update; mỗi DELETE để lại entry chết mà VACUUM phải dọn sau. Bài này nói về cái thuế đó: nó được tính ở đâu, vỡ thế nào trong production khi đánh index quá tay, và làm sao đo trước khi quyết định tạo thêm một index nữa.

Cơ chế hoạt động

Postgres dùng MVCC: mọi UPDATE thực chất là tạo một tuple version mới rồi đánh dấu version cũ "hết hạn". Index entry trỏ tới một TID cụ thể ((block_no, item_offset)), nên về nguyên tắc mỗi version mới trong heap phải có entry mới trong mọi index của bảng. Đó là gốc của write amplification: bảng có N index, mỗi INSERT ít nhất N+1 lần ghi (heap + N index), và mỗi thay đổi đều phát sinh WAL record tương ứng để đảm bảo durability và replication.

Có một lối thoát quan trọng tên là HOT (Heap-Only Tuple) update (theo Postgres docs, chương "Heap-Only Tuples"). Postgres skip toàn bộ việc cập nhật index khi đồng thời thoả hai điều kiện:

1. Không cột nào bị UPDATE xuất hiện trong bất kỳ index nào của bảng.
2. Còn đủ chỗ trong cùng page (8KB) để chứa tuple version mới — tức FILLFACTOR chưa lấp đầy page.

Khi HOT thành công, tuple cũ giữ entry index cũ, chain HOT trong cùng page nối tới tuple mới, và không index nào bị đụng tới. Khi HOT thất bại (có cột bị index trong số cột thay đổi, hoặc page đầy), Postgres phải chèn entry mới vào tất cả index — kể cả những index không liên quan tới cột vừa update.

CREATE TABLE sessions (
  id            bigserial PRIMARY KEY,
  user_id       bigint    NOT NULL,
  ip            inet      NOT NULL,
  last_seen_at  timestamptz NOT NULL,
  data          jsonb     NOT NULL
) WITH (fillfactor = 85);   -- chừa 15% mỗi page cho HOT chain

CREATE INDEX sessions_user_idx ON sessions (user_id);
-- KHÔNG index last_seen_at → UPDATE last_seen_at có thể đi HOT path

Mỗi index entry phải đi qua chu trình của B-tree: tìm leaf page đúng, chèn, nếu page đầy thì page split (chia đôi, ghi thêm WAL cho cả hai page mới và parent). Page split là chỗ chi phí ghi nhảy vọt — một INSERT bình thường thành ba hoặc nhiều page WAL. Postgres giảm tần suất split bằng B-tree FILLFACTOR (mặc định 90 theo docs) và bằng deduplication (Postgres 13+) cho key trùng lặp, nhưng không loại bỏ được.

DELETE cũng không miễn phí: nó chỉ đánh dấu tuple "dead" trong heap, để lại index entries trỏ vào TID đã chết. Một VACUUM sau đó phải mở từng index, tìm và xoá entry tương ứng — chi phí scale theo số index. Bảng nhiều index làm autovacuum chậm và tốn I/O hơn rõ rệt.

Vấn đề gặp trong production

Failure mode 1: bulk INSERT chậm hơn nhiều lần dự kiến. Một job nạp dữ liệu vào bảng có sẵn 6–8 index thường chậm hơn cùng job vào bảng "rỗng index" vài lần đến gần một bậc độ lớn, vì mỗi row phải đi qua N B-tree, và mỗi B-tree có thể split. Code điển hình hay thấy:

-- Sai: nạp vào bảng đang giữ nguyên index, vừa chậm vừa làm index bloat
COPY events FROM '/var/tmp/events.csv' WITH (FORMAT csv);

Cách xử lý chuẩn cho bulk load (theo Postgres docs "Populating a Database"):

-- Đúng: drop index không-PK trước, load, rồi tạo lại
BEGIN;
DROP INDEX events_user_idx;
DROP INDEX events_kind_created_idx;
COMMIT;

COPY events FROM '/var/tmp/events.csv' WITH (FORMAT csv);

CREATE INDEX CONCURRENTLY events_user_idx        ON events (user_id);
CREATE INDEX CONCURRENTLY events_kind_created_idx ON events (kind, created_at);
ANALYZE events;

Xây lại index một lần từ dữ liệu đã sắp xếp luôn rẻ hơn việc chèn từng entry trong lúc load. Với job append-only chạy thường xuyên, partition theo thời gian rồi chỉ index partition hiện tại cũng giảm overhead này.

Failure mode 2: index trên cột churn cao giết HOT update. Đây là loại bug thầm lặng nhất. Một bảng users cập nhật last_login_at mỗi request, có index trên cột đó "để query gần đây":

-- Sai: index trên cột UPDATE liên tục → HOT update bị phá mỗi lần
CREATE INDEX users_last_login_idx ON users (last_login_at);

-- Mỗi UPDATE users SET last_login_at = now() WHERE id = ?
--   → tạo tuple mới trong heap
--   → bắt buộc chèn entry mới vào MỌI index của bảng (PK, email, …)
--   → table và index bloat tăng nhanh, autovacuum đuổi không kịp

Triệu chứng: n_tup_hot_upd / n_tup_upd xuống thấp; pg_stat_user_tables.n_dead_tup tăng nhanh; query trên bảng chậm dần dù schema không đổi. Cách sửa: bỏ index, hoặc chuyển sang lưu last_login_at ở bảng phụ ít cột (không có index nào trên cột đang update), hoặc chấp nhận query "đăng nhập gần đây" chạy chậm hơn để cứu write throughput.

Failure mode 3: WAL volume tăng, replication lag tăng theo. Mỗi index update sinh WAL record; mỗi page split sinh nhiều record hơn. Bảng nhiều index → WAL volume bùng. Trên cluster có streaming replication hoặc logical replication, lượng WAL phải truyền sang replica tăng tuyến tính theo số index. Triệu chứng: pg_stat_replication.write_lag và flush_lag tăng đều sau khi thêm index trên bảng write-nóng; pg_wal directory phình; archive job đuổi không kịp. Cách xử lý là kiểm lại từng index đang giữ — index nào không nằm trong pg_stat_statements thực sự cần thì drop.

Failure mode 4: index "chỉ để chắc" mà không bao giờ được dùng. Đây là dạng tệ nhất: trả full write cost nhưng không thu lại được read benefit nào. Một index idx_scan = 0 sau cả tháng workload thật là dead weight thuần.

-- Tìm ứng viên drop: index không dùng và không phải unique/PK
SELECT s.schemaname, s.relname, s.indexrelname,
       s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Failure mode 5: đánh giá write cost sau khi đã rollout. Tạo CREATE INDEX trên một bảng nhiều trăm GB trong production mà chưa benchmark trước cũng là failure mode: nó vừa khoá ghi (nếu không CONCURRENTLY), vừa có thể đẩy write latency của bảng lên mức không chịu được. Quy trình đúng là benchmark write TPS trước/sau trên một staging có dữ liệu tương đương kích thước trước khi rollout.

Cách debug và monitor

Triệu chứng cần nhìn khi nghi write overhead do index:

• INSERT latency P95/P99 trên một bảng tăng đều theo thời gian dù schema không đổi, đặc biệt sau lần thêm index gần nhất.
• autovacuum chạy lâu, pg_stat_progress_vacuum cho thấy phase vacuuming indexes chiếm phần lớn thời gian.
• pg_wal size tăng nhanh, wal_bytes trong pg_stat_statements cao bất thường cho top write statement.
• Replica lag tăng đồng pha với write traffic dù network bình thường.

Các view và metric cần biết (đều có trong Postgres docs, không phải tool ngoài):

• pg_stat_user_indexes — idx_scan để bắt index dead-weight; idx_tup_read/idx_tup_fetch để biết index có productive không.
• pg_stat_user_tables — n_tup_ins, n_tup_upd, n_tup_del, và đặc biệt n_tup_hot_upd. Tỉ lệ n_tup_hot_upd / n_tup_upd thấp trên bảng update-nóng là tín hiệu có index đang giết HOT path.
• pg_stat_statements — cột wal_bytes, wal_records (Postgres 13+) cho biết statement nào sinh nhiều WAL. Một UPDATE đơn giản mà wal_bytes lớn bất thường thường do quá nhiều index phụ.
• pg_relation_size() / pg_indexes_size() — so size index với size table; tổng index lớn hơn table nhiều lần là dấu hiệu đã đánh quá tay.
• pgstattuple extension, hàm pgstatindex() — leaf_fragmentation, avg_leaf_density để biết index có cần REINDEX không.
• EXPLAIN (ANALYZE, BUFFERS, WAL) trên một INSERT/UPDATE — phần WAL báo records, bytes, fpi (full page image). Đây là cách định lượng chính xác cost của một statement.

Rule phòng ngừa thực tế:

• Trước khi CREATE INDEX, justify bằng một query có trong pg_stat_statements với total_exec_time đủ cao. Không có query thật → không tạo index.
• Tránh index cột thay đổi với churn cao (updated_at, last_seen_at, counter), trừ khi query bắt buộc.
• Set FILLFACTOR thấp hơn mặc định (vd FILLFACTOR=85 cho table update-nóng) để giữ chỗ cho HOT chain.
• Cron audit pg_stat_user_indexes định kỳ, mark index idx_scan = 0 sau N ngày là ứng viên drop.
• Với bulk load, drop index không-PK trước khi load, tạo lại sau bằng CREATE INDEX CONCURRENTLY.

Tradeoff

Index đổi write cost + storage + maintenance cost lấy read latency thấp và stable. Cụ thể write phải trả: mỗi INSERT chia đều cho N+1 cấu trúc; mỗi UPDATE có nguy cơ phá HOT nếu chạm cột bị index, kéo theo entry mới trong mọi index; mỗi DELETE để lại entry chết mà VACUUM phải dọn từng index một; mọi thao tác trên đều sinh WAL, làm WAL volume và replica lag scale theo số index. Đổi lại read được: lookup O(log n), range scan tận dụng leaf liền nhau, ORDER BY không cần sort, index-only scan với INCLUDE. Cân bằng phụ thuộc workload: bảng OLTP write-nóng, read theo PK là chính → giữ tối thiểu index; bảng analytics/reporting read-nhiều, write batch định kỳ → có thể đánh index thoải mái hơn vì write cost trả một lần khi load. Quy tắc thực tế: mỗi index thêm vào phải có một query justify nó trong pg_stat_statements, và phải đo write TPS trước/sau trên staging trước khi rollout. Một bảng write-heavy có quá 4–6 index phụ là tín hiệu cần xét lại schema hoặc nghĩ tới partition.

Câu hỏi phỏng vấn

Index ảnh hưởng INSERT (và UPDATE/DELETE) như thế nào, và làm sao để biết nên thêm một index có đáng không?

Mỗi index là một B-tree song song trên đĩa mà Postgres phải duy trì đồng bộ với heap. INSERT vào bảng có N index nghĩa là N+1 lần ghi (heap + N index), mỗi lần đều sinh WAL record; nếu B-tree page đầy còn phát sinh page split, ghi thêm WAL cho hai page con + parent. UPDATE có thể tránh đụng index nếu đi HOT path — điều kiện là không cột nào bị update có trong bất kỳ index nào và còn chỗ trong cùng page (chỉnh được qua FILLFACTOR); nếu không thoả thì mọi index đều phải có entry mới, kể cả index không liên quan tới cột vừa thay đổi. DELETE không xoá ngay; nó để lại entry chết mà VACUUM sau đó phải dọn từng index một, nên số index càng nhiều thì autovacuum càng nặng. Hậu quả production khi đánh thừa: INSERT chậm dần, table và index bloat phình, WAL volume tăng kéo theo replica lag, autovacuum không đuổi kịp. Cách quyết định có nên thêm index: chỉ thêm khi có một query thật trong pg_stat_statements cần nó (read benefit đủ lớn), và benchmark write TPS với pgbench trước/sau trên staging có data tương đương để biết cost cụ thể; sau rollout audit định kỳ qua pg_stat_user_indexes, index nào idx_scan = 0 sau quãng đủ dài thì drop. Điểm ăn điểm là nêu được cả ba: cơ chế (N+1 lần ghi + WAL + page split), HOT update và điều kiện của nó, và quy trình justify-then-measure trước khi tạo index — không phải "thấy slow là đánh index".

Hands-on

Mục tiêu: đo write TPS trước và sau khi thêm index trên cùng bảng cùng workload, quan sát mức tụt; đo tỉ lệ HOT update và ép nó vỡ; nhìn WAL bytes tăng theo số index.

Dựng Postgres và chuẩn bị bảng:

docker run -d --name pg-write -e POSTGRES_PASSWORD=pw -p 5433:5432 postgres:16
sleep 5

docker exec -i pg-write psql -U postgres <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SQL
docker restart pg-write
sleep 5

docker exec -i pg-write psql -U postgres <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE TABLE events (
  id           bigserial PRIMARY KEY,
  user_id      bigint    NOT NULL,
  kind         text      NOT NULL,
  ip           inet      NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  payload      jsonb     NOT NULL
) WITH (fillfactor = 85);

INSERT INTO events (user_id, kind, ip, payload)
SELECT (random()*100000)::bigint,
       (ARRAY['click','view','purchase','signup'])[1+floor(random()*4)],
       ('10.0.0.' || floor(random()*255))::inet,
       jsonb_build_object('sid', md5(random()::text))
FROM generate_series(1, 2000000);
ANALYZE events;
SQL

Tạo script pgbench cho INSERT thuần và UPDATE:

cat > /tmp/ins.sql <<'EOF'
\set u random(1, 100000)
INSERT INTO events (user_id, kind, ip, payload)
VALUES (:u, 'click', '10.0.0.1', '{"sid":"x"}');
EOF

cat > /tmp/upd.sql <<'EOF'
\set u random(1, 100000)
UPDATE events SET payload = jsonb_set(payload, '{sid}', to_jsonb(md5(random()::text)))
WHERE user_id = :u;
EOF

docker cp /tmp/ins.sql pg-write:/tmp/ins.sql
docker cp /tmp/upd.sql pg-write:/tmp/upd.sql

Baseline (chỉ PK, không index phụ):

docker exec pg-write pgbench -U postgres -n -f /tmp/ins.sql -c 8 -j 4 -T 30 postgres
docker exec pg-write pgbench -U postgres -n -f /tmp/upd.sql -c 8 -j 4 -T 30 postgres

docker exec -i pg-write psql -U postgres -c "
SELECT relname, n_tup_ins, n_tup_upd, n_tup_hot_upd,
       round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0),1) AS hot_pct
FROM pg_stat_user_tables WHERE relname='events';"

Thêm dần index và đo lại:

docker exec -i pg-write psql -U postgres <<'SQL'
CREATE INDEX events_user_idx       ON events (user_id);
CREATE INDEX events_kind_idx       ON events (kind);
CREATE INDEX events_created_idx    ON events (created_at);
CREATE INDEX events_ip_idx         ON events (ip);
SQL

docker exec pg-write pgbench -U postgres -n -f /tmp/ins.sql -c 8 -j 4 -T 30 postgres
docker exec pg-write pgbench -U postgres -n -f /tmp/upd.sql -c 8 -j 4 -T 30 postgres

So TPS hai lần — số INSERT/s lần sau thấp hơn rõ rệt; mức độ tuỳ I/O backend, nhưng pattern luôn giảm theo số index. Tỉ lệ HOT vẫn cao vì UPDATE chỉ động vào payload (không có index nào trên payload).

Bây giờ ép HOT vỡ bằng cách thêm index trên cột bị update:

docker exec -i pg-write psql -U postgres <<'SQL'
TRUNCATE events;
INSERT INTO events (user_id, kind, ip, payload)
SELECT (random()*100000)::bigint, 'click', '10.0.0.1', '{"sid":"x"}'
FROM generate_series(1, 500000);

-- Expression index trên trường JSON đang bị UPDATE → phá HOT path
CREATE INDEX events_payload_sid_idx ON events ((payload->>'sid'));

SELECT pg_stat_statements_reset();
SQL

docker exec pg-write pgbench -U postgres -n -f /tmp/upd.sql -c 8 -j 4 -T 30 postgres

docker exec -i pg-write psql -U postgres -c "
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0),1) AS hot_pct
FROM pg_stat_user_tables WHERE relname='events';"

hot_pct sẽ rơi mạnh so với baseline. Đo WAL bytes sinh ra cho cùng pattern UPDATE:

docker exec -i pg-write psql -U postgres -c "
SELECT substring(query, 1, 50) AS q, calls,
       wal_records, wal_bytes
FROM pg_stat_statements
WHERE query ILIKE 'UPDATE events%'
ORDER BY wal_bytes DESC LIMIT 5;"

Drop index trên cột bị update, reset stats, chạy lại để xác nhận hot_pct tăng và wal_bytes / call giảm:

docker exec -i pg-write psql -U postgres <<'SQL'
DROP INDEX events_payload_sid_idx;
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();
SQL

docker exec pg-write pgbench -U postgres -n -f /tmp/upd.sql -c 8 -j 4 -T 30 postgres

docker exec -i pg-write psql -U postgres -c "
SELECT n_tup_upd, n_tup_hot_upd,
       round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0),1) AS hot_pct
FROM pg_stat_user_tables WHERE relname='events';"

Cuối cùng, audit index dead-weight bằng truy vấn ở section "Cách debug và monitor", thử drop một index có idx_scan = 0 rồi đo lại INSERT TPS để định lượng cost của index đó.

Dọn dẹp:

docker rm -f pg-write