MySQL PostgreSQL Articles

Dropping an index is instant; rebuilding it under incident pressure takes hours. Field notes on using MySQL 8.0 invisible indexes as a safety net, plus the audit workflow that finds safe candidates.

Collations decide what counts as equal, which indexes work on JOINs, and which queries throw mixed-collation errors. Field notes on choosing between utf8mb4_0900_ai_ci, general_ci, and bin, and migrating safely.

The change buffer turns cold secondary-index page reads into deferred merges. On the right workload it is the reason writes keep up; on NVMe with in-memory indexes it does almost nothing.

Partitioning solves data-lifecycle problems, not performance problems. Field notes on verifying pruning, automating partition maintenance, living with the foreign-key restriction, and knowing when to say no.

Group Replication promises consensus-backed failover; classic async promises simplicity. Field notes on what GR actually guarantees, how flow control stalls write bursts, and when async plus an orchestrator is still the better architecture.

Triggers or binlog streaming? Field notes from running both gh-ost and pt-online-schema-change in production: cut-over risk, throttling behavior, foreign key walls, and when you need neither.

CTEs and window functions made MySQL 8.0 queries readable, and occasionally much slower. Field notes on derived_merge, materialization, window frame costs, and the cases where the old JOIN still wins.

Purge lag is usually a pinned read view, not weak purge threads. Here is how to tell the two failures apart, what innodb_purge_threads actually buys, and the alerts that matter.

ALGORITHM=INSTANT is the best schema-change feature in MySQL 8.0, and the most misunderstood. These field notes cover what qualifies, the 64 row-version limit, and how to check before you ALTER.

A replica that cannot survive kill -9 is a replica you rebuild at 3 a.m. This covers relay_log_recovery, the repository history that made replicas crash-safe, GTID auto-positioning, and why you should crash replicas on purpose.

The sys schema ships with every MySQL 8.x server and most teams never open it. These are the five views I actually use every day - statement_analysis, the index-hygiene pair, file I/O, and host_summary - with the caveats that keep them honest.

Semi-synchronous replication promises that a commit is acknowledged by at least one replica before the client sees success. The fine print — silent fallback to async, wait points, and what an ack actually means — is where production incidents live.

When an AUTO_INCREMENT column hits its type's ceiling, every insert on that table fails at once. Field notes on the incident shape, a fleet-wide detection query, signed vs unsigned headroom, and migrating to BIGINT without downtime.

An errant transaction is a write that exists on a replica but not on the source, and it sits harmlessly in gtid_executed until the exact moment you fail over. Here is how they happen, how to find them, and how to fix them.

MySQL memory is not just the buffer pool. This guide maps global versus per-session allocations, shows how to interrogate performance_schema memory instrumentation, and walks through a real OOM-kill postmortem workflow.

MySQL cannot index a JSON column directly, and the workarounds have sharp edges. Field notes on generated columns, multi-valued indexes, MEMBER OF pruning, and when to stop pretending and promote the field.

Row-based binary logging is the right default, but it makes binlog volume a function of how many rows you touch. This covers FULL vs MINIMAL row images, partial JSON logging, expiry settings, and the big-UPDATE binlog explosion.

A full disk turns MySQL into a stalled or aborted server, and the wrong rm makes it unrecoverable. This is the playbook: what actually grows, what you can purge safely, and the emergency order of operations.

SHOW PROCESSLIST is the first thing everyone runs and the most misread output in MySQL operations. Here is what the thread states actually mean, which pileups matter, and how to build a triage habit around performance_schema.threads.

Seconds_Behind_Source is the most quoted and least trusted replication metric in MySQL. This deep dive covers heartbeat-based lag measurement, multi-threaded replica tuning, and the big-transaction lag pattern.

Logical backups scale badly by default. Field notes on where mysqldump stops working, what mydumper and myloader change, and the honest boundary where physical backups win.

The CLONE plugin turned replica provisioning from a multi-tool XtraBackup ritual into two SQL statements. Field notes on setup, the remote clone gotchas, and how to watch progress while it runs.

Percona XtraBackup plus binary logs is still the workhorse recovery story for self-managed MySQL. These are field notes on the full/incremental cadence, prepare ordering, and the restore rehearsal that catches bad backups.

A JOIN between columns with different charsets or collations can quietly turn an index lookup into a full scan. Field notes on how the coercion rules work, the utf8mb3/utf8mb4 upgrade trap, and the queries that find every mismatched column.

Dropping an index is instant; rebuilding it under incident pressure takes hours. Field notes on using MySQL 8.0 invisible indexes as a safety net, plus the audit workflow that finds safe candidates.

Collations decide what counts as equal, which indexes work on JOINs, and which queries throw mixed-collation errors. Field notes on choosing between utf8mb4_0900_ai_ci, general_ci, and bin, and migrating safely.

Group Replication promises consensus-backed failover; classic async promises simplicity. Field notes on what GR actually guarantees, how flow control stalls write bursts, and when async plus an orchestrator is still the better architecture.

MySQL cannot index a JSON column directly, and the workarounds have sharp edges. Field notes on generated columns, multi-valued indexes, MEMBER OF pruning, and when to stop pretending and promote the field.

Partitioning solves data-lifecycle problems, not performance problems. Field notes on verifying pruning, automating partition maintenance, living with the foreign-key restriction, and knowing when to say no.

Triggers or binlog streaming? Field notes from running both gh-ost and pt-online-schema-change in production: cut-over risk, throttling behavior, foreign key walls, and when you need neither.

Purge lag is usually a pinned read view, not weak purge threads. Here is how to tell the two failures apart, what innodb_purge_threads actually buys, and the alerts that matter.

The change buffer turns cold secondary-index page reads into deferred merges. On the right workload it is the reason writes keep up; on NVMe with in-memory indexes it does almost nothing.

When an AUTO_INCREMENT column hits its type's ceiling, every insert on that table fails at once. Field notes on the incident shape, a fleet-wide detection query, signed vs unsigned headroom, and migrating to BIGINT without downtime.

The sys schema ships with every MySQL 8.x server and most teams never open it. These are the five views I actually use every day - statement_analysis, the index-hygiene pair, file I/O, and host_summary - with the caveats that keep them honest.

ALGORITHM=INSTANT is the best schema-change feature in MySQL 8.0, and the most misunderstood. These field notes cover what qualifies, the 64 row-version limit, and how to check before you ALTER.

FTWRL waits for every in-flight statement, then makes everything else wait for it. MySQL 8.0's LOCK INSTANCE FOR BACKUP and performance_schema.log_status give backup tools a lighter path — and you a shorter outage.

A replica that cannot survive kill -9 is a replica you rebuild at 3 a.m. This covers relay_log_recovery, the repository history that made replicas crash-safe, GTID auto-positioning, and why you should crash replicas on purpose.

CTEs and window functions made MySQL 8.0 queries readable, and occasionally much slower. Field notes on derived_merge, materialization, window frame costs, and the cases where the old JOIN still wins.

Row-based binary logging is the right default, but it makes binlog volume a function of how many rows you touch. This covers FULL vs MINIMAL row images, partial JSON logging, expiry settings, and the big-UPDATE binlog explosion.

Semi-synchronous replication promises that a commit is acknowledged by at least one replica before the client sees success. The fine print — silent fallback to async, wait points, and what an ack actually means — is where production incidents live.

Long-running and idle transactions quietly block DDL, hold locks, and stall purge until the whole instance suffers. Here is how I detect them with innodb_trx and decide what to kill.

Gap locks make InnoDB transactions collide over rows that do not exist. These are the patterns I use to shrink the lock footprint under REPEATABLE READ, and when READ COMMITTED is the honest answer.

Deadlocks are not random noise; each one is a recorded collision with a cause. This is the repeatable workflow I use to capture, parse, and permanently fix InnoDB deadlocks.

MySQL 8.0 moved row-lock diagnosis to performance_schema.data_locks and data_lock_waits. Here is how I read those tables, use sys.innodb_lock_waits, and build a blocker tree during an incident.

A single idle transaction can make an innocent ALTER TABLE queue the entire application behind it. This is my working process for diagnosing MySQL metadata lock waits and running DDL safely.

The most repeatable plan regression in MySQL: add LIMIT to an ORDER BY query and the optimizer abandons your selective index for the ordering one. Why it happens, how to spot it, and the fixes that hold.

Most bad MySQL plans trace back to one number: a row estimate built from twenty sampled pages. Field notes on innodb_stats_persistent, ANALYZE TABLE, eq_range_index_dive_limit, and 8.0 histograms.

optimizer_switch is where MySQL hides two decades of query-planner history, and a handful of its flags cause real production regressions. Field notes on the ones that bite and how to test them one at a time.

MySQL 8.0.18 gave us EXPLAIN ANALYZE, and most people read its output wrong. Here is how the per-loop numbers actually work, the misreads I keep seeing, and when the tree format tells the truth tabular EXPLAIN hides.

The MySQL slow query log is still the best forensic record of a bad deploy, but only if you configure it deliberately and digest it into query families. This is the workflow I actually run.

The 75% rule is folklore, not a sizing method. This field guide covers working-set reality, online resizing, warmup dump and restore, and the two counters that actually tell you whether the buffer pool is big enough.

Doublewrite exists for one narrow disaster: a torn page that redo cannot repair. This is an honest look at how it works, what it really costs in I/O, how to measure that overhead yourself, and when disabling it is actually defensible.

GROUP BY, DISTINCT, derived tables, and window functions can all materialize internal temp tables, and when those overflow memory your query is suddenly doing disk I/O nobody planned. Here is how the TempTable engine works and how to find the offenders.

When checkpoint age catches up with redo capacity, InnoDB stops the world to flush, and your p99 pays for it. Here is how innodb_redo_log_capacity works in 8.0.30+, how to size it against write bursts, and how furious flushing looks in the metrics.

One idle REPEATABLE READ session can pin millions of undo records and slowly melt a busy MySQL server. History list length is the canary; this is how to watch it, find the blocker, and keep undo growth under control.