MySQL vs PostgreSQL DBA Guide: Storage, WAL & Performance

InnoDB Page Architecture (MySQL)

Every InnoDB table is organized as a B+ tree clustered on the primary key. The fundamental unit of I/O is a 16 KB page (configurable via innodb_page_size to 4K, 8K, 32K, or 64K). Understanding page structure is essential for DBA tuning decisions:

• Page Header (38 bytes): LSN, page type, checksum, space ID, page number.
• Sentinel Records: Infimum/Supremum records, boundary sentinel records that define the range of the page.
• User Records: Actual row data stored in a singly-linked list ordered by primary key.
• Page Directory: Sparse array of offsets for O(log n) binary search within the page.
• Free Space: Heap of unallocated space, grown from the center outward.
• Page Trailer (8 bytes): Checksum and LSN for crash-consistency verification.

InnoDB's buffer pool (innodb_buffer_pool_size) is the single most impactful tuning parameter, set to 70-80% of available RAM on dedicated DB servers. The buffer pool is divided into chunks (innodb_buffer_pool_chunk_size) and optionally multiple instances (innodb_buffer_pool_instances) to reduce mutex contention on high-core-count systems.

DBA Tip: InnoDB Buffer Pool Warm-up

After a cold restart, InnoDB will dump and reload the buffer pool state automatically (innodb_buffer_pool_dump_at_shutdown = ON, innodb_buffer_pool_load_at_startup = ON). This avoids a cold-cache performance cliff on restart. Monitor Innodb_buffer_pool_dump_status in SHOW STATUS.

PostgreSQL Heap & TOAST Architecture

PostgreSQL stores table data in 8 KB heap pages (configurable at compile time). Unlike InnoDB, there is no clustered index, rows are stored in heap order and accessed via index pointers (ctid = block number + tuple offset). Key structural concepts:

• ctid: Physical location of a row as (page_number, tuple_offset). Secondary indexes store ctids, not primary keys, this is why HOT (Heap Only Tuple) updates matter.
• HOT Updates: If an updated row fits on the same page and no indexed column changed, PostgreSQL creates a HOT chain, the index is not updated, only a forwarding pointer in the heap. This reduces index bloat dramatically for frequently updated non-indexed columns.
• TOAST (The Oversized Attribute Storage Technique): Columns exceeding ~2 KB are automatically compressed and/or stored out-of-line in a per-table TOAST table. Strategies include PLAIN, EXTENDED, EXTERNAL, MAIN. DBAs should be aware that selecting a wide JSONB column triggers TOAST decompression per row.
• Fill Factor: Configurable per table (ALTER TABLE t SET (fillfactor=70)). A lower fill factor leaves free space on each page for HOT updates, trading storage efficiency for reduced page splits and index maintenance.

DBA Deep Dive: WAL, Redo Logs & Durability

MySQL InnoDB Redo Log

InnoDB's redo log (iblogfile0, iblogfile1, or a single ib_logfile in MySQL 8.0.30+) is a circular ring buffer of physical change records. Key parameters:

• Log File Size (innodb_log_file_size): Larger redo logs reduce checkpoint frequency (fewer I/O spikes) but increase crash recovery time. A common recommendation is to size the log to absorb 1 hour of peak write I/O.
• Flush Strategy (innodb_flush_log_at_trx_commit): Controls the durability/performance tradeoff. Value 1 (default, fully durable) fsyncs on every commit. Value 2 fsyncs once per second (risk: 1 second of data loss on OS crash). Value 0 is not durable.
• Log Buffer (innodb_log_buffer_size): In-memory buffer for redo log writes. Increase to 64-256 MB for workloads with large transactions (e.g., bulk inserts).
• Adaptive Flushing: InnoDB dynamically adjusts the flush rate based on the ratio of dirty pages and redo log consumption to avoid flush storms.

PostgreSQL WAL

PostgreSQL's WAL is a sequential append-only log in pg_wal/ (formerly pg_xlog). All heap and index changes are written to WAL before being applied. Key parameters:

• WAL Level (wal_level): minimal (no replication), replica (streaming replication), logical (logical decoding + replication). DBAs running replicas must set replica or logical.
• Synchronous Commit (synchronous_commit): Determines when a COMMIT returns to the client. on = WAL flushed to disk. remote_write = WAL written to standby OS buffer. remote_apply = WAL applied on standby. off = asynchronous (risk: recent commits lost on crash, but no data corruption).
• WAL Compression (wal_compression): Compresses WAL records (LZ4 or zstd in PG 15+). Reduces WAL volume at the cost of CPU, valuable for bandwidth-constrained replication links.
• Checkpoint Tuning (checkpoint_completion_target): Fraction of the checkpoint interval over which dirty pages are flushed. Default 0.9 spreads I/O over 90% of the checkpoint interval, avoiding I/O spikes.
• WAL Size (max_wal_size / min_wal_size): Controls the maximum WAL accumulation before forcing a checkpoint. Increase max_wal_size on write-heavy systems to reduce checkpoint frequency.

WAL Archiving & PITR

PostgreSQL supports continuous WAL archiving (archive_command) enabling Point-In-Time Recovery (PITR) to any transaction boundary. Combined with pg_basebackup, this provides a complete backup strategy. MySQL's equivalent is binary log plus mysqldump/XtraBackup with --binlog-info for PITR.

DBA Deep Dive: VACUUM, Autovacuum & Table Bloat

VACUUM is one of the most important, and most misunderstood, operational concepts in PostgreSQL. Because MVCC retains dead tuples in the heap, a background process must periodically reclaim this space.

VACUUM vs VACUUM FULL

FeatureMySQLPostgreSQLBlocks writes?No (concurrent with DML)Yes (exclusive lock)Reclaims disk space?No (marks free for reuse)Yes (rewrites table to disk)Updates visibility map?YesYesUse caseRoutine maintenanceEmergency bloat recovery onlyAlternativeAutovacuum handles automaticallypg_repack (online, no lock)

Autovacuum Tuning for High-Throughput Tables

Default autovacuum settings are conservative and optimized for small databases. High-write tables require aggressive tuning:

• Scale Factor (autovacuum_vacuum_scale_factor = 0.01): Trigger vacuum when 1% of the table has dead tuples (default 20%). For a 100M-row table, this fires at 1M dead tuples instead of 20M.
• Cost Delay (autovacuum_vacuum_cost_delay = 2ms): Reduce the nap time between cost-based throttle cycles (default 2ms in PG 13+, was 20ms earlier).
• Cost Limit (autovacuum_vacuum_cost_limit = 400): Increase the cost limit per cycle (default 200) to allow autovacuum to do more work per cycle.
• Per-table Override: Per-table storage parameters, e.g. ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 2), override the global setting for a specific table.

Transaction ID Wraparound: The DBA Emergency

PostgreSQL's MVCC uses 32-bit transaction IDs (XID). With around 4 billion possible XIDs, a database that is never vacuumed will eventually approach wraparound, at which point PostgreSQL enters safe shutdown mode and refuses all writes. Key monitoring:

• Monitor XID Age: SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age DESC. Alert when any database exceeds 1.5 billion (leave 1B buffer).
• Table-level Check: SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY age DESC LIMIT 20, to find tables closest to wraparound.
• Freeze Max Age (autovacuum_freeze_max_age, default 200M): Autovacuum will aggressively freeze tables approaching this XID age. Increase max_connections carefully, idle connections in long transactions block XID advancement.

MySQL Equivalent

MySQL InnoDB does not have a transaction ID wraparound problem, InnoDB uses 64-bit transaction IDs. However, MySQL DBAs must monitor ibdata1 growth (the system tablespace) and innodb_purge_lag, which indicates how far the purge thread is behind in cleaning old row versions, the functional equivalent of PostgreSQL dead tuple bloat.

DBA Deep Dive: Query Planner Internals

PostgreSQL Planner

PostgreSQL uses a cost-based optimizer. It estimates the cost of every possible execution plan and chooses the minimum-cost plan. Understanding the planner is essential for DBA query tuning:

• pg_stats: Per-column statistics including n_distinct, correlation, most_common_vals, histogram_bounds. Updated by ANALYZE. Stale statistics cause bad plans.
• Statistics Target (default_statistics_target, default 100): Controls the granularity of histogram buckets. For columns used in range predicates on large tables, increase to 500: ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 500.
• Plan Node GUCs: enable_seqscan / enable_hashjoin / enable_nestloop, boolean GUCs to disable specific plan nodes for debugging. Never disable in production permanently.
• Join Collapse (join_collapse_limit / from_collapse_limit): Controls how many tables the planner will reorder when searching for the optimal join order. Increase for complex queries with many tables (at the cost of planning time).
• Partition-wise: enable_partitionwise_join / enable_partitionwise_aggregate, partition-aware joins and aggregates that can dramatically improve query performance on partitioned tables by pushing joins down to partition level.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) is the DBA's primary diagnostic tool. Key metrics: actual rows vs estimated rows (large discrepancies indicate stale statistics), shared_hit vs shared_read (cache hit ratio per node), and loop counts on nested loop joins.

MySQL Optimizer

MySQL uses a cost-based optimizer with statistics stored in mysql.innodb_table_stats and mysql.innodb_index_stats. Key DBA considerations:

• ANALYZE TABLE: Resamples InnoDB statistics. Run after large bulk loads. innodb_stats_persistent = ON (default) persists stats across restarts.
• Optimizer Switch (optimizer_switch): A 30+ flag string controlling individual optimizer features (e.g., index_merge, mrr, batched_key_access, hash_join). Use EXPLAIN to verify which flags affect your query.
• ICP (index_condition_pushdown): Pushes WHERE conditions into the storage engine to reduce rows returned to the SQL layer. Enabled by default, verify with EXPLAIN Extra: Using index condition.
• MRR (Multi-Range Read): Buffers range scan row ids and sorts them by primary key before fetching rows, converting random I/O into sequential I/O. Useful for large range queries on secondary indexes.
• EXPLAIN TREE (EXPLAIN FORMAT=TREE, 8.0+): The new tree-format EXPLAIN is far more readable than the tabular format for complex queries with derived tables and window functions.

DBA Deep Dive: Partitioning Strategies

PostgreSQL Declarative Partitioning

PostgreSQL 10 introduced declarative partitioning as a first-class SQL feature. Partition types:

• RANGE partitioning: Most common for time-series data. CREATE TABLE metrics PARTITION BY RANGE (created_at). Child partitions: CREATE TABLE metrics_2024 PARTITION OF metrics FOR VALUES FROM ('2024-01-01') TO ('2025-01-01').
• LIST partitioning: Partition by discrete values, e.g., region = 'APAC' | 'EMEA' | 'AMER'.
• HASH partitioning: Distribute rows evenly by hashing a key, ideal for even load distribution without a natural partition key.
• Sub-partitioning: Combine strategies, RANGE by month, then HASH by customer_id within each month.

Critical DBA operations on partitioned tables:

• Partition pruning: The planner eliminates irrelevant partitions at plan time. Verify with EXPLAIN, look for 'Partitions removed' in the output. Use enable_partition_pruning = on (default).
• Partition-wise aggregation: SET enable_partitionwise_aggregate = on, the aggregation is pushed down to each partition, then the results are merged. Eliminates a full sort for GROUP BY on the partition key.
• Attaching / detaching partitions: ALTER TABLE metrics ATTACH PARTITION old_data FOR VALUES ... and DETACH PARTITION are near-instantaneous operations, unlike DELETE-based archival which generates massive WAL and dead tuples.
• Default Partition: A catch-all partition for rows that do not match any defined range or list. Prevents insert errors on unmapped values but should be monitored and split regularly.

MySQL Partitioning

MySQL partitioning has historically been less capable than PostgreSQL's, but MySQL 8.0 improved several aspects:

• Partition Types: RANGE, LIST, HASH, KEY partitioning, KEY is MySQL-specific and hashes on the primary key.
• Pruning: Partition pruning applies to WHERE clauses on the partition key. EXPLAIN PARTITIONS shows which partitions are accessed.
• Local Indexes Only: Global indexes are not supported, all indexes in MySQL are local to the partition. This means a UNIQUE constraint must include the partition key.
• Exchange Partition: ALTER TABLE ... EXCHANGE PARTITION, swap a partition with an identically structured table, useful for bulk loading into a staging table then atomically swapping it into the partitioned table.

DBA Deep Dive: Backup, Recovery & RTO/RPO

MySQL Backup Strategies

ToolTypeNotesmysqldumpLogical (SQL)Portable, slow for large DBs. --single-transaction for InnoDB consistency.mysqlpumpLogical (parallel)Parallel dump; faster than mysqldump for large schemas.MySQL Shell dumpLogical (chunked)Best logical option; parallelised, restorable chunk-by-chunk.Percona XtraBackupPhysical (hot)Copies InnoDB files online, applies redo log delta. Near-zero RTO.MySQL Enterprise BackupPhysical (hot)Oracle's commercial equivalent of XtraBackup.Binary logsIncrementalEnable log_bin. Combined with physical backup for PITR.

PostgreSQL Backup Strategies

ToolTypeNotespg_dumpLogical (SQL/custom)Custom format (-Fc) is parallel-restorable with pg_restore -j N.pg_dumpallLogical (globals)Dumps roles, tablespaces, always run alongside pg_dump.pg_basebackupPhysical (online)Streams base backup over replication protocol. Seeds standbys.pgBackRestPhysical (incremental)Block-level incremental backups, parallel, S3/GCS/Azure storage, encryption.BarmanPhysical (centralized)Centralised backup server; streaming + WAL archiving; catalog management.WAL archiving (PITR)Incremental WALarchive_command ships WAL segments to object storage for PITR.

RTO / RPO Planning

Database backup strategy must be defined in terms of RTO (Recovery Time Objective, how long to restore) and RPO (Recovery Point Objective, how much data loss is acceptable):

• RPO = 0: Requires synchronous replication to at least one standby. Both MySQL Group Replication (with synchronous mode) and PostgreSQL synchronous_commit = remote_apply achieve this at the cost of write latency.
• RPO less than 1 minute: WAL streaming replication (PostgreSQL) or semi-synchronous replication (MySQL) with a replica in the same region.
• RPO less than 24 hours: Daily physical backup plus WAL/binlog archiving. Sufficient for most business workloads.
• RTO target less than 1 hour: Physical backups (XtraBackup / pgBackRest) with parallel restore. Logical backups from mysqldump/pg_dump can take hours on large databases.

DBA Deep Dive: Monitoring & Observability

Key MySQL DBA Metrics

• Connection Saturation: Threads_running vs max_connections. Threads_running greater than CPU core count indicates a CPU bottleneck. Approaching max_connections triggers connection refusal errors.
• Buffer Pool Hit Ratio: Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests. Hit ratio = 1 - (reads / read_requests). Target greater than 99%. Low hit ratio means buffer pool is undersized.
• Lock Contention: Innodb_row_lock_waits and Innodb_row_lock_time_avg. High lock wait time indicates lock contention, review long-running transactions with INFORMATION_SCHEMA.INNODB_TRX.
• Replication Lag: Seconds_Behind_Master (replica), replication lag in seconds. A growing value indicates the replica cannot keep up with write throughput.
• Slow Queries: Slow query log plus pt-query-digest. Enable slow_query_log with an appropriate long_query_time, and use Percona pt-query-digest to aggregate and rank slow queries by total execution time.
• SHOW ENGINE INNODB STATUS: A goldmine of InnoDB internals, recent deadlocks, semaphore waits, transaction list, buffer pool stats, I/O summary.

Key PostgreSQL DBA Metrics

• Active Connections: pg_stat_activity, a live view of all backend connections, state (active, idle, idle in transaction), wait_event_type, query, and duration. Alert on idle in transaction longer than 30 seconds.
• BGWriter Stats: pg_stat_bgwriter tracks checkpoint and background writer I/O. A high buffers_backend ratio means checkpoints are not keeping up, starving backends.
• Replication Lag: pg_stat_replication shows streaming replication lag in bytes (write_lsn, sent_lsn, flush_lsn, replay_lsn), and also replication slot lags, unused slots can cause WAL accumulation and disk exhaustion.
• Table Bloat: pg_stat_user_tables, n_dead_tup and n_mod_since_analyze, monitor for tables that are not being vacuumed or analyzed regularly.
• Query Stats: pg_stat_statements, aggregate query statistics (total_time, mean_time, calls, rows), essential for identifying top queries by total CPU time. Enable via shared_preload_libraries = 'pg_stat_statements'.
• Lock Chains: pg_locks plus pg_blocking_pids() identify lock chains and blocking sessions.
• XID Age: age(datfrozenxid) in pg_database, monitor XID age across all databases. Alert at 1.5 billion to ensure autovacuum has time to freeze before wraparound.

DBA Deep Dive: Security & Access Control

MySQL Security Model

• User@Host Model: Identified by username@host pairs, 'app_user'@'10.0.0.%' and 'app_user'@'localhost' are distinct users. Host wildcards support subnet-level access control.
• Privilege Layers: Global (GRANT ALL ON *.*), database-level (ON mydb.*), table-level, and column-level grants. SHOW GRANTS FOR 'user'@'host' reveals effective privileges.
• Roles (8.0+): CREATE ROLE read_only; GRANT SELECT ON mydb.* TO read_only; GRANT read_only TO 'user'@'%'. Roles must be activated with SET ROLE or activate_all_roles_on_login = ON.
• TLS: Require encrypted connections per user with ALTER USER 'user'@'%' REQUIRE SSL. Global enforcement via require_secure_transport = ON.
• Audit: MySQL Enterprise Audit plugin or Percona Audit Log Plugin for open-source. Log all DDL, DML, and connection events to a tamper-evident log.

PostgreSQL Security Model

• pg_hba.conf: The Host-Based Authentication file controls which users can connect from which hosts using which authentication method (trust, md5, scram-sha-256, cert, ldap, radius, pam). Most granular authentication control of any RDBMS.
• SCRAM-SHA-256: Default password hashing since PG 14. Upgrade from md5 by setting password_encryption = scram-sha-256 and resetting passwords. md5 is deprecated.
• Row-Level Security (RLS): CREATE POLICY on a table restricts which rows a user can SELECT/INSERT/UPDATE/DELETE based on the current_user or session variables. Enables multi-tenant data isolation within a shared schema.
• Column-level privileges: GRANT SELECT (email, name) ON users TO reporting_role, restrict access to specific columns.
• Ownership Model: Separation of superuser and object ownership, a table owner can GRANT privileges without being a superuser. pg_read_all_data and pg_write_all_data are predefined roles (PG 14+) for broad but non-superuser access.
• pgaudit extension: Comprehensive audit logging with session-level and object-level granularity. Output integrates with syslog and log aggregators.

RLS: PostgreSQL's Multi-Tenancy Superpower

Row-Level Security allows a SaaS application to store all tenants in a single table and enforce tenant isolation at the database level. Set a session variable (SET app.current_tenant = 'acme') and a policy (USING (tenant_id = current_setting('app.current_tenant')::uuid)), no application-level WHERE clause required, and even a miscoded query cannot leak cross-tenant data.

DBA Deep Dive: Online Schema Changes

Altering large tables (100M+ rows) without downtime is one of the most challenging DBA tasks. MySQL and PostgreSQL take very different approaches.

MySQL: gh-ost & pt-online-schema-change

MySQL's ALTER TABLE acquires a metadata lock. For large tables, even an Online DDL operation (ALGORITHM=INPLACE) can block for minutes. Community solutions include:

• pt-online-schema-change (Percona): Creates a shadow table, copies rows in chunks, uses triggers to capture concurrent DML, then performs an atomic RENAME. Risk: triggers add write overhead; can cause replication lag.
• gh-ost (GitHub): Triggerless. Connects as a replica, reads binlog events to capture DML changes, applies them to the ghost table. Safer than pt-osc on high-write systems. Supports throttling, pausability, and dry-run mode.

PostgreSQL: pg_repack & Transactional DDL

• CREATE INDEX CONCURRENTLY: Builds an index without blocking reads or writes. Takes longer (two table scans) but is zero-downtime. Caveat: if it fails, an INVALID index is left behind, clean it up with DROP INDEX CONCURRENTLY.
• pg_repack: Online table and index rebuild without an exclusive lock. Equivalent to VACUUM FULL but non-blocking. Used to reclaim bloated table space after heavy UPDATE/DELETE workloads.
• Transactional DDL: Most DDL (ADD COLUMN, DROP COLUMN, CREATE INDEX, CREATE TABLE) can be wrapped in a transaction and rolled back if the migration script fails. This is the most underrated PostgreSQL DBA feature.
• Fast ADD COLUMN with DEFAULT (PG 11+): In PostgreSQL 11+, adding a column with a non-volatile DEFAULT is instantaneous, the default value is stored in pg_attribute, not backfilled row-by-row. Prior to PG 11, this rewrote the entire table.

Conclusion

MySQL and PostgreSQL are both production-grade, battle-tested systems with active communities and robust cloud managed offerings. The choice between them is rarely about raw performance, it is about alignment with your workload profile, schema complexity, feature requirements, and operational context.

MySQL rewards you with simplicity, speed on read-heavy workloads, and a mature ecosystem of replication and proxy tooling. PostgreSQL rewards you with standards compliance, rich data types, advanced indexing, transactional DDL, and an extension system that can adapt the database to nearly any workload, from OLTP to geospatial to vector search.

In the modern era, PostgreSQL has largely closed the performance gap on OLTP workloads while maintaining a decisive lead in feature richness. Unless you have specific operational reasons to prefer MySQL, PostgreSQL is the stronger default for new projects.