Tuning toolbox — 6 cách fix theo hierarchy chi phí thấp → cao

Bài 5 của module này đã diagnose được bad plan — bạn biết planner đang chọn sai và tại sao. Bước tiếp theo là fix. Nhưng không phải mọi vấn đề cần giải pháp nặng như thêm index hay redesign schema. Có một hierarchy 6 cấp từ chi phí thấp đến cao — luôn thử từ cấp thấp trước, chỉ leo lên cấp cao hơn khi cấp dưới không đủ.

Đây là bộ công cụ thực chiến: bạn sẽ không cần tất cả 6 cấp cho mọi query — 80% vấn đề giải quyết được ở cấp 1–3.

1. Analogy — Tăng tốc xe ô tô

Khi xe chạy chậm, bạn không bắt đầu bằng cách thiết kế lại động cơ. Bạn thử theo thứ tự từ rẻ đến đắt.

2. Tier 1 — Rewrite query

Chi phí: 0. Chỉ thay đổi SQL, không thay đổi schema hay index.

Subquery correlated → JOIN:

Correlated subquery chạy lại cho mỗi row của outer query — O(n×m) thay vì O(n+m) của join.

-- Before: correlated subquery -- chay 1 lan cho moi user row
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM tasks t
  WHERE t.assignee_id = u.id AND t.status = 'doing'
);

-- After: rewrite thanh JOIN + DISTINCT
SELECT DISTINCT u.* FROM users u
JOIN tasks t ON t.assignee_id = u.id
WHERE t.status = 'doing';

-- EXPLAIN: planner co the chon Hash Join thay nested correlated subquery
-- Hash Join O(n+m) vs correlated O(n*m) khi tasks lon

OR → UNION ALL (bài 5 của module này, Pattern 3):

-- Before: OR block index usage
SELECT * FROM tasks WHERE assignee_id = 5 OR project_id = 10;

-- After: UNION ALL -- moi nhanh dung index rieng
SELECT * FROM tasks WHERE assignee_id = 5
UNION ALL
SELECT * FROM tasks WHERE project_id = 10;

Tránh SELECT *:

SELECT * buộc planner phải fetch toàn bộ column từ heap — không có cơ hội dùng Index Only Scan dù index đủ rộng.

-- Before: SELECT * -> bat buoc heap fetch
SELECT * FROM tasks WHERE assignee_id = 5 AND status = 'todo';

-- After: chi lay column can -> co the Index Only Scan
SELECT id, title, due_at FROM tasks
WHERE assignee_id = 5 AND status = 'todo';

3. Tier 2 — ANALYZE

Chi phí: cheap. Sample-based, vài giây cho table 10M row. Không cần thay đổi schema hay index.

Planner quyết định dựa trên statistics — số liệu snapshot tại lần ANALYZE cuối cùng. Sau bulk insert hoặc bulk update lớn, statistics lag so với data thực tế → planner estimate sai → plan sai (bài 4 của module này).

-- Sau bulk INSERT 1M task: chay ANALYZE ngay, khong doi autovacuum
ANALYZE tasks;  -- 5-10 giay, update n_live_tup, MCV, histogram

-- Kiem tra estimate truoc vs sau:
EXPLAIN ANALYZE SELECT * FROM tasks WHERE assignee_id = 5;
-- Truoc ANALYZE: planner doan 0.5 row -> chon Nested Loop sai
-- Sau ANALYZE: doan ~200 row -> chon Index Scan dung

-- Kiem tra lan ANALYZE cuoi va muc do thay doi data:
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'tasks';
-- n_mod_since_analyze lon -> stats co kha nang stale -> can ANALYZE

Autovacuum trigger ANALYZE khi n_mod_since_analyze > 50 + 0.1 × n_live_tup — với table 1M row: threshold 100.050 modification, có thể lag 10–30 phút sau bulk load. Chạy thủ công là an toàn nhất (bài 5 Module 6 của khoá này — autovacuum tuning).

4. Tier 3 — Add index

Chi phí: storage + write cost (mỗi INSERT/UPDATE/DELETE phải update index). Đổi lại: query read nhanh hơn nhiều.

Composite index — column order theo leftmost prefix (bài 3 Module 5 của khoá này):

-- Dashboard TaskFlow: filter theo assignee + status, sort theo due_at
-- Column order: equality truoc (assignee_id, status), range sau (due_at)
CREATE INDEX idx_tasks_dashboard
ON tasks(assignee_id, status, due_at);

Covering index với INCLUDE (bài 3 Module 5 của khoá này — Index Only Scan):

-- INCLUDE column khong dung de filter/sort, chi de cover SELECT list
-- -> Index Only Scan: doc tu index, bo qua heap hoan toan
CREATE INDEX idx_tasks_dashboard_covering
ON tasks(assignee_id, status) INCLUDE (due_at, title);

Partial index — chỉ index hot row:

-- Chi index task chua archive (hot path -- dashboard khong bao gio show archived)
-- Index nho hon (500k thay vi 1M), scan nhanh hon, maintenance nhe hon
CREATE INDEX idx_tasks_active
ON tasks(due_at) WHERE status != 'archived';

Expression index (bài 5 của module này, Pattern 1 — function wrapper):

-- Query WHERE LOWER(email) = '...' khong dung index tren email
-- Fix: index tren bieu thuc LOWER(email)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Gio planner match index cho predicate LOWER(email) = 'x'

GIN cho JSONB (bài 4 Module 5 của khoá này) và EXPLAIN before/after partial index:

-- GIN: tasks.metadata la JSONB column
CREATE INDEX idx_tasks_meta ON tasks USING gin(metadata jsonb_path_ops);

-- Before (khong co index): Seq Scan, Execution Time: ~1800 ms
EXPLAIN ANALYZE
SELECT id, title, due_at FROM tasks
WHERE assignee_id = 5 AND status != 'archived'
ORDER BY due_at NULLS LAST LIMIT 20;

-- Tao partial covering index
CREATE INDEX idx_tasks_active_assignee
ON tasks(assignee_id, due_at) INCLUDE (title)
WHERE status != 'archived';

ANALYZE tasks;

-- After: Index Only Scan, Heap Fetches: 0, Execution Time: ~1.5 ms (~1200x)

5. Tier 4 — Tăng default_statistics_target

Chi phí: memory tăng nhẹ + planning time chậm lại một chút. Đổi lại: estimate chính xác hơn cho column có distribution skewed.

default_statistics_target = 100 (mặc định) thu thập MCV 100 entry và histogram 100 bucket. Với column có distribution cực kỳ lệch — ví dụ 10% top assignee chiếm 80% task — MCV 100 entry không bắt được outlier, planner estimate sai.

-- Kiem tra distribution hien tai
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'tasks' AND attname = 'assignee_id';
-- Neu most_common_vals chi co 10 entry cho 100 possible assignee -> tang target

-- Tang statistics target cho column cu the
ALTER TABLE tasks ALTER COLUMN assignee_id SET STATISTICS 1000;
ANALYZE tasks;  -- BAT BUOC chay lai sau khi tang target

-- Hoac toan cuc trong postgresql.conf / ALTER SYSTEM:
ALTER SYSTEM SET default_statistics_target = 500;
SELECT pg_reload_conf();  -- ap dung khong can restart
ANALYZE;  -- Re-analyze tat ca table de ap dung target moi

Sau khi tăng target, EXPLAIN ANALYZE sẽ cho rows estimate chính xác hơn → planner chọn đúng join algorithm và scan strategy.

Chỉ tăng cho column quan trọng trong WHERE clause của query critical — không tăng toàn cục trừ khi cần thiết.

6. Tier 5 — Materialized view

Chi phí: storage 1 lần + refresh window (data không real-time). Đổi lại: query analytics instant thay vì aggregate full table mỗi lần.

Phù hợp cho analytics aggregate — dashboard "tổng task per project per month" không cần real-time, chấp nhận delay đến lần refresh kế tiếp.

-- Dashboard analytics: tong task per project per month
CREATE MATERIALIZED VIEW mv_task_monthly AS
SELECT
  project_id,
  date_trunc('month', created_at) AS month,
  count(*)                         AS task_count,
  count(*) FILTER (WHERE status = 'done') AS done_count
FROM tasks
GROUP BY project_id, date_trunc('month', created_at);

-- UNIQUE INDEX bat buoc de dung REFRESH CONCURRENTLY
-- (neu khong co UNIQUE INDEX, REFRESH phai dung lock block SELECT)
CREATE UNIQUE INDEX ON mv_task_monthly(project_id, month);

-- Refresh non-blocking -- cho phep SELECT tiep tuc trong khi refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_task_monthly;

-- Query MV thay vi aggregate full table moi lan
SELECT * FROM mv_task_monthly WHERE project_id = 1 ORDER BY month;
-- ~1 ms thay vi ~500 ms aggregate raw tren 1M row

-- Kiem tra size MV
SELECT pg_size_pretty(pg_relation_size('mv_task_monthly'));
-- Truoc (aggregate raw): HashAggregate -> Seq Scan, ~480 ms tren 1M row
-- Sau (query MV): Index Scan, ~0.8 ms (~600x faster)

Tradeoff cần hiểu rõ: data trong MV chỉ fresh đến lần REFRESH cuối. Client phải biết staleness này — API contract cần document "dashboard data cập nhật mỗi 5 phút" hoặc tương tự. Bài 11 của khoá này sẽ bàn về refresh schedule và monitor MV bloat.

7. Tier 6 — Restructure schema (last resort)

Chi phí cao: migration risk, data backfill, downtime hoặc dual-write transition window. Chỉ áp dụng khi 5 cấp trên không đủ.

• Denormalize (bài 4 Module 4 của khoá này): pre-join vào wide table cho hot path query analytics — embed project_name, assignee_name sẵn, tránh join runtime lặp đi lặp lại. Chỉ hiệu quả khi read >> write.
• Partition (bài 12 của khoá này): split tasks theo date range — tasks_2025, tasks_2026. Query WHERE created_at >= '2026-01-01' chỉ scan partition 2026. Hiệu quả khi table rất lớn (100M+) và query hay filter theo date.
• Sharding (bài 10 của khoá này): cross-node split khi 1 node không đủ throughput. Phức tạp nhất — distributed query overhead, cross-shard join không efficient.

Migration cost luôn phải tính: data migration script, backfill job, dual-write window, rollback plan.

8. pg_hint_plan — last resort hack

Extension cho phép override quyết định planner bằng hint comment inline. Hữu ích khi planner persistently chọn sai và không thể fix bằng 5 tier trên trong thời gian ngắn.

-- Cai extension (chua co trong PG core):
-- CREATE EXTENSION pg_hint_plan;

-- Hint inline: ep planner dung HashJoin va IndexScan cu the
/*+ HashJoin(t u) IndexScan(t idx_tasks_assignee) */
SELECT t.id, t.title, u.name
FROM tasks t JOIN users u ON t.assignee_id = u.id
WHERE t.status = 'doing';

Dùng pg_hint_plan với thận trọng: hint khóa plan cứng → mất khả năng adapt khi data distribution thay đổi. Nếu dữ liệu thay đổi nhưng hint không cập nhật, bạn có thể bị kẹt với plan tệ hơn trước. Coi pg_hint_plan là băng dán tạm thời — không phải giải pháp lâu dài.

-- Audit index khong duoc dung sau 30 ngay
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE tablename = 'tasks' AND idx_scan = 0;
-- idx_scan = 0 -> index khong bao gio duoc dung -> can xem xet DROP

-- SET chi anh huong session hien tai -- khong persist
SET default_statistics_target = 500;

-- ALTER SYSTEM ghi vao postgresql.auto.conf -- can RELOAD de ap dung
ALTER SYSTEM SET default_statistics_target = 500;
SELECT pg_reload_conf();  -- reload khong restart, ap dung ngay
ANALYZE;  -- Phai chay lai ANALYZE sau khi doi target

-- Mot so GUC can restart daemon (vd shared_buffers):
-- ALTER SYSTEM SET shared_buffers = '2GB';
-- -> Sau do phai restart PostgreSQL, pg_reload_conf() khong du

-- REFRESH CONCURRENTLY can UNIQUE INDEX
-- Neu khong co UNIQUE INDEX -> REFRESH phai dung thuong
-- -> Lock block tat ca SELECT trong khi refresh -> downtime!
CREATE UNIQUE INDEX ON mv_task_monthly(project_id, month);  -- BAT BUOC

-- Client phai biet data co the lag
-- Document trong API: "task monthly data refreshed every 5 minutes"
-- Khong dung MV cho data can real-time (vd so du tai khoan, ton kho hien tai)

9. Applied — TaskFlow dashboard: 3-tier fix

Baseline: dashboard query đếm task theo status cho một assignee, chạy 125ms trên 1M row.

-- Baseline: Seq Scan + HashAggregate
EXPLAIN ANALYZE
SELECT status, count(*) FROM tasks
WHERE assignee_id = 5 AND status != 'archived'
GROUP BY status;
-- Seq Scan on tasks (cost=0..30000 rows=1000000 width=...)
--   Filter: ((assignee_id = 5) AND (status <> 'archived'))
--   Rows Removed by Filter: ~987000
-- HashAggregate
-- Execution Time: ~125 ms

Fix 1 — Tier 3: Composite partial covering index:

CREATE INDEX idx_tasks_status_count
ON tasks(assignee_id, status) WHERE status != 'archived';

ANALYZE tasks;

EXPLAIN ANALYZE
SELECT status, count(*) FROM tasks
WHERE assignee_id = 5 AND status != 'archived'
GROUP BY status;
-- Index Only Scan using idx_tasks_status_count on tasks
--   Index Cond: (assignee_id = 5)
--   Filter: (status <> 'archived')
--   Heap Fetches: 0
-- HashAggregate
-- Execution Time: ~2 ms  (~62x faster)

Fix 2 — Tier 5: Materialized view cho analytics aggregate:

-- Precompute status breakdown per assignee
CREATE MATERIALIZED VIEW mv_user_status_count AS
SELECT assignee_id, status, count(*) AS task_count
FROM tasks
WHERE status != 'archived'
GROUP BY assignee_id, status;

CREATE UNIQUE INDEX ON mv_user_status_count(assignee_id, status);

-- Query MV thay vi aggregate raw moi lan
EXPLAIN ANALYZE
SELECT * FROM mv_user_status_count WHERE assignee_id = 5;
-- Index Scan on mv_user_status_count
-- Execution Time: ~0.3 ms  (~400x faster vs baseline)

Tóm tắt 3-tier fix:

Rewrite query (Tier 1) và ANALYZE (Tier 2) đã được áp trước — query đã viết đúng, ANALYZE đã chạy sau bulk load. Tier 3 và 5 là hai bước tiếp theo cho phần lợi suất còn lại.

10. Deep Dive

11. Tóm tắt

• Hierarchy 6 tier: rewrite (free) → ANALYZE (cheap) → index (storage+write) → statistics_target (memory+planning) → materialized view (storage+staleness) → restructure (migration risk). Đi từ thấp lên — đừng nhảy thẳng vào Tier 6.
• Tier 1 — Rewrite: correlated subquery → JOIN, OR → UNION ALL, SELECT * → chỉ column cần, EXISTS thay IN large list.
• Tier 2 — ANALYZE: chạy sau bulk load, không đợi autovacuum — estimate sai là root cause của hầu hết bad plan.
• Tier 3 — Index: composite theo leftmost prefix, INCLUDE cho Index Only Scan, partial cho hot-row subset, expression cho function wrapper. Mỗi index là write tax — audit idx_scan = 0 thường xuyên.
• Tier 4 — statistics_target: tăng per-column cho distribution skewed cao, bắt buộc ANALYZE lại sau khi thay đổi.
• Tier 5 — Materialized view: query instant thay vì aggregate, REFRESH CONCURRENTLY cần UNIQUE INDEX, client phải biết staleness. Bài 11 của khoá này: refresh schedule + monitor MV bloat.
• Tier 6 — Restructure: denormalize, partition (bài 12 của khoá này), sharding (bài 10 của khoá này) — last resort khi 5 tier trên không đủ.
• pg_hint_plan: last resort hack override planner — hint cứng mất khả năng adapt khi data thay đổi, dùng tạm thời không phải lâu dài.

12. Tự kiểm tra

Bài tiếp theo: Mini-challenge — dashboard 2s → 50ms (5M dataset)