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 isa 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: 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): Check sum 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-countsystems.

DBA Tip: InnoDB Buffer Pool Warm-up

After a cold restart, InnoDB will dump and reload the bufferpool 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. MonitorInnodb_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: 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: 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: 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 singleib_logfile in MySQL 8.0.30+) is a circular ring buffer of physical change records. Key parameters:

• LogFile 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: 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 replicaor logical.
• SyncCommit: 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 onstandby. 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/Ospikes.
• WAL Size: max_wal_size/ min_wal_size: Controls the maximum WAL accumulation before forcing a checkpoint. Increasemax_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 equivalent is binary log + 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

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: Triggervacuum 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 workper cycle.
• Per-table Override: Per-table storage parameters: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_delay = 2) - overrides the global setting for a specific table.

Transaction ID Wraparound - The DBA Emergency

PostgreSQL's MVCC uses 32-bittransaction IDs (XID).With ~4 billion possible XIDs, a database that is never vaccumed 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 monitor XID age. 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 - 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 ibdata1growth (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 (default100): 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: Controlshow 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 - 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 pernode), 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(ICP): 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 (MRR):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.
• EXPLAINTREE: 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: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: LIST partitioning: Partition by discrete values - e.g., region= 'APAC' | 'EMEA' | 'AMER'.
• HASH: 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:

• Pruning: 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 Agg: 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. EXPLAINPARTITIONS 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 ... EXCHANGEPARTITION: 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

PostgreSQL Backup Strategies

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: 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 < 1 min: RPO < 1 minute: WAL streaming replication (PostgreSQL) or semi-synchronous replication (MySQL) with a replica in the same region.
• RPO < 24h: RPO < 24 hours: Daily physical backup + WAL/binlog archiving. Sufficient for most business workloads.
• RTO Target:RTO < 1 hour: Physical backups(XtraBackup / pgBackRest) with parallel restore. Logical backups frommysqldump/pg_dump can take hours on large databases.

DBA Deep Dive: Monitoring & Observability

Key MySQL DBA Metrics

• Connection Saturation: Threads_runningvs max_connections: Threads_running > CPU core count indicates a CPU bottleneck. Approaching max_connections triggers connection refusal errors.
• BufferPool Hit Ratio: Innodb_buffer_pool_readsvs Innodb_buffer_pool_read_requests: Hit ratio= 1 - (reads / read_requests). Target> 99%. Low hit ratio means buffer pool is undersized.
• Lock Contention: Innodb_row_lock_waits + 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 + pt-query-digest: Enableslow_query_log with long_query_time =
• Use Perconapt-query-digest to aggregate and rank slow queries by total execution time.
• INNODB STATUS:SHOW ENGINE INNODB STATUS: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: Live view of all backend connections - state (active, idle, idle in transaction),wait_event_type, query, and duration. Alert on idle in transaction > 30 seconds.
• BGWriterStats: pg_stat_bgwriter: Tracks checkpoint and background writer I/O. buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) - highbuffers_backend means checkpoints are not keeping up, starving backends.
• Replication Lag: pg_stat_replication: Streaming replication lag in bytes (write_lsn - sent_lsn,flush_lsn, replay_lsn). Also shows 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 viashared_preload_libraries = 'pg_stat_statements'.
• Lock Chains:pg_locks + pg_blocking_pids(): Identifylock chains. SELECTpid, pg_blocking_pids(pid) AS blocking FROM pg_stat_activity WHEREcardinality(pg_blocking_pids(pid)) > 0.
• XID Age: age(datfrozenxid) in pg_database: MonitorXID age across all databases. Alert at 1.5 billion to ensure auto vacuum has time to freeze before wraparound.

DBA Deep Dive: Security & Access Control

MySQL Security Model

• User@Host Model: User accounts: 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: Privilege system:Global (GRANT ALL ON *.*), database-level (ON mydb.*),table-level, and column-level grants. SHOW GRANTS FOR 'user'@'host' reveals effective privileges.
• Roles: Roles (8.0+): CREATE ROLEread_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_logi= ON.
• TLS: TLS: Require encrypted connections per user: ALTER USER 'user'@'%' REQUIRESSL. Global enforcement:require_secure_transport = ON.
• Audit: Audit: MySQL Enterprise Audit plugin or Percona AuditLog Plugin for open-source. Log all DDL, DML, and connection events to a tamper-evident log.

PostgreSQL Security Model

• pg_hba.conf: pg_hba.conf: 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: 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.
• RLS:Row-Level Security (RLS):CREATE POLICY on a table restricts which rows a user canSELECT/INSERT/UPDATE/DELETE based on the current_user or session variables. Enables multi-tenantdata isolation within a shared schema.
• Column Grants:Column-level privileges: GRANT SELECT (email, name) ON users TOreporting_role - restrict access to specific columns.
• OwnershipModel: 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: 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. The community solutions:

• pt-osc: 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: gh-ost (GitHub): Triggerless. Connects as a replica, readsbinlog events to capture DML changes, applies them to the ghosttable. Safer than pt-osc on high-write systems. Supports throttling, pausability, and dry-run mode.

PostgreSQL: pg_repack & Transactional DDL

• ConcurrentIndex: CREATE INDEXCONCURRENTLY: Builds an index without blocking reads or writes. Takes longer (two tablescans) but is zero-downtime. Caveat:if it fails, a INVALIDindex is left behind - clean it up with DROP INDEX CONCURRENTLY.
• pg_repack: pg_repack: Online table and index rebuild withoutan exclusive lock. Equivalent to VACUUM FULL but non-blocking. Used toreclaim bloated table space after heavy UPDATE/DELETE workloads.
• Txn DDL: TransactionalDDL: Most DDL (ADD COLUMN,DROP COLUMN, CREATEINDEX, CREATE
• TABLE) can be wrapped in a transaction and rolled back if the migration scriptfails. This is themost underrated PostgreSQL DBA feature.
• Fast ADD COLUMN: ADD COLUMN with DEFAULT (PG 11+): In PostgreSQL 11+, adding a column with a non-volatile DEFAULT is instantaneous - the default value is stored inpg_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.