4 ms·
Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other h
by kentonv 3y ago
Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb.
This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database.
Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit.
As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.
- deleted 3y ago[deleted]
- fauigerzigerk 3y ago>This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database. You're right, it's a very interesting strategy. Unfortunately, last time I looked Cloudflare's D1 didn't support this approach as there was no API to create a database at runtime from Worker code. Has that changed?
- candiddevmike 3y agoNow your app is stateful, you need storage for it, a backup strategy, a free space monitoring strategy, a way to have the storage follow the app, etc. Depending on your situation, that could be harder than just getting a Postgres database.
- ngrilly 3y agoI used to be in the stateless camp. But I think we pushed that argument too far. Stateless apps are not very useful. Most useful apps are stateful. What we are doing with promoting stateless services is just delegating the necessary stateful complexity to someone else, sweeping it under the carpet. By doing that, we are losing so many opportunities to do smarter and better things where the complexity really lies, which is where the state is. That’s why I really appreciate SQLite and other approaches like using KV embedded databases like RocksDB making a come back. That’s the job of the infrastructure providers, AWS, GCP, DO, and others, to provide the tools solving the problems you mentioned: block storage that is synchronously replicated across data centers (GCP does this), snapshots and backups on block storage, possibility to quickly reattach the block storage to a new computing node if the previous one died, etc.
- kentonv 3y agoEdge compute platforms aim to take care of all that for you.