What if we treated Postgres like SQLite?

> You can just install Postgres on your single big and beefy application server (because there’s just the one when you use SQLite, scaled vertically), and run your application right next to it.

Am I getting old ? Seems obvious to me.

The PostgreSQL data directory format is not very stable or portable. You can't just ZIP it up and move it to a different machine, unless the new machine has the same architecture and "sufficiently" similar PostgreSQL binaries.

In theory the data directory works with any PostgreSQL binaries from the same major version of PostgreSQL, but I have seen cases where this fails e.g. because the binaries were from the same major version but compiled with different build options.

Some apps do, the most used I know of is Blackmagic's Davinci Resolve, the video editor with a relatively full featured free edition available. I think this has more to do with its roots being in a high end networked environment but still, the local desktop version installs Postgres.

When I want to treat my Postgres like SQLite , I use DuckDB using PostgreSQL Extension https://duckdb.org/docs/stable/core_extensions/postgres.html and this is one of may extensions, there is more and I love DuckDB for that

I think this is a neat direction to explore. I've wondered in the past whether you could use https://pglite.dev/ as if it were SQLite.

I do this using the Docker approach, especially for low scale web apps that run on a single VM. I like that its full Postgres versus the sometimes odd limits of SQLite. My usual approach uses a Trafik container for SSL, Django+gunicorn web app, and Postgres container; all running as containers one VM. Postgres uses a volume, which I back up regularly. For testing I use `eatmydata` which turns off sync, and speeds up test cycles by a couple percent.

I haven't tried the unix socket approach, I suppose I should try, but it's plenty performant as is. One project I built using this model hit the HN front page. Importantly, the "marketing page" was static content on a CDN, so the web app only saw users who signed up.

I think this is a great idea for testing. MSSQL has LocalDB which is used a lot throughout the .NET ecosystem:

https://learn.microsoft.com/en-us/sql/database-engine/config...

For heavy duty production use (i.e., pushing the actual HW limits), I would feel more comfortable with the SQLite vertical. Unix sockets are fast, but you are still going across process boundaries. All of SQLite can be ran on the same thread as the rest of the application. This can dramatically reduce consumption of memory bandwidth, etc.

You can, just embed it in your Go app:

https://github.com/fergusstrange/embedded-postgres

Instead of sqlite-vec you can take a look at the new sqlite-vector extension: https://substack.com/home/post/p-172465902

From what I've read about it, DuckDB comes close. Regular files, like sqlite, but pg functionality.

I don’t recall the mechanics but I do know that folks have bundled starting a local instance of PG solely for unit tests.

There’s a pre-install step to get PG on the machine, but once there, the testing framework stands it up, and shuts it down. As I recall it was pretty fast.

The huge advantage of SQLite is not that it's on the same machine, but it's that is it in process which makes deployment and everything else just simpler.