You got my hopes up, but it's WASM for now, not something I could add into a golang [1] or python app and have running like sqlite. OK, still hoping...!
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.
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.
SQLite is used more in resource-constrained environments. If I had much memory to waste, I would've used a local PostgreSQL installation to begin with.
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.
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.
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.
You'd still be incurring client/server IPC for individual database queries, which would cost you one of the benefits of using SQLite (IDGAF-queries that just hammer tables repeatedly because there's almost no cost to doing so).
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.
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.
> 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.
I've been using postgres as a local database for one of my personal projects, a GUI app or to run python tests that depend on it without having to rely on installing it in my environment.
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
18 comments
[ 4.3 ms ] story [ 33.7 ms ] thread[1]https://github.com/electric-sql/pglite/issues/89
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.
https://electric-sql.com/
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.
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.
https://github.com/fergusstrange/embedded-postgres
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 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.
Am I getting old ? Seems obvious to me.
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