4 ms·

But why SQLite, if you're going for vertical scaling? Nobody's stopping you from self hosting Postgres (or Supabase!) on the same server as your app, and I can

But why SQLite, if you're going for vertical scaling? Nobody's stopping you from self hosting Postgres (or Supabase!) on the same server as your app, and I can't think of any disadvantage other than more effort to set up. Now if you're willing to go DB-less, keep your whole global state in literal memory on one big server (no round-tripping to Redis or whatever, actual in-process objects), just occasionally snapshotting that memory to disk (this part's tricky), and use a compiled, multi-threaded language -- then you can saturate a Gbit or bigger NIC and literally serve the world from one box. I kind of wish I had a real use case for that architecture.

thomascountz 3y ago
Why not SQLite? :) Of course the answer is always "it depends," but lately, I've seen the general "SQLite isn't a real database" ethos challenged more and more. Outside of standard relational persistence patterns, there can be significant feature differences that could very well mean Postgres is the better option. However, for some architectural patterns, SQLite could come out ahead! For a content-heavy application like BusinessInsider, the Baked Data pattern with SQLite might very well offer better cost and latency performance! simonw (datasette) has built troves and troves of tools and writings about SQLite's production use for content-heavy and/or data rich websites: https://simonwillison.net/2021/Jul/28/baked-data/ https://simonwillison.net/2021/Jul/28/baked-data/
klabb3 3y ago
> But why SQLite, if you're going for vertical scaling? From the benchmarks I’ve seen, because it’s significantly faster, specifically round trip times. This would make sense since SQLite is in-process and doesn’t need serialization[1]. Which in turn offers a second, optional advantage within reach - serial processing of operations, which is significantly easier to test, reason about, and build supporting cache layers around. [1]: If you don’t use a Unix socket you also have networking overhead - but you said same server so I’ll leave this as a side-note since it’s extremely common to put postgres on a different machine for isolation. In fact, it’s one of the main advantages with networked dbs.
gozzoo 3y ago
The author probably means that we can use SQLite on the edge. https://blog.cloudflare.com/introducing-d1 https://blog.cloudflare.com/introducing-d1
- Sammi 3y ago
  No. The author is clearly and explicitly saying the edge does not make sense for most use cases. The author means sqlite makes sense if you're running one machine, which is what makes most sense for most use cases.
Sammi 3y ago
If I'm running on a single machine then sqlite comes out ahead of postgres/mysql. Sqlite has everything I need plus the simplicity and speed is superior. Sqlite can do terabytes of data, can handle multiple readers, has live streaming backup, and is a pretty well rounded sql implementation in general. I would only consider postgres/mysql if I outgrew vertically scaling a single box.

thomascountz 3y ago

Why not SQLite? :) Of course the answer is always "it depends," but lately, I've seen the general "SQLite isn't a real database" ethos challenged more and more. Outside of standard relational persistence patterns, there can be significant feature differences that could very well mean Postgres is the better option. However, for some architectural patterns, SQLite could come out ahead! For a content-heavy application like BusinessInsider, the Baked Data pattern with SQLite might very well offer better cost and latency performance! simonw (datasette) has built troves and troves of tools and writings about SQLite's production use for content-heavy and/or data rich websites: https://simonwillison.net/2021/Jul/28/baked-data/ https://simonwillison.net/2021/Jul/28/baked-data/

klabb3 3y ago

> But why SQLite, if you're going for vertical scaling? From the benchmarks I’ve seen, because it’s significantly faster, specifically round trip times. This would make sense since SQLite is in-process and doesn’t need serialization[1]. Which in turn offers a second, optional advantage within reach - serial processing of operations, which is significantly easier to test, reason about, and build supporting cache layers around. [1]: If you don’t use a Unix socket you also have networking overhead - but you said same server so I’ll leave this as a side-note since it’s extremely common to put postgres on a different machine for isolation. In fact, it’s one of the main advantages with networked dbs.

gozzoo 3y ago

The author probably means that we can use SQLite on the edge. https://blog.cloudflare.com/introducing-d1 https://blog.cloudflare.com/introducing-d1

Sammi 3y ago
No. The author is clearly and explicitly saying the edge does not make sense for most use cases. The author means sqlite makes sense if you're running one machine, which is what makes most sense for most use cases.

Sammi 3y ago

If I'm running on a single machine then sqlite comes out ahead of postgres/mysql. Sqlite has everything I need plus the simplicity and speed is superior. Sqlite can do terabytes of data, can handle multiple readers, has live streaming backup, and is a pretty well rounded sql implementation in general. I would only consider postgres/mysql if I outgrew vertically scaling a single box.