Sharding Is Hard, So Draw Good Boundaries: Tess Griffin on Shard Keys, Routing, and Proxies
Ruby on Rails
What do you do when your database has outgrown the biggest machine you can buy? Tess Griffin, a senior developer at thoughtbot, works through that question using a fictional multi-clinic healthcare app. Their central claim is that you don't make sharding easy by getting better at its hardest parts, such as cross-shard joins and distributed transactions. You choose boundaries so those parts rarely come up. The talk covers choosing a shard key, assigning and migrating data, routing queries with Rails' built-in sharding or with a proxy like PgDog, and handling the operations that still have to cross shards.
The Scenario: A Database That Can't Get Any Bigger
Griffin opens with a familiar story. An app has run on one database for years, and as it grew, the team did the obvious thing and kept buying a bigger machine. In their hypothetical, the bill has reached $500,000 a month and the database still can't keep up. Queries that used to be instant now crawl, a small disturbance knocks the system over, and a traffic spike sets the whole database on fire without anyone knowing why. A bigger box either doesn't exist or costs too much.
Their first advice is to measure before doing anything drastic. Postgres itself can tell you whether the workload is read-heavy or write-heavy, which tables take the writes, and which tables have grown huge. If a few large tables absorb most of the writes, those tables are the ones to target.
Why Replicas Aren't Enough
If reads are the problem, Griffin says there is a cheaper fix: most read load can leave the primary entirely. A replica is a live copy of the database that serves reads, and you scale reads by adding more of them. Replicas have two limits, though. Every replica still holds every row, and the primary still takes every write. In Griffin's framing, a single SQL database deliberately trades slower writes for fast reads. When writes are the bottleneck, a single database has nowhere else to send them.
Table size is a separate problem. A table can grow so large that everything touching it slows down, including reads, writes, and even indexes. Vertical scaling has already been tried until the next size up didn't exist. The other direction is horizontal scaling: more machines rather than bigger ones. Sharding applies horizontal scaling to the database itself. One database becomes several, the rows are spread across them, and each machine takes a slice of the writes and holds a slice of the rows. Griffin presents this as a fix for both slow writes and giant tables.
The Shard Key and the Cost of Getting It Wrong
To split the data, you choose one column whose value decides where each row lives. That column is the shard key. A query that includes the shard key goes to exactly one shard. A query that doesn't include it must go to all of them. Griffin illustrates this with three shards: one query becomes three, every machine stops to answer, and two of them had nothing to return. If most queries look like that, sharding has bought you nothing.
As the ideal case, Griffin introduces "Napify," a storefront platform whose resemblance to other storefront platforms is, they joke, coincidental. Every table hangs off the shop. If you draw a circle around one shop, nothing sticks out, so the shard key is obviously the shop ID. The few things outside that circle, such as admins, plans, and feature flags, belong to no shop and stay on the primary.
The Case Study: A Clinic App on Fire
The real example is the speaker's fictional healthcare app, introduced with the same joke about originality. It serves hundreds of clinics from a single Postgres database, and nobody considered sharding until now. The tables currently falling over are visits and audit logs. The audit logs are especially large because the app aims to be HIPAA compliant, so every access of data must be logged.
The shard key has to split those two burning tables, and a table can only be split by a column it carries. A visit has four IDs: the patient, the practitioner, the clinic, and its own ID. Griffin treats each as a candidate, tries the cut, and looks at what breaks. On the slides, every red line is a query that lacks the key and therefore visits every shard.
Patient ID is tempting because patients are what the whole app is about. But patients belong to more than one clinic. As a result, half the queries wouldn't know which patient to route by, and the other half don't carry a patient at all.
Practitioner ID has the same kind of problem. Practitioners work at more than one clinic, and almost nothing in the schema connects to a practitioner. Griffin's rule: if almost nothing connects to your key, almost no query knows which shard to go to.
Visit ID, sharding on the burning table itself, would spread the load evenly, and Griffin acknowledges that some systems do this. In this schema, though, a visit owns almost nothing and touches everything. Everything about a visit lives elsewhere, so every query would cross shards. You can split the burning table and still not have picked the best shard key.
Why Clinic ID Wins
Griffin's guiding principle is to shard on the data that is easiest to separate, and in this app that is the clinic. Every write a clinic makes stays inside its circle. Only one relationship crosses the boundary: treatments point at remedies. But remedies are the same for every clinic, so the solution is to copy the remedies table to every shard so queries stay local. Billing has nothing to do with individual clinics, so it stays on the primary.
This cut also splits the fire. Audit logs and visits both carry a clinic ID, so each shard takes only its own clinics' logs and visits, and the load spreads across the new shards. Everything a clinic does happens on one machine. Joins work normally, and transactions are ordinary single-database transactions.
Griffin also points to an isolation benefit. If a query misfires or a shard is compromised, the damage stops at that shard's clinics rather than reaching all of them. On the earlier problem of patients who visit several clinics, Griffin explains that the app wants their records kept separate anyway: each clinic is its own covered entity and keeps its own patient records. And if a customer needs true single-tenant isolation, you can give them a shard of their own.
Assigning Clinics to Shards: Compute or Store
With clinic ID chosen, two jobs remain: moving the data onto the shards and routing every query to the correct one. Both can be done in Rails or delegated to a proxy. A proxy sits between the app and the database and speaks plain SQL, so the app still talks to one thing while the proxy spreads data across shards. For Postgres, Griffin names PgDog, an open-source proxy. For MySQL there is Vitess, with PlanetScale as the managed version. With a proxy, Rails' database.yml points at the proxy instead of directly at Postgres.
Before any data moves, every clinic needs an assigned shard. Griffin describes two ways to do this.
The first is to compute the shard by running the clinic ID through a hashing function. The simplest example is clinic ID modulo N, where N is the number of shards. Griffin warns that you probably don't want this in production, because changing N moves every clinic to a different shard. Whatever hashing function you choose, the property is the same: the function decides where a clinic lives, not you, so you can't move just one clinic. For a deeper treatment of hashing schemes, Griffin recommends Martin Kleppmann's Designing Data-Intensive Applications.
The second is to store the mapping in a table with one row per clinic: a clinic goes in, a shard comes out. The mapping is now data, and Griffin says that is the point, because you choose the placements. If one huge clinic is dragging down its shard, a single update can move it to a shard of its own. The trade-off is that every lookup goes through this table, so if it becomes unreachable, every shard behind it is effectively unreachable too. Griffin notes that the table is tiny and is written to only when a clinic is added or moved. The rest of the talk's code examples use this stored shard map.
Moving the Data
Doing the migration yourself is a lot of work, according to Griffin. You backfill the existing rows, catch up on the writes that arrived during the backfill, and then cut over, and each step is tricky. The copy must be throttled, or the copy itself can take down your database. Griffin calls this a fairly complex part of the project.
The alternative is to hand it to the proxy. With PgDog, you provision empty databases as your shards, create a publication, and run a reshard. PgDog fills the shards while the app keeps running. Griffin is careful to say the steps don't disappear: the backfill, catch-up, and cutover all still happen. What changes is who runs them.
Routing in Rails
Once the data is migrated, each incoming request needs to reach the right shard. Either the application picks, with Rails choosing a shard on every request, or the proxy picks, with the app treating it as one ordinary database.
Rails has supported multiple databases since 6.0 and sharding since 6.1, and Griffin notes that it works the same on Postgres and MySQL. Setup starts with configuration: every shard is an entry in database.yml, each on its own server with its own read replica. One abstract model then wires the shards together, connecting a writer and a reading replica for each, and the tenant-scoped models inherit from it. Remedies are identical on every shard, and non-clinic data like billing stays on the primary.
The core primitive is to ask the shard map which shard a clinic lives on, then wrap the work in connected_to. Everything inside the block runs against that shard. In Griffin's words, Rails answers exactly one question: which shard do I talk to?
Keeping Shard Logic in One Place
The shard key is a single detail, but once you shard, every query needs it, and Griffin warns that this logic tends to spread. You need a clinic's shard in the visits controller, so you open it there. Then the reminder job needs it, so you open it there too, and eventually it is everywhere. When you set up your data for sharding, you have to find every place that touches a clinic, and if each one handles routing on its own, it's easy to miss one.
Griffin's answer is to contain the logic in one place, and they note that Rails already ships configuration for shard support out of the box, which gives you a single place that knows how to find a shard. Going a step further, you can move routing out of the app entirely and onto the proxy: the app hands the proxy the shard key and the proxy does the routing. There is no shard map to maintain and no resolver to write. Griffin describes the result as the app going back to believing there is one database while you still get the benefits of sharding.
Background Jobs
Background jobs follow the same rule. Put the clinic ID in the job's arguments, then add a single around_perform in ApplicationJob that resolves the shard and wraps perform in connected_to. One place decides, and individual jobs never mention shards. If someone enqueues a job without a clinic ID, it raises an error instead of quietly hitting the wrong shard.
With a proxy, every process connects to the same endpoint. You use the same around_perform, but instead of connected_to, it issues a SQL SET LOCAL that sets the sharding key inside a transaction. Griffin flags one detail for Solid Queue: because it is database-backed, its tables should stay on the primary or get their own database, and should not be sharded. The SET LOCAL approach is the same with Sidekiq. Whether you use Rails or a proxy, Griffin's point is that the sharding logic should live in one place.
Outgrowing the Original Shards
When you outgrow the shards you started with, Rails leaves resharding to you. With a hashing function, the function decides what moves. With a stored shard map, you move only the clinics you choose. Griffin argues the table costs more to set up but pays for itself the first time you need more shards. If a very large clinic needs its own shard, you spin one up, point the clinic's row at it, and you're done. A proxy, by contrast, can reshard while the app keeps serving traffic, and since you never wrote a mapping, there is no mapping to get wrong.
Cross-Shard Joins: Scatter-Gather
Because the shard key keeps a clinic's data together, most joins never cross a shard. The ones that must cross can't run as a single query. Instead, you ask each shard and merge the answers yourself, a pattern called scatter-gather: loop over the shards, run the same query on each, and combine the results, for example by adding up counts. A proxy does this automatically by fanning out cross-shard reads. You write one query, and it hits every shard and merges the results.
Cross-Shard Writes and Two-Phase Commit
A few writes do cross shards, and this is where Griffin says the real difficulty lies. On one database, a transaction is atomic: the write is only ever seen as a single action. Griffin states that keeping a transaction atomic across shards in that same sense isn't possible in a distributed system. Once data lives on machines that communicate over a network, you have to choose: either wait for every machine to agree before anyone answers, or answer now and let something catch up later. You can't have both.
Two-phase commit is the "wait for everyone" option. The coordinator asks every shard whether it can do the work, each shard locks what it needs and promises yes, and only if all of them promise does anyone commit. It stays transactional, but everyone waits on the slowest shard. If the coordinator dies mid-handshake, the shards are left in doubt: each has promised, can't take the promise back, and sits holding its locks. Griffin emphasizes that two-phase commit is a blocking protocol.
According to Griffin, the protocol itself is the smallest part of the problem. Postgres provides PREPARE TRANSACTION, and a prepared transaction survives crashes. The hard part of building this yourself is everything around it. If your code crashes between prepare and commit, every shard is stuck holding its locks, and Postgres won't clean that up for you. You have to write the code that notices stuck transactions and finishes them.
A proxy can do this instead. You write one plain transaction, and the proxy runs the two-phase commit. Griffin stresses that it provides the same guarantee with the same costs: the locks are still real, and you just didn't have to build the plumbing. The proxy gives you all-or-nothing behavior across shards, but it can't bring back the single-database atomicity you gave up when the data was split. A good shard key, Griffin says, means you need this for only a very small percentage of your writes.
What a Proxy Does and Doesn't Change
Griffin closes by restating the limits of the tooling. A proxy can hide the hard parts, but it doesn't remove them. A cross-shard join is still slow, and a two-phase commit is still fragile. In their view, the win was never making the hard parts easy. It was arranging things so you rarely have to deal with them.
They summarize the choice between approaches this way. In Rails, you own every piece: the migration, the mapping, the routing, the joins, and the transactions, and you have to keep each one correct and running. With a proxy, you can hand that work over while the app still thinks it is talking to one database, getting sharding's benefits without building the plumbing yourself.
The final message is about data layout rather than tooling. You don't beat sharding by working harder on its hard parts. You arrange your data so each tenant's work happens in one place and those parts barely come up. Get that right, Griffin argues, and most of sharding's pain never happens. Shard on the data that's easiest to separate: good sharding is good boundaries.
Hi everyone. This is Sharding Is Hard. Let's Make It Easier! My name is Tess Griffin and I'm a senior developer at thoughtbot.
Let's say your app has had one database for years, and as you grew, you did the obvious thing. You made it bigger and bigger and bigger, and now your infra bill is $500,000 a month and it's not even keeping up. Queries that used to be instant now crawl. A light breeze and it falls over. You get a spike in traffic and suddenly your entire database is on fire and you have no idea why. And you can't buy a bigger box. It doesn't exist or is too expensive. What now?
The first thing you want to do is measure. You want to determine why the database is falling over. You can ask Postgres itself. Are we read-heavy or write-heavy? Which tables take the writes? Which tables got huge? If there are a few big tables taking most of those writes, those are the tables you'll want to target.
If the reads are slow, there's a cheaper fix. Most read load can leave the database entirely. A replica is a live copy of your database that serves reads. Writes still go to one place, the primary. Reads scale by adding boxes, but every replica still holds every row and the primary still takes every write.
A single database has one primary and that primary takes every write. SQL databases make this trade on purpose. They trade slower writes so reads can be fast. If it's the writes that are slow, there's nowhere else to send them with one database. A table can get so big that everything it touches slows down. Reads, writes, even indexes get slow.
We already tried one fix, vertical scaling: a bigger box until the next size up doesn't exist. The other direction is horizontal: more machines. Nothing has to be bigger. There are just more of them.
Sharding is horizontal scaling applied to your database. It can fix both slow writes and giant tables. One database into several and spread the rows across them. Every machine takes a slice of the writes and holds a slice of the rows. To split, you have to decide which rows go to which machine. You pick one column and that column's value decides where each row lives.
That column is your shard key. A query that carries the shard key goes to one shard. A query that doesn't goes to all of them. One query becomes three. Every machine stops to answer and two of them had nothing. So each of those, and sharding has bought you nothing.
Meet Napify. Any resemblance to other storefront platforms is coincidental. This is an ideal case for sharding: a storefront platform. Every table hangs off of the shop. Draw a circle around one shop and nothing sticks out. The shard key is shop ID. And the things outside of your box—admins, plans, feature flags—they don't belong to any shop. Those stay behind on your primary.
And this is our app. It is currently on fire. It's not ideal. It's called TheirChart. Also a completely original platform. Hundreds of clinics, one Postgres database. Nobody considered sharding before now. Visits and audit logs are the tables currently falling over, and audit logs are huge for us because we want to be HIPAA compliant, and so every access of data requires a log. This is the schema that we need to shard.
We need to determine the shard key. The key has to split the biggest tables on fire, which are visits and audit logs. And a table can only be split by a column it carries. A visit has four IDs on it: the patient, the practitioner, the clinic, and its own. Those are our candidates. For each one, we'll try to cut and see what breaks.
The tempting shard key is the patient, the thing the whole app is about. But look at what breaks. Every red line is a query without the key, which visits every shard. The patients belong to more than one clinic. So half your queries wouldn't know which patient to route by and the other half don't carry a patient at all.
What about the practitioner? It's the same kind of problem. Practitioners work at more than one clinic and almost nothing connects to a practitioner. If almost nothing connects to your key, almost no query knows which shard to go to.
What if we shard on the burning table itself? Splitting by visit ID spreads the fire evenly. And some places do do this. But for our case, a visit owns almost nothing and touches everything. Everything for the visit lives somewhere else. Every query now crosses shards. You can split the burning table and still not pick the best shard key.
Shard on the data that's easiest to separate. For TheirChart, that's the clinic. Every write a clinic makes stays inside the circle. One edge crosses: treatments point at remedies. But remedies are the same for every clinic. So what you can do is copy the remedies to every shard. That way your queries still stay local. Billing doesn't really have anything to do with the clinics, so that can stay on the primary.
The fire splits with this cut. Audit logs and visits carry clinic ID. So each shard only takes its own audit logs and visits, which spreads out the fire to our new shards.
Everything a clinic does happens on one machine. Joins work. Transactions are plain single-database transactions. If a query misfires or a shard is compromised, damage stops at that shard's clinics, not all of them.
Patients can belong to more than one clinic, but we want to keep their records separate. Each clinic is its own covered entity, so it keeps its own patient records. If you need true single-tenant isolation for somebody, you can give them a shard just to themselves.
You've chosen clinic ID as the shard key. Now we have a couple jobs left. We need to move the data onto the shards and we need to route every query to the correct shard. You can do this in Rails or you can use a proxy.
A proxy is a box that sits between your app and the database and speaks plain SQL. Your app still talks to one thing while the proxy spreads the data across the shards. In Postgres there is PgDog, which is an open-source proxy. On MySQL there is Vitess, and PlanetScale is the managed version. Your database.yml in Rails points at it instead of at Postgres.
Before any data moves, every clinic needs an assigned shard. There are two ways you can assign the shard. You can compute it or you can store it.
The first option is to compute it. You run the clinic ID through a hashing function. This first example is simply clinic ID modulo n, n being the number of shards that you've spun up. You likely don't actually want to use this one in production because if you change n, then every clinic will move to a different shard. But whatever you choose the hashing function to be, the mapping works the same way. The function decides where the clinic lives, not you. You can't move just one clinic.
The other option is to store it: a table with one row per clinic. A clinic goes in and the shard comes out. The mapping is data now. And that's the point. You choose the placements. Do you have a huge clinic dragging down its whole shard? Move it to a shard of its own with one update.
The trade-off would be now every lookup goes through this one table. If for some reason this table is unreachable, then every shard behind it is also unreachable. But it's tiny and it only gets written to when a clinic is added or moved. For the rest of the code samples, we'll be using this stored shard map.
For an in-depth explanation of different hashing functions you can use, I recommend Martin Kleppmann's book, Designing Data-Intensive Applications. For our examples, we're going to be storing the mapping.
Every clinic has a shard now, but we need to actually move the data. You can do this all yourself. It's definitely a lot of work. You have to backfill the rows, catch up the writes that came in while you did the backfill, and then you cut over. Each one is tricky. The copy has to be throttled or the copy itself can take down your database. This is a pretty complex part of the project.
Or you can give it to the proxy. You create a publication, then run reshard. The app keeps moving while the data moves. You provision the empty databases, which are your shards, and then PgDog fills them. The steps didn't go away. The backfill, the catch-up, the cutover all still happen. The difference is who runs them.
Now that we've migrated the data, the next job is: when a request comes in, which shard do we talk to? Either your application picks—Rails knows about the shards and chooses on every request—or the proxy picks. The app talks to it like one ordinary database and the proxy routes.
We'll start at the app level. Rails has supported multiple databases since 6.0 and sharding since 6.1. It works the same on Postgres or MySQL. Step one is config. Every shard is an entry in database.yml. Each its own server. Each gets a read replica.
Then one abstract model wires the shards together with connects_to: a writer and a reading replica for each, and the tenant-scoped models inherit it. Remedies are the same on every shard. Anything that isn't a clinic's, like billing, stays on the primary.
And this is the primitive. You ask ShardMap which shard the clinic lives on and wrap your query in connected_to. Everything inside runs against that shard. That's how you reach one clinic's data. Rails answers exactly one question: which shard do I talk to?
Your shard key is one detail, but the moment you shard, every query needs it. That requires a clinic. It wants to spread. You want to keep it in one place.
You need to reach a clinic shard in the visits controller, so you open it right here. Then again in reminder job, so you also open it here, and eventually it spreads everywhere. When you're setting up your data for sharding, you have to find everywhere you find every clinic, and if you do it this way, you would have to add this logic to every place. And it's really easy to miss one.
You want to contain it in one place, and luckily Rails already gives you that. Rails ships configuration for shard support out of the box. This is the one place that knows how to find a shard. This is the only place.
You can go one step further and move routing out of the app completely onto the proxy. Hand it the shard key and it does the routing. You no longer have the shard map to maintain. You don't have to write the resolver. Your app goes back to believing that there's one database while you get the benefits of sharding.
Now what about background jobs? The same rule: handle it in one place. You put clinic ID in the job's arguments. Then a single around_perform in ApplicationJob resolves the shards and wraps perform in connected_to. One place decides. Individual jobs never mention shards. And if someone enqueues a job without a clinic, it'll raise instead of looking at the wrong shard.
With the proxy, every process connects to the same endpoint. The same around_perform, but instead of connected_to, we pass it this SQL SET LOCAL and we set the sharding key. This is inside of a transaction.
Solid Queue is database-backed. You want to keep its tables on the primary or give them their own database. You don't want to shard this. The SET LOCAL is the same as with Sidekiq. Whether you use Rails or the proxy, the sharding logic should live in one place.
What happens if you outgrow the shards that you spun up originally? In Rails, you need to handle that. If you use the hashing function, that will decide what moves. If you stored everything on your shard map, you can just move the clinics that you choose. The table was more work to set up front, but it pays for itself the first time you need to add more shards because, let's say you have a really big clinic that needs its own shard. You can simply spin up a new one, assign it the ID of the new shard, and you're done.
The proxy can reshard while the app keeps serving. There's no mapping to get wrong because you never wrote one if you use the proxy.
Because the shard key keeps the clinic's data together, most joins will never have to cross a shard. The joins that do need to cross can't be done in one go. You ask each shard and merge the answers yourself. This is called scatter-gather. You loop the shards, run the same query on each and add the numbers up. A proxy will do this for you. It fans out the cross-shard reads. You write one query, it hits every shard and merges.
A few writes do cross shards. Transactions on one database are atomic. The write is only ever seen as a single action. Can you keep a transaction atomic across shards? That's actually not possible in a distributed system. Once your data lives on machines that talk over a network, you have a choice. You either wait for every machine to agree before anyone answers or you answer now and have something that catches up to it later. You can't have both.
In a two-phase commit, you ask every shard, "Hey, can you do this?" Each locks what it needs and promises yes. Only if everyone promises does anyone commit. This is the wait-for-everyone answer. It stays transactional, but everyone waits on the slowest shard. And if the coordinator dies mid-handshake, the shards are left in doubt. Each has promised and can't take it back, and it sits holding its locks. A two-phase commit is a blocking protocol.
The protocol is the smallest part. Postgres gives you PREPARE TRANSACTION, and a prepared transaction survives crashes. The hard part of hand-rolling this yourself is the plumbing and everything around it. If your code crashes between the prepare and commit, every shard is stuck holding its locks and Postgres will not clean that up for you. You have to write what does the plumbing, what notices that things are stuck, and you have to write the thing that goes and finishes that job.
Or the proxy can do this for you. You write one plain transaction, it runs the two-phase commit. The same guarantee, the same costs. The locks are still real. You just didn't have to build the plumbing. The proxy gives you all or nothing across shards. It runs it for you. What it can't give you is atomicity. That left when data split. A good shard key means you only have to do this for a very small percent of your writes.
A proxy can hide the hard parts. It doesn't remove them. A cross-shard join is still slow. A two-phase commit is still fragile. The win was never making the hard parts easy. It's arranging things so you rarely have to handle this.
In Rails, you own all the pieces: the migration, the mapping, the routing, the joins, the transactions. Every piece is yours to keep correct and keep running. With a proxy, you can hand that work over and your app still thinks it's on one database. You get the benefits of sharding without all the plumbing. And that's pretty cool.
You don't beat sharding by working harder on the hard parts. You want to structure things in a way that you avoid them as much as possible. Arrange your data so each thing's work is in one place and the hard parts barely come up. Get that right and most of sharding's pain never happens. Shard on the data that's easiest to separate. Good sharding is good boundaries. Thank you.
Article published · Updated