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:

sql

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:

sql

-- 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:

sql

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:

sql

-- 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:

sql

-- 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:

sql

-- 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.

sql

-- 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:

sql

-- 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):

sql

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:

sql

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:

sql

-- 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:

sql

-- 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:

sql

-- 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?

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

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

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

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

Lab 4 — Planner chọn plan sai ​

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

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

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

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

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

Lab 4 — Planner chọn plan sai

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