Learning Outcome & Exercises: Database (PostgreSQL)

Đọc hiểu chưa đủ để đi phỏng vấn. Mỗi chương kết thúc bằng những việc làm được và chứng minh được: có code chạy, có test cho cả trường hợp lỗi, có log hoặc số đo. Bài tập nằm trong repo riêng java-backend-interview-labs, thư mục labs/database.

1. Mục tiêu đầu ra

Học xong chương này, bạn tự làm được:

• Đọc EXPLAIN (ANALYZE, BUFFERS) và chứng minh index được dùng hay không.
• Tái hiện bằng 2 phiên psql: non-repeatable read, deadlock và cách PostgreSQL xử lý.
• Quan sát dead tuple tăng sau UPDATE hàng loạt và giảm sau VACUUM.

2. Basic: Index & EXPLAIN

Trạng thái: ⬜ Chưa triển khai: chưa có code/test, chưa tính là hoàn thành

Bảng 1 triệu đơn hàng: query theo (user_id, created_at) trước/sau composite index, so sánh plan và thời gian.

Bài liên quan: Index & EXPLAIN, Thiết kế schema & query

Hoàn thành khi:

• 2 plan EXPLAIN ANALYZE trong evidence/

3. Intermediate: Isolation & deadlock bằng 2 phiên

Trạng thái: ⬜ Chưa triển khai: chưa có code/test, chưa tính là hoàn thành

Script 2 phiên psql: READ COMMITTED vs REPEATABLE READ; tạo deadlock và đọc log PostgreSQL.

Bài liên quan: ACID & isolation level, Lock & concurrent transaction

Hoàn thành khi:

• Timeline 2 phiên
• Thông báo deadlock detected trong log

4. Challenge: MVCC & VACUUM

Trạng thái: ⬜ Chưa triển khai: chưa có code/test, chưa tính là hoàn thành

UPDATE 1 triệu dòng, đo n_dead_tup và kích thước bảng qua pg_stat_user_tables; giữ một transaction dài và chứng minh VACUUM không dọn được.

Bài liên quan: MVCC & VACUUM, Kiến trúc PostgreSQL & WAL, Connection pool

Hoàn thành khi:

• Số liệu pg_stat trước/sau
• Giải thích vì sao transaction dài gây bloat

5. Tiêu chí hoàn thành chung

• Tự giải thích được bài toán và vì sao chọn giải pháp.
• Code chạy được bằng lệnh ghi trong README, không chứa secret.
• Có test tự động cho happy path và failure path.
• Có log hoặc số đo trong evidence/ chứng minh kết quả.
• Có câu trả lời phỏng vấn ngắn bằng tiếng Việt và tiếng Anh.
• README của lab có link ngược về bài học tương ứng.

6. Lab và trạng thái

Repo lab: java-backend-interview-labs/labs/database. TODO: link GitHub là placeholder cho tới khi repo được push.

Q1. Vì sao một transaction mở quá lâu làm bảng PostgreSQL phình to?

🇻🇳 Tiếng Việt:

MVCC giữ phiên bản cũ cho các snapshot còn đang dùng. Transaction dài giữ xmin cũ nên VACUUM không được xóa dead tuple, bảng và index phình dần.

🇬🇧 English:

MVCC keeps old row versions for snapshots that may still need them. A long-running transaction holds an old xmin, so VACUUM cannot remove dead tuples and tables and indexes bloat.

Cách nhớ ngắn gọn

• Mỗi bài tập = code + test + log.
• Luôn có ít nhất một failure case.
• Chưa có bằng chứng thì chưa xong.