Workload-driven index: chọn index theo query thật, không theo cảm tính
Không có "best practice" chung cho index — chỉ có index phù hợp với workload cụ thể của bảng đó. Một bảng orders OLTP write-nóng và một bảng orders_reporting read-nhiều cùng schema vẫn cần hai chiến lược index hoàn toàn khác. "Workload-driven design" nghĩa là quyết định index xuất phát từ ba câu hỏi đo được: bảng này nhận bao nhiêu read so với write, các query nóng tra theo cột nào với selectivity bao nhiêu, và planner đang chọn plan gì khi chạy. Bài này nói về quy trình đó, về việc đánh index "theo cảm tính" vỡ thế nào trong production, và cách review lại chiến lược index khi workload trôi.
Cơ chế hoạt động
Postgres planner là cost-based: với mỗi câu query, nó so các plan khả thi (Seq Scan, Index Scan, Index-Only Scan, Bitmap Index Scan…) bằng một hàm cost dựa trên thống kê thu từ ANALYZE (pg_statistic) và các tham số như random_page_cost, seq_page_cost, effective_cache_size. Index chỉ được dùng khi cost của plan dùng index thấp hơn cost của Seq Scan trên cùng query — không phải cứ có index là planner chọn.
Hai biến quan trọng nhất quyết định cost:
-
Selectivity của predicate: tỉ lệ row khớp. Cột boolean
is_active = truetrên bảng 99% active có selectivity ≈ 1.0 — Seq Scan thắng. Cộtcustomer_id = ?trên bảng triệu khách hàng có selectivity ≈ 1e-6 — Index Scan thắng tuyệt đối. -
Visibility: cho Index-Only Scan, page tương ứng phải nằm trong visibility map (do
VACUUMcập nhật). Index cóINCLUDEcột thoả query nhưng table vừa update nhiều → planner vẫn phải đi vào heap để check visibility, lợi ích index-only mất.
Workload pattern điều chỉnh cách áp dụng:
- OLTP (point read theo PK/FK + INSERT/UPDATE đều): tối thiểu index, ưu tiên B-tree trên FK và cột filter nóng nhất. Mỗi index thêm vào trả thuế write.
-
Read-heavy (dashboard, search, reporting trên replica): chấp nhận nhiều index hơn — partial index theo nhánh filter cố định, covering index với
INCLUDE, expression index cho query cólower(),(col->>'key'). - Write-heavy (event log, ingestion): cắt index tới mức tối thiểu; nếu phải có range index trên cột thời gian thì cân nhắc BRIN (rẻ hơn B-tree nhiều lần khi dữ liệu correlated theo physical order); xét partition theo thời gian để chỉ index partition gần đây.
-- Quy trình tối thiểu trước khi tạo bất kỳ index nào
-- 1. Lấy top slow query thật từ pg_stat_statements
SELECT queryid, calls, mean_exec_time, total_exec_time, rows,
substring(query, 1, 120) AS q
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 20;
-- 2. EXPLAIN từng query với data thật
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_amount
FROM orders
WHERE customer_id = 8421
AND status IN ('pending','confirmed')
AND created_at >= now() - interval '30 days';
-- 3. Nhìn cost, rows ước lượng vs thật, và phương án scan đang chọn
-- → từ đó mới quyết định index gì, không phải "thêm cho chắc"
Quy trình này đảo ngược thói quen "thấy chậm là CREATE INDEX": index chỉ được tạo khi có một query đo được trong pg_stat_statements cần nó, và đã xác nhận qua EXPLAIN rằng index dự định thực sự sẽ được planner chọn.
Vấn đề gặp trong production
Failure mode 1: index theo cảm tính, planner không bao giờ dùng. Một developer thấy filter WHERE status = 'pending' thì tạo ngay CREATE INDEX ON orders(status). Bảng có 50 triệu row, status chỉ năm giá trị, 'pending' chiếm 40% row. Planner ước lượng filter trả về 20 triệu row → Seq Scan rẻ hơn → index không bao giờ chạm. Kết quả: trả full write cost cho mỗi INSERT/UPDATE mà không thu lại read benefit nào.
-- Sai: low-selectivity, planner không dùng
CREATE INDEX orders_status_idx ON orders (status);
-- Đúng: partial index chỉ cho nhánh thật sự cần lookup nhanh
CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status IN ('pending','confirmed');
-- Selectivity cao (chỉ vài % row), index nhỏ hơn nhiều,
-- write cost chỉ trả cho row khớp WHERE clause của index.
Failure mode 2: predicate non-sargable, planner bỏ qua index. Code app gọi WHERE date(created_at) = '2026-06-30' hoặc WHERE lower(email) = $1 mà index lại đánh thẳng trên created_at / email — planner không thể chuyển hàm sang vế kia, đành Seq Scan.
-- Sai: hàm bọc cột, index thường không khớp
SELECT * FROM orders WHERE date(created_at) = '2026-06-30';
SELECT * FROM customers WHERE lower(email) = 'a@b.com';
-- Đúng: hoặc viết lại predicate thành dạng sargable
SELECT * FROM orders
WHERE created_at >= '2026-06-30' AND created_at < '2026-07-01';
-- Đúng: hoặc tạo expression index khớp đúng predicate
CREATE INDEX customers_lower_email_idx ON customers (lower(email));
Implicit cast cũng gây dạng này: cột bigint mà code truyền string, hoặc cột text so với một số literal — Postgres có thể cast sang kiểu rộng hơn và bỏ index. EXPLAIN lúc đó hiện một Filter: thay vì Index Cond:.
Failure mode 3: thứ tự cột multicolumn index sai. Index (status, created_at) không phục vụ query WHERE created_at >= … đơn thuần — leading column phải xuất hiện trong predicate dạng equality để leaf traversal đi đúng. Đây là lỗi review code phổ biến: dev nhìn thấy hai cột trong WHERE thì gộp vào một index mà không cân nhắc thứ tự.
-- Sai: query chỉ lọc created_at, leading column status không có trong WHERE
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
EXPLAIN SELECT * FROM orders WHERE created_at >= now() - interval '1 day';
-- → Seq Scan (hoặc bitmap scan kém hiệu quả)
-- Đúng: đảo thứ tự, hoặc tách thành hai index theo từng access path
CREATE INDEX orders_created_idx ON orders (created_at);
CREATE INDEX orders_status_created_idx ON orders (status, created_at)
WHERE status IN ('pending','confirmed'); -- partial nếu có nhánh nóng
Failure mode 4: thêm index reporting trên bảng OLTP write-nóng. Team report cần query SUM(total_amount) GROUP BY customer_id, date_trunc('day', created_at) chạy nhanh, dev đánh thêm hai index trên bảng orders primary OLTP. Write throughput sụt vì mỗi INSERT/UPDATE phải duy trì thêm hai B-tree; HOT update tỉ lệ tụt nếu một trong các cột bị update lọt vào index mới. Đây là dạng nhầm workload: query reporting nên chạy trên replica hoặc trên view materialized/bảng denormalized riêng, không nên đánh index "kiêm cả hai".
Failure mode 5: chiến lược index cũ không match workload mới. Hệ thống chạy 18 tháng, bảng orders ban đầu có 2 index FK cơ bản. Trong quá trình phát triển, team thêm dần 11 index khác nhau, mỗi cái phục vụ một feature theo từng sprint. Không ai audit lại. Khi traffic chuyển từ "tra theo customer_id" sang "tra theo merchant_id" (do mở B2B), nửa số index thành dead weight nhưng vẫn ăn write cost. Triệu chứng: INSERT P99 latency tăng đều, autovacuum chậm, pg_stat_user_indexes.idx_scan = 0 cho 5–6 index. Đây là lý do chiến lược index bắt buộc phải review định kỳ, không phải "set-and-forget".
Cách debug và monitor
Triệu chứng nghi index không match workload:
- Query chậm xuất hiện top trong
pg_stat_statementsmàEXPLAINhiệnSeq Scandù có index trông như khớp. - Một số index
idx_scan = 0sau nhiều ngày traffic thật. -
INSERT/UPDATEP95 trên bảng tăng đều theo thời gian dù schema không đổi nhiều. - Index hit ratio thấp bất thường trên bảng nóng — cache không đủ giữ working set của index.
Các view/tool cần biết (đều là built-in hoặc extension chính thức trong Postgres docs):
-
pg_stat_statements— top slow query theototal_exec_timevàmean_exec_time. Đây là điểm bắt đầu của mọi quyết định index; không có query thật trong view này thì không có lý do tạo index. -
EXPLAIN (ANALYZE, BUFFERS)— xác nhận planner thực sự chọn index dự định. Sorowsước lượng vsactual rows: lệch nhiều bậc nghĩa là statistics chưa đủ, cầnANALYZEhoặc tăngdefault_statistics_targetcho cột đó. -
pg_stat_user_indexes—idx_scan,idx_tup_read,idx_tup_fetch.idx_scan = 0sau nhiều ngày = ứng viên drop.idx_tup_read >> idx_tup_fetch= index trả quá nhiều row rồi bị filter sau, dấu hiệu selectivity kém. -
pg_statio_user_indexes—idx_blks_hit,idx_blks_read. Index hit ratio =idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0). Trên bảng nóng, ratio dưới khoảng 95–99% là dấu hiệu index lớn hơn cache hoặc query pattern không tận dụng được index hiện tại. -
auto_explainextension — log plan của query vượt ngưỡng thời gian (auto_explain.log_min_duration). Cách rẻ nhất để bắt query chậm mà không cần dev gọiEXPLAINthủ công. -
log_min_duration_statement+ pgBadger — log statement chậm + aggregate thành report theo bảng/endpoint, tìm pattern query không khớp index nào.
Query audit chuẩn để chạy định kỳ:
-- 1. Index dead-weight: tốn write, không trả read
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;
-- 2. Index hit ratio per index
SELECT relname, indexrelname,
idx_blks_hit, idx_blks_read,
round(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2)
AS hit_ratio_pct
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC
LIMIT 20;
-- 3. Index low-productivity: đọc nhiều, trả ít row khớp
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
round(idx_tup_read::numeric / NULLIF(idx_scan, 0), 1) AS rows_per_scan
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_tup_read DESC
LIMIT 20;
Rule phòng ngừa thực tế: review pg_stat_user_indexes mỗi tháng (hoặc trước mỗi major release); mark index idx_scan = 0 sau ≥ 30 ngày là ứng viên drop; với mỗi PR thêm CREATE INDEX, yêu cầu kèm EXPLAIN ANALYZE chứng minh planner chọn index đó trên query thật.
Tradeoff
Workload-driven design đánh đổi công sức audit liên tục lấy write/read cost cân bằng theo đúng pattern hiện tại. Cụ thể được: index tối thiểu cho write-heavy bảng giữ INSERT P99 thấp, partial/covering cho read-heavy giảm latency reporting, expression index cứu query non-sargable. Cụ thể mất: review định kỳ (tháng/quý), benchmark mỗi lần thêm/bỏ, kỷ luật bắt PR kèm EXPLAIN. Đối nghịch lại là chiến lược "đánh chắc, đầy đủ": rẻ về thời gian dev ban đầu nhưng tích luỹ dead-weight index, write phình, autovacuum không đuổi kịp, sau một thời gian phải dọn dẹp lớn. Phương án thứ ba — không đánh index gì ngoài PK — chỉ hợp với bảng cực bé hoặc bảng append-only chỉ scan toàn bộ; mọi bảng OLTP thật đều cần ít nhất một số B-tree trên FK và cột filter nóng.
Một điểm nữa: chiến lược index không phải tài sản tĩnh. Workload trôi (tính năng mới, traffic shift, partition thêm), nên index phù hợp 6 tháng trước có thể thừa hôm nay. Quy tắc thực tế: mỗi index tồn tại phải justify được bằng một query trong pg_stat_statements còn active; với bảng write-heavy, tổng index nên giữ ở mức tối thiểu khả dĩ và review hàng quý; với bảng read-heavy/reporting có thể đánh thoải mái hơn nhưng vẫn phải audit idx_scan định kỳ để cắt dead-weight.
Câu hỏi phỏng vấn
Bạn thiết kế index cho production như thế nào?
Quy trình bắt đầu từ workload, không từ schema. Đầu tiên phân loại bảng theo pattern: OLTP write-balanced, read-heavy reporting, write-heavy ingestion — mỗi loại có ngưỡng index khác nhau. Tiếp theo, không tạo bất kỳ index nào nếu không có query thật justify nó: lấy top theo total_exec_time trong pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS) từng query, nhìn xem planner đang chọn plan gì, rows ước lượng có lệch actual nhiều bậc không (nếu có thì cần ANALYZE hoặc nâng default_statistics_target trước khi nghĩ tới index). Khi đã chắc cần index, chọn loại match selectivity: B-tree cho point/range trên cột chọn lọc cao, partial index cho cột boolean hoặc enum skewed (vd WHERE status = 'pending' chỉ chiếm vài % row), expression index cho predicate non-sargable kiểu lower(email) hoặc (payload->>'sid'), BRIN cho bảng append-only correlated theo time-order. Trước khi rollout, benchmark write TPS trước/sau trên staging có data tương đương; sau rollout, xác nhận idx_scan tăng và pg_stat_statements của query đó giảm mean_exec_time. Hậu quả production khi bỏ qua quy trình: index không bao giờ được planner dùng (low selectivity, predicate non-sargable, sai thứ tự cột multicolumn) nhưng vẫn ăn full write cost — INSERT chậm dần, autovacuum nặng lên, replica lag tăng. Cuối cùng, chiến lược index không phải set-and-forget: review pg_stat_user_indexes định kỳ, mark index idx_scan = 0 sau quãng đủ dài là ứng viên drop, vì workload trôi và index cũ dễ thành dead weight. Điểm ăn điểm là nêu được ba thứ: justify trước khi tạo (query thật + EXPLAIN), match loại index với selectivity và pattern (B-tree/partial/expression/BRIN), và audit định kỳ (review hàng quý, drop dead-weight) — không phải "thấy slow là CREATE INDEX".
Hands-on
Mục tiêu: thiết kế index cho một schema Order Management thật, lần lượt theo quy trình workload-driven — đo top query, EXPLAIN, chọn loại index đúng selectivity, đo trước/sau bằng pg_stat_statements, rồi audit drop dead-weight.
Dựng Postgres và schema:
docker run -d --name pg-orders -e POSTGRES_PASSWORD=pw -p 5434:5432 postgres:16
sleep 5
docker exec -i pg-orders psql -U postgres <<'SQL'
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SQL
docker restart pg-orders
sleep 5
docker exec -i pg-orders psql -U postgres <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE TABLE customers (
id bigserial PRIMARY KEY,
email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
merchant_id bigint NOT NULL,
status text NOT NULL,
total_amount numeric(12,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
) WITH (fillfactor = 85);
CREATE TABLE order_items (
id bigserial PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id),
sku text NOT NULL,
qty int NOT NULL,
price numeric(10,2) NOT NULL
);
INSERT INTO customers (email)
SELECT 'user' || g || '@example.com' FROM generate_series(1, 200000) g;
INSERT INTO orders (customer_id, merchant_id, status, total_amount, created_at)
SELECT (random()*199999+1)::bigint,
(random()*999+1)::bigint,
(ARRAY['pending','confirmed','shipped','delivered','cancelled'])
[1 + floor(random()*5)::int],
(random()*500)::numeric(12,2),
now() - (random()*interval '365 days')
FROM generate_series(1, 5000000);
INSERT INTO order_items (order_id, sku, qty, price)
SELECT (random()*4999999+1)::bigint,
'SKU-' || floor(random()*10000),
1 + floor(random()*5)::int,
(random()*100)::numeric(10,2)
FROM generate_series(1, 15000000);
ANALYZE;
SQL
Bước 1 — chạy workload mô phỏng để có top query thật:
cat > /tmp/orders_read.sql <<'SQLF'
\set cid random(1, 200000)
\set mid random(1, 1000)
SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = :cid
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC LIMIT 20;
SELECT o.id, o.total_amount, count(i.id)
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.merchant_id = :mid
AND o.status = 'pending'
GROUP BY o.id ORDER BY o.created_at DESC LIMIT 50;
SQLF
docker cp /tmp/orders_read.sql pg-orders:/tmp/orders_read.sql
docker exec -i pg-orders psql -U postgres -c "SELECT pg_stat_statements_reset();"
docker exec pg-orders pgbench -U postgres -n -f /tmp/orders_read.sql -c 8 -j 4 -T 30 postgres
Bước 2 — xem top slow query và EXPLAIN từng cái:
docker exec -i pg-orders psql -U postgres <<'SQL'
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 0) AS total_ms,
substring(query, 1, 100) AS q
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY total_exec_time DESC LIMIT 10;
SQL
docker exec -i pg-orders psql -U postgres <<'SQL'
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = 12345
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC LIMIT 20;
SQL
Plan ban đầu nhiều khả năng là Seq Scan trên orders (5M row, không có index ngoài PK) — actual rows ~ vài chục, nhưng Buffers shared read rất lớn.
Bước 3 — thiết kế index theo workload:
docker exec -i pg-orders psql -U postgres <<'SQL'
-- Pattern OLTP: tra theo customer_id + sort theo created_at
-- → multicolumn (customer_id, created_at DESC), không partial
CREATE INDEX CONCURRENTLY orders_cust_created_idx
ON orders (customer_id, created_at DESC);
-- Pattern reporting: merchant + status = 'pending' (skewed)
-- → partial index, nhỏ và rẻ duy trì
CREATE INDEX CONCURRENTLY orders_merchant_pending_idx
ON orders (merchant_id, created_at DESC)
WHERE status = 'pending';
-- FK lookup cho JOIN order_items → orders
CREATE INDEX CONCURRENTLY order_items_order_idx
ON order_items (order_id);
ANALYZE orders;
ANALYZE order_items;
SQL
Bước 4 — chạy lại workload, so mean_exec_time:
docker exec -i pg-orders psql -U postgres -c "SELECT pg_stat_statements_reset();"
docker exec pg-orders pgbench -U postgres -n -f /tmp/orders_read.sql -c 8 -j 4 -T 30 postgres
docker exec -i pg-orders psql -U postgres <<'SQL'
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
substring(query, 1, 100) AS q
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY total_exec_time DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = 12345
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC LIMIT 20;
SQL
Plan giờ là Index Scan using orders_cust_created_idx với Buffers nhỏ hơn nhiều bậc; mean_exec_time giảm rõ rệt.
Bước 5 — tái hiện failure mode "index theo cảm tính" và audit:
docker exec -i pg-orders psql -U postgres <<'SQL'
-- Thêm một index low-selectivity để xem planner có dùng không
CREATE INDEX orders_status_idx ON orders (status);
SELECT pg_stat_reset();
SQL
docker exec pg-orders pgbench -U postgres -n -f /tmp/orders_read.sql -c 8 -j 4 -T 30 postgres
docker exec -i pg-orders psql -U postgres <<'SQL'
-- Audit dead-weight: index nào không có idx_scan
SELECT 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 NOT i.indisunique AND NOT i.indisprimary
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;
SELECT relname, indexrelname,
idx_blks_hit, idx_blks_read,
round(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2)
AS hit_ratio_pct
FROM pg_statio_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_blks_read DESC;
SQL
orders_status_idx sẽ có idx_scan rất thấp hoặc bằng 0 (planner ưu tiên partial index hoặc Seq Scan tuỳ selectivity). Đây là dead-weight thuần — drop:
docker exec -i pg-orders psql -U postgres <<'SQL'
DROP INDEX CONCURRENTLY orders_status_idx;
SQL
Bước 6 — đo write impact của chiến lược tốt vs xấu:
cat > /tmp/orders_write.sql <<'SQLF'
\set cid random(1, 200000)
\set mid random(1, 1000)
INSERT INTO orders (customer_id, merchant_id, status, total_amount)
VALUES (:cid, :mid, 'pending', (random()*500)::numeric(12,2));
SQLF
docker cp /tmp/orders_write.sql pg-orders:/tmp/orders_write.sql
# Baseline: chiến lược workload-driven (chỉ index thật cần)
docker exec pg-orders pgbench -U postgres -n -f /tmp/orders_write.sql -c 8 -j 4 -T 30 postgres
# "Đánh chắc": thêm vài index cảm tính
docker exec -i pg-orders psql -U postgres <<'SQL'
CREATE INDEX orders_updated_at_idx ON orders (updated_at);
CREATE INDEX orders_total_idx ON orders (total_amount);
CREATE INDEX orders_status_idx2 ON orders (status);
SQL
docker exec pg-orders pgbench -U postgres -n -f /tmp/orders_write.sql -c 8 -j 4 -T 30 postgres
So TPS hai lần — lần sau (3 index thêm) thấp rõ rệt so với baseline, dù query path không nhanh hơn chút nào. Đây là cost cụ thể của "index theo cảm tính" trên một bảng OLTP write-balanced.
Dọn dẹp:
docker rm -f pg-orders
Top comments (0)