5 ms·

> 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

> 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.

laurencerowe 5y ago
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-elastic-file-system-amazon-efs-now-supports-nfsv4-lock-upgrading-and-downgrading/ 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.
- marcosdumay 5y ago
  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.
- slapfrog 5y ago
  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.
bobthebuilders 5y ago
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.
- deleted 5y ago
  [deleted]
- 411111111111111 5y ago
  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
  - mdaniel 5y ago
    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 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_hsqldb_db http://hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_hsqld... https://db.apache.org/derby/#What+is+Apache+Derby%3F https://db.apache.org/derby/#What+is+Apache+Derby%3F
    - 411111111111111 5y ago
      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.
chungy 5y ago
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.
- quietbritishjim 5y ago
  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.

laurencerowe 5y ago

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-elastic-file-system-amazon-efs-now-supports-nfsv4-lock-upgrading-and-downgrading/ 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.

marcosdumay 5y ago
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.
slapfrog 5y ago
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.

bobthebuilders 5y ago

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.

deleted 5y ago
[deleted]
411111111111111 5y ago
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
- mdaniel 5y ago
  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 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_hsqldb_db http://hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_hsqld... https://db.apache.org/derby/#What+is+Apache+Derby%3F https://db.apache.org/derby/#What+is+Apache+Derby%3F
  - 411111111111111 5y ago
    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.

chungy 5y ago

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.

quietbritishjim 5y ago
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.