MySQL vs PostgreSQL from an interview perspective: Read/Write-Heavy and MVCC mechanism

MySQL vs PostgreSQL is a classic topic in backend engineer interviews. The interviewer asks this question not to hear you list superficial features like “Postgres supports JSON better” or “MySQL is more popular”. They want to assess your in-depth understanding of storage architecture, transaction control mechanisms, and how the system operates under various high-load conditions.

This article focuses on key technical points to help you pass the interviewer’s in-depth questions.

1. Nature of Storage Architecture: Clustered Index vs Heap Table

To answer the question “which one to choose for Read/Write-heavy”, we first need to understand how these two databases organize physical data on the hard drive. This difference directly affects read and write performance.

MySQL (InnoDB Engine) - Clustered Index

MySQL uses InnoDB as the default engine. InnoDB organizes tables according to the Index-Organized Table (IOT) architecture, specifically Clustered Index.

graph TD
    PK_Tree[B+Tree Primary Key] -->|Leaf node contains| RowData[Actual row data]
    Sec_Index[Secondary Index] -->|Leaf node contains| PK_Val[Primary Key Value]
    PK_Val -.->|Key Lookup| PK_Tree

• Actual row data is stored directly at the leaf nodes of the Primary Key B+Tree.
• When searching by Primary Key, InnoDB only needs to traverse the B+Tree once to retrieve all row data (Single Hop).
• For secondary indexes, the leaf nodes do not point directly to the row’s disk address, but store the Primary Key value. Therefore, a query using a secondary index will have to go through the Key Lookup process (two tree traversals): find the Primary Key from the secondary index, then use the Primary Key to traverse the Clustered Index tree to retrieve row data.

PostgreSQL - Heap Table

PostgreSQL organizes data according to the Heap Table architecture.

graph TD
    HeapPages[Heap Pages] -->|Physical storage| RowDataPG[Tuples containing row data]
    Index_Tree[B-Tree Index] -->|Leaf node contains| TID[TID - Page & Offset Pointer]
    TID -.->|Direct Hop| HeapPages

• Table data is written to a physical file (divided into Heap Pages) in append-only mode or written to remaining gaps. The physical location of a row is identified by a pointer called TID (Tuple ID), which includes the page number and offset within the page.
• All indexes in PostgreSQL (including Primary Key and secondary indexes) are Secondary Indexes. The leaf nodes of all indexes contain TID pointers that point directly to the physical location of the row in the Heap Page.
• Therefore, whether searching by Primary Key or secondary index, Postgres only needs to perform one index tree traversal and one hop to the Heap Page to retrieve data.

2. Read-Heavy vs Write-Heavy: Choosing MySQL or PostgreSQL?

Read-Heavy Case (Frequent Reads)

If most read queries are Point Queries (reading by ID/Primary Key):

• MySQL has a significant advantage thanks to its Clustered Index architecture. Only one traversal of the B+Tree is needed to retrieve data, without additional I/O to jump to the Heap Page like Postgres. The InnoDB cache (Buffer Pool) is also highly optimized for storing Clustered Index nodes in RAM.
• Postgres will incur an additional step to read the Heap Page from disk or shared buffers after indexing.

If the system has complex read queries (Complex Query, Aggregate, Joins across multiple tables):

• PostgreSQL is a much better choice. Postgres’s query optimizer (Query Planner) is much more intelligent than MySQL’s. Postgres supports many complex Join algorithms (Hash Join, Merge Join), while MySQL mainly relies on Nested Loop Join (only supporting Hash Join from version 8.0 with many limitations). Postgres also supports parallel queries (running queries on multiple CPU cores) very powerfully.

Write-Heavy Case (Frequent Writes)

INSERT (Insertion) Problem:

• PostgreSQL writes faster in cases where the table has many indexes. New data only needs to be appended to the end of the Heap file or an empty page (a simple append operation). Although indexes still need to be updated, the Heap structure allows for extremely fast insertion of new records.
• MySQL must insert new rows into the correct logical position of the Clustered Index based on the primary key value. If the primary key is not auto-incrementing (e.g., random UUIDv4), insertion will cause Page Split (page fragmentation), significantly slowing down write performance and increasing disk I/O.

UPDATE (Update - The Crucial Point):

• MySQL performs updates in-place. InnoDB writes old data to the Undo Log (for transaction rollback and concurrent reading), then overwrites the new data directly onto the page containing the current row. If the updated columns are not part of any index, MySQL does not need to update other secondary indexes.
• PostgreSQL uses an Append-only mechanism. Essentially, a PostgreSQL UPDATE command is a DELETE (marking the old row as dead) and an INSERT (writing a new row - a new tuple with new values). This leads to two major issues:
  1. Write Amplification: Because the new row is located at a different physical position on disk (new TID), all indexes on that table must be updated to point to the new TID, including indexes of columns that were not changed.
  2. Table Bloat: Old data (dead tuples) remains on disk and occupies space until the VACUUM process runs to clean up.

To mitigate Postgres’s UPDATE disadvantage, they introduced the HOT (Heap-Only Tuples) feature. If the new row is written on the same page as the old row and the changed column is not part of any index, Postgres will create an internal pointer within the page to link from the old row to the new row without updating external indexes. However, if the page is full or the updated column is part of an index, write amplification still occurs.

3. The Nature of MVCC (Multi-Version Concurrency Control)

MVCC is a mechanism that allows read transactions (SELECT) to not be blocked by write transactions (INSERT/UPDATE/DELETE) and vice versa (“readers don’t block writers, writers don’t block readers”). However, the implementation of MVCC in MySQL and Postgres is completely different.

How MySQL (InnoDB) Implements MVCC

InnoDB uses Undo Log in combination with Read View to implement MVCC.

• Each row in the InnoDB table has hidden system fields: DB_TRX_ID (ID of the transaction that last modified the row) and DB_ROLL_PTR (rollback pointer pointing to the Undo Log record containing the old data before modification).
• When a read transaction starts at the REPEATABLE READ or READ COMMITTED isolation level, it creates a Read View containing a list of active transactions at that time.
• If the current data row has a DB_TRX_ID greater than or is in the active list of the Read View (i.e., the data has been modified by a transaction that has not committed or committed after the read), InnoDB uses DB_ROLL_PTR to find the old data in the Undo Log to recreate the old data state compatible with that Read View.

• Advantages:
  • The main table always remains compact, containing only the latest version of the data. No physical table bloat occurs, unlike Postgres.
  • Secondary indexes do not need to be updated when there is an UPDATE (if the indexed column does not change).
• Disadvantages:
  • If there is a SELECT transaction running extremely long (e.g., running an end-of-day report), it will prevent the purge thread from deleting old Undo Logs. The Undo log will grow on disk, and recreating old row versions from the deep Undo Log will significantly slow down SELECT queries.

How PostgreSQL Implements MVCC

PostgreSQL stores different data versions (tuples) directly in the same Heap Table.

• Each tuple on disk contains hidden headers: xmin (ID of the transaction that inserted the row) and xmax (ID of the transaction that deleted/updated the row).
• When a read transaction starts, it takes a snapshot list of running transactions. Based on xmin, xmax, and the transaction state in the CLOG (Commit Log), Postgres decides which tuple to display to the user.
• When updating data, Postgres simply overwrites the xmax of the old tuple with the current transaction ID and inserts a new tuple with xmin equal to the current transaction ID.

• Advantages:
  • The architecture design is very consistent and simple.
  • The rollback process is extremely fast. Postgres only needs to mark the transaction state in the Commit Log (CLOG) as Aborted. The newly written data in the Heap automatically becomes invisible to the entire system without needing cleanup or data reversal.
• Disadvantages:
  • Table Bloat: Old tuples (dead tuples) still occupy physical disk space. The system must be configured to run the VACUUM process (usually Autovacuum) in the background to reclaim space. If Autovacuum cannot keep up with the high write load, the disk will become full, and read performance will degrade severely due to having to scan through many dead tuples.
  • Write Amplification: As analyzed above, every UPDATE has the potential to update the entire index of the table.

4. Interview Cheat Sheet

Below are some real interview questions along with suggested answers to help you demonstrate your in-depth knowledge:

Question 1: “What happens if there is a long-running SELECT transaction for several hours in a system with a high write volume?”

• With MySQL: The Undo Log will not be released because the Purge thread cannot delete old data while the SELECT transaction may still need to read it. This increases the size of the tablespace file (ibdata1 or .ibd files if using file-per-table) and slows down other read operations due to the long Undo Log chain.
• With PostgreSQL: Autovacuum cannot clean up any dead tuples created after the SELECT transaction started. The table and indexes will bloat quickly, causing severe performance degradation for the entire database.

Question 2: “Why is using UUIDv4 as a primary key a performance disaster for MySQL InnoDB, but less severe for PostgreSQL?”

• MySQL (InnoDB): Because InnoDB uses a Clustered Index, table data is physically sorted by the primary key. UUIDv4 is highly random, so new rows inserted will be scattered randomly throughout the B+Tree. This leads to frequent loading of old data pages into RAM, causing Page Split continuously, resulting in a surge in disk I/O and severe table fragmentation.
• PostgreSQL: Postgres uses a Heap Table, where new rows are simply appended to any empty Heap Page, without the need for physical sorting. However, it still causes fragmentation of the table’s secondary B-Tree indexes, but the primary table structure is not heavily affected like in MySQL.

Question 3: “How can you optimize UPDATE performance in PostgreSQL to utilize the HOT mechanism?”

• Avoid indexing columns that are frequently updated.
• Configure the fillfactor parameter of the table to around 80-90% (default is 100%). This leaves about 10-20% of free space on each data page, allowing Postgres to write new rows on the same physical page as the old row when updated, successfully triggering the HOT mechanism and avoiding Write Amplification on indexes.

-- Create a table with a fillfactor of 80% to optimize UPDATE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    status VARCHAR(20)
) WITH (fillfactor = 80);

Conclusion

There is no perfect database, only the most suitable choice for the actual problem of the business:

• Choose MySQL if the system is inclined towards simple read and write operations based on Key/Value, prioritizes extremely fast primary key reads, has limited hardware resources, and wants to avoid the risk of physical table bloating.
• Choose PostgreSQL if the system needs to handle complex analytical queries, reports, requires a rich set of data types (JSONB, GIS/PostGIS), or the system has a large scale of new insertions (INSERT).