Looks like the overhead is not insignificant:

    Running 100,000 `SELECT 1` queries:
    PostgreSQL (localhost): 2.77 seconds
    SQLite (in-memory): 0.07 seconds

(https://gist.github.com/leifkb/1ad16a741fd061216f074aedf1eca...)

▲ piker 11 hours ago | parent | next [-]

I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. If you’re doing CRUD, simulating that workflow may favor Postgres given the transactional read/write work that needs to take place across multiple concurrent connections.

▲

locknitpicker 10 hours ago | parent [-]

> I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries.

Exactly. Back in the real world,anyone who is faced with that sort of usecase will simply add memory cache and not bother with the persistence layer.

	▲	piker 8 hours ago \| parent [-]
		Not sure that’s always right either though. For example Mapbox used to use an SQLite database as the disk cache for map tile info. You cannot possibly store that amount of data in memory, so it’s a great use case.

▲ bob1029 12 hours ago | parent | prev | next [-]

This is mostly about thread communication. With SQLite you can guarantee no context switching. Postgres running on the same box gets you close but not all the way. It's still in a different process.

	▲	andersmurphy 9 hours ago \| parent [-]
		This. Run an app on the same box as PG and you can easily be plagued by out of memory etc (as there's memory contention between the two processes).

▲ madduci 11 hours ago | parent | prev | next [-]

Most important is that that local SQLite gets proper backups, so a restore goes without issues

	▲	pdhborges 9 hours ago \| parent [-]
		Gets proper backups if you back it up the right way https://sqlite.org/backup.html

▲ vixalien 11 hours ago | parent | prev | next [-]

Would be nice to see PGLite[1] compared too

1: https://pglite.dev/

	▲	j45 37 minutes ago \| parent [-]
		Interesting, but I'm not sure how relevant it would be for a SaaS that on average queries hundreds to thousands of rows.

▲ locknitpicker 10 hours ago | parent | prev | next [-]

A total performance delta of <3s on ~300k transactions is indeed the definition of irrelevant.

Also:

> PostgreSQL (localhost): (. .) SQLite (in-memory):

This is a rather silly example. What do you expect to happen to your data when your node restarts?

Your example makes as much sense as comparing Valkey with Postgres and proceed to proclaim that the performance difference is not insignificant.

▲ iLoveOncall 11 hours ago | parent | prev | next [-]

Why are you comparing PostgreSQL to an in-memory SQLite instead of a file-based one? Wow, memory is faster than disk, who would have thought?

▲ eurleif 11 hours ago | parent [-]

Because it doesn't make a difference, because `SELECT 1` doesn't need to touch the database:

    Running 100,000 `SELECT 1` queries:
    PostgreSQL (localhost): 2.71 seconds
    SQLite (in-memory): 0.07 seconds
    SQLite (tempfile): 0.07 seconds

(https://gist.github.com/leifkb/d8778422d450d9a3f103ed43258cc...)

▲ oldsecondhand 10 hours ago | parent | next [-]

Why are you doing meaningless microbenchmarks?

	▲	saturn_vk 7 hours ago \| parent [-]
		Are you claiming that this does not show the speed difference between socket vs in process communication?

▲ j45 an hour ago | parent | prev | next [-]

Queries for small SaaS are usually in the thousands of records, if not hundreds.

▲ locknitpicker 10 hours ago | parent | prev | next [-]

> Because it doesn't make a difference, because `SELECT 1` doesn't need to touch the database:

I hope you understand that your claim boils down to stating that SQLite is faster at doing nothing at all, which is a silly case to make.

	▲	eurleif 10 hours ago \| parent [-]
		The original claim being discussed is about the overhead of an in-process database vs. a database server in a separate process, not about whether SQLite or PostgreSQL have a faster database engine.

▲ nchmy 4 hours ago | parent | prev [-]

How about pg on Unix socket?

▲ eurleif 2 hours ago | parent [-]

    Running 100,000 `SELECT 1` queries:
    PostgreSQL (localhost): 2.84 seconds
    PostgreSQL (Unix socket): 1.93 seconds
    SQLite (in-memory): 0.07 seconds
    SQLite (tempfile): 0.06 seconds

(https://gist.github.com/leifkb/b940b8cdd8e0432cc58670bbc0c33...)

	▲	nchmy 20 minutes ago \| parent [-]
		seems about right

▲ stavros 10 hours ago | parent | prev | next [-]

It is insignificant if you're doing 100k queries per day, and you gain a lot for your 3 extra seconds a day.

▲ Izmaki 10 hours ago | parent | prev [-]

What a useful "my hello-world script is faster than your hello-world script" example.