Month: June 2026

HELL — Satan confirmed this week that he was, in fact, responsible for raisins.

Speculation had run rampant for thousands of years of human history regarding the origin of the alleged "dried grapes," but the Father of Lies held a press conference on Tuesday to take full credit.

"Ooooh, yeah. That was me," the Prince of Darkness said. "I did that. Those little tiny BBs of gritty, overly sweet nastiness? Yep. Totally me. I thought about what I could inflict on the Earth and its inhabitants that would unleash maximum frustration and disgust, and it just sort of came to me."

The Devil admitted to reporters he spent years trying to think of something that would be more universally hated, admitting that he originally tried to come up with an odious snack, something like dried seaweed, but realized that the hippies had cornered that market years ago.

"Then one day it came to me: let’s take grapes, which everyone loves, and dry the heck out of them, and then sell them in little boxes and market them as healthy snacks. It was a devilish idea, if I do say so myself. Pun very much intended."

Satan said it was also he who came up with the idea to put raisins in oatmeal cookies and make them look like chocolate chips.

"Yes, yes, that was a good one. No one even saw it coming. I still can’t believe I pulled it off."

At publishing time, Satan had also taken credit for mosquitos, people talking on their speakerphones in public, meetings that could have been emails, and The View.

Every hour a racist loses hope, will you help the Southern Poverty Law Center to help a racist in need?

Click to watch the latest sketch!

-- Every hot-path query carries customer_id, so it routes to one shard.
SELECT * FROM orders
WHERE customer_id = 88213        -- shard key: single-shard lookup
  AND status = 'shipped'
ORDER BY created_at DESC;

The trade-off to accept consciously is that cross-shard-key queries become expensive. Analytics that aggregate across all customers, admin searches by email, or reports spanning every tenant cannot be answered from a single shard. The pattern is not to avoid these but to serve them differently: route analytical queries to a separate columnar store or data warehouse, maintain secondary lookup tables for the few alternate access paths you truly need, and accept that the operational database is optimized for its dominant access pattern, not every possible one.

Even if you are not sharding today, choosing and consistently populating a shard key now means that when you do shard, it is a routing change rather than a schema migration across billions of rows.

Pattern 3: Denormalize Deliberately to Kill the Join Fan-Out

Normalization is the right default. It prevents update anomalies and keeps each fact in exactly one place. But the relational join—the mechanism that makes normalization work—is precisely the operation that does not partition cleanly. A join between two tables on different shard keys forces a cross-shard operation. Even on a single server, a query that joins five tables to render one screen multiplies the work and the lock surface.

The scalability pattern is targeted denormalization: duplicate the specific fields that hot-path queries need so those queries can be answered from a single row or a single table, without joins.

The discipline here matters. Indiscriminate denormalization creates a maintenance nightmare where every update has to touch a dozen copies. The pattern is to denormalize only the fields that appear together in your highest-volume read paths, and to be explicit about which table owns the source of truth versus which holds a cached copy.

-- Instead of joining orders -> customers on every order list view,
-- store the small, slowly-changing customer fields the list needs.
CREATE TABLE orders (
    id              BIGINT UNSIGNED NOT NULL,
    customer_id     BIGINT UNSIGNED NOT NULL,
    customer_name   VARCHAR(120) NOT NULL,   -- denormalized copy
    customer_tier   TINYINT NOT NULL,        -- denormalized copy
    total_cents     INT UNSIGNED NOT NULL,
    created_at      DATETIME(3) NOT NULL,
    PRIMARY KEY (customer_id, id)
) ENGINE=InnoDB;

When the source data changes (a customer renames their account), you update the copies asynchronously—via application logic, a change-data-capture pipeline, or a background job. The key insight is that for many fields the consistency requirement is “eventually,” not “immediately,” and trading a small consistency window for join-free reads is exactly the trade that buys linear read scalability.

A common and powerful variant is the read model or materialized view table: a table whose sole purpose is to serve one expensive query shape, populated by aggregating or flattening normalized source data. The write path stays normalized; the read path gets a purpose-built, join-free table.

Pattern 4: Split Tables Vertically to Keep Hot Rows Small

InnoDB reads and writes data in 16 KB pages. The narrower your rows, the more rows fit per page, the more of your working set fits in the buffer pool, and the less I/O each query costs. A wide table—dozens of columns including large TEXT, JSON, or BLOB fields—wastes buffer pool space on data that most queries never touch.

Vertical partitioning splits one wide table into a narrow “hot” table and one or more “cold” tables, joined by the same primary key. The columns accessed on every request stay in the slim hot table; the rarely-read bulk moves to a companion table.

-- Hot table: tiny rows, queried constantly, fits entirely in memory.
CREATE TABLE users (
    id            BIGINT UNSIGNED PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    status        TINYINT NOT NULL,
    last_login_at DATETIME(3),
    UNIQUE KEY (email)
) ENGINE=InnoDB;

-- Cold table: large, rarely-read fields kept out of the hot path.
CREATE TABLE user_profiles (
    user_id     BIGINT UNSIGNED PRIMARY KEY,
    bio         TEXT,
    preferences JSON,
    avatar_blob LONGBLOB,
    CONSTRAINT fk_profile FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

This pattern is especially valuable for tables that mix frequently-updated columns with large static ones. Updating a single counter on a row that also holds a megabyte JSON document means InnoDB may rewrite far more than the changed bytes and generate large undo and redo records. Separating volatile small columns from stable large ones reduces write amplification and replication payload, both of which directly affect how far you can scale writes.

Pattern 5: Use Native Partitioning for Time-Series and Lifecycle Data

Not all scaling is horizontal across servers. A single MySQL instance can manage far larger tables when the table is partitioned internally so that queries and maintenance touch only the relevant slice. MySQL’s native PARTITION BY RANGE on a date or sequential ID is the canonical pattern for time-series, event, and log-style data.

The benefits compound at scale. Queries with a date predicate undergo partition pruning—the optimizer skips every partition outside the range, so a query over last week’s data never scans last year’s. Equally important, dropping old data becomes instant: ALTER TABLE ... DROP PARTITION is a near-metadata-only operation, compared to a DELETE that would scan, lock, and log millions of rows and bloat the table.

CREATE TABLE events (
    id          BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    event_type  VARCHAR(40) NOT NULL,
    created_at  DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)          -- partition column must be in the PK
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2026_05 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p2026_06 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p2026_07 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION pmax      VALUES LESS THAN MAXVALUE
);

Two constraints shape this pattern. The partitioning column must be part of every unique key, including the primary key—hence (id, created_at) above. And partition maintenance (adding next month’s partition, dropping the oldest) should be automated, because a partitioned table that runs out of defined ranges or accumulates unbounded partitions reintroduces the very problems it was meant to solve. Treat partition rotation as a scheduled operational job, not a one-time DDL.

Pattern 6: Eliminate Write Hotspots and Contention Points

A hotspot is any single row, page, or counter that a large fraction of writes must touch. Hotspots are the enemy of linear scaling because they serialize writes that should run in parallel—no amount of added hardware helps when everything queues behind one lock.

The most common hotspot is the global counter: a single row holding a total that every transaction increments, such as a “likes” count on a viral post or a balance on a shared account. Under load, every writer contends for the lock on that one row.

The fix is the sharded counter pattern. Instead of one row, maintain N rows and have each writer increment a randomly or hash-chosen shard. The true total is the sum across shards, computed at read time (or periodically rolled up).

CREATE TABLE post_like_counters (
    post_id   BIGINT UNSIGNED NOT NULL,
    shard     TINYINT UNSIGNED NOT NULL,    -- e.g. 0..63
    likes     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, shard)
) ENGINE=InnoDB;

-- Writes spread across 64 rows instead of contending on one:
UPDATE post_like_counters
SET likes = likes + 1
WHERE post_id = 991 AND shard = FLOOR(RAND() * 64);

-- Reads aggregate (cache the result if needed):
SELECT SUM(likes) FROM post_like_counters WHERE post_id = 991;

Related hotspot patterns worth internalizing: prefer append-only inserts over in-place updates where the domain allows it, because inserts to ordered keys contend far less than updates to shared rows; avoid status-flag columns that the entire fleet polls and updates in lockstep; and be wary of “queue” tables where every worker hammers the same few “ready” rows—use techniques like SELECT ... FOR UPDATE SKIP LOCKED to spread workers across rows. Each of these is the same lesson: find the shared point and break it into many independent points.

Pattern 7: Index for the Working Set, Not Every Possible Query

Indexes accelerate reads but tax writes—every secondary index must be updated on insert, and in InnoDB each one carries the full primary key as its row pointer. On a write-heavy table at scale, an over-indexed schema can spend more effort maintaining indexes than storing data, capping write throughput.

The scalable approach is to index for your actual hot query shapes and prune the rest. A few principles carry most of the value:

Composite indexes should match query order. An index on (customer_id, status, created_at) serves equality-on-customer-then-status-then-range-on-date queries efficiently, following the leftmost-prefix rule. The column order should mirror how predicates are applied.

Covering indexes eliminate row lookups. If an index contains every column a query needs, InnoDB answers the query from the index alone, never touching the clustered index. For high-frequency queries, designing a covering index is one of the highest-return optimizations available.

Keep secondary indexes narrow, because every one of them stores the primary key. This is another reason fat primary keys (random UUIDs) hurt: they bloat not just the table but every index on it. A compact key keeps the whole index footprint small enough to stay in memory.

Periodically audit for unused and redundant indexes. An index that no query uses is pure write overhead. At scale, removing it can measurably lift write throughput—a rare optimization that costs nothing and helps everywhere.

Pattern 8: Model for Replication and Eventual Consistency from Day One

Linear read scaling on MySQL almost always means read replicas: a primary handles writes, and reads spread across replicas. This works beautifully—until the schema and the application assume read-your-own-writes consistency that asynchronous replication cannot guarantee.

The schema-level pattern is to make data tolerant of replication lag. Avoid designs that require reading a value immediately after writing it on a different node. Where read-after-write matters (a user updating their own profile and expecting to see the change), route those specific reads to the primary, and design the schema so that the set of such “must-be-fresh” reads is small and well-defined rather than pervasive.

This also influences how you model derived data. Counters, aggregates, and denormalized copies (Patterns 3 and 6) are naturally eventually-consistent, which fits replication well. Trying to keep them transactionally exact across replicas reintroduces coordination. Embracing eventual consistency for the data that can tolerate it is what lets the read tier scale out linearly.

A practical corollary: keep transactions short and touch as few rows as possible. Long, wide transactions hold locks longer, generate large binlog events, and widen replication lag—all of which erode the headroom replicas give you. Schema choices that naturally lead to small, focused writes (narrow hot tables, append-only patterns, sharded counters) pay off again here.

Pattern 9: Bound Every Table’s Growth

An unbounded table is a deferred outage. A table that grows forever will eventually exceed the buffer pool, then sensible index sizes, then maintenance windows, until routine operations like adding a column or rebuilding an index become multi-hour ordeals. Linear scalability assumes you can keep adding capacity; an unbounded table eventually makes each unit of capacity more expensive than the last.

The pattern is to design a retention and archival strategy into the schema itself, not bolt it on later. Time-range partitioning (Pattern 5) is the cleanest mechanism: old partitions drop instantly. For data that must be retained but not served hot, move it to cheaper archival tables or external storage on a schedule, keeping the operational table sized to the working set. Define, in writing, how large each high-growth table is allowed to get and what happens when it approaches that limit—before the table forces the answer on you at the worst possible time.

Bringing the Patterns Together

These patterns reinforce one another, and the through-line is consistent. Distribution-friendly primary keys make sharding possible. A well-chosen shard key makes the dominant queries single-shard. Deliberate denormalization and read-model tables remove the joins that would otherwise force cross-shard work. Vertical partitioning and tight indexing keep the hot working set in memory. Native partitioning and bounded growth keep individual tables fast and maintainable. Hotspot elimination and replication-aware modeling remove the serialization points that flatten the scaling curve.

The unifying principle is worth restating because it is the thing to carry into your own design reviews: scalability is the absence of shared bottlenecks. Every pattern here is a way of taking something that wanted to be a single shared point—one counter, one global ID, one wide row, one giant table, one join across everything—and turning it into many independent things that can live on many machines. When you can add a server and the work genuinely spreads, you have linear scalability. When every server still has to consult the same hot resource, you do not, no matter how much hardware you buy.

The expensive truth is that almost all of this is far cheaper to do early. Retrofitting a shard key onto a billion rows, re-keying a clustered index, or splitting a table that the entire application reads from are migrations measured in weeks and risk. Sketching the schema with these patterns in mind from the start costs a few extra hours of thought. That trade—hours now against weeks later—is the best return available in database engineering.

Frequently Asked Questions

Does MySQL scale linearly out of the box? No. MySQL gives you the tools—replication, partitioning, and a flexible schema—but linear scalability is a property of how you model and access your data, not a default behavior. A poorly designed schema will hit a ceiling regardless of MySQL version or hardware.

Should I shard from the beginning? Usually not. Premature sharding adds operational complexity before you need it. The realistic advice is to choose and populate a shard key from the start so that sharding later is a routing change rather than a schema rewrite, while running on a single primary with read replicas until the data or write volume genuinely requires splitting.

Is denormalization always bad for data integrity? Denormalization trades some integrity guarantees for read performance and partitionability. The risk is managed by being explicit about which table owns each fact, denormalizing only hot-path fields, and updating copies through a reliable asynchronous mechanism. For data that tolerates eventual consistency, the trade is usually worth it at scale.

What primary key should I use for a distributed MySQL system? A time-ordered, coordination-free identifier such as UUIDv7, a ULID, or a Snowflake-style 64-bit ID. These preserve insert locality (unlike random UUIDv4) while remaining globally unique without a central counter (unlike plain auto-increment).

When should I use native partitioning versus sharding across servers? Use native partitioning when a single server can hold the data but individual tables have grown large, especially time-series data where partition pruning and instant partition drops help. Shard across servers when a single primary can no longer handle the write volume or total data size, regardless of partitioning.