I realize that this isn't the main point, but that aside strikes me as high praise for SQLite — which has also always struck me as exceptionally well built software.
SQLite occupies the domain of embedded (possibly in-memory and on low-resource systems) databases in which the subset of SQL unrelated to access control is all that's needed. Think of databases that take the place of application configuration, your browser bookmarks and cookies, a music library database.
They are both excellent pieces of software, yes, but they don't even live in the same problem space despite both having "SQL" in their names.
(I highly recommend to watch it, if only to get a sense of the smart and funny energy that Dr Richard Hipp puts off!)
That's true in theory, but I do find that they end up competing in practice when you're looking for something in between the two extremes.
I often seem to find myself in a position where I need a database, but it's only for one day's data, which is not huge but not tiny (say 100's of MB). It may be helpful to have a couple of processes accessing it, but probably only one will be a writer, and it's very likely (but not certain) that those processes will be on the same computer. It useful to have that data in a single file for archiving. Those are situations where you currently face a geniune choice between PostgreSQL and SQLite - more because both of them are a bit wrong, rather than because they're both perfect for it.
I wish there was something in the middle: a separate program you could spin up, so it's a client-server database like PostgreSQL, but it's just a single binary that doesn't need installing. And then it operates on a single file that it creates on the fly (or can open an existing one of course), a lot like SQLite. Hmm, as I think about it, it actually wouldn't be hard to make program that does that by making a thin wrapper around SQLite with e.g. gRPC.
I would definitely not try using this in a write heavy workload but could be interesting to consider in a write occasionally scenario.
At the time I was able to push it into something like 10 simultaneous write accesses (transparently serialized by SQLite). It was an adaptation of a MS Access file that would fail at approximately 5 people opening it at the same time (independently of their actual usage). Luckily I got access to a Postgres installation shortly afterwards so I didn't have to push SQLite any further.
I think it's a pretty different use case to what the parent wanted
Exact same story for its HyperSQL and Derby friends: http://hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_hsqld... https://db.apache.org/derby/#What+is+Apache+Derby%3F
Redis is considered an in memory store as well, even though it can persist it's data on disk too. And the capability isn't what makes it in-memory either, as you can store a SQLite db only in-memory too, and nobody would consider it to be an in-memory database. You could technically do the same for PostgreSQL, though that's not officially supported and needs a little effort on the users part.
I guess my phrasing was poor if you misunderstood me there, but I do stand by what I said: while H2 is a great project in Java-Land, it's very different to what I understood the parents goal to be.
The main consideration is typically about how the database is used. "Probably only one will be a writer" is a strong argument in favor of SQLite, which doesn't really have much of a concurrency story for writes (multiple processes can open the same SQLite database for writing, but they implement a block so only one at a time can actually write).
The file locks can be tricky to get working over NFS/CIFS with multiple computers, but it's still possible.
I agree it could be better packaged, but it's actually not impossible to ship a Postgres with your program.
What do you mean? It's not like postgres is bundled by default with most distros.
Yes, the OS doesn't impose any opinion. It's just a matter of what application developers do, and the developers of Linux applications tend to write code that doesn't require installing. As an example, there's Postgres :)
There is nothing about linux that makes postgres more or less portable than on windows or osx, and I'd be interested if you have any examples of postgres being used in a non-packaged way (outside of development tools like postgresapp.com).
Besides that, one of the strengths of most linux distros is that they provide a centralized way to install/upgrade packages like apt/yum/dnf/pacman and don't have to rely on third party tools like homebrew.
WRT "embeddable postgres":
Postgres relies on having multiple processes that collaborate on making database access fast. And it requires there to be only a single instance of postgres that can access the data. There's some inherent increase in difficulty of embedding something like that compared to something with sqlite's architecture.
Until recently PG didn't have a way to provide non-tcp access on windows, which made embedding on windows a bit more problematic. But since Win 10 unix domain sockets are available on windows, so things have gotten better.
I'd guess that after that the fact that a postgres installation consists out of many files, instead of a shared library or two, is the biggest difficulty. Those files are relocatable at least, but it's still far less convenient.
I don't know how much of a relevant factor the layout of the "data directory" is - for some database embedding scenarios it sure is convenient to only have to deal with a file or two. But moving a directory around isn't that much harder...
My guess is that somebody with interest could improve the situation measurably within a reasonable timeframe...
1. Postgres doesn't really didn't want to be statically compiled, according to [1].
2. Messing with LD_LIBRARY_PATH to point to an embedded glibc and openssl when running the heremetic postgres binaries.
3. Postgres will absolutely refuse to run the server process as root. That's sensible for a production environment but a pain in CI because it would require setting up another user on machines. I patched away the problem to allow Postgres to run as root.
On the bright side, with some minor tweaking, Postgres can start a fresh database in 300 ms which is plenty fast for tests and you avoid spinning up Docker containers. I ended up using template databases to only spin up the database once per test suite. Each tests then copies the database template for each test in the suite which reduced the setup time per test to ~20 ms.
Instead of using rules_foreign_cc to build Postgres from source with Bazel, I ended up building Postgres outside of Bazel with Docker and zipping it up by target platform (linux|darwin)_(arm64|amd64).
https://www.postgresql.org/message-id/4E0DE1B6.6070600@postn...
I'm guessing it's not open source - as I couldn't find it on your Github account or your company's account. If that's the case, would you be able to create a quick gist with a copy/paste of any of the code you can share? If you have the time I'd appreciate it!
Related links for anyone interested: - dataform uses bazel for ci tests. They builds redis from source, but run postgres as a container. See here: https://www.reddit.com/r/bazel/comments/kcmbwb/how_to_run_se...
Right. There's a lot of features that'll flat out not work if you somehow compile it statically. I don't think it's a useful thing to run tests with a version of postgres built like that. Nor does it really solve anything - postgres will still require a bunch of on-disk files.
You shouldn't need have to do so anyway - you can relocate the postgres installation itself when compiled normally. Binaries like postgres will try to find the files they need relative to their own location if not found at the builtin location.
Of course, libraries that PG binaries dynamically link to need to be somewhere in the library path. If you need to adjust that you can do it by adding rpaths to the binaries/libraries to additional locations, so you don't need to adjust LD_LIBRARY_PATH.
It’s lightweight, reliable, knows what it’s tasked for, does exactly wat it does, and easy to extend. I personally reach SQLite first in so many cases, including web backends. That the DB is a file that’s literally a cp away to backup is super-useful in development and debugging.
Really the only complaint is that type enforcement is lacking… which I wish I could have said not a big deal, but it turns out it is. I wish I had a embedded DB that’s as reliable and trustful, lightful as SQLite, but with types enforced. :-(
But this is worth considering as an option.
Firebird can be used as an embedded DB or an client/server DB. It supports a wide variety of data types, is open source and cross-platform too.
It's a database that's rarely discussed here (a mystery why not). It certainly deserves wider attention.
Firebird features: https://firebirdsql.org/en/features/
I know it’s completely unreasonable to judge Firebird today for Interbase 20 years ago, but admit that’s still the first thing that pops to my mind.
Do you feel Firebird offers something SQLite doesn’t?
Firebird can be used as a proper client/server database engine. It supports static data types and stored procedures (unlike SQLite). It sits somewhere between SQLite and PostgreSQL. So it may be worth evaluating if you don't want the heft of PostgreSQL but need features missing from SQLite.
Sounds like it's not quite as embedded as SQLite? Seems like it just smushes the server into your app and spins that up from within. Also it only seems to be properly "embedded" on Windows (no macOS support and Linux is a bit weird?)
> Finally, you can't just ship libfbembed.so with your application and use it to connect to local databases. Under Linux, you always need a properly installed server, be it Classic or Super.
Oracle has a lot of advanced functionality that no other RDBMS really has, and I do hit sharp edges of PG (especially in unexpected query planning decisions) more than I do in SQL Server.
And, as much as I hate MySQL, I have a few instances that are approaching 10 years old with billions of records that have almost never needed query optimization. I just added indices in logical places and they just work somehow.
It's intended to be and is deserved.