Skip to content
PostgreSQL vs MySQL: Operational Internals, Benchmarks & Selection Guide
Databases 62 min read

PostgreSQL vs MySQL: Operational Internals, Benchmarks & Selection Guide

This article compares PostgreSQL and MySQL across MVCC implementation, replication models, connection handling, version lifecycles, hardware costs, and benchmark results, providing production configurations, migration pitfalls, troubleshooting cases, and a decision framework for database selection.

MaGe Linux Operations
MaGe Linux Operations
MaGe Linux Operations

Part 1: MVCC and WAL Physical Implementation Differences

1.1 Same "Reads Don't Block Writes" Promise, Two Different Engines

Both databases claim MVCC supports read-write concurrency, but physical implementations differ fundamentally, directly determining the three most painful operational issues: space bloat, replication lag, long-transaction blast radius .

PostgreSQL: old versions stay in heap tuple chain
 heap page: [tuple_v1 | tuple_v2 | tuple_v3(dead)]
                    ^head                   ^old version still occupies space
 Reclaimer: autovacuum -> mark reusable -> only available on same page
 Read: follow xmin/xmax snapshot along chain to find visible version

MySQL InnoDB: old versions moved to undo tablespace
 clustered index: [PK -> latest row]
 undo log:        [old image] <- cleaned by purge thread
 Read: construct old version from undo via read view (rollback pointer)

Key difference in one sentence:

PG's MVCC is "in-place multi-version" - versions live in table files, must be reclaimed by VACUUM; delayed reclamation = table bloat.

MySQL's MVCC is "index + undo" - table files hold only latest version, old versions in undo, cleaned by purge thread; delayed reclamation = undo tablespace bloat + history list length growth.

1.2 WAL vs Binlog: One is "Redo", One is "Archive Stream"

PostgreSQL Physical Streaming Replication
 PG -> WAL segment (pg_wal/000000010000000000000001)
      -> walsender reads WAL -> network -> walreceiver
      -> standby writes to disk -> replay (physical page-level replay)
 Replication unit: data page + page header, requires primary/standby binary consistency

MySQL Binlog Replication
 InnoDB -> redo log (physical, for crash recovery)
          -> binlog (logical, row/statement, for replication)
          -> dump thread reads binlog -> IO thread -> relay log
          -> SQL/applier thread applies to replica
 Replication unit: row events (ROW format), primary/replica can differ in minor versions

This explains two operational phenomena:

PG primary/standby must be same major version, same architecture ; cross-version requires pg_upgrade or logical replication transition. MySQL can replicate from 8.0 to 8.4 as long as binlog_format=ROW and no removed features.

PG's WAL is "page-level physical log" ; page repair pitfalls ( full_page_writes) directly tie to WAL volume. MySQL's redo log only used for local crash recovery, doesn't affect replication bandwidth.

1.3 Connection Model: Process vs Thread Determines Capacity Planning

PostgreSQL: one connection = one process
 postmaster
  |- backend (pid 1001)  RSS 8-15MB base
  |- backend (pid 1002)
  |- backend (pid 1003)
 Memory allocated per connection via work_mem, peak uncontrollable

MySQL: one connection = one thread (or thread pool in 8.0+)
 mysqld
  |- thread (tid ...)  stack 256KB, shared buffer pool
 Memory concentrated in innodb_buffer_pool_size, precisely budgetable

Operational consequences are real: PG at 2000 connections suffers process-switching overhead and per-connection private memory (sort/hash) exhausting RAM; MySQL at 2000 connections mainly risks thread_cache and lock contention. Therefore PG must use PgBouncer, MySQL must use thread pool or ProxySQL - these are the first things to break after containerization.

1.4 Architecture Difference Diagram

MySQL (single-node strong -> sharding scale-out)
 app -> ProxySQL -> [mysql-a:3306] --ROW binlog--> [replica]
                         |                              (async/semi-sync)
                         |- InnoDB buffer pool (centralized budget)
                         |- redo log (circular, can "catch up")
                         +- binlog (continuous growth, needs expiry cleanup)

    PostgreSQL (single-node strong -> read horizontal scale)
 app -> PgBouncer -> [pg-primary:5432] --WAL--> [standby1]
                         |                              (physical streaming)
                         |- shared_buffers (double cache: OS + PG)
                         |- WAL (continuous growth, cleaned by archive)
                         +- table bloat (managed by autovacuum)

1.5 Version Landscape: Three Lines to Clarify Before 2026

Align versions before writing - this is prerequisite for selection discussion.

MySQL 8.0 EOL April 2026 (8.0.46) - staying on 8.0 means no security patches, only Sustaining Support.

MySQL 8.4 LTS is current safe path - 8.4 removed mysql_native_password among other legacy features; SHOW SLAVE STATUS syntax deprecated, use SHOW REPLICA STATUS.

MySQL 9.7 LTS (GA April 2026) - mysql_native_password plugin completely removed; old clients without CLIENT_PLUGIN_AUTH cannot connect.

PostgreSQL 18 (GA September 2025) - biggest change is new async I/O subsystem ( io_method = worker | io_uring | sync), sequential scan and vacuum read performance significantly improved.

Version differences directly change benchmark conclusions, so all numbers in this article are version-annotated.

Version          Status          Operational Must-Know
MySQL 8.0.46     EOL (2026-04)   No more patches, migrate to 8.4 or 9.7 ASAP
MySQL 8.4 LTS    Stable support  SHOW SLAVE series syntax deprecated
MySQL 9.7 LTS    Latest LTS      mysql_native_password completely removed
PostgreSQL 16    Stable support  Logical replication supports DDL in some scenarios
PostgreSQL 18    Latest stable   New io_method, must benchmark before enabling

Part 2: Multi-Dimensional Selection Decision Matrix

2.1 Architecture Feature Table (Only Points That Change Operations)

Dimension          PostgreSQL              MySQL/InnoDB          Operational Impact
MVCC Implementation heap multi-version + VACUUM  undo + purge         PG manages bloat, MySQL manages long transactions
Replication Model  WAL physical streaming    binlog logical         PG primary/standby version strongly bound
Default Isolation  Read Committed            Repeatable Read        Business deadlock patterns differ
DDL Behavior       Transactional DDL         Online DDL (INPLACE)   PG DDL rollbackable, MySQL needs gh-ost

Additional points for architecture review:

DDL is PG's hidden advantage : PG's ALTER TABLE runs in transaction, failure rolls back; MySQL's DDL supports INPLACE but failure leaves intermediate state, large table changes usually require gh-ost or pt-osc.

Default isolation level is misunderstanding hotspot : MySQL default RR with gap locks causes deadlocks completely different from PG's RC. Migrating without changing isolation level yields "runs fine on PG, deadlocks on MySQL".

Index structures : PG has B-tree/Hash/GIN/GiST/BRIN; BRIN nearly free for time-series. MySQL primary key is clustered index, secondary indexes require lookup, covering index design differs entirely.

2.2 Hardware & Cost Bill (Baseline 8C32G / 1TB NVMe Single Node)

Item                    PostgreSQL 18          MySQL 9.7            Notes
Memory Allocation       shared_buffers 25%     buffer_pool 60-70%   PG double cache, OS also needs room
Connection Layer        PgBouncer mandatory    ProxySQL optional    Add when connections > 500
Space Bloat Management  autovacuum + monitoring basically no table bloat PG needs extra 20% space headroom
HA Solution             Patroni + etcd         MHA/Orchestrator     Neither native
Backup Tool             pgBackRest             XtraBackup           Both production-grade incremental
Cloud RDS Unit Price    5-15% higher           baseline             Same spec measured

Often overlooked cost: PG's bloat headroom . Same 1TB data, PG needs 1.2TB disk (high-update tables even 1.5TB); MySQL undo independently cleanable, innodb_undo_tablespaces separately expandable, disk planning more compact.

2.3 One-Veto Scenario Mapping

Business Scenario              Recommendation   One-Veto Reason
Ultra-high point lookup (KV)   MySQL            PG process model connection cost high
Complex reports + window func  PostgreSQL       MySQL window function performance significantly weaker
Time-series + partition prune  PostgreSQL       BRIN + declarative partitioning cheaper
JSON-heavy data                PostgreSQL       JSONB + GIN index mature
Team only knows MySQL          MySQL            Operational capability is hard constraint
Deep GIS features needed       PostgreSQL       PostGIS ecosystem irreplaceable

Part 3: Recommended Solution (PostgreSQL 18) Production Config & Four-Dimensional Breakdown

3.1 System Layer Baseline: Tune OS First

# /etc/sysctl.d/90-pg.conf
# Apply: sysctl --system (no PG restart)

# --- Shared memory: shared_buffers needs sufficient shmmax ---
kernel.shmmax = 17179869184
kernel.shmall = 4194304

# --- Dirty pages: PG manages checkpoints, OS shouldn't over-accumulate ---
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
vm.swappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

# --- Network: connection-intensive scenarios ---
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 10000 65535
# /etc/security/limits.d/90-postgres.conf
# Apply: re-login postgres user + systemd unit sync
postgres  soft  nofile   65535
postgres  hard  nofile   65535
postgres  soft  nproc    16384
postgres  hard  nproc    16384
postgres  soft  memlock  unlimited
postgres  hard  memlock  unlimited
vm.overcommit_memory = 2

is double-edged: prevents fork failure but requires overcommit_ratio and shmmax calculated precisely, else postmaster won't start.

3.2 Application Layer Core Config: postgresql.conf

# /var/lib/pgsql/18/data/postgresql.conf
# Effective: reload for [R], restart for [RS]

# ---------- Connection [RS] ----------
listen_addresses = '172.16.30.11'
port = 5432
max_connections = 400  # [RS] with PgBouncer
superuser_reserved_connections = 5

# ---------- Memory [RS] ----------
shared_buffers = 8GB          # [RS] 25% physical memory
work_mem = 16MB               # [RS] per sort/hash node limit
maintenance_work_mem = 1GB    # [RS] VACUUM/CREATE INDEX
effective_cache_size = 24GB   # [R] planner's assumed available cache

# ---------- WAL [RS] ----------
wal_level = replica
max_wal_size = 16GB
min_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = zstd        # [RS] reduces WAL bandwidth 30%+
full_page_writes = on         # [RS] absolutely forbidden to change

# ---------- Replication [RS] ----------
max_wal_senders = 10
wal_keep_size = 2GB
hot_standby = on

# ---------- autovacuum [R] ----------
autovacuum = on
autovacuum_max_workers = 5    # [RS]
autovacuum_naptime = 20s
autovacuum_vacuum_scale_factor = 0.02  # default 0.2 too large
autovacuum_analyze_scale_factor = 0.05

# ---------- PG 18 Async IO [RS] ----------
io_method = worker
io_combine_limit = 128kB
effective_io_concurrency = 200

3.3 Four-Dimensional Breakdown

① Must-Change Items

Parameter                          Suggested Value          Consequence If Unchanged
listen_addresses                   explicit internal IP     default localhost, app cannot connect
shared_buffers                     memory 25%               too small -> all reads fall to OS cache, low hit rate
wal_level                          replica                  default replica but often reverted during upgrade
max_connections                    calculate with PgBouncer   too large -> process switching avalanche
autovacuum_vacuum_scale_factor     0.02                     large tables never trigger vacuum

② Coupled Parameters max_connections × work_mem = memory ceiling : 400 × 16MB = 6.4GB at extreme concurrent sort, must budget with shared_buffers. max_wal_size with checkpoint_timeout jointly determine checkpoint frequency, also determine replication lag burst magnitude : max_wal_size too large -> WAL flood before checkpoint saturates standby bandwidth. autovacuum_max_workers with autovacuum_vacuum_cost_limit must tune together: default cost_limit 200 makes workers collectively slower, adding workers without raising limit = no effect.

PG 18 io_combine_limit align with storage IO size: NVMe suggest 128kB, HDD suggest 1MB.

③ Performance Tuning Items (Formulas)

shared_buffers  = physical_memory x 0.25          (cap 16GB, diminishing returns beyond)
effective_cache_size = physical_memory x 0.75     (planner only)
work_mem cap    = (physical_memory x 0.25) / max_connections
maintenance_work_mem = min(physical_memory x 0.05, 2GB)

Baseline: 64GB RAM, 400 connections
shared_buffers         = 16GB
work_mem cap           = 16GB / 400 = 40MB -> take 16MB conservative
maintenance_work_mem   = 3.2GB -> take 1GB (avoid multi-worker OOM)

autovacuum_max_workers formula:
workers = max(3, round(table_count / 500))
cost_limit suggested = 2000 ~ 4000 (default 200 too small)

④ Absolute Red Lines (Never Change)

Parameter                     Consequence If Changed Wrong
full_page_writes = off        Torn page after power loss, data corruption
fsync = off                   Crash loses transactions, WAL untrustworthy
wal_level = minimal           Replication breaks, archive breaks
Synchronous_commit = off      Crash loses last few seconds transactions
Directly modify pg_control    Cluster fails to start

3.4 Effect Verification

# After reload, verify each takes effect
psql -h 172.16.30.11 -U postgres -Atc 
"SELECT name || ' = ' || setting || coalesce(' ('||unit||')','')
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','io_method',
               'autovacuum_vacuum_scale_factor');"
shared_buffers = 1048576 (8MB)  <-- [KEY] unit is 8kB blocks!
work_mem = 16384 (kB)
io_method = worker
autovacuum_vacuum_scale_factor = 0.02
pg_settings.setting

returns block count not bytes , 1048576 x 8kB = 8GB - most common misjudgment of "config not applied".

# Verify async IO actually enabled (PG 18)
psql -h 172.16.30.11 -U postgres -c "SHOW io_method;"
grep -i 'aios\|io_uring' /var/log/pgsql/18/postgresql-Mon.log | tail -5
io_method
-----------
 worker
(1 row)

LOG:  registered async IO method "worker"
LOG:  async IO workers started, count: 3

If log lacks async IO workers started, io_method set but io_workers = 0, need to also set io_workers = min(3, CPU_cores/4).

Part 4: High-Frequency Commands & Monitoring: Two Systems' Checklist

4.1 Status & Connections: First Glance

# PG: connections grouped by state (more info than MySQL Threads_*)
psql -Atc "
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY 2 DESC;"
active            12
idle              284   <-- [interpretation] 284 idle connections means need pool
idle in transaction 3   <-- [critical anomaly] long transaction, VACUUM blocked
# MySQL: connections & threads
mysql -h 172.16.30.21 -e "SHOW GLOBAL STATUS WHERE
Variable_name IN ('Threads_connected','Threads_running',
'Threads_created','Aborted_connects');"
Threads_connected  512  <-- [interpretation] approaching max_conn alert
Threads_running    18
Threads_created    1830 <-- [critical] continuous growth means thread_cache small
Aborted_connects   7    <-- [critical] non-zero check auth failure/handshake timeout

4.2 Replication Status: Syntax Changed

# MySQL 8.4 / 9.x: SHOW SLAVE STATUS deprecated, must use REPLICA
mysql -h 172.16.30.21 -e "SHOW REPLICA STATUS\G" | \
 grep -E 'Replica_IO_Running|Replica_SQL_Running' | \
 grep -E 'Seconds_Behind|Last_.*Error'
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Last_IO_Error:
# PG: physical streaming three-layer view
psql -h 172.16.30.11 -Atc "
SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;"
172.16.30.12 streaming 3/9A000000 3/99F80000  524288  <-- normal
172.16.30.13 streaming 3/9A000000 3/8C000000 14336000 <-- 14MB

PG lag expressed in bytes ( lag_bytes), MySQL in seconds ( Seconds_Behind_Source). Cross-continent replication PG byte count naturally larger due to RTT, doesn't mean real lag.

4.3 Table Bloat vs Undo Bloat: Biggest Watershed

# PG: need pgstattuple extension for precise bloat
psql -Atc "CREATE EXTENSION IF NOT EXISTS pgstattuple;"
psql -Atc "
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total,
       round(100.0 * (pgstattuple(oid)).dead_tuple_len
              / nullif((pgstattuple(oid)).table_len,0),1)
       AS dead_pct
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC LIMIT 5;"
orders           1842 MB  12.4  <-- [interpretation] <20% acceptable
order_items      9221 MB  38.7  <-- [critical] needs VACUUM or rebuild
audit_log        412 MB   61.2  <-- [critical] autovacuum can't catch up

When dead_pct > 30%, VACUUM only recycles for same table reuse, cannot shrink disk; then need VACUUM FULL or pg_repack (online, no downtime).

# MySQL: check history list length & undo tablespace
mysql -h 172.16.30.21 -e "SHOW ENGINE INNODB STATUS\G" | \
 grep -A3 'TRANSACTIONS' | head -8
------------
TRANSACTIONS
------------
Trx id counter 981234567
Purge done for trx's n:o < 981234120 undo n:o < 0 state: running
History list length 48213  <-- [critical] >10k means purge falling behind
History list length

is MySQL's "bloat metric". >10k check long transactions; >1M undo tablespace grows continuously, read consistency cost rises (traverse longer version chain).

4.4 Slow Queries: Three Syntaxes, Don't Mix

# PG: pg_stat_statements (must install)
psql -Atc "
SELECT round(total_exec_time::numeric,1) AS ms,
       calls,
       round(mean_exec_time::numeric,2) AS avg_ms,
       left(query, 60)
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;"
182344.2  1204  151.45  SELECT o.*, i.* FROM orders o JOIN...
98120.1   8821  11.12   UPDATE order_items SET status = $1 ...
# MySQL: performance_schema statements_summary
mysql -h 172.16.30.21 -e "
SELECT ROUND(SUM_TIMER_WAIT/1e12,2) AS sec, COUNT_STAR,
       ROUND(AVG_TIMER_WAIT/1e9,2) AS avg_ms,
       LEFT(DIGEST_TEXT,50)
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;"
182.34  1204  151.45  SELECT `o` . * , `i` . * FROM `orders` ...

MySQL's TIMER_WAIT unit is picoseconds , divide 1e9 for ms, 1e12 for seconds - most common dimensional error when writing monitoring dashboards.

4.5 PromQL: Two Exporters' Differences

# PostgreSQL: postgres_exporter
# 1) Connection usage (>80% alert)
pg_stat_database_numbackends{datname="appdb"}
  / on() pg_settings_max_connections > 0.8

# 2) Long transaction (>5 min, blocks vacuum)
pg_stat_activity_max_tx_duration{
  state="idle in transaction"} > 300

# 3) Replication lag (bytes)
pg_stat_replication_pg_wal_lsn_diff > 104857600

# 4) Table bloat rate (needs pg_stat_user_tables collection)
pg_table_dead_tuples / pg_table_live_tuples > 0.3
# MySQL: mysqld_exporter
# 1) Connection usage
mysql_global_status_threads_connected
  / mysql_global_variables_max_connections > 0.8

# 2) Slow query growth rate (>10/s indicates problem)
rate(mysql_global_status_slow_queries[5m]) > 10

# 3) Replication lag seconds
mysql_slave_status_seconds_behind_master > 30

# 4) InnoDB buffer pool hit rate (<99% scale up)
1 - rate(mysql_global_status_innodb_buffer_pool_reads[5m])
    / rate(
      mysql_global_status_innodb_buffer_pool_read_requests[5m])

Note mysql_slave_status_seconds_behind_master retained in 8.0.22+ but new metric name changed to mysql_perf_schema_replication_applier_status series; 8.4+ exporters recommend mysql_replica_status_*.

4.6 Backup: Two Completely Different Paths

# PG: pgBackRest full + incremental, supports parallel & verification
pgbackrest --stanza=appdb --type=full backup
pgbackrest --stanza=appdb info
stanza: appdb
  status: ok
  cipher: aes-256-cbc
  db (current)
    wal archive min/max (18-1): 0000000100000000/0000002A
    full backup: 20261115-020000F
      timestamp start/stop: 2026-11-15 02:00:00 / 05:31:12
      database size: 842.1GB, backup size: 214.3GB
      repo size: 214.3GB          <-- [interpretation] compression ~3.9:1
# MySQL: XtraBackup full + incremental + binlog position
xtrabackup --backup --target-dir=/backup/full \
  --user=backup --password="$(cat /etc/mysql/backup.pwd)"
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full
[INFO] Streaming binlog thread started
[INFO] MySQL binlog position: filename 'mysql-bin.000842',
         position '194823'
Completed OK!              <-- [interpretation] must prepare after

Part 5: Real-World Benchmarks on Same Hardware: Four Load Types

Test environment: same 8C32G / 1TB NVMe / Rocky Linux 9.4, PG 18.0 vs MySQL 9.7.0, pgbench and sysbench each 3 rounds median, client on localhost loopback to avoid network noise.

5.1 Read-Only Point Lookup (PK Query, 128 Concurrency)

# PG: pgbench select-only script
pgbench -h 172.16.30.11 -U app -c 128 -j 8 -T 120 \
  -S --progress=30 appdb
progress: 30.0 s, 24812.4 tps, lat 5.02 ms stddev 1.81
progress: 60.0 s, 25301.7 tps, lat 4.95 ms stddev 1.66
progress: 120.0 s, 25412.9 tps, lat 4.91 ms stddev 1.72
# MySQL: sysbench point select
sysbench oltp_point_select --mysql-host=172.16.30.21 \
  --mysql-db=appdb --tables=10 --table-size=2000000 \
  --threads=128 --time=120 run
SQL statistics:
  queries performed:  3,081,240
  transactions:       3,081,240  (25,677 per sec.)
Latency (ms):
  95th percentile:    12.41
Metric           PostgreSQL 18   MySQL 9.7
Throughput (TPS) 25412           25677
P95 Latency      5.1 ms          12.4 ms
CPU Peak         78%             82%

Conclusion: point lookup throughput roughly equal, but MySQL P95 tail latency more affected by thread scheduling and row locks. Pure point lookup no decisive difference , selection shouldn't rely on this.

5.2 Read-Only Complex Query (Multi-table JOIN + Window Functions)

psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT ... " > /dev/null
Planning Time: 3.118 ms
Execution Time: 842.117 ms
 Buffers: shared hit=48213 read=1204
  -> Hash Join (rows=182344)

Same SQL on MySQL 9.7:

1 row in set (4.32 sec)   <-- [critical] 5x gap
Metric                PostgreSQL 18   MySQL 9.7
Complex JOIN exec time 842 ms          4320 ms
Window function scene  310 ms          1980 ms

Conclusion: Complex analytical queries are PG's decisive advantage , due to hash join, parallel query, more mature cost model.

5.3 Write Throughput & Bloat (High Update Load)

# Same update load both DBs, run 30 min, observe bloat
steamer() {
  pgbench -c 64 -j 8 -T 1800 -N appdb
}
# PG side, after 30 min
orders           1842 MB -> 2914 MB   dead_pct 18.2%
audit_log        412 MB -> 1180 MB    dead_pct 47.9%

# MySQL side, after 30 min
orders           1842 MB -> 1961 MB    hll 48213
Metric           PostgreSQL 18   MySQL 9.7
Write TPS        8420            10120
30-min table growth +58% (orders)  +6% (orders)
Reclamation lag  autovacuum async  purge async

Conclusion: MySQL better at high-concurrency simple writes , PG advantage is stricter transaction semantics on write path (transactional DDL, sequence safety), cost is update-heavy tables need autovacuum to manage bloat.

5.4 Crash Recovery Time (kill -9 Primary Then Restart)

Scenario                     PostgreSQL 18   MySQL 9.7
Crash restart to accepting   18.4 s          11.2 s
Recovery replay log volume   1.2 GB WAL      640 MB redo
Failover time (external)     12-25 s (Patroni) 8-20 s (Orchestrator)

PG crash recovery longer because must replay WAL to consistency point and execute fsync verification; MySQL only redo forward + undo rollback. Trade-off: "data safety first" vs "recovery speed first".

5.5 Four Load Types Selection Conclusion Table

Load Type              Better   Gap Magnitude
PK Point Lookup        Tie      within 5%
Complex Analytical     PG       3-5x
High-Concurrency Write MySQL    ~20%
Crash Recovery Speed   MySQL    40% faster

Part 6: Alternative Config Differences & Smooth Migration Pitfalls

6.1 MySQL 9.7 Core Config (vs PG Correspondence)

# /etc/my.cnf
# Effective: [RS] restart, [D] dynamic

[mysqld]
server_id = 21                    # [RS] replication must be unique
port = 3306
datadir = /data/mysql
socket = /data/mysql/mysql.sock

# ---------- Memory [RS] ----------
innodb_buffer_pool_size = 20G     # [RS] 60-70% physical
innodb_buffer_pool_instances = 8  # [RS] each instance >=1G
innodb_log_buffer_size = 256M     # [RS]
tmp_table_size = 64M              # [D]
max_heap_table_size = 64M         # [D]

# ---------- Redo Log [RS] ----------
innodb_redo_log_capacity = 8G     # [RS] 8.0.30+ unified param
innodb_flush_log_at_trx_commit = 1 # [RS] absolutely forbidden
innodb_flush_method = O_DIRECT    # [RS] avoid double cache

# ---------- Concurrency [D] ----------
innodb_thread_concurrency = 0     # [D] 0 = auto
innodb_io_capacity = 4000         # [D] NVMe suggest 4000+
innodb_io_capacity_max = 8000     # [D]
innodb_flush_neighbors = 0        # [D] SSD must be 0

# ---------- Connection [D] ----------
max_connections = 2000            # [D]
thread_cache_size = 128           # [D]
wait_timeout = 28800              # [D]
interactive_timeout = 28800       # [D]

# ---------- Replication / Binlog [RS] ----------
binlog_format = ROW               # [RS] production must ROW
binlog_row_image = MINIMAL        # [D] reduce binlog volume
sync_binlog = 1                   # [RS] absolutely forbidden
gtid_mode = ON                    # [RS]
enforce_gtid_consistency = ON     # [RS]
binlog_expire_logs_seconds = 604800 # [D] 7 days

6.2 Four-Dimensional Breakdown (MySQL Side)

① Must-Change Items

Parameter                          Value              Consequence If Unchanged
server_id                          unique per instance primary/replica conflict, replication dead loop
binlog_format                      ROW                statement format causes primary/replica data inconsistency
sync_binlog                        1                  crash loses binlog, primary/replica inconsistency
innodb_flush_log_at_trx_commit     1                  crash loses transactions
innodb_flush_method                O_DIRECT           double cache wastes memory

② Coupled Parameters innodb_buffer_pool_size with innodb_buffer_pool_instances: too many instances fragments memory, suggest size / instances >= 1GB. innodb_io_capacity with innodb_flush_neighbors: SSD neighbor flush must off, else single page flush amplifies IO 4x. binlog_row_image = MINIMAL with downstream CDC tools: if subscriber needs full row image, MINIMAL causes missing fields, must choose between FULL and MINIMAL. thread_cache_size with max_connections: rule of thumb thread_cache_size ~= max_connections / 8, too small -> Threads_created keeps growing.

③ Performance Tuning (Formulas)

innodb_buffer_pool_size = physical_memory x 0.65      (dedicated)
                        = physical_memory x 0.50      (co-located)
innodb_redo_log_capacity = peak_write_MBps x 3600
                          baseline 800MB/s -> 2.8G, take 4-8G

innodb_io_capacity = single_disk_IOPS x 0.5
  NVMe enterprise 8k IOPS -> 4000
  innodb_io_capacity_max = io_capacity x 2

Note innodb_redo_log_capacity after 8.0.30 replaces innodb_log_file_size x innodb_log_files_in_group; 9.x old params still settable but overridden, don't follow old articles.

④ Absolute Red Lines

Parameter                     Consequence If Changed Wrong
innodb_page_size              Cannot change after init, change -> won't start
innodb_log_file_size (old)    Conflicts with redo_log_capacity
lower_case_table_names        Cannot change after init, change -> tables not found
character_set_server          Change after init -> existing data garbled
sql_mode remove STRICT_*      Silent data truncation

6.3 Five Real Pitfalls Migrating MySQL -> PostgreSQL

Pitfall 1: AUTO_INCREMENT vs Sequence Out of Sync

-- After migration insert immediately PK conflict
INSERT INTO orders (user_id, amount) VALUES (1001, 99.00);
-- ERROR: duplicate key value violates unique constraint
--          "orders_pkey"  DETAIL: Key (id)=(1001) already exists.
-- Fix: align auto-increment max to sequence
SELECT setval(pg_get_serial_sequence('orders','id'),
              (SELECT max(id) FROM orders), true);

Pitfall 2: MySQL int unsigned Doesn't Exist in PG

-- PG no unsigned, map to domain or bigint
CREATE DOMAIN uint4 AS bigint
CHECK (VALUE >= 0 AND VALUE <= 4294967295);
ALTER TABLE orders ALTER COLUMN user_id TYPE uint4;

Pitfall 3: ON DUPLICATE KEY UPDATE Becomes ON CONFLICT

-- MySQL syntax
INSERT INTO stats (k, v) VALUES ('a', 1)
ON DUPLICATE KEY UPDATE v = v + VALUES(v);

-- PG equivalent (must explicitly write conflict target)
INSERT INTO stats (k, v) VALUES ('a', 1)
ON CONFLICT (k) DO UPDATE SET v = stats.v + EXCLUDED.v;

Pitfall 4: Default Isolation Level Off by One Level

MySQL default RR ( REPEATABLE-READ), PG default RC ( READ COMMITTED). Post-migration two behaviors change:

Phantom reads still possible in PG RC, business logic relying on RR breaks.

PG's SELECT ... FOR UPDATE in RC doesn't hold gap locks, fewer deadlocks; but if business relies on RR "snapshot stability", must explicitly SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.

Pitfall 5: GROUP BY Implicit Sorting Disappears

MySQL 5.7 and earlier GROUP BY had implicit sort, PG never sorts. Post-migration pagination order changes, must explicitly add ORDER BY.

Pitfall          Manifestation              Detection
Sequence misalign  Insert immediate PK conflict  Post-migration load test write
Type mapping       int unsigned overflow       Full table checksum compare
Upsert syntax      Syntax error directly       Code static scan
Isolation level    "Occasional" read inconsistency Business regression cases
Implicit sort      Pagination order wrong      API response diff

6.4 Reverse Pitfalls: PostgreSQL -> MySQL

RETURNING clause : MySQL only supports INSERT ... RETURNING, not general RETURNING; PG heavy usage must rewrite to SELECT LAST_INSERT_ID() or query after insert.

:: type cast : PG's '1'::int syntax error in MySQL, must use CAST('1' AS SIGNED).

ILIKE : PG's ILIKE no direct equivalent, need LOWER(col) LIKE LOWER('%x%') or set collation_connection = utf8mb4_0900_ai_ci then use LIKE.

Partial indexes : MySQL lacks partial indexes, must use generated column + index:

ALTER TABLE t ADD COLUMN is_active TINYINT GENERATED ALWAYS AS (status=1), ADD INDEX idx_a(is_active)

.

CTE performance : PG 12+ CTE can inline, MySQL 8.0 CTE still materialized, large CTE significantly slower.

Part 7: Two Real Migration Troubleshooting Closed Loops

7.1 Case 1: PG Large Table Bloat Fills Disk, autovacuum Blocked by Long Transaction

Symptom : Midnight disk usage 71% -> 94%, alert PG_DISK_USAGE, app UPDATE latency 8ms -> 480ms.

Evidence :

psql -h 172.16.30.11 -U postgres -Atc "
SELECT pid, state, xact_start,
       now() - xact_start AS dur,
       left(query, 45)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY dur DESC LIMIT 3;"
8841  idle in transaction  3:47:12  SELECT * FROM orders WHERE
8842  idle in transaction  3:41:54  BEGIN; UPDATE inventory
psql -Atc "
SELECT relname,
       round(100.0*(pgstattuple(oid)).dead_tuple_len
              /nullif((pgstattuple(oid)).table_len,0),1)
       AS dead_pct
FROM pg_class WHERE relname IN ('orders','inventory');"
orders      64.2   <-- [critical] 64% dead tuples
inventory   58.8
psql -Atc "
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';"
12033  Lock  relation  autovacuum: VACUUM public.orders

Root Cause : Two idle in transaction connections each holding old snapshot >3 hours on orders, preventing oldest_xmin advancement, autovacuum cannot reclaim any tuples, orders bloats to 64%. Bloat increases seq scan cost, app UPDATE slows, positive feedback loop.

Fix Steps (Order Matters) :

# 1) Confirm what these transactions doing, safe to terminate?
psql -Atc "
SELECT pid, usename, application_name, client_addr, backend_start
FROM pg_stat_activity WHERE pid IN (8841,8842);"
8841  app  order-svc  172.16.30.31  2026-11-17 23:41:12
8842  app  inv-svc    172.16.30.32  2026-11-17 23:46:52
# 2) Prefer app side commit; if unreachable gracefully cancel
psql -c "SELECT pg_cancel_backend(8841);"
psql -c "SELECT pg_terminate_backend(8842);"
pg_cancel_backend
-------------------
 t
# 3) Let autovacuum catch up immediately (NO VACUUM FULL, locks table)
psql -c "VACUUM (VERBOSE, ANALYZE) orders;"
INFO:  vacuuming "public.orders"
INFO:  scanned index "orders_pkey" to remove 4821331 row versions
INFO:  "orders": removed 4821331 row versions in 214553 pages
INFO:  index "orders_pkey" now contains 2103442 row versions
# 4) Space reusable but disk not shrunk, verify bloat drop
psql -Atc "SELECT
  pg_size_pretty(pg_total_relation_size('orders')),
  round(100.0*(pgstattuple('orders')).dead_tuple_len
         /nullif((pgstattuple('orders')).table_len,0),1);"
1842 MB   0.2      <-- [normal] dead reclaimed
# 5) Add long-transaction sentinel to prevent recurrence
psql -c "ALTER SYSTEM SET \
  idle_in_transaction_session_timeout = '5min';"
psql -c "SELECT pg_reload_conf();"

Before/After :

Metric              Before    After
orders dead_pct     64.2%     0.2%
UPDATE P99 latency  480 ms    9 ms
disk usage          94%       73%
autovacuum blocked  Lock relation  None

Rollback :

ALTER SYSTEM RESET idle_in_transaction_session_timeout; pg_reload_conf()

. Note if business has ultra-long read-only transactions (reports), this param kills them, must whitelist via connection pool.

7.2 Case 2: MySQL Replica Lag Spikes to 3600s, Root Cause Large Transaction + Single-Thread Replay

Symptom : Replica Seconds_Behind_Source 0 -> 3600, business reads replica sees "order placed but not in list".

Evidence :

mysql -h 172.16.30.22 -e "SHOW REPLICA STATUS\G" | \
 grep -E 'Replica_SQL_Running|Seconds_Behind'
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Read_Source_Log_Pos: 892341122
Exec_Source_Log_Pos: 881200331  <-- [critical] behind 110MB
Seconds_Behind_Source: 3600       <-- hit ceiling
mysql -h 172.16.30.22 -e "SHOW PROCESSLIST;" | grep -i 'system user'
11  system user  Connect  4821  Waiting for dependent transaction
                <-- [critical] stuck on dependent transaction
mysql -h 172.16.30.21 -e "
SELECT trx_id, trx_rows_modified, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, now()) AS dur
FROM information_schema.innodb_trx
ORDER BY trx_rows_modified DESC;"
981234999  18420000  2026-11-17 02:11:44  3241
           ^ [critical] 18.42M rows single transaction
mysql -h 172.16.30.22 -e "
SELECT * FROM performance_schema
.replication_applier_status_by_worker\G" | head -20
WORKER_ID: 1
APPLYING_TRANSACTION: ... trx_id 981234999
LAST_ERROR_MESSAGE:

Root Cause : Primary executed DELETE 18.42M rows in one transaction, binlog ROW format generated massive events; these events on replica must be replayed by same worker serially (same row set, cannot parallelize), other transactions blocked by Waiting for dependent transaction, overall lag accumulates linearly.

Fix :

# 1) Boost replica parallel replay (dynamic, online)
mysql -h 172.16.30.22 -e "
STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 16;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
START REPLICA SQL_THREAD;"
# 2) Watch lag convergence (every 30s)
watch -n 30 "mysql -h 172.16.30.22 -e 'SHOW REPLICA STATUS\G' \
  | grep -E 'Seconds_Behind|Exec_Source'"
Seconds_Behind_Source: 3120
Seconds_Behind_Source: 2410
Seconds_Behind_Source: 1560  <-- [interpretation] converging
Seconds_Behind_Source: 0
# 3) Primary side enable large transaction protection (8.0.31+)
mysql -h 172.16.30.21 -e "
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';
SET GLOBAL binlog_row_image = 'MINIMAL';"
binlog_row_image = MINIMAL

makes UPDATE log only changed columns, in this case single event size dropped ~65%, most direct lever to alleviate large transaction lag.

Before/After :

Metric              Before      After
Replica lag         3600 s      0 s (converged 22 min)
Parallel workers    1 (default 4 unused)  16
Avg event size      ~1.8 KB     ~0.6 KB
Business reads stale Yes         No

Rollback : SET GLOBAL replica_parallel_workers = 4; restart SQL thread. LOGICAL_CLOCK on low-concurrency primary less beneficial, rollback no data risk.

7.3 Common Lessons from Both Cases

Dimension        PG Case                    MySQL Case
Trigger Action   App forgot commit          Primary single tx delete 18.42M rows
First Signal     Disk growth + UPDATE slow  Replica lag accumulation
Misdiagnosis     Add disk                   Rebuild replica
Correct Action   Kill long tx + VACUUM      Raise parallelism + shrink binlog

Commonality : Both databases' "burst performance issues" ultimately trace back to long/large transactions - the shared enemy. This is why during selection, "can we effectively govern long transactions" matters more than "who has 5% higher TPS".

Part 8: One Production Script: Dual-DB Health Scan

Single script: collect key health metrics for both PG and MySQL into structured output for patrol & alert reuse.

#!/usr/bin/env bash
# File: /usr/local/bin/db-health-scan.sh
# Purpose: PG / MySQL dual-stack health patrol, read-only
# Usage: db-health-scan.sh --type pg|mysql --host <ip> [--json]
set -euo pipefail
IFS=$'
\t'

DB_TYPE=""
DB_HOST=""
OUT_JSON=0

while [[ $# -gt 0 ]]; do
case "$1" in
  --type) DB_TYPE="${2:-}"; shift 2 ;;
  --host) DB_HOST="${2:-}"; shift 2 ;;
  --json) OUT_JSON=1; shift ;;
  *) echo "Unknown arg: $1" >&2; exit 2 ;;
esac
done

if [[ -z "${DB_TYPE}" || -z "${DB_HOST}" ]]; then
  echo "Usage: $0 --type pg|mysql --host <ip> [--json]" >&2
  exit 2
fi

# Credentials from controlled file, no hardcode
ENV_FILE="/etc/sysconfig/db-scan.env"
if [[ ! -r "${ENV_FILE}" ]]; then
  echo "Missing cred file: ${ENV_FILE} (should be chmod 600)" >&2
  exit 3
fi
# shellcheck disable=SC1090
source "${ENV_FILE}"

scan_pg() {
  psql -h "${DB_HOST}" -U "${PG_SCAN_USER}" \
    -d postgres -At -F $'\t' -c "
    SELECT 'conn', count(*) FROM pg_stat_activity
    UNION ALL
    SELECT 'long_tx', count(*) FROM pg_stat_activity
      WHERE now() - xact_start > interval '5 min'
    UNION ALL
    SELECT 'repl_lag_bytes',
      coalesce(max(pg_wal_lsn_diff(sent_lsn, replay_lsn)), 0)
      FROM pg_stat_replication
    UNION ALL
    SELECT 'bloat_top',
      max(round(100.0*n_dead_tup/nullif(n_live_tup,0)))
      FROM pg_stat_user_tables;"
}

scan_mysql() {
  mysql -h "${DB_HOST}" -u "${MY_SCAN_USER}" \
    -p"${MY_SCAN_PASS}" -N -B -e "
    SELECT 'conn', count(*) FROM information_schema.processlist
    UNION ALL
    SELECT 'long_tx', count(*) FROM information_schema.innodb_trx
      WHERE TIMESTAMPDIFF(SECOND, trx_started, now()) > 300
    UNION ALL
    SELECT 'hll', VARIABLE_VALUE FROM
      performance_schema.global_status
      WHERE VARIABLE_NAME='Innodb_history_list_length'
    UNION ALL
    SELECT 'slow_1m', count(*) FROM performance_schema
      .events_statements_summary_by_digest
      WHERE SUM_TIMER_WAIT/1e12 > 1;"
}

case "${DB_TYPE}" in
  pg)   RESULT="$(scan_pg)" ;;
  mysql) RESULT="$(scan_mysql)" ;;
  *)    echo "Unknown type: ${DB_TYPE}" >&2; exit 2 ;;
esac

# Thresholds (overridable by env)
TH_CONN="${TH_CONN:-800}"
TH_LONG_TX="${TH_LONG_TX:-1}"
TH_LAG="${TH_LAG:-104857600}"

ALERT=0
while IFS=$'\t' read -r k v; do
  [[ -z "${k}" ]] && continue
  status="ok"
  case "${k}" in
    conn)        [[ "${v}" -gt "${TH_CONN}" ]] && status="warn" ;;
    long_tx)     [[ "${v}" -ge "${TH_LONG_TX}" ]] && status="warn" ;;
    repl_lag_bytes) [[ "${v}" -gt "${TH_LAG}" ]] && status="warn" ;;
    hll)         [[ "${v}" -gt 10000 ]] && status="warn" ;;
  esac
  [[ "${status}" == "warn" ]] && ALERT=1
  printf '%s\t%s\t%s
' "${k}" "${v}" "${status}"
done << "${RESULT}"

if [[ "${OUT_JSON}" -eq 1 ]]; then
  printf '{"type":"%s","host":"%s","alert":%s}
' \
    "${DB_TYPE}" "${DB_HOST}" "${ALERT}"
fi
exit "${ALERT}"
db-health-scan.sh --type pg --host 172.16.30.11
conn            312     ok
long_tx         0       ok
repl_lag_bytes  524288  ok
bloat_top       0       ok
db-health-scan.sh --type mysql --host 172.16.30.22
conn            512             ok
long_tx         2               warn  <-- [critical] has >5 min transaction
hll             48213           warn  <-- [critical] purge falling behind
slow_1m         0               ok

Script constraints: read-only, credentials via chmod 600 env file, thresholds overridable by env vars, exit code = alert state (0 ok / 1 warn), directly callable by cron or K8s Job.

Part 9: Selection One-Veto Red Lines

Red Line 1: No Connection Pool -> Don't Use PostgreSQL. One-connection-one-process model dictates PG connection ceiling far lower than MySQL. 2000 app connections straight to PG isn't performance problem, it's availability problem.

Red Line 2: Need Cross-Version Primary/Standby Replication -> Prefer MySQL. PG physical streaming requires identical versions, rolling upgrade needs logical replication transition, operational complexity significantly higher. MySQL 8.0 -> 8.4 replication path far more mature.

Red Line 3: Update-Intensive & Cannot Govern Long Transactions -> Don't Choose PG. As long as idle in transaction exists long-term, VACUUM fails, table bloat inevitable. If team lacks long-transaction monitoring capability, MySQL's undo mechanism more "resilient".

Red Line 4: Rely on Complex SQL & Strong Consistency Analytics -> Don't Choose MySQL Just Because Team Knows It. MySQL's weak hash join & parallel query is architectural, sharding can't bypass, eventually becomes data warehouse dual-write.

Red Line 5: Version Must Stay in Support Window. MySQL 8.0 EOL (April 2026), continuing uses unpatchable risk. PG must stay on 16/17/18, pg_upgrade path pre-validated.

Red Line 6: Backup Unverified -> Any Selection Is Empty Talk. Both do physical backup (pgBackRest / XtraBackup), but restore drill must run quarterly. Most skipped in selection comparison, most accident-prone: "can we recover within 2 hours".

Part 10: Summary: Ground Selection in Daily Ops Actions

The real difference isn't benchmark numbers, but what you do every day :

Daily Action      PostgreSQL                    MySQL
Daily watch       Dead tuple ratio, long tx     HLL, slow query, replica lag
Weekly do         Check autovacuum lag          Check binlog usage & purge
Monthly do        Bloat governance (pg_repack)  Undo tablespace assessment
Before change     DDL lock? (ACCESS EXCLUSIVE)  Online DDL supported?
Worst failure     Bloat + long transaction      Large tx + replica lag

Final executable decision path:

New business, read-heavy, complex queries, team willing to learn -> PostgreSQL 18, with PgBouncer + Patroni + pgBackRest.

Existing MySQL team, write-heavy, mature sharding system -> MySQL 8.4 or 9.7 LTS, first complete 8.0 EOL migration.

Need both -> PG for analytics & core accounting, MySQL for high-concurrency hot writes, one-way sync via CDC (Debezium), no dual-write.

Selection isn't "who's stronger" debate, but put team capability, failure modes, and operational cost on same spreadsheet and calculate clearly . Once calculated, either choice is correct.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

migrationconfigurationMySQLreplicationtroubleshootingPostgreSQLbenchmarkdatabase selectionWALMVCC
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.