PostgreSQL Labs — Tình huống thực tế

Mỗi lab là một sự cố hoặc tình huống thường gặp trong production. Đọc triệu chứng → tự chẩn đoán → xem hướng giải quyết.

---

Lab 1 — Query chậm không rõ nguyên nhân

Tình huống:

Endpoint /api/orders?user_id=12345 trước đây response < 50ms. Sau khi data tăng lên 10 triệu records, response time lên 4–8 giây. Code không thay đổi, index orders(user_id) đã có từ trước.

Bước 1 — Chạy EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, o.total_amount, o.created_at
FROM orders o
WHERE o.user_id = 12345
ORDER BY o.created_at DESC
LIMIT 20;

Bước 2 — Đọc output, tìm dấu hiệu:

Index Scan using idx_orders_user_id on orders
  (cost=0.56..18432.12 rows=9821 width=48)
  (actual time=0.043..3812.445 rows=9821 loops=1)
  Index Cond: (user_id = 12345)
  Buffers: shared hit=42 read=9779

Dấu hiệu:

• read=9779 — gần như toàn bộ từ disk, không phải cache
• rows=9821 — planner ước đúng nhưng quá nhiều rows để sort sau đó

Bước 3 — Xác định vấn đề:

Index (user_id) tìm được 9821 rows cho user này. Sau đó PG phải đọc heap để lấy created_at và sort. Vấn đề là thiếu covering index.

Fix:

-- Tạo composite index: filter trên user_id, sort trên created_at, INCLUDE các column cần select
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC)
INCLUDE (status, total_amount);

-- Sau đó chạy lại EXPLAIN — phải thấy Index Only Scan
-- Buffers: shared hit tăng, read giảm về ~20

Kiểm tra hiểu biết:

• Tại sao ORDER BY created_at DESC trong index lại quan trọng ở đây?
• INCLUDE khác gì với thêm column vào key của index?
• Khi nào thì Index Only Scan không hoạt động dù có covering index?

---

Lab 2 — Lock contention làm nghẽn toàn hệ thống

Tình huống:

DBA chạy lệnh bảo trì lúc thấp điểm:

ALTER TABLE users ADD COLUMN last_seen_at timestamptz;

Lệnh này block trong 30 giây. Sau đó mọi request đến /api/users/* đều bị timeout hàng loạt trong 2 phút, dù ALTER TABLE đã xong.

Giải thích cơ chế:

ALTER TABLE yêu cầu ACCESS EXCLUSIVE lock
    ↓
Đang có long transaction T1 giữ ACCESS SHARE lock (đọc users)
    ↓
ALTER TABLE phải đợi T1 xong
    ↓
Trong lúc đó, tất cả request mới (đọc/ghi users) queue sau ALTER TABLE
    ↓
T1 xong → ALTER TABLE chạy (nhanh) → nhả lock
    ↓
Hàng queue tràn vào cùng lúc → spike

Chẩn đoán khi đang xảy ra:

-- Xem ai đang chờ và ai đang block
SELECT
  blocked.pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  now() - blocked.query_start AS waiting_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY waiting_duration DESC;

Fix cho lần sau:

-- 1. Luôn set lock_timeout khi chạy DDL
SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN last_seen_at timestamptz;
-- Nếu không lấy được lock trong 3s → lệnh fail thay vì block vô hạn

-- 2. Với index: PHẢI dùng CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_last_seen ON users (last_seen_at);

-- 3. Với NOT NULL constraint: dùng NOT VALID + VALIDATE tách biệt
ALTER TABLE users
  ADD CONSTRAINT users_last_seen_not_null
  CHECK (last_seen_at IS NOT NULL) NOT VALID;
-- Chạy giờ thấp điểm:
ALTER TABLE users VALIDATE CONSTRAINT users_last_seen_not_null;

Kiểm tra hiểu biết:

• Tại sao CREATE INDEX CONCURRENTLY không gây vấn đề này?
• NOT VALID constraint có được enforce cho INSERT/UPDATE mới không?
• Nếu không thể tránh ACCESS EXCLUSIVE, quy trình deploy an toàn là gì?

---

Lab 3 — Table bloat làm query chậm dần

Tình huống:

Bảng sessions có ~5 triệu rows, nhưng query SELECT count(*) FROM sessions WHERE expires_at < now() mất 8 giây, trong khi pg_relation_size('sessions') trả về 12GB — quá lớn so với data thực tế.

Chẩn đoán:

-- Kiểm tra dead rows
SELECT
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'sessions';

Nếu dead_pct > 20% và last_autovacuum là null hoặc rất cũ → autovacuum bị miss.

-- Xem autovacuum có đang chạy không
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%';

-- Xem config autovacuum của table này
SELECT reloptions FROM pg_class WHERE relname = 'sessions';

Nguyên nhân thường gặp:

Nguyên nhân	Dấu hiệu	Fix
Table write quá nhanh, autovacuum không kịp	n_dead_tup tăng liên tục	Giảm autovacuum_vacuum_scale_factor
Autovacuum bị throttle	autovacuum_vacuum_cost_delay cao	Tăng autovacuum_vacuum_cost_limit
Long transaction giữ xmin	age(relfrozenxid) lớn	Tìm và kill long transaction

Fix:

-- Force vacuum ngay lập tức (manual)
VACUUM ANALYZE sessions;

-- Nếu muốn reclaim disk space thực sự (full lock, dùng cẩn thận)
VACUUM FULL sessions;  -- hoặc dùng pg_repack để không lock

-- Tune autovacuum cho table write-heavy
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum khi 1% rows là dead (thay vì 20%)
  autovacuum_vacuum_cost_limit = 800        -- cho autovacuum chạy nhanh hơn
);

Kiểm tra hiểu biết:

• Tại sao VACUUM thường không trả disk space về OS?
• pg_repack khác VACUUM FULL ở điểm gì quan trọng nhất?
• Long transaction giữ xmin gây ra vấn đề gì ngoài bloat?

---

Lab 4 — Planner chọn plan sai

Tình huống:

Query sau chạy 200ms trên staging (100k rows) nhưng mất 45 giây trên production (50M rows):

SELECT u.email, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2024-01-01'
  AND u.country = 'VN'
GROUP BY u.id, u.email
HAVING count(o.id) > 10;

Chẩn đoán:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2024-01-01'
  AND u.country = 'VN'
GROUP BY u.id, u.email
HAVING count(o.id) > 10;

Tìm trong output:

• Estimated rows vs actual rows — lệch nhiều không?
• Join strategy là gì? Hash Join hay Nested Loop?

Ví dụ output đáng ngờ:

Hash Join  (cost=1234..89234 rows=150 width=32)
           (actual rows=48291 loops=1)   ← actual gấp 300 lần estimated!

Planner nghĩ chỉ 150 rows match điều kiện country = 'VN' nên chọn Nested Loop — thực tế 48k rows → catastrophic.

Nguyên nhân: Statistics lỗi thời hoặc không đủ chi tiết cho column country.

Fix:

-- Chạy ANALYZE để refresh statistics
ANALYZE users;

-- Nếu vẫn sai: tăng statistics target cho column low-cardinality nhưng skewed
ALTER TABLE users ALTER COLUMN country SET STATISTICS 500;
ANALYZE users;

-- Kiểm tra histogram sau khi analyze
SELECT most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats
WHERE tablename = 'users' AND attname = 'country';

-- Nếu country và created_at correlated: tạo extended statistics
CREATE STATISTICS stat_users_country_created ON country, created_at FROM users;
ANALYZE users;

Kiểm tra hiểu biết:

• pg_stats.most_common_vals lưu tối đa bao nhiêu giá trị theo mặc định?
• Extended statistics (CREATE STATISTICS) giúp gì mà single-column statistics không làm được?
• Khi nào nên dùng SET enable_hashjoin = off và khi nào không nên?

---

Lab 5 — Replication lag đột ngột tăng

Tình huống:

Grafana alert: replication lag trên replica vượt 30 giây và đang tăng. Application đang đọc từ replica cho reporting queries.

Chẩn đoán ngay lập tức:

-- Trên primary: xem replication status
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS total_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

-- Trên replica: xem recovery status
SELECT
  now() - pg_last_xact_replay_timestamp() AS replication_delay,
  pg_is_in_recovery(),
  pg_last_wal_receive_lsn(),
  pg_last_wal_replay_lsn();

Phân tích theo loại lag:

Lag tăng ở đâu	Nguyên nhân	Hướng xử lý
write_lag cao	Network giữa primary và replica	Kiểm tra network, xem xét synchronous_commit = off
flush_lag cao	Disk trên replica chậm	Upgrade disk replica hoặc dùng async replication
replay_lag cao	Replica đang bị query nặng	Query conflict với recovery — xem max_standby_streaming_delay

Xử lý query conflict trên replica:

-- Xem query đang block recovery trên replica
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Option 1: Tăng tolerance — replica chờ query xong rồi mới apply WAL
-- postgresql.conf:
-- max_standby_streaming_delay = '30s'

-- Option 2: Cho phép replica cancel conflicting queries ngay
-- hot_standby_feedback = on  ← primary sẽ không cleanup rows replica đang đọc
-- max_standby_streaming_delay = '10s'

-- Option 3: Dùng connection khác cho long-running reports
-- Kết nối trực tiếp primary cho queries > 30s, replica chỉ cho short reads

Kiểm tra hiểu biết:

• hot_standby_feedback = on giải quyết conflict nhưng có tác dụng phụ gì trên primary?
• Tại sao replica lag tăng đột ngột trong khi trước đó ổn định?
• Khi nào nên cân nhắc dùng synchronous_commit = remote_apply?