4/13/2026 at 7:47:47 PM
Oh hey, I wrote this! Happy to chat more about the article here. Databases are kinda my thing.by bddicken
4/13/2026 at 9:00:34 PM
Thanks for writing this! The visualisations really drive a better understanding than pure text does, and it's quite clear that you have a better understanding of what database do under the hood than I do.As such, I have a question for you: contrary to your article, I've always been taught that random primary keys are better than sequential ones. The reason for this, I was told, was to avoid "hotspots". I guess it only really applies once sharding comes into play, and perhaps also only if your primary key is your sharding key, but I think that's a pretty common setup.
I'm not really sure how to formulate a concrete question here, I guess I would like to hear your thoughts on any tradeoffs on sequential Vs random keys in sharded setups? Is there a case there random keys are valid, or have I been taught nonsense?
by amarant
4/13/2026 at 9:10:22 PM
B+trees combined with sequential IDs are great for writes. This is because we are essentially just appending new rows to the "linked list" at the bottom level of the tree. We can also keep a high fill % if we know there isn't a lot of data churn.If you're sharding based purely on sequential ID ranges, then yes this is a problem. Its better practice to shard based on a hash of your ID, so sequential id assignments turn into non-sequential shard keys, keeping things evenly distributed.
by bddicken
4/13/2026 at 9:18:13 PM
Oh wow, that's a super simple solution, and I can immediately see how this gets you the best of both worlds!And since it's only used for speedy lookup we can even use a fast, cheap and non-secure hashing algorithm, so it's really a low-cost operation!
Thanks! This was really one of those aha-moments where I feel kinda stupid to not have thought of it myself!
by amarant
4/13/2026 at 9:21:04 PM
I've also written about sharding.by bddicken
4/13/2026 at 9:50:18 PM
Thanks! Another great article! It strikes me that modulo sharding on a sequential id would probably work rather well, but it was not mentioned in this article. Is there a reason I'm not seeing that this is bad? I guess resharding might be problematic, as you can't easily split a shard in two without rewriting every shard if you do that...by amarant
4/14/2026 at 1:58:30 AM
> I guess resharding might be problematicyes, that's the crux of the problem. when you have a sharded database, typically you want to be able to add (and/or remove) shards easily and non-disruptively.
for example - your database is currently sharded across N nodes, and it's overloaded due to increased traffic, so you want to increase it to N+1 nodes (or N+M nodes, which can add complexity in some cases)
if adding a shard causes a significant increase in load on the database, that's usually a non-starter for a production workload, because at the time you want to do it, the database is already overloaded
you can read about this in the original Dynamo paper [0] from almost 20 years ago - consistent hashing is used to select 3 of the N nodes to host a given key. when node N+1 is added, it joins the cluster in such a way that it will "take over" hosting 1/Nth of the data, from each of the N nodes - meaning that a) the joining process places a relatively small load on each of those N nodes and b) once the node is fully joined, it reduces overall load evenly across all N nodes.
0: https://www.allthingsdistributed.com/2007/10/amazons_dynamo....
by evil-olive
4/13/2026 at 10:18:17 PM
For our DBs (which are often unsharded), we've found the best performance using the user account ID as the first part of the cluster key and then a sequential id for whatever the record is as the second.It's not as good as just a sequential ID at keeping the fragmentation and data movement down. However, it does ultimately lead to the best write performance for us because the user data ends up likely still appending to an empty page. It allows for more concurrent writes to the same table because they aren't all fighting over that end page.
UUIDv4 is madness.
by cogman10
4/13/2026 at 9:29:05 PM
Spanner in particular wants random primary keys. But there are sharded DBMSes that still use sequential PKs, like Citus. There are also some use cases for semi-sequential PKs like uuid7.by traderj0e
4/13/2026 at 10:20:07 PM
What about spanner specifically benefits from random ids over sequential ones?by bddicken
4/14/2026 at 5:21:08 PM
I'm not an expert on Spanner, supposedly it's due to hotspotting. Your data is partitioned by primary key, and if you make that sequential, all new writes will hit the same master. https://docs.cloud.google.com/spanner/docs/schema-and-data-m... explicitly recommends a uuid4 or some other optionsThat's another thing, some say to use uuid7 for sharded DBs, but this is a serious counterexample.
by traderj0e
4/13/2026 at 8:48:33 PM
I remember this article for when I was researching for https://spacetimedb.com/. The interactivity is very cool, BTW!One neat realization is that a database is in fact more about indexes than the actual raw tables (all things interesting work under this assumption), to the point that implementing the engine you get the impression that everything start with "CREATE INDEX" than "CREATE TABLE". This includes sequential scans, where as visualized in your article show that lay the data sequentially is in fact a form of index.
Now, I have the dream of make a engine more into this vision...
by mamcx