[orm]

Not directly an answer, but similar what if thinking got me wondering: wouldn't it be cool to be able to use postgres much like how you use sqlite with python? I implemented this idea as a pip-installable python package, https://github.com/orm011/pgserver, and your feedback would be great :) I use it for my projects.

[khimaros]

neat project! how have you found resource usage and startup time compares with SQLite?

[orm]

I have not compared it. Have you had issues in this front?

If you are asking in human terms, its instantaneous to start, it simply creates a few files etc and starts a process. If you were using Sqlite, there is no extra associated process. But the processes are simply waiting for your input, so normally this is not really extra work.

In benchmark terms, the way I'm using it startup time is not a huge issue, instead being able to use postgres is helpful (has extensions and you know you can eventually move to a hosted postgres if you want)

If startup really matters, the startup can be amortized (if multiple processes want to access the same file, they get a handle to the same already started process, so only the first one would have waited for startup).

In terms of ongoing resource consumption, I can easily have a bunch of separate servers running for different files (it depends on your workload how much work will happen behind the scenes.) I'm not sure how much extra work postgres needs to do vs sqlite beyond what is kind of inherent for a workload (eg index creation, saving a bunch of data etc).