Part 04 · PostgreSQL & Redis · 4.3A

PostgreSQL và Redis execution labs

Ba lab này đi sâu vào execution evidence. Mọi kết luận về visibility, lock, plan hoặc cache resilience phải đi kèm query, metric và invariant có thể tái hiện.

Môi trường: dùng PostgreSQL và Redis thật trong local container/VM riêng. Ghi exact version, configuration overrides và resource limits. Không chạy fault injection hoặc destructive commands trên shared environment.

Lab A · Transaction visibility, locks và deadlock

Setup

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    balance NUMERIC(19,2) NOT NULL CHECK (balance >= 0),
    version BIGINT NOT NULL DEFAULT 0
);

INSERT INTO accounts(id, balance, version)
VALUES (1, 1000, 0), (2, 1000, 0);

Mở hai psql sessions A/B và một session C chỉ dùng quan sát. Bật timestamp trong transcript; trước mỗi scenario reset balances bằng transaction riêng.

Scenario 1: visibility

  1. A bắt đầu transaction và update account 1 nhưng chưa commit.
  2. B đọc cùng row ở Read Committed: không được thấy uncommitted value.
  3. A commit; B đọc lại. Ở Read Committed statement mới thấy value mới, còn Repeatable Read transaction cũ giữ snapshot.
  4. Lặp lại với rollback để chứng minh dirty value không xuất hiện.

Scenario 2: blocking và timeout

  1. A chạy SELECT ... FOR UPDATE account 1.
  2. B đặt SET LOCAL lock_timeout = '2s' rồi update row đó.
  3. Từ C, query pg_stat_activity, pg_locks và blocking PIDs trước khi timeout.
  4. Xác nhận B nhận lock-timeout SQLSTATE và transaction không chứa partial business update.

Scenario 3: deadlock và fix

-- Session A                         -- Session B
BEGIN;                              BEGIN;
SELECT ... WHERE id=1 FOR UPDATE;   SELECT ... WHERE id=2 FOR UPDATE;
SELECT ... WHERE id=2 FOR UPDATE;   SELECT ... WHERE id=1 FOR UPDATE;
  1. Lưu deadlock report và SQLSTATE của victim.
  2. Sửa transfer để luôn sort IDs và lock ID nhỏ trước.
  3. Thêm retry toàn transaction với bounded attempts, jitter và fresh reads.
  4. Chạy concurrent transfers nhiều vòng; verify balance mỗi account không âm và tổng balance luôn 2000.
Gate: transcript chứng minh uncommitted data không visible; timeout/deadlock không để partial transfer; stable lock order loại cycle; retry có metric và invariant tổng balance giữ nguyên.

Lab B · Index và execution plan

Dataset có skew

Tạo ít nhất một triệu transfers. Phần lớn rows ở trạng thái hoàn tất, một tỷ lệ nhỏ pending; một số accounts có traffic lớn hơn rõ rệt. Lưu seed và generator để lần chạy sau có cùng distribution.

SELECT account_id, id, created_at, amount
FROM transfers
WHERE tenant_id = :tenant
  AND account_id = :account
  AND status = 'PENDING'
  AND created_at < :cursor_time
ORDER BY created_at DESC, id DESC
LIMIT 50;

Actions

  1. Chạy EXPLAIN (ANALYZE, BUFFERS, SETTINGS) khi chưa có index mục tiêu; lưu estimated/actual rows, loops, buffers và time.
  2. Đề xuất composite/partial index từ equality, status subset và ordering. Viết giả thuyết trước khi tạo index.
  3. Chạy lại với cold/warm cache được ghi rõ; kiểm tra scan type, rows removed, sort và heap fetches.
  4. Thử index có INCLUDE(amount); quan sát index-only scan trước/sau VACUUM và write churn.
  5. So OFFSET page sâu với keyset (created_at,id), gồm concurrent inserts.
  6. Đo insert/update TPS và bytes của mỗi index; loại index nếu read gain không bù write/storage cost.
Gate: giải thích được planner decision từ cardinality/cost, không chỉ nói “index tồn tại”; report có plans trước/sau, latency distribution, buffer/temp I/O, pagination correctness và write amplification.

Lab C · Redis cache failure

Baseline cache-aside

AccountView get(long id) {
    return cache.get(id).orElseGet(() -> {
        AccountView value = database.load(id);
        cache.put(id, value, ttlWithJitter());
        return value;
    });
}

Thêm metrics tối thiểu: cache hit/miss/error/latency, origin calls, DB pool wait/active, request latency/error và key class. Key phải chứa tenant/security scope nếu response phụ thuộc chúng.

Scenario 1: cold-key stampede

  1. Xóa một hot key rồi gửi burst concurrent requests.
  2. Ghi số origin calls, peak DB concurrency và p99.
  3. Thêm single-flight; chạy lại trong một instance và nhiều instances để thấy local coalescing boundary.
  4. Thêm bounded distributed coordination hoặc stale-while-revalidate nếu cần; kill holder và quan sát waiter fallback.

Scenario 2: timeout và outage

  1. Inject Redis latency lớn hơn cache timeout, sau đó stop Redis.
  2. Xác nhận cache timeout nhỏ hơn request deadline và retry không nhân tải.
  3. Giới hạn concurrent DB fallbacks, rate-limit hoặc shed optional traffic; đo DB QPS/pool wait.
  4. Khởi động Redis và warm hot keys có rate control.

Scenario 3: invalidation failure

  1. Update DB thành công rồi làm cache invalidation thất bại.
  2. Đo stale window tới TTL; tái hiện reader cũ repopulate sau invalidation.
  3. Áp versioned values hoặc outbox invalidation, chạy lại race và kiểm tra out-of-order/duplicate events.
  4. Chọn fail-open/fail-closed theo data class; không dùng cùng policy cho public catalog và account balance.
Gate: Redis down/slow không kéo DB sập; cache không trở thành source of truth; stale window được định lượng; origin amplification và hit/miss/error/latency đều có evidence.

Deliverables

Nguồn tham khảo