Bên trong storage engine
MVCC, WAL, checkpoint, B-tree vs LSM-tree, heap và clustered index, VACUUM và bloat, HOT update, buffer pool, TOAST, transaction ID wraparound.
Ở mức senior/staff, người phỏng vấn muốn biết bạn hiểu database làm gì với dữ liệu trên đĩa: vì sao UPDATE trong Postgres thực chất là INSERT, vì sao bảng phình to dù số dòng không đổi, vì sao Cassandra ghi nhanh mà đọc chậm hơn. Hiểu tầng này giúp bạn giải thích được các hiện tượng hiệu năng mà người chỉ biết SQL phải đoán mò.
📋 Danh sách câu hỏi
- 1MVCC trong PostgreSQL hoạt động thế nào? Vì sao 'người đọc không chặn người ghi'?●●●○ Khó
- 2Write-Ahead Log (WAL) là gì? Database đảm bảo durability (chữ D trong ACID) thế nào khi mất điện?●●●○ Khó
- 3So sánh B-tree và LSM-tree. Khi nào chọn storage engine dựa trên LSM (RocksDB, Cassandra, ScyllaDB)?●●●● Cực khó
- 4Heap table (Postgres) và clustered index (InnoDB) khác nhau thế nào? Điều đó ảnh hưởng gì tới việc chọn khoá chính?●●●○ Khó
- 5VACUUM làm gì? Bloat là gì, phát hiện và xử lý thế nào? Autovacuum nên tinh chỉnh ra sao?●●●○ Khó
- 6HOT update là gì? Vì sao thêm một index có thể làm UPDATE chậm hơn và bảng phình to?●●●○ Khó
- 7Buffer pool / shared buffers và page cache của hệ điều hành hoạt động thế nào? Cache hit ratio nói lên điều gì?●●○○ Trung bình
- 8Postgres lưu giá trị lớn (text dài, jsonb lớn) thế nào? TOAST ảnh hưởng gì tới hiệu năng?●●●○ Khó
- 9Transaction ID wraparound trong PostgreSQL là gì và vì sao nó có thể làm database ngừng nhận ghi?●●●● Cực khó
- 10Database page là gì và vì sao kích thước dòng, kiểu dữ liệu, thứ tự cột lại ảnh hưởng hiệu năng?●●○○ Trung bình
Xem câu trả lời & giải thích
Mỗi dòng (tuple) mang xmin (transaction tạo ra nó) và xmax (transaction xoá/cập nhật nó). UPDATE không ghi đè mà tạo phiên bản mới và đánh dấu bản cũ hết hiệu lực. Mỗi transaction có một snapshot cho biết transaction nào đã commit lúc bắt đầu; nó chỉ thấy phiên bản hợp lệ theo snapshot đó. Vì người đọc đọc phiên bản cũ còn người ghi tạo phiên bản mới, hai bên không cần khoá lẫn nhau.
- Visibility: tuple hiển thị nếu
xminđã commit và thuộc snapshot, vàxmaxtrống/chưa commit/không thuộc snapshot. - Bản cũ thành dead tuple khi không còn snapshot nào cần nó → VACUUM thu hồi không gian.
- Ghi–ghi vẫn xung đột: hai UPDATE cùng dòng → người sau chờ khoá dòng; ở Repeatable Read/Serializable người sau bị lỗi serialization.
- So sánh: InnoDB (MySQL) và Oracle cập nhật tại chỗ và lưu phiên bản cũ trong undo log; Postgres lưu mọi phiên bản trong heap → cần VACUUM nhưng rollback tức thì.
- Transaction mở lâu (kể cả
idle in transaction) giữ snapshot cũ → VACUUM không dọn được dead tuple ở mọi bảng → bloat, chậm. Đặtidle_in_transaction_session_timeout, theo dõipg_stat_activity.xact_start. - Bảng cập nhật liên tục (counter, trạng thái job) sinh nhiều dead tuple nhất — cần autovacuum tuning riêng.
- Báo cáo chạy 2 giờ trên primary là nguyên nhân bloat phổ biến → chuyển sang replica.
Câu hỏi nối tiếp: "Vì sao SELECT count(*) trên Postgres chậm hơn MySQL MyISAM?" → Do MVCC, mỗi
transaction có thể thấy số dòng khác nhau, nên phải quét để kiểm tra visibility (có tối ưu bằng visibility map + index only
scan). Cần số gần đúng thì dùng pg_class.reltuples.
Nói MVCC 'không có khoá'; không biết dead tuple và mối liên hệ với transaction dài.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Mọi thay đổi được ghi vào WAL (log tuần tự) và fsync xuống đĩa trước khi COMMIT trả về; trang dữ liệu thật có thể ghi xuống sau. Khi crash, database đọc WAL từ checkpoint gần nhất và replay để khôi phục trạng thái đã commit. Ghi tuần tự vào log nhanh hơn nhiều so với ghi ngẫu nhiên vào nhiều trang dữ liệu.
- Checkpoint: định kỳ ghi mọi trang bẩn (dirty page) xuống đĩa, đánh dấu điểm WAL từ đó trở đi mới cần replay → giới hạn thời gian khôi phục. Checkpoint dày → khôi phục nhanh nhưng I/O nhiều.
- Full page writes: lần đầu sửa một trang sau checkpoint, ghi cả trang vào WAL để chống torn page (ghi dở trang khi mất điện).
synchronous_commit = off: COMMIT trả về trước khi fsync → nhanh hơn, có thể mất vài trăm ms giao dịch gần nhất khi crash (nhưng không hỏng dữ liệu).- WAL cũng là nền tảng của replication (gửi WAL cho replica) và PITR (lưu trữ WAL để khôi phục theo thời điểm).
- Đĩa/cloud volume nói dối về fsync (write cache không có pin) có thể làm mất dữ liệu đã commit — chọn lưu trữ đáng tin, không tắt
fsync. - Ghi hàng loạt: gom nhiều dòng vào một transaction giảm số lần fsync rất nhiều;
synchronous_commit=offtheo từng transaction cho dữ liệu không quan trọng (log, analytics). - Dung lượng WAL tăng vọt khi replication slot bị bỏ rơi (replica/CDC ngừng đọc) → đầy đĩa → sập DB. Theo dõi
pg_replication_slots.
Tình huống: "Đĩa primary đầy 100% lúc nửa đêm dù dữ liệu không tăng." → Kiểm tra thư mục pg_wal: một
replication slot của công cụ CDC đã ngừng tiêu thụ từ 3 ngày trước. Xoá/khôi phục consumer, đặt max_slot_wal_keep_size
và alert theo độ trễ slot.
Nghĩ dữ liệu được ghi trực tiếp vào bảng khi COMMIT; không biết WAL liên quan replication.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
B-tree cập nhật tại chỗ trên các trang cố định: đọc nhanh, ổn định (O(log n), ít trang), ghi ngẫu nhiên tốn I/O. LSM-tree ghi vào bộ nhớ (memtable) và log, khi đầy thì flush thành file đã sắp xếp bất biến (SSTable), rồi compaction gộp dần: ghi rất nhanh (tuần tự), nhưng đọc có thể phải tìm qua nhiều file (giảm bằng bloom filter) và compaction tốn tài nguyên nền. Chọn LSM cho tải ghi rất nặng: time-series, log sự kiện, IoT, feed, đếm.
| B-tree | LSM-tree | |
|---|---|---|
| Write amplification | Ghi cả trang cho thay đổi nhỏ | Dữ liệu bị ghi lại nhiều lần qua compaction |
| Read amplification | Thấp | Cao hơn (nhiều level), bloom filter giảm bớt |
| Space amplification | Phân mảnh trang | Bản cũ và tombstone tồn tại tới khi compaction |
| Xoá | Xoá tại chỗ | Ghi tombstone; đọc phải bỏ qua tombstone cho tới khi compact |
| Ví dụ | Postgres, InnoDB, SQL Server | RocksDB, LevelDB, Cassandra, ScyllaDB, MyRocks, nhiều TSDB |
Chiến lược compaction: leveled (đọc tốt, ghi nhiều hơn) vs size-tiered (ghi tốt, tốn không gian, đọc kém hơn).
- Cassandra: mô hình theo truy vấn, xoá hàng loạt sinh tombstone làm truy vấn chậm/timeout — thiết kế TTL theo partition thời gian thay vì DELETE.
- Nhiều hệ thống mới (CockroachDB, TiDB, YugabyteDB) dùng LSM (Pebble/RocksDB) bên dưới một lớp SQL.
- Tải OLTP hỗn hợp đọc/ghi bình thường: B-tree của Postgres/MySQL vẫn là lựa chọn mặc định.
Câu hỏi nối tiếp: "Cluster Cassandra thỉnh thoảng có độ trễ đọc p99 tăng vọt." → Thường do compaction nền cạnh tranh I/O, hoặc truy vấn quét qua nhiều tombstone. Kiểm tra số SSTable mỗi lần đọc, tombstone warning, điều chỉnh chiến lược compaction và throughput giới hạn.
Nói LSM 'nhanh hơn' B-tree mà không nói đánh đổi đọc/ghi/không gian.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Postgres lưu dòng trong heap không theo thứ tự; mọi index (kể cả primary key) trỏ tới vị trí vật lý (ctid). InnoDB lưu dòng bên trong B-tree của primary key (clustered), và secondary index lưu giá trị PK để tra ngược. Hệ quả: trong InnoDB, PK ngẫu nhiên (UUIDv4) gây chèn vào giữa cây → tách trang, phân mảnh, buffer pool kém hiệu quả; PK lớn làm mọi secondary index phình to. PK tăng dần (bigint, UUIDv7/ULID) tốt hơn nhiều.
- InnoDB: truy vấn theo PK đọc thẳng dòng (rất nhanh); truy vấn theo secondary index cần hai lần tra (index → PK → dòng), trừ khi index phủ đủ cột.
- Postgres: UPDATE tạo bản dòng mới ở vị trí khác → phải cập nhật mọi index (trừ HOT update); có lệnh
CLUSTERsắp xếp lại một lần nhưng không duy trì. - Locality: PK theo thời gian giúp dữ liệu mới nằm gần nhau → cache tốt cho tải "đọc dữ liệu gần đây".
- Cần ID không đoán được cho API mà vẫn tốt cho index: UUIDv7 (có tiền tố thời gian), hoặc PK bigint nội bộ + public_id ngẫu nhiên.
- Với Postgres, UUIDv4 vẫn làm index B-tree của PK phân tán và tăng WAL (full page writes nhiều hơn) — ảnh hưởng nhỏ hơn InnoDB nhưng có thật.
Tình huống: "Bảng MySQL 500 triệu dòng PK UUIDv4, tốc độ INSERT giảm dần theo thời gian." → Tách trang và buffer pool không chứa nổi các trang bị chèn ngẫu nhiên. Chuyển sang PK tuần tự cho bảng mới, hoặc UUID dạng sắp xếp được.
Không biết khái niệm clustered index; nghĩ mọi DB lưu dữ liệu giống nhau.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
VACUUM đánh dấu dead tuple thành không gian có thể tái sử dụng, cập nhật visibility map (cho index-only scan) và freeze tuple cũ để chống wraparound; nó thường không trả không gian về hệ điều hành. Bloat là bảng/index lớn hơn nhiều so với dữ liệu sống, do dead tuple tích tụ nhanh hơn VACUUM dọn → quét nhiều trang hơn, cache kém. Xử lý: tinh chỉnh autovacuum cho bảng nóng, loại bỏ transaction dài, và khi bloat nặng thì rebuild trực tuyến (pg_repack, REINDEX CONCURRENTLY).
- Autovacuum mặc định kích hoạt khi dead tuple > 50 + 20% số dòng — với bảng 100 triệu dòng là 20 triệu dead tuple, quá muộn. Đặt theo bảng:
autovacuum_vacuum_scale_factor = 0.01hoặc ngưỡng tuyệt đối. - Tăng
autovacuum_vacuum_cost_limit/số worker để vacuum chạy kịp trên đĩa nhanh. VACUUM FULLghi lại bảng và trả không gian nhưng khoá toàn bảng — không dùng trên production đang chạy.- Phát hiện:
pg_stat_user_tables(n_dead_tup, last_autovacuum), extensionpgstattuple, các truy vấn ước lượng bloat.
- Mẫu bảng "hàng đợi" (INSERT rồi UPDATE/DELETE liên tục) là sát thủ bloat → vacuum dày, hoặc partition theo thời gian và DROP partition cũ.
- Index bloat giải bằng
REINDEX INDEX CONCURRENTLY(Postgres 12+). - Alert theo tỷ lệ dead tuple và thời điểm autovacuum gần nhất của bảng quan trọng.
Câu chuyện mẫu: "Bảng jobs 2GB dữ liệu sống nhưng chiếm 80GB, truy vấn lấy job tăng từ 2ms lên 400ms. Nguyên
nhân: một connection BI giữ transaction mở nhiều ngày. Kill session, đặt timeout, chạy pg_repack, chỉnh autovacuum riêng cho bảng
— về 3GB, 2ms."
Chạy VACUUM FULL trên production; tắt autovacuum vì 'tốn tài nguyên'.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Heap-Only Tuple: khi UPDATE không thay đổi cột nào có index và trang hiện tại còn chỗ, Postgres đặt phiên bản mới cùng trang và không phải cập nhật bất kỳ index nào — nhanh hơn và ít bloat index hơn nhiều. Thêm index lên một cột hay bị cập nhật (ví dụ updated_at, status) làm mọi UPDATE mất khả năng HOT → mọi index phải nhận entry mới.
- Điều kiện HOT: (1) không cột nào trong bất kỳ index nào thay đổi; (2) trang chứa dòng còn đủ chỗ cho bản mới.
fillfactor(ví dụ 80-90) chừa sẵn chỗ trống trong trang cho bảng hay UPDATE → tăng tỷ lệ HOT.- Theo dõi:
pg_stat_user_tables.n_tup_hot_updso vớin_tup_upd.
- Trước khi thêm index, hỏi: cột này có bị cập nhật thường xuyên không? Index trên
updated_atcủa bảng nóng thường có giá rất đắt. - Tách cột cập nhật liên tục (counter, last_seen_at) sang bảng riêng hẹp để bảng chính ít bị ảnh hưởng.
- Xoá index không dùng — mỗi index thừa vừa tốn ghi vừa giết HOT.
Câu hỏi nối tiếp: "Sau khi thêm index để tăng tốc một báo cáo, latency ghi của API tăng 40%." → Index mới trên cột hay cập nhật làm mất HOT. Cân nhắc partial index, chạy báo cáo trên replica có index riêng không được (replica vật lý giống primary) → dùng logical replica/kho phân tích.
Nghĩ index chỉ có chi phí lưu trữ.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Database đọc ghi theo trang (8KB ở Postgres, 16KB ở InnoDB). Trang hay dùng được giữ trong bộ nhớ của DB (shared_buffers / innodb_buffer_pool); Postgres còn dựa vào page cache của OS (double buffering), còn InnoDB thường dùng O_DIRECT và buffer pool lớn (60-80% RAM). Cache hit ratio thấp nghĩa là working set không vừa bộ nhớ → đọc đĩa nhiều, latency tăng.
- Postgres:
shared_buffersthường ~25% RAM, phần còn lại để OS cache;effective_cache_sizebáo cho planner biết tổng cache khả dụng. - Thuật toán thay thế: clock-sweep (Postgres), LRU có vùng young/old (InnoDB) để một lần quét toàn bảng không đẩy hết trang nóng ra.
- Working set: phần dữ liệu thực sự được truy cập thường xuyên — quan trọng hơn tổng kích thước DB.
EXPLAIN (ANALYZE, BUFFERS):shared hit(trong cache) vsread(từ OS/đĩa).
- Hit ratio trung bình cao (99%) vẫn có thể che giấu một truy vấn quan trọng luôn đọc đĩa — xem theo từng truy vấn.
- Sau restart/failover cache trống → latency cao vài phút;
pg_prewarmlàm ấm cache. - Giảm working set: bỏ cột thừa, partition dữ liệu cũ, index hẹp hơn, archive.
Tình huống: "Sau failover sang replica, mọi truy vấn chậm 10 lần trong 15 phút." → Cache của replica lạnh với workload ghi/đọc của primary. Dùng pg_prewarm (autoprewarm), hoặc cho replica phục vụ một phần đọc thường xuyên để cache luôn ấm.
Đặt shared_buffers = 90% RAM cho Postgres.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Một dòng phải vừa trong trang 8KB. Giá trị lớn hơn ~2KB được nén và/hoặc chuyển sang bảng TOAST riêng, dòng chính chỉ giữ con trỏ. Truy vấn không đọc cột đó thì không tốn chi phí; đọc thì phải giải nén và đọc thêm trang. Cập nhật một trường nhỏ trong jsonb lớn sẽ ghi lại toàn bộ giá trị jsonb.
- Chiến lược cột: PLAIN, MAIN, EXTERNAL, EXTENDED (mặc định cho text/jsonb: nén rồi đẩy ra ngoài). Postgres 14+ hỗ trợ nén LZ4 nhanh hơn pglz.
SELECT *trên bảng có cột TOAST lớn kéo theo rất nhiều I/O không cần thiết.- jsonb bất biến khi lưu:
jsonb_settạo bản mới toàn bộ → document 1MB cập nhật liên tục = WAL và bloat khổng lồ.
- Tách trường hay cập nhật/hay lọc của document jsonb ra cột riêng; giữ jsonb cho phần ít đổi.
- Không lưu file nhị phân lớn (ảnh, PDF) trong DB — để object storage, DB lưu key/metadata.
- Chỉ SELECT cột cần thiết trong danh sách; cột nặng tải ở trang chi tiết.
Câu hỏi nối tiếp: "Bảng documents lưu nội dung editor dạng jsonb 500KB, autosave mỗi 5 giây." → WAL tăng vọt,
replication lag, bloat. Lưu thay đổi dạng diff/operation log, chụp snapshot định kỳ, hoặc chuyển nội dung lên object storage.
Nghĩ jsonb cập nhật từng phần tại chỗ.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
XID của Postgres là số 32-bit (~4 tỷ, so sánh vòng tròn nên chỉ ~2 tỷ 'quá khứ'). Nếu tuple cũ không được freeze (đánh dấu 'luôn hiển thị') trước khi XID quay vòng, dữ liệu cũ sẽ đột nhiên trông như 'ở tương lai' và biến mất. Để ngăn điều đó, khi tiến gần giới hạn Postgres chạy autovacuum chống wraparound bắt buộc, và nếu vẫn không kịp thì từ chối mọi transaction ghi cho tới khi VACUUM xong.
- Theo dõi tuổi:
SELECT datname, age(datfrozenxid) FROM pg_database;— cảnh báo khi vượt vài trăm triệu, nguy hiểm khi gần 2 tỷ. - Nguyên nhân không freeze kịp: autovacuum bị huỷ liên tục (do lock), quá chậm so với tốc độ ghi, transaction dài/replication slot/prepared transaction bị bỏ rơi giữ xmin cũ.
autovacuum_freeze_max_age(mặc định 200 triệu) quyết định khi nào vacuum chống wraparound bắt đầu.- Đã có nhiều sự cố công khai của các công ty lớn bị dừng ghi hàng giờ vì vấn đề này.
- Alert theo
age(datfrozenxid)vàage(relfrozenxid)của bảng lớn nhất. - Tải ghi rất cao: chạy VACUUM FREEZE chủ động giờ thấp điểm, partition bảng để freeze theo phần nhỏ.
- Dọn prepared transaction và replication slot bị bỏ rơi.
Tình huống: "Log Postgres cảnh báo 'database must be vacuumed within 10 million transactions'." → Sự cố cấp cao: tìm thứ giữ
xmin (transaction dài, slot, prepared xact), loại bỏ, chạy VACUUM (có thể với VACUUM (FREEZE, VERBOSE) trên bảng cũ nhất),
giảm tải ghi không quan trọng trong lúc chờ.
Chưa từng nghe tới; nghĩ chỉ cần restart database.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Database đọc ghi theo trang (8KB/16KB); số dòng vừa trong một trang quyết định số I/O cho một lần quét. Dòng nhỏ hơn → nhiều dòng mỗi trang → quét nhanh hơn, cache chứa được nhiều hơn. Chọn kiểu dữ liệu vừa đủ (int vs bigint, date vs timestamptz, enum/smallint cho trạng thái), tránh cột thừa; ở Postgres, căn chỉnh (alignment) làm thứ tự cột ảnh hưởng tới padding.
- Header mỗi tuple Postgres ~23 byte + căn chỉnh — bảng nhiều dòng hẹp có overhead đáng kể.
- Sắp cột cố định lớn trước (bigint, timestamptz 8 byte), rồi 4 byte, 2 byte, cuối cùng là cột độ dài biến đổi để giảm padding.
- Kiểu
uuid(16 byte) vsbigint(8 byte): khác biệt nhân lên ở mọi index và FK. - Tiền:
numericchính xác nhưng chậm hơn; số nguyên đơn vị nhỏ nhất (bigint cent) nhanh và chính xác.
- Với bảng tỷ dòng (event, log), tối ưu vài byte mỗi dòng tiết kiệm hàng chục GB và chi phí cloud.
- Không lưu chuỗi trạng thái dài lặp lại hàng triệu lần — dùng enum hoặc bảng tra cứu.
Câu hỏi nối tiếp: "Dùng int cho PK bảng sự kiện có ổn không?" → Int 32-bit tối đa ~2.1 tỷ; nhiều công ty đã
phải migrate khẩn cấp khi cạn ID. Dùng bigint cho bảng có thể lớn.
Coi thiết kế kiểu dữ liệu là chi tiết không quan trọng.
- Tôi trả lời được câu này mà không cần xem đáp án