Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

Joshua - adding to what Fabio said (with which I agree wholeheartedly!) -

It will be worthwhile for you to itemize the attributes and needed features
for making a final decision about the architecture of the application you
plan to deploy.

While you are familiar with PostgreSQL and like its features, it may not
wind up being the best match for what you are trying to accomplish. I'm not
sure your concerns about SQLite are true roadblocks. If you are looking to
deploy an executable supported by one or more DLLs but no external
processes, you won't find many mature and truly embeddable DBMSs out there.
SQLite is widely used and well supported, and some of the functionality
you'd normally consider putting in stored procedures might fit in with the
idea of creating custom functions. A few more steps than creating a
PostgreSQL stored procedure or function, but perhaps this approach would
make SQLite a reasonable choice for you. See:

https://stackoverflow.com/questions/2108870/how-to-create-custom-functions-in-sqlite

Back to the question about attributes and features of the DBMS, and Fabio's
question to you re: your definition of an embedded database. An embedded
database is typically used by a single user and is only available within
the context of the current application and user / memory space. It should
also be self-tuning and self-managing, as a typical end user can't and
shouldn't be expected to also handle any DBA tasks. Several databases are
considered "embedded", but the criteria used for this label may not match
your own. If you can spell out your specific expectations (e.g., the app
and database can be deployed in a single exe, etc.), this will help
tremendously in homing in on the right choice.

- Jon

<https://www.linkedin.com/in/jonstrong/>
<https://www.jonathanrstrong.com>

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong(at)gmail(dot)com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong>*

On Tue, Sep 22, 2020 at 10:18 AM Fabio Ugo Venchiarutti <
f(dot)venchiarutti(at)ocado(dot)com> wrote:

>
> On 21/09/2020 17:53, Joshua Drake wrote:
> > 3. The ability to embed PG to run in an automatic, quiet manner as part
> > of something else. I know about SQLite, but it's extremely limited to
> > the point of being virtually useless IMO, which is why I cannot use that
> > for anything nontrivial. I want my familiar PostgreSQL, only not require
> > it to be manually and separately installed on the machine where it is to
> > run as part of some "application". If I could just "embed" it, this
> > would allow me to create a single EXE which I can simply put on a
> > different machine to run my entire "system" which otherwise takes *tons*
> > of tedious, error-prone manual labor to install, set up and maintain. Of
> > course, this is probably much easier said than done, but I don't
> > understand why PG's architecture necessarily dictates that PG must be a
> > stand-alone, separate thing. Or rather, why some "glue" cannot enable it
> > to be used just like SQLite from a *practical* perspective, even if it
> > still is a "server-client model" underneath the hood. (Which doesn't
> > matter at all to me, nor should it matter to anyone else.)
>
> It depends what you mean by "embedded".
> If you want sqlite's linked library approach, where the database calls
> literally run your process' address space, then that's a no go, as
> postgres is a multi-user database server with its own process hierarchy.
>
>
> However, postgres also is a rather agnostic command that does not detach
> from the parent's terminal/stdio unless instructed to, so nothing stops
> your bespoke application from launching and managing its own postmaster
> as a directly managed child process - started as part of application
> initialisation - and having that listen on a local socket only available
> to the application itself; this is what we implemented in some of our
> installations where postgres is enslaved to the cluster control system -
> the configuration is re-generated at every restart (IIRC Patroni does
> something similar minus the unix socket part).
>
>
> A plethora of systems are built around the notion of programs calling
> other programs and managing the process' life cycle. The limiting factor
> to such architecture tends to be the OS's process control semantics and
> API (notoriously weaker or more contrived on non-unix-like OSs), but
> that's not postgres' fault.
>
>
>
>
>
> --
> Regards
>
> Fabio Ugo Venchiarutti
> OSPCFC Network Engineering Dpt.
> Ocado Technology
>
> --
>
>
> Notice:
> This email is confidential and may contain copyright material of
> members of the Ocado Group. Opinions and views expressed in this message
> may not necessarily reflect the opinions and views of the members of the
> Ocado Group.
>
> If you are not the intended recipient, please notify us
> immediately and delete all copies of this message. Please note that it is
> your responsibility to scan this message for viruses.
>
> References to the
> "Ocado Group" are to Ocado Group plc (registered in England and Wales with
> number 7098618) and its subsidiary undertakings (as that expression is
> defined in the Companies Act 2006) from time to time. The registered
> office
> of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
> Hatfield, Hertfordshire, AL10 9UL.
>
>
>