Comment by quietbritishjim | Hacker News Reader

Comment by quietbritishjim | Hacker News Reader

Parent Full thread

quietbritishjim

> they don't even live in the same problem space despite both having "SQL" in their names.

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.

It's possible to run SQLite on an NFS share (with caveats: file locking must work correctly, WAL mode does not work at all). AWS EFS Elastic Filesystem specifically calls out its compatibility with SQLite. https://aws.amazon.com/about-aws/whats-new/2017/03/amazon-el...

I would definitely not try using this in a write heavy workload but could be interesting to consider in a write occasionally scenario.

It's also possible to put a two-stroke engine on a mountain bike and ride it on a limited access highway. But that's really not what the bike is meant for, and would almost never be the right way of going about things.

I did once run SQLite on a Windows SMB share. It works unreasonably well.

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.

SQLite isn't just for datasets that are tiny. There are a few multi-TB-sized SQLite databases around the world.

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.

But there are a couple of "probably"s in my comment above doing a lot of work. There is probably only one writer, and it's probably all running on one machine - but occasionally one or both of those isn't true, and suddenly SQLite starts to look less attractive.

We use H2 Database (JVM only) at work for that. It solves the problem really well of a bunch of stuff that needs access to a single file DB, while still being able to embed the DB engine.

H2 is a jdbc only in-memory database, so that's only an option for volatile data from/to Java.

I think it's a pretty different use case to what the parent wanted

It most certainly is not just in-memory; it can do in-memory, but also has file and server options: https://h2database.com/html/features.html#connection_modes

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

It's jdbc only, with the main usage being in-memory.

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.

Reply on news.ycombinator.com