What if we treated Postgres like SQLite?
maragu.dev
maragu.dev
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.
I often wish that Firebird had a license that people found friendlier to use as it always felt like a perfect technical option for many use cases from embedded to a standalone server. PG has clearly eclipsed it at this point though.
Would be nice if PG could at least automagically forward update when it starts and a database is from a prior version.
I love the pg_* commands but they ain't exactly the speediest things around.
It does rule out some common SQLite use cases, such as using the database for app persistence. The app would never be able to upgrade the major version of PostgreSQL, unless it bundled PostgreSQL binaries for every major version ever used.
I wonder whether packaging everything in Docker (including a specific Postgres container identified by hash or whatever) and deploying on the same architecture would solve this?
Our docker-compose.yaml has Postgrs, Redis, OPA, Traefik and then our four services. Works a treat.
One thing we haven't solved for is how to have the PG upgrade work when we update major in the image. Nice that 16 should work for a long while.
Even with SQLite, for a simple file copy to work reliably, one has to set these three:
"_pragma=synchronous(FULL)",
"_pragma=checkpoint_fullfsync(ON)",
"_pragma=fullfsync(ON)",For sqlite, I would recommend "sqlite3 app.db '.backup backup.db'" though, if that's an option. Guaranteed consistent!
It's an embeddable Postgres you can run in process as a local client DB, just like SQLite but it's actually Postgres.
https://github.com/electric-sql/pglite-bindings
It would still be missing the standardized disk format aspect of sqlite, but otherwise will be neat.
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.
Yeah, this way you're not going to notice performance issues with your data model or application. Test databases should be of sizes comparable to what's expected in production, specifically after several years in production.
I'm curious, when do you want to treat your Postgres like SQLite? :-) That's basically the opposite of what I was thinking of in the article.
ATTACH 'dbname=mydb user=dbname host=25.25.25.25 password=aaaa AS db (TYPE postgres, READ_ONLY);
You can CREATE TABLE my_duck_table_stream AS SELECT * FROM db.table_stream;
It's just fun
I created a Python package that downloads an embedded Postgres binary, sets up the database, and gives you a database URL: https://github.com/kketch/tinypg
It downloads pg binaries from this project: https://github.com/zonkyio/embedded-postgres-binaries. There are other similar projects listed on that page that provide this for Java, Go, Rust and Node
I've wrapped it with yet another shell script to make it usable just like this:
`export DB_URL=$(./pgtest.sh)`
This version (pgtest.sh), just creates a disposable ephemeral postgres: https://gist.github.com/kketch/d4e19a7fb6ebc1cfc265af44c1b41...
This version (pgdev.sh), starts a postgres instance (if not already running) and persists the DB. Also supports seeding it with a SQL script (dev_seed.sql): https://gist.github.com/kketch/88bb5b766994e247a9f2c37f13306...
UPDATE: Actually, I see it is downloaded (and I think cached?). I can't recall if you can embed as an option or not.
Beyond that, why care whether the "embedding" involves a spawned process? It still works great for integration tests which I suspect is the main use case, and for specialized data analysis software where a spawned process is no big deal.
This sounds like it could be pretty useful.
With SQLite, although all approaches are available, my fav is to use https://github.com/ncruces/go-sqlite3 which uses Wazero.
I try to avoid CGO if I can because it adds compile-time complexity, making it unfriendly for a user to compile.
Since Postgres is always a network connection, I don't believe any CGo is required.
> I try to avoid CGO if I can because it adds compile-time complexity, making it unfriendly for a user to compile.
Using zig as your C compiler mostly fixes this, but you can't 100% get rid of the complexity, but I've cross compiled using Zig cc to Windows/Mac/Linux pretty easily via CGo.
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.
So you do periodic backups, not incremental on every write or something (read replica-like)?
It's important to me to not lose any data once committed if at all possible.
(For testing, I've sped everything up by running migrations on `template1` and every test gets a random database name. Works wonders.)
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 most annoying part of it is that Postgres absolutely detests running as PID 0, which makes running `make check` in Docker containers an unnecessarily frustrating experience. I understand why Postgres rejects PID 0 by default, but I would really like for them to recognize strcmp(getenv("I_AM_IN_DOCKER_AND_I_DONT_CARE"), "true") or something.
Note that advanced vector-embedding querying techniques for approximate nearest neighbor search inevitably always need an absurd amount of memory, so it typically doesn't make sense to use them in a resource-constrained environment to begin with.
Next embedded model version moved to 8MB flash and 64MB RAM with the same software infrastructure.
It might sound confusing but I wish to genuinely have something of a simpler postgres (in sqlite?) and then later if need be, i could migrate to postgres I suppose.
Am I getting old ? Seems obvious to me.
It’s always given me a separation of concerns good feeling by seeing a dedicated db and app server and doesn’t seem like much overhead, given they are nearby machines in datacenter.
Also, our main reason was sharing a database license to have a well resourced multi-tenant/app db sever serving peripheral web app servers.
In the cloud, as you probably know, the usual way now is to spin up Postgres separately (RDS, Supabase, Planetscale, Crunchy Bridge, you name it). We've gotten so used to it that a different way of doing it is often not even considered.
But I think tooling has come a long way, and there have been a lot of learnings from the cloud, so it's time to swing the pendulum back and reconsider assumptions!
I meet an unexpected number of young devs that:
- Find hypermedia to be exotic. JSON web API is the only way that makes sense to them.
- Say they use the cloud to "save money".
- Use third-party services for things as basic as authentication because "it's hard".
- Encode security stuff in the frontend code.
It's the cycle of life.
After fully reading the article I came to understand it really was not referring to anything sqllite specific, was really 'what if you ran postgres as an application on a server', there really is nothing more to be gained from reading the article beyond this, and this is kind of the most basic deployment model for postgres for like the last 40 years.
And if not, I hope you didn't spend too long reading. :-)