Rendered at 13:51:25 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
groundzeros2015 4 hours ago [-]
I disagree with the opening premise:
> A single database server cannot handle such demand, so we must spread the queries and data out across many servers with database sharding
Did you max out the capacity of the best server you can buy?
Such a database can serve millions of customers (the numbers given).
You always want to scale up the other parts first, request handlers, caching, etc. The day you can no longer inspect the essential state of your system is the day your company better be included in NASDAQ and ready to pay a few hundred engineers 300k salaries.
exiguus 31 minutes ago [-]
As I understand the author: They describe the journey of scale from a single database server over read replicas to sharding.
And its not only about a single server, its also about network (backup, restore).
anonzzzies 3 hours ago [-]
Well, they are selling this thing so they don't want you to buy a big server (with a read replica) as that's much cheaper.
There are truly customers that bigger machine no longer cuts.
Disclaimer: I'm an Engineer at PS.
jackb4040 3 hours ago [-]
I found the article very helpful from a technical perspective, and didn't focus on the number too much, as it could easily be swapped and the decision-making process for when to shard is kinda out of scope.
But I hadn't considered this, so thanks for pushing back. Good to keep in mind their incentives.
I will say, since their product is a proxy whose interface is a single SQL connection, you should in theory be able to do dev queries through that black box, much the same as application queries? What is so scary here that it would require a hundred engineers?
groundzeros2015 3 hours ago [-]
> you should in theory be able to do dev queries through that black box
Because it’s a leaky abstraction which is trying to make guarantees over network connections which are extremely difficult to make within the same kernel.
A few questions I would start with:
- is the system even ACID compliant?
In my reading of this article, no.
- is my sql feature set limited? Will it enforce all constraints? Or are their cross-shard limitations?
For example the article doesn’t discuss transactions or how they would roll back, or how they guarantee a consistent view of data.
Next:
- how does it respond to error?
- how does it respond to load?
This is a complex system and complexity breeds bugs. But now you don’t have the tools or procedures to investigate those bugs because you can’t poke the system at desk with tools. You can’t run experiments; you can’t even see all the data.
jackb4040 2 hours ago [-]
Thank you, good points. I'm just learning about this in real time.
The more I read the more I'm struggling to understand the benefit of a router like this that sits on top of a monolithic SQL, vs a truly distributed DB like cockroach.
Like you I'd love to learn more about the internals of their actual SQL engine, which is just barely touched on in the article. The idea of ripping out the layer of a SQL interpreter that does just enough to route it to a real server tickles my brain in the same way as when I learned how node.js ripped the js interpreter out of a browser.
groundzeros2015 9 minutes ago [-]
What problem are you trying to solve? What did you try?
Unfortunately the SQL language is the simplest part of the database. The concurrency and consistency guarantees are the key technology,
anichhangani 3 hours ago [-]
Surely the cost of running sql server on premium hardware with replication would be more than running on commodity hardware with sharding?
hilariously 2 hours ago [-]
It depends on who you are paying for it, but generally a distributed system is harder to reason about, harder to fix, has weirder edge cases, and much more easily get into situations where it requires even MORE expertise to fix than just having a big honking server.
When you start calculating things that are not just the server, the single server looks cheaper and cheaper. How do you get a consistent backup? How do you do DR? How do you tune queries when it could go to this node or that node? Now writes are going to be significantly slower if you need multi-node commit because no matter what you are racing the speed of light on the network.
groundzeros2015 3 hours ago [-]
Only if your engineering resources and free and unbounded.
Even then I would probably use those resources to optimize software instead.
In the physical world of trucks and cranes no company would make that mistake to try to save 30-80k.
p1necone 3 hours ago [-]
Most non FAANG orgs could probably serve all their customers from postgres on a laptop.
don-code 35 minutes ago [-]
Let's hypothesize this is true.
I worked at a startup that had _money_ - a lot of it - flowing in. Stakeholders wanted to see growth, which meant features. They did not want to see us making platform improvements; those didn't show well to clients. Best throw money at the problem, rather than sacrifice the all-important features.
We had a number of architectural quirks that reflected this. One was that we ran our databases in AWS on ephemeral instance store. Yes, the kind that doesn't persist when you stop or lose the instance. The kind that you should never run a database on. We did it so that we could join them eight of them together in RAID 0 on the biggest instance types, and pull 400,000 IOPS to the disk that way. We paired this with hot spares of each shard, as well as hourly backups.
We did this to support a Rails / ActiveRecord data model, for data that should never have been part of a Rails data model. We had exactly one person working part-time on query optimization.
The maintenance burden was immense. The AWS bill, even more so. But again, we are hypothesizing that we could host all of this data on a laptop.
To an extent, maybe up until our thousandth customer or so, I actually think we could have. But we wouldn't have spent most of our time working on _features_ - we'd have instead spent most of our time trying to performance optimize. That would've meant the investors wouldn't have seen growth, which in turn would've highly impacted our success. We had a well defined domain, and optimizing the queries for performance was not it.
skeptic_ai 2 hours ago [-]
Hetzner has this for 100usd a month, 256gb ram can handle quite a lot of traffic already
€84.70
max. per month
€0.1357
per hour
CPU
Intel Xeon E5-1650V3
RAM
256 GB
Drives
2 × 6.0 TB Enterprise HDD
Location
#FSN1-DC1
Information IPv4 ECC iNIC
Tostino 2 hours ago [-]
Lol that isn't a database server. Come on, if it has hard drives rather than (good) SSD in 2026, it's not a server you should be using for databases unless it's for something with next to no load, and you are also running your application server on the same machine.
drdexebtjl 9 hours ago [-]
What about sequences? The example shows an auto-incrementing user ID. How’s that possible without contention between all shards? Is the proxy responsible for sequences?
What about foreign keys? Do they all have to live on the same shard? How do you do distributed transactions?
On cross-shard reads: how do you do sorting? And cross-shard joins?
I’d love to be proven wrong, but I suspect the 768 servers look like 1 only on the very surface, and you’ll get wildly different characteristics from cross-shard and single-shard queries.
I personally would prefer if they _didn’t_ look like 1 if they can’t behave like 1.
vkazanov 6 hours ago [-]
Of course 768 servers NEVER behave as 1. This is physically impossible.
Global services using relational dbs typically severely restrict queries that run against the cluster. So no joins, no intervals, no grouping, etc.
Transactional queries are usually limited to something like "get a single record, preferably from cache". For many typical web services this can go VERY FAR. Only a handful of global services needs more than a few dozen database servers and a caching cluster. In fact, i have seen major businesses running off a pair of very big postgres instances.
Analytical stuff is extracted into dedicated storages optimized for throughput, like Snowflake or Redshift or BigQuery.
ahk-dev 5 hours ago [-]
This seems like the important distinction: making the infrastructure look like one database to the application is different from making it behave like one unrestricted relational database.
At what point does hiding the sharding become counterproductive? I imagine teams still need a fairly deep understanding of shard keys, query routing, and failure modes to avoid accidentally expensive cross-shard operations.
vkazanov 3 hours ago [-]
Yes!
Distributed systems introduce severe restrictions on what can be reasonably done at scale. Having a single connection string is one thing, being able to do a massive JOIN is another (and should only be ever done in analytical databases).
The question is not "when sharding becomes counter-productive" but "when it starts making sense".
With sharding something somewhere has to know how to route queries to subsets of data. So it is a complexity price paid for being able to scale. If one can avoid paying this cost then he should.
And USUALLY cross-shard queries are not just expensive but simply impossible in operational clusters. Like, if you do COUNT on a table, you only count within a single db shard table.
jackb4040 3 hours ago [-]
I had this question too, even their "Database Scaling" course doesn't get to it.
Looks like it's not drop-in, you need to heavily modify your data and indexes structurally to make sharding performant. Cross-shard joins are possible but need to be designed for explicitly (to be fair, indexes also need to be designed explicitly in regular SQL). IDK if a drop-in solution is possible or if this is an information-theoretical limitation.
citrin_ru 3 hours ago [-]
> The example shows an auto-incrementing user ID. How’s that possible without contention between all shards?
Why not divide ID range (63bit?) by the maximum planned number of shards and then set on each shard it's min/max value so ranges will not overlap?
chiph 19 minutes ago [-]
Because if the business grows, then you will need to add servers, and reallocate each existing shard's min/max values. If you left room to grow (divide amongst 2^31 instead of 2^63) you can go a long way before you have to solve that problem. If the business turns out to be wildly successful, you'd probably set up a whole new pool anyway and migrate over (and get a new set of pre-allocated ID ranges).
This might be the perfect case to use an UUIDv7 as a key instead of a number.
javier2 2 hours ago [-]
low-traffic auto increment like user creation can be relatively performant by reserving batches up front, then using those in each shard.
But the use-case for this is mostly if you do not need, or have very limited use for anything cross-shard.
random3 7 hours ago [-]
A 767 servers KV store should be enough for everyone
themgt 1 hours ago [-]
Even with a large database servers (10s of CPU cores, 100s of gigabytes of RAM) bottlenecks arise pretty quickly.
Err, do they? For what percent of real world use cases?
The database can scale to handle more traffic by adding replicas. An extreme example of this is OpenAI's use of 50 replicas on a single Primary.
So an extreme example is OpenAI needing 50 replicas, but we're doing five blades ... err, we're doing 768 servers because the need arose "pretty quickly"?
When we needed to store a petabyte of data (one million gigabytes), we'd need many more shards
For who? The United States government? How many end-users are running 1PB Postgres database on DBaaS?
exiguus 10 minutes ago [-]
Personally, I know several databases where single tables have +500GB and the database has +100TB.
With this huge databases the restore and backup process over network become indeed a bottleneck. So I can agree with the author.
Also, the author does not say that they can't start with a single database server and just read replicas and max hardware out.
Real world use cases with +100TB I know about are Stock Market, Traffic Data, Warehouses, Analytics and Monitoring.
hasyimibhar 37 minutes ago [-]
I'm surprised no one has mentioned Multigres yet, they are the competitor of Neki. I'm a big fan of both and has been following them since they were announced last year. I think this blog post is the first time they are talking about the internals of Neki. In contrast, Multigres is being built in public since day 1, you can see their high-level architecture here [1], though I'm still waiting for more details on their sharding model.
Took me an embarrassing amount of time to realize this is an ad.
kjellsbells 2 hours ago [-]
I sometimes feel that when the industry moved from pets to cattle, what really happened is that the cattle turned out to need an exotic farm to live on, negating the savings. You can have a few honking servers or you can hand massage exotic k8s setups on your fleet. Pick your poison, but dont delude yourself that the TCO of the latter is lower than the former.
jdw64 9 hours ago [-]
Looks like the GIF is fully built out in code. It's really nice to look at, well made, and easy to understand too.
I wonder what program or code they used. I'd love to know.
p.sI thought it was a GIF, but it's an iframe. That was a nice little surprise.
gurjeet 9 hours ago [-]
Specifically, it's an JS-controlled/animated SVG embedded in an iframe.
jdw64 9 hours ago [-]
Yeah, I'm looking at it in developer mode. It's using a GSAP timeline approach to update SVG properties. I'm curious how they handle security and caching for something like this. It looks like they're using Tailwind, at least. but this approach is really clean and nice.
It really feels like the best way to learn is by studying other people's code.
stavros 7 hours ago [-]
What kind of security and caching concerns do they need to handle to animate an SVG?
jdw64 2 hours ago [-]
I didn't explain myself very well. With iframes, there can be security concerns in general. Though in practice, there's usually no real issue. It just surfaces the surface level risk that JavaScript could be tampered with, but it's almost never something to actually worry about.
As for caching strategy, cache invalidation issues do pop up from time to time. Since the filenames usually include a content hash, I'm guessing they're using a Vite style caching strategy. Cache invalidation might almost never be a problem, but you still plan for it just in case.
foxhill 3 hours ago [-]
i do wonder how something like this can be generally implemented. i presume this must only support a subset of SQL/plpgsql, as some things would be.. utterly insane to manage manually. e.g., if i have a table with a btree-gist overlap constraint, or some inclusion-exclusion check-constraint (or literally any constraint that requires multiple rows to be fully determined - there are quite a lot of them), how on earth does this work?
there's a reason why postgres writing is (mostly) serialised (asterisk) to a single writer (asterisk asterisk). something something ACID, but in short by having multiple writers improves availability, but weakens integrity.
skeptic_ai 2 hours ago [-]
How does Sharding works when
You do complex joins ? Seems tricky , runs on each server and gets data back and aggregate it again?
metalliqaz 1 hours ago [-]
what did they use to make those diagrams/animations?
alightsoul 9 hours ago [-]
Load balancers, microservices and horizontal scaling?
> A single database server cannot handle such demand, so we must spread the queries and data out across many servers with database sharding
Did you max out the capacity of the best server you can buy?
Such a database can serve millions of customers (the numbers given).
You always want to scale up the other parts first, request handlers, caching, etc. The day you can no longer inspect the essential state of your system is the day your company better be included in NASDAQ and ready to pay a few hundred engineers 300k salaries.
There are truly customers that bigger machine no longer cuts.
Disclaimer: I'm an Engineer at PS.
But I hadn't considered this, so thanks for pushing back. Good to keep in mind their incentives.
I will say, since their product is a proxy whose interface is a single SQL connection, you should in theory be able to do dev queries through that black box, much the same as application queries? What is so scary here that it would require a hundred engineers?
Because it’s a leaky abstraction which is trying to make guarantees over network connections which are extremely difficult to make within the same kernel.
A few questions I would start with:
- is the system even ACID compliant?
In my reading of this article, no.
- is my sql feature set limited? Will it enforce all constraints? Or are their cross-shard limitations?
For example the article doesn’t discuss transactions or how they would roll back, or how they guarantee a consistent view of data.
Next:
- how does it respond to error?
- how does it respond to load?
This is a complex system and complexity breeds bugs. But now you don’t have the tools or procedures to investigate those bugs because you can’t poke the system at desk with tools. You can’t run experiments; you can’t even see all the data.
It looks like it does support transactions, but they basically destroy the performance benefits: https://vitess.io/docs/faq/sharding/advanced/can-i-use-vites...
The more I read the more I'm struggling to understand the benefit of a router like this that sits on top of a monolithic SQL, vs a truly distributed DB like cockroach.
Like you I'd love to learn more about the internals of their actual SQL engine, which is just barely touched on in the article. The idea of ripping out the layer of a SQL interpreter that does just enough to route it to a real server tickles my brain in the same way as when I learned how node.js ripped the js interpreter out of a browser.
Unfortunately the SQL language is the simplest part of the database. The concurrency and consistency guarantees are the key technology,
When you start calculating things that are not just the server, the single server looks cheaper and cheaper. How do you get a consistent backup? How do you do DR? How do you tune queries when it could go to this node or that node? Now writes are going to be significantly slower if you need multi-node commit because no matter what you are racing the speed of light on the network.
Even then I would probably use those resources to optimize software instead.
In the physical world of trucks and cranes no company would make that mistake to try to save 30-80k.
I worked at a startup that had _money_ - a lot of it - flowing in. Stakeholders wanted to see growth, which meant features. They did not want to see us making platform improvements; those didn't show well to clients. Best throw money at the problem, rather than sacrifice the all-important features.
We had a number of architectural quirks that reflected this. One was that we ran our databases in AWS on ephemeral instance store. Yes, the kind that doesn't persist when you stop or lose the instance. The kind that you should never run a database on. We did it so that we could join them eight of them together in RAID 0 on the biggest instance types, and pull 400,000 IOPS to the disk that way. We paired this with hot spares of each shard, as well as hourly backups.
We did this to support a Rails / ActiveRecord data model, for data that should never have been part of a Rails data model. We had exactly one person working part-time on query optimization.
The maintenance burden was immense. The AWS bill, even more so. But again, we are hypothesizing that we could host all of this data on a laptop.
To an extent, maybe up until our thousandth customer or so, I actually think we could have. But we wouldn't have spent most of our time working on _features_ - we'd have instead spent most of our time trying to performance optimize. That would've meant the investors wouldn't have seen growth, which in turn would've highly impacted our success. We had a well defined domain, and optimizing the queries for performance was not it.
€84.70 max. per month €0.1357 per hour CPU Intel Xeon E5-1650V3 RAM 256 GB Drives 2 × 6.0 TB Enterprise HDD Location #FSN1-DC1 Information IPv4 ECC iNIC
What about foreign keys? Do they all have to live on the same shard? How do you do distributed transactions?
On cross-shard reads: how do you do sorting? And cross-shard joins?
I’d love to be proven wrong, but I suspect the 768 servers look like 1 only on the very surface, and you’ll get wildly different characteristics from cross-shard and single-shard queries.
I personally would prefer if they _didn’t_ look like 1 if they can’t behave like 1.
Global services using relational dbs typically severely restrict queries that run against the cluster. So no joins, no intervals, no grouping, etc.
Transactional queries are usually limited to something like "get a single record, preferably from cache". For many typical web services this can go VERY FAR. Only a handful of global services needs more than a few dozen database servers and a caching cluster. In fact, i have seen major businesses running off a pair of very big postgres instances.
Analytical stuff is extracted into dedicated storages optimized for throughput, like Snowflake or Redshift or BigQuery.
At what point does hiding the sharding become counterproductive? I imagine teams still need a fairly deep understanding of shard keys, query routing, and failure modes to avoid accidentally expensive cross-shard operations.
Distributed systems introduce severe restrictions on what can be reasonably done at scale. Having a single connection string is one thing, being able to do a massive JOIN is another (and should only be ever done in analytical databases).
The question is not "when sharding becomes counter-productive" but "when it starts making sense".
With sharding something somewhere has to know how to route queries to subsets of data. So it is a complexity price paid for being able to scale. If one can avoid paying this cost then he should.
And USUALLY cross-shard queries are not just expensive but simply impossible in operational clusters. Like, if you do COUNT on a table, you only count within a single db shard table.
https://vitess.io/docs/faq/sharding/advanced/
Looks like it's not drop-in, you need to heavily modify your data and indexes structurally to make sharding performant. Cross-shard joins are possible but need to be designed for explicitly (to be fair, indexes also need to be designed explicitly in regular SQL). IDK if a drop-in solution is possible or if this is an information-theoretical limitation.
Why not divide ID range (63bit?) by the maximum planned number of shards and then set on each shard it's min/max value so ranges will not overlap?
This might be the perfect case to use an UUIDv7 as a key instead of a number.
But the use-case for this is mostly if you do not need, or have very limited use for anything cross-shard.
Err, do they? For what percent of real world use cases?
The database can scale to handle more traffic by adding replicas. An extreme example of this is OpenAI's use of 50 replicas on a single Primary.
So an extreme example is OpenAI needing 50 replicas, but we're doing five blades ... err, we're doing 768 servers because the need arose "pretty quickly"?
When we needed to store a petabyte of data (one million gigabytes), we'd need many more shards
For who? The United States government? How many end-users are running 1PB Postgres database on DBaaS?
[1] https://multigres.com/docs/architecture
p.sI thought it was a GIF, but it's an iframe. That was a nice little surprise.
It really feels like the best way to learn is by studying other people's code.
As for caching strategy, cache invalidation issues do pop up from time to time. Since the filenames usually include a content hash, I'm guessing they're using a Vite style caching strategy. Cache invalidation might almost never be a problem, but you still plan for it just in case.
there's a reason why postgres writing is (mostly) serialised (asterisk) to a single writer (asterisk asterisk). something something ACID, but in short by having multiple writers improves availability, but weakens integrity.