MySQL vs PostgreSQL dưới góc nhìn phỏng vấn: Read/Write-Heavy và cơ chế MVCC

MySQL vs PostgreSQL là chủ đề kinh điển trong các buổi phỏng vấn backend engineer. Người phỏng vấn hỏi câu này không phải để nghe bạn liệt kê các tính năng bề nổi như “Postgres hỗ trợ JSON tốt hơn” hay “MySQL phổ biến hơn”. Họ muốn đánh giá mức độ hiểu biết sâu sắc của bạn về mặt kiến trúc lưu trữ, cơ chế kiểm soát giao dịch và cách hệ thống vận hành dưới các loại tải thực tế (High Load).

Bài viết này tập trung vào các điểm mấu chốt kỹ thuật giúp bạn vượt qua những câu hỏi xoáy sâu của người phỏng vấn.

1. Bản chất kiến trúc lưu trữ: Clustered Index vs Heap Table

Để trả lời được câu hỏi “nên chọn cái nào cho Read/Write-heavy”, trước hết cần hiểu cách hai cơ sở dữ liệu này tổ chức dữ liệu vật lý trên đĩa cứng. Sự khác biệt này quyết định trực tiếp đến hiệu năng đọc ghi.

MySQL (InnoDB Engine) - Clustered Index

MySQL sử dụng InnoDB làm engine mặc định. InnoDB tổ chức bảng theo kiến trúc Index-Organized Table (IOT), cụ thể là Clustered Index.

graph TD
    PK_Tree[B+Tree Primary Key] -->|Nút lá chứa| RowData[Dữ liệu dòng thực tế]
    Sec_Index[Secondary Index] -->|Nút lá chứa| PK_Val[Primary Key Value]
    PK_Val -.->|Key Lookup| PK_Tree

• Dữ liệu thực tế của các dòng được lưu trực tiếp tại các nút lá (leaf nodes) của cây B+Tree của Khóa chính (Primary Key).
• Khi bạn tìm kiếm theo Khóa chính, InnoDB chỉ cần duyệt cây B+Tree một lần là lấy được toàn bộ dữ liệu dòng (Single Hop).
• Đối với các index phụ (Secondary Indexes), nút lá của chúng không trỏ trực tiếp đến địa chỉ dòng trên đĩa, mà lưu giá trị của Khóa chính. Vì vậy, một truy vấn dùng index phụ sẽ phải trải qua quá trình Key Lookup (hai lần duyệt cây): tìm Khóa chính từ index phụ, sau đó dùng Khóa chính duyệt cây Clustered Index để lấy dữ liệu dòng.

PostgreSQL - Heap Table

PostgreSQL tổ chức dữ liệu theo kiến trúc Heap Table.

graph TD
    HeapPages[Heap Pages] -->|Lưu trữ vật lý| RowDataPG[Các tuple chứa dữ liệu dòng]
    Index_Tree[B-Tree Index] -->|Nút lá chứa| TID[TID - Page & Offset Pointer]
    TID -.->|Direct Hop| HeapPages

• Dữ liệu của bảng được ghi vào một file vật lý (chia thành các trang Heap Pages) theo dạng append-only hoặc ghi vào các khoảng trống còn dư. Vị trí vật lý của dòng được định danh bằng một con trỏ gọi là TID (Tuple ID) gồm số thứ tự trang và offset trong trang.
• Tất cả các index trong PostgreSQL (bao gồm cả Khóa chính và index phụ) đều là Secondary Indexes. Nút lá của mọi index đều chứa con trỏ TID trỏ trực tiếp đến vị trí vật lý của dòng trong Heap Page.
• Do đó, dù tìm kiếm bằng Khóa chính hay index phụ, Postgres chỉ cần thực hiện đúng một lần duyệt cây index và một lần nhảy (Hop) tới Heap Page để lấy dữ liệu.

2. Read-Heavy vs Write-Heavy: Chọn MySQL hay PostgreSQL?

Trường hợp Read-Heavy (Đọc nhiều)

Nếu hầu hết các truy vấn đọc là Point Query (đọc theo ID/Khóa chính):

• MySQL chiếm ưu thế vượt trội nhờ kiến trúc Clustered Index. Chỉ một lần duyệt cây B+Tree là lấy được dữ liệu, không tốn thêm I/O để nhảy sang Heap Page như Postgres. Cache của InnoDB (Buffer Pool) cũng tối ưu cực tốt cho việc giữ các nút của Clustered Index trên RAM.
• Postgres sẽ tốn thêm một bước đọc Heap Page từ đĩa hoặc shared buffers sau khi duyệt index.

Nếu là hệ thống có truy vấn đọc phức tạp (Complex Query, Aggregate, Joins nhiều bảng):

• PostgreSQL là lựa chọn tốt hơn nhiều. Trình tối ưu hóa truy vấn (Query Planner) của Postgres thông minh hơn MySQL rất nhiều. Postgres hỗ trợ nhiều thuật toán Join phức tạp (Hash Join, Merge Join), trong khi MySQL chủ yếu dựa vào Nested Loop Join (chỉ mới hỗ trợ Hash Join từ bản 8.0 với nhiều hạn chế). Postgres cũng hỗ trợ parallel query (chạy truy vấn trên nhiều CPU core) cực kỳ mạnh mẽ.

Trường hợp Write-Heavy (Ghi nhiều)

Bài toán INSERT (Chèn mới):

• PostgreSQL ghi nhanh hơn trong trường hợp bảng có nhiều index. Dữ liệu mới chỉ cần ném vào cuối file Heap hoặc trang trống (thao tác append đơn giản). Mặc dù vẫn phải cập nhật index, cấu trúc Heap giúp ghi bản ghi mới cực kỳ nhanh.
• MySQL phải chèn dòng mới vào đúng vị trí logic của cây Clustered Index dựa theo giá trị Khóa chính. Nếu Khóa chính không tự tăng (ví dụ UUIDv4 ngẫu nhiên), việc chèn sẽ gây ra hiện tượng Page Split (phân mảnh trang), làm chậm đáng kể hiệu năng ghi và tốn nhiều I/O đĩa.

Bài toán UPDATE (Cập nhật - Điểm cốt tử):

• MySQL thực hiện cập nhật In-place (tại chỗ). InnoDB ghi dữ liệu cũ vào Undo Log (để phục vụ transaction rollback và đọc đồng thời), sau đó ghi đè dữ liệu mới trực tiếp lên trang chứa dòng hiện tại. Nếu các cột được cập nhật không nằm trong bất kỳ index nào, MySQL không cần cập nhật lại các index phụ khác.
• PostgreSQL áp dụng cơ chế Append-only. Bản chất của một câu lệnh UPDATE trong Postgres là DELETE (đánh dấu dòng cũ đã chết) và INSERT (ghi một dòng mới - tuple mới với giá trị mới). Điều này dẫn tới hai vấn đề lớn:
  1. Write Amplification (Khuyếch đại ghi): Vì dòng mới nằm ở một vị trí vật lý khác trên đĩa (TID mới), tất cả các index trên bảng đó đều phải cập nhật để trỏ về TID mới, kể cả những index của các cột không hề bị thay đổi dữ liệu.
  2. Table Bloat (Phình bảng): Dữ liệu cũ (dead tuples) vẫn nằm trên đĩa và chiếm không gian cho đến khi tiến trình VACUUM chạy qua để dọn dẹp.

Để khắc phục nhược điểm UPDATE của Postgres, họ đã giới thiệu tính năng HOT (Heap-Only Tuples). Nếu dòng mới được ghi trên cùng một trang với dòng cũ và cột bị thay đổi không nằm trong index nào, Postgres sẽ tạo một con trỏ liên kết nội bộ trong trang để trỏ từ dòng cũ sang dòng mới mà không cần cập nhật các index ngoài. Tuy nhiên, nếu trang bị đầy hoặc cột bị update nằm trong index, write amplification vẫn xảy ra.

3. Bản chất MVCC (Multi-Version Concurrency Control)

MVCC là cơ chế giúp cho các transaction đọc (SELECT) không bị chặn bởi các transaction ghi (INSERT/UPDATE/DELETE) và ngược lại (“readers don’t block writers, writers don’t block readers”). Tuy nhiên, cách triển khai MVCC của MySQL và Postgres hoàn toàn khác nhau.

Cách MySQL (InnoDB) triển khai MVCC

InnoDB sử dụng Undo Log kết hợp với Read View để thực hiện MVCC.

• Mỗi dòng trong bảng InnoDB có các trường hệ thống ẩn: DB_TRX_ID (ID của transaction vừa sửa dòng đó) và DB_ROLL_PTR (con trỏ rollback trỏ tới bản ghi Undo Log chứa dữ liệu cũ trước khi sửa).
• Khi một transaction đọc bắt đầu ở isolation level REPEATABLE READ hoặc READ COMMITTED, nó sẽ tạo ra một Read View chứa danh sách các transaction đang active tại thời điểm đó.
• Nếu dòng dữ liệu hiện tại có DB_TRX_ID lớn hơn hoặc nằm trong danh sách active của Read View (tức là dữ liệu đã bị sửa bởi một transaction chưa commit hoặc commit sau khi ta đọc), InnoDB sẽ dùng DB_ROLL_PTR để tìm ngược lại trong Undo Log nhằm tái tạo lại trạng thái dữ liệu cũ phù hợp với Read View đó.

• Ưu điểm:
  • Bảng chính luôn gọn gàng, chỉ chứa phiên bản dữ liệu mới nhất. Không bị hiện tượng phình bảng vật lý (Table Bloat) như Postgres.
  • Các index phụ không cần cập nhật khi có UPDATE (nếu cột index không đổi).
• Nhược điểm:
  • Nếu có một transaction SELECT chạy cực kỳ lâu (ví dụ chạy report cuối ngày), nó sẽ ngăn cản tiến trình dọn dẹp (Purge thread) xóa các Undo Log cũ. Undo log phình to trên đĩa và việc tái tạo lại các phiên bản dòng cũ từ Undo Log sâu sẽ làm chậm đáng kể các truy vấn SELECT.

Cách PostgreSQL triển khai MVCC

PostgreSQL lưu trực tiếp các phiên bản dữ liệu (tuples) khác nhau trong cùng một Heap Table.

• Mỗi tuple trên đĩa chứa các header ẩn: xmin (ID của transaction chèn dòng này) và xmax (ID của transaction xóa/cập nhật dòng này).
• Khi một transaction đọc bắt đầu, nó lấy một danh sách snapshot của các transaction đang chạy. Dựa vào xmin, xmax và trạng thái transaction trong CLOG (Commit Log), Postgres quyết định tuple nào hiển thị cho người dùng.
• Khi cập nhật dữ liệu, Postgres đơn giản là ghi đè xmax của tuple cũ thành ID transaction hiện tại và chèn một tuple mới có xmin bằng ID transaction hiện tại.

• Ưu điểm:
  • Thiết kế kiến trúc rất nhất quán và đơn giản.
  • Quá trình Rollback cực kỳ nhanh. Postgres chỉ cần đánh dấu trạng thái của transaction trong Commit Log (CLOG) là Aborted. Dữ liệu mới ghi vào Heap tự động trở thành vô hình với toàn bộ hệ thống mà không cần dọn dẹp hay đảo ngược dữ liệu.
• Nhược điểm:
  • Table Bloat: Các tuple cũ (dead tuples) vẫn chiếm không gian đĩa vật lý. Hệ thống bắt buộc phải cấu hình và chạy tiến trình VACUUM (thường là Autovacuum) chạy ngầm để thu hồi khoảng trống. Nếu Autovacuum không kịp chạy dưới tải ghi lớn, đĩa sẽ bị đầy và hiệu năng đọc sẽ giảm nghiêm trọng do phải quét qua quá nhiều dead tuples.
  • Write Amplification: Như đã phân tích ở trên, mọi UPDATE đều có nguy cơ cập nhật lại toàn bộ index của bảng.

4. Bộ câu hỏi phỏng vấn thực chiến (Interview Cheat Sheet)

Dưới đây là một số câu hỏi phỏng vấn thực tế đi kèm gợi ý cách trả lời giúp bạn thể hiện chiều sâu kiến thức:

Câu 1: “Điều gì xảy ra nếu có một transaction SELECT kéo dài vài tiếng trong hệ thống có lượng ghi cao?”

• Với MySQL: Undo Log sẽ không được giải phóng do Purge thread không thể xóa dữ liệu cũ mà transaction SELECT kia vẫn có thể cần đọc. Điều này làm tăng dung lượng file tablespace (ibdata1 hoặc các file .ibd nếu dùng file-per-table) và làm chậm các câu lệnh đọc khác do chuỗi liên kết Undo Log quá dài.
• Với PostgreSQL: Autovacuum không thể dọn dẹp bất kỳ dead tuple nào được tạo ra sau khi transaction SELECT đó bắt đầu. Bảng và các index sẽ phình to nhanh chóng (Bloat), gây suy giảm hiệu năng nghiêm trọng cho toàn bộ database.

Câu 2: “Tại sao sử dụng UUIDv4 làm Khóa chính lại là một thảm họa hiệu năng đối với MySQL InnoDB, nhưng lại đỡ nghiêm trọng hơn với PostgreSQL?”

• MySQL (InnoDB): Vì InnoDB sử dụng Clustered Index, dữ liệu bảng được sắp xếp vật lý theo Khóa chính. UUIDv4 mang tính ngẫu nhiên cao, nên các dòng mới chèn vào sẽ nằm rải rác ngẫu nhiên trên toàn bộ cây B+Tree. Điều này dẫn đến việc phải liên tục nạp các trang dữ liệu cũ lên RAM, gây ra hiện tượng Page Split liên tục, làm tăng I/O đĩa đột biến và phân mảnh bảng nặng nề.
• PostgreSQL: Postgres dùng Heap Table, dữ liệu dòng mới chỉ việc chèn vào Heap Page trống bất kỳ (append-like), không cần sắp xếp lại vật lý. Tuy nhiên, nó vẫn làm phân mảnh các index B-Tree phụ của bảng, nhưng cấu trúc bảng chính không bị ảnh hưởng tiêu cực nặng nề như MySQL.

Câu 3: “Làm thế nào để tối ưu hóa hiệu năng UPDATE trong PostgreSQL nhằm tận dụng cơ chế HOT?”

• Tránh đánh chỉ mục (Index) trên các cột thường xuyên bị cập nhật.
• Cấu hình tham số fillfactor của bảng xuống khoảng 80-90% (mặc định là 100%). Việc này giúp chừa lại khoảng 10-20% khoảng trống trên mỗi trang dữ liệu để khi có UPDATE, Postgres có thể ghi dòng mới ngay trên cùng một trang vật lý với dòng cũ, kích hoạt thành công cơ chế HOT và tránh được Write Amplification trên các index.

-- Tạo bảng với fillfactor là 80% để tối ưu hóa UPDATE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    status VARCHAR(20)
) WITH (fillfactor = 80);

Kết luận

Không có database nào hoàn hảo hơn database nào, chỉ có sự lựa chọn phù hợp nhất với bài toán thực tế của doanh nghiệp:

• Chọn MySQL nếu hệ thống thiên về đọc ghi đơn giản theo Key/Value, ưu tiên đọc theo Khóa chính cực nhanh, tài nguyên phần cứng giới hạn và muốn tránh rủi ro phình bảng vật lý.
• Chọn PostgreSQL nếu hệ thống cần xử lý các truy vấn phân tích phức tạp, báo cáo, cần kiểu dữ liệu phong phú (JSONB, GIS/PostGIS), hoặc hệ thống có lượng ghi chèn mới (INSERT) quy mô lớn.