Sharding

Databases ran on one machine for a long time. When things got slow you gave that machine more memory and disk, and when reads piled up you attached a few servers holding a copy of the same data and sent reads to them.

Two problems don't yield to that. One is capacity: when a single document table reaches 800 million rows and fills two terabytes, you can buy a larger disk, but there is a ceiling on what one machine handles. The other is writes. However many copies you attach, one primary machine still does all the writing.

In practice the decline is quiet. As a table grows, the index that made lookups fast stops fitting in memory, and inserting a single row starts taking longer. A save that took 0.02 seconds yesterday takes 0.4 seconds today, and to a user that reads as a sluggish save button rather than a database problem.

Horizontal scaling — handling load by adding machines rather than enlarging one — had been the standard move on the application side for years. Sharding applies it to data. Instead of growing one machine, you cut the data up and spread it, so each server is responsible for its own piece rather than the whole.

Five steps, and the first two decide the rest.

1. Choose a shard key: the column consulted to decide which piece a row belongs to. For a document table the candidate is the organization ID; for a member table, the member ID. There is one criterion — do most queries arrive already knowing that value? If plenty of queries show up with only a document ID, every piece has to be searched, which undoes most of the benefit before you start.
2. Choose how to divide. Cutting by ranges (IDs 1 to a million on piece one) is easy to reason about but crowds recently created organizations onto one piece. Hashing the value spreads rows evenly but gives up queries that scan a range. Dividing by a fixed list, such as region or plan, is a third option.
3. Build the layer that finds the piece. Something has to decide which server a query goes to: either compute it from the shard key, or keep a lookup table recording which values live where and ask it. The lookup table makes moving pieces later much easier.
4. Move the data you already have. To do it without downtime, split the migration into four moves — write new rows to both the old server and the new pieces, copy historical data in the background, compare counts and values on both sides, and only then point reads at the new pieces.
5. Prepare the operational side. Eight pieces means eight backups and eight sets of dashboards. Write down in advance how you will add pieces and rebalance when one of them grows too large again, because doing that improvised while the service is live is where sharding tends to go wrong.

The fit is narrow: you have actually hit the capacity or write throughput of one machine, the data divides along a natural boundary, and most queries finish inside one piece. A collaboration tool used by organization suits those conditions, since reading one organization's documents never involves another's, and the boundary is already visible in every query the product sends.

Four limits come with it.

1. Queries that cross pieces get harder. A total usage figure or a search spanning two organizations has to ask each piece and combine results, so what was one query becomes eight.
2. A transaction — the bundle that either fully commits or fully unwinds — only holds inside a single piece. Work spanning two pieces can end with one side committed, and the application has to handle that case itself.
3. A badly chosen shard key concentrates load. Divide by organization and one large customer holding 30 percent of all documents makes that one piece struggle, which leaves you where you were before splitting.
4. It's hard to undo. The shard key ends up embedded not just in data placement but across the code that builds queries.

So there's an order to try things in. First tidy the indexes — the prepared lookups that make searches fast — then keep frequently used results somewhere separate, then move old data elsewhere. Sharding comes out when those three aren't enough.

"This table is sharded by organization ID, so any cross-organization statistic has to be assembled separately."

Aggregations that cross pieces need their own path.

"Shard by member ID and one large customer lands entirely on a single piece."

The key choice is what wrecks or saves the distribution.

"Let's hold on with index work and revisit sharding next quarter."

Cheaper options remain, so it isn't time for this method yet.

At Pagedeck only the document table is sharded, into eight pieces by organization ID. The member and permission tables are small enough to stay on one machine. The reason for splitting was that document saves got slow, and the reason for keying on organization ID is that nearly every screen that reads documents already knows which organization it is in.

The move took three weeks. Theo ran writes to both sides for two of them, reconciling counts daily, before switching reads over. What's left is the total usage statistic: values scattered across the pieces get gathered into one table nightly and the screen reads that table, so the number is current as of yesterday rather than live — traded for the save latency.

The most common mistake is expecting the database to get faster across the board. Eight servers sounds like eight times the speed. What gets faster is queries that finish inside one piece. Queries crossing pieces now ask eight servers and merge the answers, which is slower than before. Miss that and you ship a system where saving a document got faster while search went from two seconds to six, and the complaints arrive about the part you thought you had improved.

The belief that you can always do it later also misses. Deferring is usually the right call, since splitting early only adds things to manage. The catch is that the cost of deferring grows: moving 800 million rows and moving three billion differ in copy time, and in how many queries the codebase has accumulated in the meantime. So teams defer the execution but settle the shard key early. Fixing which column divides the data means every new query can be written to carry that value, which is less code to change when the split finally happens.