Skip to content

SYSTEM Cited by 19 sources

MySQL

MySQL is a widely deployed open-source relational database, typically the default OLTP store for teams that need a familiar SQL engine and strong ecosystem support. It's single-writer, row-oriented, and doesn't horizontally partition itself by default — scaling is by instance size first, then replicas, then manual sharding.

Pattern of appearance in system-design stories

MySQL tends to show up as the start-small choice that outgrew its workload shape. The narrative is familiar:

  • Launch on MySQL for a workload that's shaped OLTP but has an aggregation tail nobody worried about in v1.
  • Aggregation workers use single-threaded sequential scans + per-record DB round-trips; scales as O(N) queries per pass.
  • Storage doubles faster than the instance size can be grown without operational pain; shared-instance blast radius grows.
  • Move raw events off first (to a horizontally-scaling store like DynamoDB or S3), then move the aggregation workload off entirely to an OLAP warehouse.

Canva's Creators-payment counting pipeline walked this exact path over several years: RDS MySQL, doubling every 8–10 months, reaching several TB, with shared-instance risk driving a split and then the move to Snowflake + DBT. (Source: sources/2024-04-29-canva-scaling-to-count-billions)

Honest assessment from the Canva post

Canva explicitly defends MySQL as the correct choice for the first 2 years — "it served its purpose well … using the initial architecture also ensured that we could deliver our functionality to users in a reasonable timeframe". The issue wasn't MySQL; it was using OLTP MySQL for a scan-and-aggregate workload at exponential growth. (See concepts/oltp-vs-olap.)

Operational notes from real deployments

  • Vertical scaling wall — doubling the RDS instance is the growth strategy; at TB-class sizes, zero-downtime upgrades dominate on-call budget.
  • Native same-server partitioning (RANGE/LIST/KEY/HASH) exists but is not sharding — MySQL splits a table into multiple on-disk partitions on the same server with partition pruning and per-partition maintenance benefits, but does not horizontally partition across machines. Sharding across servers remains an application-level exercise or a Vitess-class external-framework exercise. (Source: )
  • Shared-instance blast radius — shared with other critical features, downtime in one hurts all; Canva did a "database split" to mitigate.
  • Free-space burn rate is the useful forward metric. Canva reports ~500 GB / ~50% of free storage consumed in 6 months as the signal the strategy was ending.

Seen in

  • Brian Morrison II's 2023-10-10 What is MySQL partitioning? canonicalises MySQL's native same-server table-split feature on the wiki for the first time. (Source: ) Walks three strategies (RANGE, LIST, KEY) on a library_books worked example; names three benefits (pruning, cross-partition parallelism, per-partition maintenance) and three drawbacks (column-type restrictions, access-pattern sensitivity, single-server fault domain); fixes the load-bearing partitioning-vs-sharding distinction the wiki had been underspecifying across many sharding pages — partitioning is same-server MySQL-native (vertical lever); sharding is cross-server external-framework (horizontal lever).

  • Ben Dicken's 2024-04-11 Profiling memory usage in MySQL. Canonical wiki companion on the memory axis of MySQL query diagnosis, pairing with the same author's 2024-03-29 time-and-rows post. Walks the full memory instrumentation catalog, the five-way granularity split with its missing per-query grain, the per-thread substitute via the CONNECTION_ID() → threads.thread_id mapping, and the two-connection setup required to observe a running query. Canonical worked example: a 100M-row SELECT ... ORDER BY <unindexed> LIMIT 100000 shows memory/sql/Filesort_buffer::sort_keys (203 KiB) + memory/innodb/memory (170 KiB) dominating — the instrument is undocumented (documentation IS NULL in setup_instruments), flagging that MySQL's memory catalogue is partially self-describing-by-name-only. A FULLTEXT index build in the second visualisation grows into "hundreds of megabytes". Python + matplotlib stackplot monitor sampling at 250–500 ms canonicalises the periodic sampling pattern. (Source: sources/2026-04-21-planetscale-profiling-memory-usage-in-mysql.)

  • sources/2025-09-24-planetscale-processes-and-threads — Ben Dicken's 2025-09-24 pedagogical article canonicalises thread-per- connection as MySQL's architectural choice, in explicit contrast to Postgres's [[patterns/ process-per-connection-database|process-per-connection]] model. Verbatim: "MySQL is a great contrast, designed to run as a single process (mysqld). However, it is also capable of handling thousands of queries per-second, hundreds of connections, and utilizing multi-core CPUs. It achieves this via threads." The OS-substrate justification — [[concepts/ thread-os|threads]] share the process's address space (buffer pool, caches, metadata), pay ~1 μs per [[concepts/context- switch|context switch]] (vs ~5 μs for process switching) — is the why beneath MySQL's structurally lower per-connection memory cost than Postgres. But "Even with threads, each connection requires dedicated memory resources to manage connection state" — the max_connections memory ceiling (concepts/max-connections-ceiling) still applies, which is why the universal mitigation is external [[concepts/ connection-pool-exhaustion|connection pooling]] with "a pool of direct connections to the database, typically between 5 and 50" — the canonical connection-pool sizing datum. OS- fundamentals altitude complement to Liz van Dijk's 2022-11-01 benchmark (which canonicalises the memory-economics + two-tier-pooler at production scale): Dicken canonicalises the OS-level why (thread-per-connection as architectural choice) that the van Dijk benchmark demonstrates the production-scale how-we-still-pool of.

  • — Liz van Dijk (PlanetScale, 2022-11-01) canonicalises the memory-economics framing of MySQL's max_connections variable as the load-bearing constraint on standalone-DB scaling: "A standalone database relies heavily on its ability to compartmentalize memory use to provide the strong isolation guarantees we expect, so it needs to allocate certain memory buffers on a per-connection basis. The more connections we create, the less memory we have available for the overall buffer pool, and so MySQL comes with a max_connections variable built in that acts as a 'last resort' safety measure." First canonical wiki citation of the memory-overcommit risk of naively raising the knob: "making MySQL live outside its means (i.e. overcommitting memory) opens the door to dangerous crashes and potential downtime, so this is not recommended." Canonical new concepts concepts/max-connections-ceiling + [[concepts/ memory-overcommit-risk]] anchor the framing. Empirical anchor: 1,000,000 concurrent open connections sustained against a PlanetScale database via [[concepts/lambda-fanout- benchmark|AWS Lambda 1,000-worker × 1,000-connections fan-out]], 62.5× above RDS MySQL's 16,000-connection ceiling — demonstrates the [[patterns/two-tier-connection- pooling|two-tier proxy-pool architecture]] (VTTablet in-cluster + Global Routing Infrastructure at edge) as the structural answer to standalone-DB max_connections economics.

  • — Brian Morrison II (PlanetScale, 2023-11-15) publishes the canonical wiki MySQL replication best-practices field manual: eight orthogonal configuration axes — topology (active/passive vs active/active), transaction identity (GTIDs vs binlog-position), replication mode (async vs semi-sync), mixed modes (patterns/mixed-sync-replication-topology), binlog disk placement (concepts/separate-binlog-disk), monitoring (Prometheus at PlanetScale), unplanned-failover procedure (concepts/unplanned-failover-playbook), cross-AZ vs cross-region topology (patterns/async-replication-for-cross-region). Load-bearing PlanetScale postures: active/passive only, shard for write scale ("We always recommend using an active/passive configuration for replication, and sharding if you need more throughput from your database"); semi-sync within region with extremely high timeout ("We set the timeout value extremely high to ensure that the data for our databases are always consistent"); Prometheus for replication monitoring ("At PlanetScale, we use Prometheus to monitor replication, along with other metrics, for the clusters we manage"). Canonical wiki anti-pattern framing for active/active MySQL: "conflicts can easily occur as there is no native conflict resolution logic within MySQL. When conflicts do occur, neither node can be considered the source of truth for a rebuild without significant data loss." Canonical four-step unplanned-failover procedure: fence downed source → promote replica (ideally the semi-sync one) → re-point application → re-point replicas — ordering is load-bearing (fence first to prevent split-brain). Cross-region network denominators: single-digit ms cross-AZ (AWS-cited) vs 60ms+ cross-region (us-east-1 ↔ us-west-1 per cloudping.co) drive the rule async across regions, semi-sync within region. Brian Morrison II's fourth wiki ingest (after 2023-02-09 Postgres-to-MySQL + two declarative schema posts + MySQL isolation levels); first to canonicalise a MySQL replication-topology best-practices field manual.

  • — Brian Morrison II (PlanetScale, 2024-01-08) canonicalises MySQL's four-level transaction isolation taxonomy as the frame for the I-of-ACID in MySQL: READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ (the MySQL default — "Repeatable read is the default MySQL isolation level used for all connections unless configured otherwise") / SERIALIZABLE. Each level characterised by which of three dirty / non-repeatable / phantom read anomalies it permits. InnoDB MVCC gives REPEATABLE READ a pinned-transaction-ID snapshot whereas READ COMMITTED creates a fresh snapshot per SELECT — canonical mechanism distinction. MySQL-specific strengthening of REPEATABLE READ to prevent phantom reads via InnoDB gap locking on locking reads — diverges from the SQL-standard where only SERIALIZABLE forbids phantoms (Berenson et al. 1995). Two foundational row-lock kinds canonicalised: shared (S) and exclusive (X), plus gap locks as the InnoDB-specific third kind. SERIALIZABLE = REPEATABLE READ + implicit S lock on every SELECT — higher correctness, worse concurrency, elevated deadlock risk. Developer-facing FOR SHARE / FOR UPDATE locking read clauses upgrade a plain SELECT into a lock-taking read. Critical InnoDB subtlety canonicalised: missing indexes inflate lock scope under REPEATABLE READ locking reads — "If an index is NOT used and the table is scanned, MySQL will lock all of the rows it reads regardless if they match the where, as well as perform gap locking to prevent inserts that may alter the data if the query is run multiple times." Three setting mechanisms canonicalised: SET [SESSION] TRANSACTION ISOLATION LEVEL … (per-next-transaction); SET GLOBAL TRANSACTION ISOLATION LEVEL … (future sessions only, requires CONNECTION_ADMIN); --transaction-isolation=… CLI / transaction-isolation = … [mysqld] config (server-startup default). Pedagogy voice / tier-3 PlanetScale post; reusable across every other RDBMS that implements the same four-level ladder with its own mechanism choices.

  • — Adnan Kukic's 2023-02-09 Postgres → MySQL migration audit. Canonicalises MySQL as the target side of a cross-engine migration taxonomy: three column-type incompatibilities (unconstrained VARCHAR → VARCHAR(N) / TEXT, Postgres human-readable POINT → MySQL Well-Known Binary requiring ST_asText(), native UUID + gen_random_uuid() → [VARCHAR(36)

  • UUID()](<../concepts/uuid-string-representation-mysql.md>), JSONB → JSON) plus five operational-dialect gaps (DROP TEMPORARY TABLE, TRUNCATE … CASCADE / RESTART IDENTITY and transaction safety, stored-procedure languages, identifier case sensitivity, Postgres extensions). Worked example of the new patterns/cross-engine-database-migration-audit pattern — a schema + dialect readiness check that has to happen before the zero-downtime data-movement playbook in

can run. Also surfaces the SERIAL width gap (MySQL's SERIAL = BIGINT UNSIGNED AUTO_INCREMENT; Postgres's is INTEGER-backed) and the table-partitioning vocabulary asymmetry (MySQL: 6 modes; Postgres: 3).

  • sources/2026-04-21-planetscale-identifying-and-profiling-problematic-mysql-queries — Ben Dicken (2024-03-29) publishes the canonical wiki field manual for native MySQL query diagnosis: performance_schema (~113 tables, in-memory storage engine) + sys schema as the diagnostic substrate, digest-based query prioritization via events_statements_summary_by_digest, per-table index-usage diagnostic via table_io_waits_summary_by_index_usage (worked datum: 2.57 × 10⁹ unindexed vs 164,500 indexed reads on game.message), and stage-timing profiling via events_stages_history_long with the three-toggle + thread-id + event-id-bracket workflow (worked datum: 735.3 ms in stage/sql/executing out of ~736 ms total). Positions PlanetScale Insights as the productised UX over the same digest data.

  • — JD Lien's 2023-03-24 MySQL 5.7 → 8.0 upgrade checklist canonicalises the five orthogonal breaking-change classes of a MySQL major-version upgrade: charset + collation default flip (utf8 → utf8mb4), deprecated data types (YEAR(2), ENUM, TINYTEXT/MEDIUMTEXT/LONGTEXT, inline NATIONAL/CHARACTER SET/COLLATE clauses), auth-plugin flip ( mysql_native_password → caching_sha2_password), stricter SQL mode default ( ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION), and ~40 new reserved words (concepts/mysql-reserved-words-upgrade-break). Canonical framing: each class is independent, each needs its own audit, and skipping any one risks a post-upgrade outage on the uncovered axis. Closes with the zero-downtime alternative — online database import to PlanetScale — quoting AWS's own documentation that in-place RDS upgrades "require downtime" even under Blue/Green.

  • — Andres Taylor (PlanetScale / Vitess core, 2022-06-24) canonicalises the framing that MySQL is much faster than Vitess at every relational operator MySQL supports: "we want to delegate as much work as possible to MySQL — it is much faster than Vitess at doing all the database operations, including grouping and aggregation. When possible, we want work to be done there." This is the canonical wiki datum for why the VTGate query planner's pushdown discipline is aggressive: MySQL's local execution engine has 20+ years of optimisation (storage locality, index-only scans, covering indexes, cache warmth); Vitess's Go-based executors cannot match it on per-row work. The architectural consequence: Vitess tries to transform every query into "as many Routes as possible, pushed to MySQL in parallel" — the cross-shard operators (join, global aggregation, multi-shard sort) run at VTGate only as necessity dictates. Also canonicalises MySQL as the pushdown target for the local- global aggregation decomposition primitive — each shard's MySQL runs the local count(*) / SUM / GROUP BY; VTGate runs the global merge. And for the push-aggregation- under-join rewrite, MySQL runs the pre-aggregation step on each join side locally before VTGate executes the nested-loop join across shards.

  • — Aaron Francis's second canonical wiki ingest (after charset + collation) canonicalises the generated hash column pattern: hash a wide or long value into a fixed-width generated column (url_md5 BINARY(16) AS (UNHEX(MD5(url)))), index the hash, and use the compact index for strict-equality lookups on values too large to index directly (TEXT/BLOB can only be prefix-indexed). The multi-column variant (patterns/composite-hash-uniqueness-constraint) uses CONCAT_WS + UNIQUE INDEX to enforce tuple-uniqueness at commit. Canonicalises the redundant condition framing (add url = ? alongside url_md5 = UNHEX(MD5(?)) to eliminate MD5 collision risk while keeping the index seek) as patterns/redundant-hash-plus-value-predicate. Canonicalises functional indexes (MySQL 8.0.13+) as implicit virtual-generated-columns with no syntactic difference at runtime. Load-bearing non-obvious points: BINARY(16) over CHAR(32) because byte-equality doesn't care about character set / collation; hashing here is a query-performance primitive, explicitly not a security primitive ("this technique has nothing to do with securely storing passwords"); CONCAT_WS over CONCAT to avoid tuple-boundary ambiguity and NULL propagation. Engine-agnostic MySQL reference content — no PlanetScale internals disclosed.

  • — canonical wiki introduction of the declarative schema management pattern against MySQL, via the third-party Atlas CLI tool. Brian Morrison II's 2022 PlanetScale tutorial frames schema-as-code as "Database as Code" (direct analogy to Infrastructure as Code) and walks through the two-command atlas schema inspect → edit HCL → atlas schema apply flow. Canonical new concepts/schema-as-code concept. The post flags that PlanetScale's safe migrations feature must be disabled to use Atlas — declarative tools and vendor-managed schema-change pipelines are rivals, not complements. Tutorial voice; no MySQL-specific internals disclosed (INSTANT / INPLACE envelope selection, rename-detection heuristics, charset-change handling in the diff engine — all elided).

  • sources/2026-04-21-planetscale-consensus-algorithms-at-scale-part-7-propagating-requests — canonical wiki disclosure of MySQL's native support for the patterns/external-metadata-for-conflict-resolution pattern. Sugu Sougoumarane canonicalises MySQL's binlog as carrying two pieces of per-transaction metadata sufficient to resolve most propagation-race ambiguities without protocol round-trips: "(1) A Global Transaction ID (GTID), which includes the identity of the leader that created the transaction. (2) A timestamp." This metadata is faithfully propagated to all replicas — which formally violates Sugu's per-request-new-version rule (patterns/version-per-request-to-resolve-conflicts, rule 3: propagation assigns a new version rather than preserving the original). The residual-correctness gap is closed operationally rather than formally through anti-flapping rules in the external elector (Orchestrator / VTOrc). Canonical wiki datum: "organizations have been able to avoid split-brain scenarios while running MySQL at a massive scale" — the combination of GTID + timestamp metadata + Orchestrator anti-flapping is empirically sufficient for large-scale deployments despite not being formally correct. Architecturally: the MySQL substrate + external- coordinator + anti-flapping stack is the canonical production instance of the [[patterns/external-metadata- for-conflict-resolution]] pattern. Reading MySQL into the consensus framework: MySQL provides durability + replication + metadata; Orchestrator provides failover + anti-flapping; Vitess layers VTOrc (fork of Orchestrator) + etcd lock for the full large-scale consensus stack.

  • — canonical wiki introduction of MySQL's character set and collation semantics. Aaron Francis (PlanetScale) canonicalises three primitives: (1) the four-level inheritance hierarchy (column > table > database > server default, with most-specific-wins); (2) the collation suffix taxonomy (_ai / _as / _ci / _cs / _ks / _bin) — string-comparison sensitivity is a collation property, demonstrated with the COLLATE cast operator returning 1 vs 0 for "MySQL" = "mysql" under insensitive vs sensitive collations; and (3) the canonical MySQL utf8-is-not-UTF-8 trap — utf8 charset has MAXLEN=3 and only covers the Basic Multilingual Plane (no emoji, no supplementary-plane CJK), utf8mb4 is the real 4-byte UTF-8 and the MySQL 8 default. First wiki canonicalisation of the information_schema.character_sets catalogue (41 charsets with DEFAULT_COLLATE_NAME + MAXLEN) and the SHOW CREATE TABLE diagnostic primitive for discovering resolved charset/collation. PlanetScale-published pedagogy; no PlanetScale-specific machinery — the content applies to any MySQL 8 deployment. Complements the same-publication-day

which shipped Vitess 21's Online DDL charset-change handling via programmatic text conversion rather than CONVERT(... USING utf8mb4) — the Aaron Francis post is the why, the Vitess 21 primitive is the how.

  • sources/2024-04-29-canva-scaling-to-count-billions — MySQL RDS as the v1 store for Canva's usage-counting pipeline; hit vertical- scaling wall and was replaced by DynamoDB (raw events) + Snowflake (analytics).
  • sources/2026-04-16-cloudflare-deploy-postgres-and-mysql-databases-with-planetscale-workers — MySQL (via Vitess) as one of the two engines behind PlanetScale's Cloudflare-provisioned managed-database offering. Accessed from Workers through the Hyperdrive binding — same binding shape as the Postgres path. Canonical instance of patterns/partner-managed-service-as-native-binding on the MySQL side. The article doesn't show MySQL code snippets (every example is Postgres via pg), but the integration is framed as co-equal to Postgres.

  • — MySQL's InnoDB storage engine as the canonical instance of a clustered-index database: every table is a B+tree keyed on the primary key, with row data in the leaves; secondary indexes are separate B+trees keyed on the indexed column with primary-key values in the leaves. Central architectural claim: primary-key choice determines the on-disk layout of the table, so sequential primary keys beat random UUID keys on three axes (insert-path locality, split write-amplification, range-scan I/O shape). First canonical PlanetScale first-party source on the wiki.

  • — MySQL's InnoDB row-level locking as the mechanical cause of the hot-row problem for counter workloads. UPDATE counters SET count = count + 1 WHERE id = 1 under bursty parallel writes against the same entity serialises every writer on one row's lock_mode X locks rec but not gap — surfaced by SHOW ENGINE INNODB STATUS\G as TRX HAS BEEN WAITING N SEC FOR THIS LOCK TO BE GRANTED. Canonical workaround: the slotted counter pattern — split one row into N rows keyed by (record_type, record_id, slot), pick RAND() * 100 per write (via MySQL's INSERT ... ON DUPLICATE KEY UPDATE count = count + 1), sum on read. Pattern originated at GitHub (the article's diagnostic snippet names index PRIMARY of table github.downloads) and travelled with PlanetScale's founders.

  • — MySQL as the reference C++ implementation that Vitess's evalengine VM must match bug-for-bug on SQL semantics. Vitess's differential fuzzer runs every random SQL expression against three implementations — Vitess's AST interpreter, Vitess's new VM, and MySQL's native C++ evaluator — and has routinely surfaced bugs in MySQL itself that Vitess upstreams: collation bug PR 602, insert SQL function PR 517, substring PR 515. First wiki datum on an external reference implementation being a fuzz-oracle participant whose bugs get discovered by the system using it as ground truth. Benchmarks on the same post show Vitess's pure-Go VM catching up with MySQL's C++ expression engine (geomean −43.58% sec/op for MySQL vs old Vitess AST baseline, vs −48.60% for the new Vitess VM — the Go VM is faster than MySQL C++ on 4 of 5 benchmarks). First canonical Go-vs-C++-on-database-internals benchmark disclosure on the wiki.

  • sources/2025-07-08-planetscale-caching — Ben Dicken names MySQL's InnoDB buffer pool as one of the two canonical database-tier caching instances: "MySQL does a similar thing with the buffer pool. Like Postgres, this is an internal cache to keep recently used data in RAM." Canonicalises the philosophical split between MySQL's single-layer design (commonly run with O_DIRECT bypassing the OS page cache) and Postgres's explicit double-buffered design over shared_buffers + OS page cache. The Dicken framing also canonicalises the ACID-aware caching argument — "more complex than a 'regular' cache as they also have to be able to operate with full ACID semantics and database transactions" — applicable equally to both engines.

  • — MySQL as the target engine for Vitess VReplication's zero-downtime petabyte-scale migration machinery. The post is the canonical wiki walkthrough of the exact MySQL primitives Vitess composes to perform online database imports: LOCK TABLES ... READ (millisecond-scale), START TRANSACTION WITH CONSISTENT SNAPSHOT, @@global.GTID_EXECUTED, COM_BINLOG_DUMP_GTID, the clustered-index-ordered streaming read, binlog retention as an operational constraint on long copies, and the MoveTables SwitchTraffic cutover with VTGate-layer query buffering + reverse-replication-workflow creation for rollback. Canonicalises MySQL's mature GTID + binlog-replication stack as the mechanical enabler of what Vitess can do for MySQL that PlanetScale is re-deriving from scratch for Postgres. First canonical wiki MySQL-side description of an end-to-end zero-downtime migration mechanism at petabyte scale.

  • — canonical wiki MySQL-side description of how Vitess's Consistent Lookup Vindex uses native MySQL primitives (three independent connections, SELECT ... FOR UPDATE row-locks, INSERT ... ON DUPLICATE KEY UPDATE duplicate-key error handling, commit ordering) to maintain consistent user-facing query results across a sharded data table + Vindex lookup tables without running 2PC. Canonical wiki instance of patterns/ordered-commit-without-2pc — the Vitess pattern of reserving Pre / Main / Post MySQL connections per DML and committing them in a fixed order, with rollback in the same order on failure. The identity-update no-op optimisation exists specifically to avoid a DML deadlocking against itself across its own Post-connection delete and Pre-connection insert, both holding InnoDB row-level X locks on the same lookup-table row. First wiki datum on how a sharding layer composes over MySQL's native row-locking + unique-constraint primitives to engineer around the cross-shard transaction cost.

  • sources/2026-04-21-planetscale-anatomy-of-a-throttler-part-2 — canonical wiki introduction of MySQL replication- lag heartbeats as the dominant lag-measurement technique in the MySQL world. Shlomi Noach: "The most reliable way to evaluate replication lag is by injecting timestamps on a dedicated table on the Primary server, then reading the replicated value on a replica, comparing it with the system time on said replica." pt-heartbeat named as the canonical tool. Highlights the binlog-volume cost as the principal production downside — "It is not uncommon to see MySQL deployments where the total size of binary logs is larger than the actual data set" — and canonicalises the failover-aware write-only-on-primary requirement. See concepts/replication-heartbeat for the full treatment. MySQL's binlog shape is what makes the heartbeat technique both reliable (every failure mode surfaces correctly) and expensive (every heartbeat event replays on every replica + is backed up).

  • — canonical wiki MySQL-side substrate for the expand-migrate- contract pattern. Taylor Barnett walks the six-step sequence (Expand → Dual-write → Backfill → Read-new → Stop-old-writes → Delete) using MySQL-flavoured DDL (ALTER TABLE repo ADD COLUMN star_count INT;) and the MySQL 8.0 invisible-column feature as the Step-6 deprecation-discovery primitive. First wiki canonicalisation of MySQL 8.0's invisible- column feature, positioned as "the column still exists and participates in explicit-column queries; it's skipped by SELECT *" — a schema-migration safety tool that inverts the "who's still reading this column?" discovery flow by silently hiding the column from unqualified reads while explicit name-based reads still work. Also canonicalises the five-reason structural argument against coupled schema-and-app deploys (concepts/coupled-vs-decoupled-database-schema-app-deploy) — two critical systems with independent deploy clocks cannot deploy atomically, migration duration scales with data size (seconds → days), and forcing decoupled deploys yields backward compatibility as a construction property rather than a design goal. PlanetScale's table-rename warning surfaces as the query-telemetry- as-deploy-safety signal at the Vitess-level (PlanetScale implements renames as add-column + backfill

  • drop, enforcing the pattern at the platform level).

  • sources/2026-04-21-planetscale-behind-the-scenes-how-schema-reverts-work — canonical wiki MySQL-side description of the pt-online-schema-change / gh-ost / VReplication-driven online-DDL shape and the MySQL primitives that make it work. Guevara + Noach frame all online-DDL tools as instantiations of the same four-step shadow- table online schema change: copy schema to a shadow table, apply DDL to shadow, backfill + track incoming changes, cut over. The post names the exact MySQL primitives: START TRANSACTION WITH CONSISTENT SNAPSHOT for each copy batch (concepts/consistent-non-locking-snapshot), MySQL GTID as the portable position marker for copy/catch-up interleaving, a brief write-locked freeze point at swap time so the source and shadow are declared equivalent at a specific GTID, then a table rename. Canonicalises the PlanetScale-specific instant-revert extension as rest­ing on one MySQL property (the binlog is bidirectional enough to drive an inverse replication stream) plus one Vitess property (VReplication doesn't terminate after cut-over, so the inverse stream can be primed without a fresh snapshot). First wiki description of the online-DDL family as a category and of MySQL's primitive-level support for it.

  • — Matt Lord's post canonicalises the OLTP / OLAP framing as the architectural motivation for CDC pipelines around MySQL: "Vitess and MySQL are ideally suited for use as an Online Transaction Processing (OLTP) system — where the end-user interacts directly with the system and fast response times are essential … They are not optimized for Online Analytical Processing (OLAP) workloads and other use cases and needs that you will encounter as your product, company, and data needs grow." Canonical wiki framing of MySQL as the operational-truth tier that CDC pipelines pull from, with the target-tier being analytics warehouses / data lakes / integration targets. Also canonicalises the unified- change-stream-across-shards framing: engine-native MySQL CDC tools (Debezium MySQL connector, direct binlog tail) are single-shard-blind when the source is sharded Vitess, so consumers must use the Vitess-native VStream driver. First wiki datum on the sharding-layer-owns-the-change-stream structural rule for sharded MySQL fleets.

  • — Canonical disclosure of MySQL unique-index enforcement as the final arbiter of uniqueness against a racing Rails application-layer validation. PlanetScale's own database_branch_password table carries t.index ["database_branch_id", "display_name"], name: "idx_branch_id_display_name", unique: true; the accompanying Rails validates :display_name, uniqueness: { scope: [:database_branch_id] } is advisory only — a SELECT-then-INSERT sequence with no lock between the two steps. Under concurrent request arrival, both threads' validations pass, both proceed to INSERT, and MySQL's unique index rejects one at commit with AlreadyExists. Canonicalised as patterns/database-as-final-arbiter-of-uniqueness and the check-then-act race hazard class. Error volume disclosed at PlanetScale-internal-staff workload scale: "a few 10s to a few hundred per day".

  • — canonical operational datum for MySQL under serverless-PHP via Bref + Laravel. Ships the Lambda-specific SSL trust-store path gotcha (MYSQL_ATTR_SSL_CA=/opt/bref/ssl/cert.pem inside Lambda vs /etc/ssl/cert.pem on the host) as a first-class developer- experience issue, and names the per-request TLS handshake as the dominant latency cost under shares-nothing PHP (0.3 ms PlanetScale query time vs 75 ms request p50). Pair with Laravel Octane + OCTANE_PERSIST_DATABASE_SESSIONS=1 to recover 5.4× via persistent connections. See concepts/ssl-handshake-as-per-request-tax, concepts/shared-nothing-php-request-model, and patterns/persistent-process-for-serverless-php-db-connections.

  • — Canonical longevity + battle-hardening framing for MySQL. Sam Lambert (PlanetScale CEO, 2023-06-28) closes the data-safety envelope post with "MySQL has been serving mission-critical applications at web scale for 28 years. Layering on Vitess, which has served some of the largest sites on the planet for over a decade, you know that every code path has been battle hardened. Database storage engines take a long time to get right. If you are trusting a storage engine that has been around for less than a decade, you are taking extreme risk with your most important asset: your data." Canonicalises 28-year-code-path-age as the empirical evidence for MySQL (i.e., InnoDB's) durability claim, and frames the less-than- a-decade threshold as "extreme risk" — an implicit positioning argument against 2023-era NewSQL peers (Spanner, CockroachDB, TiDB, Yugabyte, Aurora MySQL/Postgres). See the new canonical wiki concept concepts/storage-engine-maturity-as-data-risk for the full framing.

Seen in: semi-sync split-brain

  • sources/2026-04-21-planetscale-consensus-algorithms-at-scale-part-6-completing-requests — Sugu Sougoumarane calls out two concrete production hazards in the MySQL semi-sync replication protocol relative to the generic two-phase completion protocol: (1) replicas "apply-on-receive" rather than holding the write as a tentative record — there is no cheap cancellation path if the write never reaches durability; (2) a primary restarting after a crash "completes all in-flight requests without verifying that they received the necessary acks" — producing semi-sync split-brain where writes that were never durable are observable to some replicas. Vitess on top of MySQL manages the hazard operationally via reparenting (PRS / ERS), vttablet lameduck drain, and vtgate query buffering.

Seen in — EXPLAIN as operational inspection surface

  • — Savannah Longoria's 2023 PlanetScale pedagogical deep-dive on reading MySQL's EXPLAIN output column-by-column. Canonical wiki source for what each EXPLAIN column means and how to use them together to verify index usage. Introduces four new wiki concept pages (mysql-explain, mysql-explain-analyze, mysql-access-type, composite-index) and the composite- index-for-AND-predicate pattern with its canonical worked example: a WHERE last_name='Puppo' AND first_name='Kendra' query on the MySQL Employees sample database flips from type: ALL, rows: 299,202 to type: ref, rows: 1 after CREATE INDEX fullnames ON employees(last_name, first_name) — a 300,000× reduction in rows examined. Also canonicalises EXPLAIN ANALYZE (MySQL 8.0.18+) as the execution-profiling variant that runs the query to produce real per-iterator timings (with the "do not use on production" warning), the type column's colour-coded access-method ladder (green NULL/system/const/eq_ref/ref → yellow fulltext/index/range → red ALL), and key_len as the only way to detect partial composite-index usage. Naturally complements (what are indexes?) and (how are they stored?) — Longoria's post is the "how do you inspect whether they're being used?" field manual.

Seen in — offset-pagination cost model

  • — Mike Coutermarsh's 2022 PlanetScale post canonicalises three new MySQL-flavoured concept pages on this wiki: offset-pagination cost (cost of LIMIT N OFFSET M scales with M + N, not N), deferred join (the index-only key-selection + primary-key-hydrate rewrite documented in High Performance MySQL), and cursor pagination (the strictly-dominant alternative that loses skip-to-page-N UX). Introduces fast_page — a PlanetScale-authored Rails gem that mechanically applies the deferred-join rewrite via a single .fast_page method on ActiveRecord::Relation — and its companion pattern pair: patterns/deferred-join-for-offset-pagination (the SQL- level recipe) + patterns/conditional-optimization-by-page-depth (the apply-only-past-the-crossover meta-recipe). Benchmark: 1M-row AuditLogEvent table, LIMIT 25 OFFSET 100 falls from 1,228.7 ms → 457.3 ms (2.7×) after rewrite; a 2000-page sweep shows the deferred-join line staying near- flat while the baseline climbs superlinearly. The optimisation exploits InnoDB's clustered-index layout: secondary-index leaves store only primary keys, so the first query walks the created_at index without hydrating rows, and only the surviving 25 primary keys pay the clustered-index descent cost. Cross-ecosystem packaging: Aaron Francis / Hammerstone's Laravel fast-paginate gem is the 2022 cousin; both are ORM-level wrappers over the same SQL rewrite.

Seen in — binlog + GTID as HA-CDC decoupler

  • sources/2026-04-21-planetscale-postgres-high-availability-with-cdc — Canonical wiki disclosure of MySQL's binlog + per- transaction GTIDs + replica re-emission as the substrate that decouples HA from CDC. Sam Lambert (PlanetScale CEO, 2025-09-12) frames MySQL's binlog as an action log (every transaction carries a GTID; log_replica_updates=ON replicas re-emit applied transactions into their own binlogs preserving GTID continuity) and contrasts it with Postgres's state-log-plus- primary-local-slot shape. Canonical MySQL failover semantics: "Promote a replica. Point the connector at any replica and it resumes from it's GTID position." Success is determined by binlog retention, not by CDC-subscriber freshness; "A lagging consumer can't stall switchover." Canonical wiki structural datum: MySQL has no slot object, no eligibility gate, and no per-subscriber server- side progress state — the consumer persists its own GTID cursor, and any replica is a valid resume point. See patterns/action-log-vs-state-log-replication for the generalised design-space framing and concepts/ha-cdc-coupling for the canonical coupling that MySQL's design avoids.

Seen in — TAOBench social-graph benchmark stress

  • — MySQL as the substrate under test for a social-graph-shaped benchmark. Liz van Dijk (PlanetScale, 2022-09-08) runs TAOBench — Audrey Cheng's VLDB 2022 benchmark modelling Meta's production TAO workload — against a Vitess-on-MySQL cluster. TAOBench's objects + edges schema (concepts/social-graph-objects-and-edges) deliberately stresses MySQL/InnoDB's hot-row behaviour under viral-content load — a different substrate axis than sysbench-tpcc (shard-key-aligned, no hot rows). Van Dijk's load-bearing framing: "On the database level, beyond a sudden surge in connections, this can also translate into various types of locks centered around the backing rows for that piece, which can have rippling effects that ultimately translate to slower content access times for the users on the platform." This is the canonical MySQL-side framing of what happens to InnoDB row-locks under viral-content concurrency — the same pathology the slotted counter pattern addresses for the counter instance. TAOBench is the benchmarkable generalisation — a whole workload shape (not just a counter) that stresses the same substrate property.

Seen in — Foreign-key constraint support via Vitess-owned logic

  • — Shlomi Noach + Manan Gupta (PlanetScale / Vitess, 2023-12-05) canonicalise the MySQL FK rule set as MySQL-specific: parent table must exist, referenced columns must be indexed in-order, child/parent column types must match (with the interesting exception that VARCHAR(32) child / VARCHAR(64) parent is allowed), all enforced by FOREIGN_KEY_CHECKS=1. Canonicalises that FK enforcement is an InnoDB primitive, not a MySQL-server-layer feature — the server-level FK effort was abandoned when InnoDB became MySQL's default engine, and this architectural split is the load-bearing reason for cascaded child changes never reaching the binlog. The post is the wiki's canonical home for concepts/foreign-key-constraint and names the surrounding MySQL + InnoDB limitations: cyclic FKs forbidden in Vitess, ANSI-SQL constraint-name uniqueness (constraint names change on every deployment under shadow-table Online DDL), RENAME TABLE following the parent-table pointer rather than the name by default (addressed by rename_table_preserve_foreign_key in PlanetScale's MySQL fork). First canonical wiki citation of the planetscale/mysql-server fork as a distinct-from-Vitess PlanetScale asset. The MySQL- side Online DDL story (shadow tables
  • atomic rename + the need for FK-exempt tables) becomes FK-compatible only via the two fork patches plus Vitess-level cascade orchestration; vanilla MySQL remains unable to Online- DDL an FK-carrying table.

Seen in — per-query index-usage telemetry via handler-hook sidecar

  • — Rafer Hazen (PlanetScale, 2024-08-14) canonicalises MySQL's storage-handler API as the narrow waist between MySQL's engine-agnostic executor and engine-specific storage implementation, and identifies it as the right intercept point for per-query index-usage telemetry. The post's mechanism hooks the InnoDB handler's index_init() callback, records the index name in a per-query data structure, and bolts the datum onto the MySQL wire-protocol response packet returned to the client. Verbatim: "When the query is finished we return the list of used indexes in the final packet returned by MySQL to the client, and ultimately to VTGate, Vitess's query proxying layer." Three MySQL-specific properties this surfaces: (1) the handler API is a stable callback surface every executed query flows through, so hooking once suffices for 100% coverage; (2) the MySQL wire protocol already carries a final response packet — extending it with a string list of used indexes is zero-round-trip and "negligible overhead"; (3) the neighbouring primitive — performance_schema.table_io_waits_summary_by_index_usage — has two documented gaps the post quotes verbatim: global counters reset on MySQL restart (no time series), and only per-(table, index) granularity (no query-pattern attribution). Insights closes both gaps by capturing at the handler layer per execution rather than polling aggregated counters. Canonical wiki framing: MySQL's handler API is a first-class telemetry substrate, adjacent to but distinct from performance-schema polling
  • slow-log capture + EXPLAIN-plan inspection. The SELECT-only caveat follows from the capture-point choice — the handler hook only fires on read paths that declare the index to InnoDB; DML index walks during predicate evaluation and constraint enforcement remain covered by the performance-schema primitive.
Last updated · 542 distilled / 1,571 read