On Mon, Oct 13, 2025 at 4:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rob Sargent <robjsargent@gmail.com> writes:
> > On 10/12/25 16:10, David Barsky wrote:
> >>> Postgres is not an embedded database, if you want that experience then
> >>> use a database that is designed to be embedded.
>
>>> That's fair, especially from an operational standpoint. However, I _think_
>>> Postgres can get really close to an embedded database's development
>>> experience by doing a few tricks that I'll elaborate on later on in this email.
>
>> Were I a betting man, I would bet heavily against this community, which
>> prides itself on NOT losing data, allowing an option that would do just that.
>
> But I concur with the point that we're not here to pretend to be an
> embedded database, as there are other projects that do that better
> (for example, our good friends at SQLite).
Heavy user of SQLite here, and I have to disagree Tom.
The main reason PostgreSQL can't be embedded is because
of its process-based model with globals. And perhaps locking
as well. But otherwise it would a dream-come-true for single-user
mode of our app, and for testing as well, I have to agree with David.
Even a localhost-only mode that still functions as a normal cluster
except auth is entirely OS-based and it uses a random port
(or bypasses TCP entirely in a *CROSS*-platform way), based on
which pgdata-directory is used, would be OK.
There's apparently no way to abstract the "transport" between libpq
and the server, must be TCP (or *nix only socket files), cannot be an
in-memory channel (for the embedded non-shared case),
nor shared-memory (for the shared-case across localhost processes).
SQLite is fantastic, but it's type-system and lock-model are too restrictive,
for a general DB. Similar to David, I think PostgreSQL is close to my ideal
above, yet still far-enough (and perhaps unwilling enough, as a community)
to venture into embedded and localhost use-cases, that it's frustrating.
My $0.02. --DD
