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.
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 versionsThis 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 budgetableOperational 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 enablingPart 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-ostAdditional 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 measuredOften 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 irreplaceablePart 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 unlimitedvm.overcommit_memory = 2is 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 = 2003.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 start3.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.02pg_settings.settingreturns 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 -5io_method
-----------
worker
(1 row)
LOG: registered async IO method "worker"
LOG: async IO workers started, count: 3If 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 timeout4.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 <-- 14MBPG 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 upWhen 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 behindHistory list lengthis 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 infostanza: 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 afterPart 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 appdbprogress: 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 runSQL statistics:
queries performed: 3,081,240
transactions: 3,081,240 (25,677 per sec.)
Latency (ms):
95th percentile: 12.41Metric 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/nullPlanning 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 gapMetric PostgreSQL 18 MySQL 9.7
Complex JOIN exec time 842 ms 4320 ms
Window function scene 310 ms 1980 msConclusion: 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 48213Metric PostgreSQL 18 MySQL 9.7
Write TPS 8420 10120
30-min table growth +58% (orders) +6% (orders)
Reclamation lag autovacuum async purge asyncConclusion: 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% fasterPart 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 days6.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 2Note 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 truncation6.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 diff6.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 inventorypsql -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.8psql -Atc "
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';"12033 Lock relation autovacuum: VACUUM public.ordersRoot 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 NoneRollback :
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 ceilingmysql -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 transactionmysql -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 transactionmysql -h 172.16.30.22 -e "
SELECT * FROM performance_schema
.replication_applier_status_by_worker\G" | head -20WORKER_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 = MINIMALmakes 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 NoRollback : 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 binlogCommonality : 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.11conn 312 ok
long_tx 0 ok
repl_lag_bytes 524288 ok
bloat_top 0 okdb-health-scan.sh --type mysql --host 172.16.30.22conn 512 ok
long_tx 2 warn <-- [critical] has >5 min transaction
hll 48213 warn <-- [critical] purge falling behind
slow_1m 0 okScript 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 lagFinal 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
