8 ms·
> I've been advocating for SQLite+NVMe for a while now. Why SQLite instead of a traditional client-server database like Postgres? Maybe it's a smidge faster on
by crazygringo 2y ago
> I've been advocating for SQLite+NVMe for a while now.
Why SQLite instead of a traditional client-server database like Postgres? Maybe it's a smidge faster on a single host, but you're just making it harder for yourself the moment you have 2 webservers instead of 1, and both need to write to the database.
> Latency is king in all performance matters.
This seems misleading. First of all, your performance doesn't matter if you don't have consistency, which is what you now have to figure out the moment you have multiple webservers. And secondly, database latency is generally miniscule compared to internet round-trip latency, which itself is miniscule compared to the "latency" of waiting for all page assets to load like images and code libraries.
> Especially in those where items must be processed serially.
You should be avoiding serial database queries as much as possible in the first place. You should be using joins whenever possible instead of separate queries, and whenever not possible you should be issuing queries asynchronously at once as much as possible, so they execute in parallel.
- conradev 2y agoUntil you hit the single-writer limitation in SQLite, you do not need to spend more CPU cycles on Postgres
- chatmasta 2y agoThat’s a limitation you’ll hit pretty quickly unless you’ve specifically planned your architecture to be mostly read-only SQLite or one SQLite per session.
- spratzt 2y agoYou certainly won’t hit it with most corporate OLAP processing, which is nearly all read-only SQlite. Writes are generally batched and processed outside ‘normal’ business hours, where the limitations of SQlite writing are irrelevant.
- conradev 2y agoI'm having a hard time imagining this. Aren't most CRUD apps using OLTP mostly read-only in the first place? I just feel like you'd need thousands of concurrent users on a typical CRUD app to even get close to straining SQLite.
- theamk 2y agoI'd recommend going with postgres if there is a good chance you'll need it, instead of starting with SQLite and switching later - as their capabilities and data models are quite different. For small traffic, it's pretty simple to run it on the same host as web app, and unix auth means there are no passwords to manage. And once you need to have multiple writers, there is no need to rewrite all the database queries.
- bob1029 2y agoThe entire point is to avoid the network hop. Application <-> SQLite <-> NVMe has orders of magnitude less latency than Application <-> Postgres Client <-> Network <-> Postgres Server <-> NVMe > You should be avoiding serial database queries as much as possible in the first place. I don't get to decide this. The business does.
- sedatk 2y ago"...has orders of magnitude less latency than..." [citation needed]. Local network access shouldn't be much different than local IPC.
- deleted 2y ago[deleted]
- bob1029 2y ago> Local network access In what production scenarios do MySQL, Postgres, DB2, Oracle, et. al., live on the same machine as the application that uses them? I am pretty sure most of these vendors would offer strict guidance to not do that.
- chatmasta 2y agoIt’s not a stretch to imagine that a scenario where you’re willing to run SQLite locally is also one where it’s acceptable to run Postgres locally. You’ve presumably already got the sharding problem solved, so why not? It’s less esoteric of an architecture than multiwriter SQLite.
- TylerE 2y agoLike 95% of websites that aren’t Amazon or google? Ton of sites that run in a single small vm. Postgres scales down quite nicely and will happily run in say, 512MB.
- crazygringo 2y ago> I am pretty sure most of these vendors would offer strict guidance to not do that. Then you'd be wrong. Running Postgres or MySQL on the same host where Apache is running is an extremely common scenario for sites starting out. They run together on 512 MB instances just fine. And on an SSD, that can often handle a surprising amount of traffic. As popularity grows, the next step is to separate out the database on its own server, but mostly as a side effect of the fact that you now need multiple web servers, but still a single source of truth for data. Databases are lighter-weight than you seem to think.