[bob1029]

The 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.

[_1tem]

Postgres supports Unix sockets when running on the same machine. That’s what I use, for a significant latency improvement over the TCP stack even at 127.0.0.1.

[sedatk]

"...has orders of magnitude less latency than..."

[citation needed]. Local network access shouldn't be much different than local IPC.

[bob1029]

> 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.

[TylerE]

Like 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.

[chatmasta]

It’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.

[crazygringo]

> 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.

[immibis]

In the scenario where you were choosing between it and SQLite...

[bobmcnamara]

Context switches plus mmap accesses are often slower than mmap accesses.

[badmintonbaseba]

You don't have IPC for sqlite, do you?

[nolist_policy]

You do of you access the same database from miltiple processes.

[badmintonbaseba]

What IPC mechanisms exist between sqlite processes accessing the same database, other than file locking and some atomic IO operations ensured by the OS.

[_1tem]

I’ve tested this before and Postgres is measurably faster over Unix socket than over local network.

[crazygringo]

Perhaps I wasn't clear enough in my comment. When I said "database latency is generally miniscule compared to internet round-trip latency", I meant between the user and the website. Because they're often thousands of miles away, there are network buffers, etc.

But no, a local network hop doesn't introduce "orders of magnitude" more latency. The article itself describes how it is only 5x slower within a datacenter for the roundtrip part -- not 100x or 1,000x as you are claiming. But even that is generally significantly less than the time it takes the database to actually execute the query -- so maybe you see a 1% or 5% speedup of your query. It's just not a major factor, since queries are generally so fast anyways.

The kind of database latency that you seem to be trying to optimize for is a classic example of premature optimization. In the context of a web application, you're shaving microseconds for a page load time that is probably measured in hundreds of milliseconds for the user.

> I don't get to decide this. The business does.

You have enough power to design the entire database architecture, but you can't write and execute queries more efficiently, following best practices?

[392]

Sqlite can be run in process. Latency and bandwidth can be made 10x worst by process context switching alone. Plus being able to get away with n+1s could save a lot of dev time depending on the crew, before Claude (tho the dev still needs to learn that the speed problem is due to this and refactor the query, or write it fast the first time)

[crazygringo]

> Latency and bandwidth can be made 10x worst by process context switching alone.

No they can't. That doesn't even make sense as a claim regarding bandwidth since SQLite doesn't use any, but please re-read what I said about being a 1% or 5% difference in speed. Not 10x.

[bob1029]

Yes they absolutely can.

Same-core context switching costs a few microseconds.

Going across core complexes can cost tens to hundreds of microseconds.

These figures are several orders of magnitude (5-6) slower than L1 access on the same thread.

[crazygringo]

Hundreds of microseconds? L1 access? I don't have the faintest idea of what you're talking about.

Communication between processes is negligible compared to all of the sequential disk/SSD accesses and processing required for executing queries.

The database isn't stored in L1 and communication isn't taking hundreds of microseconds. I don't know where you're getting your information.

The fact that SQLite is in-process is primarily about simplicity and convenience, not performance. Performance can even be worse, e.g. due to the lack of a query cache.

[immibis]

If you're concerned about the overhead of IPC when using postgres on the same server, weigh your intuition of it against your intuition of the savings from having a persistent process. SQLite can't cache a lot of things because some other process might have completely changed the database between transactions. Postgres knows everything that happens to the database.