What if we treated Postgres like SQLite?

You can, just embed it in your Go app:

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

This just runs Postgres as a process, right?

Yes, but it embeds it in your executable so it is transparent to the end user.

UPDATE: Actually, I see it is downloaded (and I think cached?). I can't recall if you can embed as an option or not.

Does this use an external binary or CGO or Wazero (Wasm) or is it rewritten in Go?

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.

> Does this use an external binary or CGO or Wazero (Wasm) or is it rewritten in Go?

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.

Not OP, but I think it does run Postgres as a process. However, IMHO the general use case for SQL is for external actors (humans, machines) to get access to the underlying data in a structured way. So I see a benefit for a true in-process embedding of Postgres if the process exposed a Postgres TCP/IP port 5432, etc. (Hook your software up to a query tool, a reporting interface, etc.)

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.

Can you have a socket that's only shared between a parent and child process?

This sounds like it could be pretty useful.

Sure, socketpairs on Linux.