3 ms·

PostgreSQL occupies the domain of, and competes with, relational databases in a server-client model, especially those with extremely large datasets. Think of da

by chungy 5y ago

PostgreSQL occupies the domain of, and competes with, relational databases in a server-client model, especially those with extremely large datasets. Think of databases to track inventory, manage user accounts on a web domain, and so forth. 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.

srcreigh 5y ago
This point is echoed by the creator of SQLite, Dr Richard Hipp, in his excellent talk "SQLite: The Database at the Edge of the Network". [1] "SQLite doesn't compete with client-server databases, it competes with fopen" (I highly recommend to watch it, if only to get a sense of the smart and funny energy that Dr Richard Hipp puts off!) [1]: https://www.youtube.com/watch?v=Jib2AmRb_rk https://www.youtube.com/watch?v=Jib2AmRb_rk
quietbritishjim 5y ago
> 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

by chungy 5y ago

srcreigh 5y ago

This point is echoed by the creator of SQLite, Dr Richard Hipp, in his excellent talk "SQLite: The Database at the Edge of the Network". [1] "SQLite doesn't compete with client-server databases, it competes with fopen" (I highly recommend to watch it, if only to get a sense of the smart and funny energy that Dr Richard Hipp puts off!) [1]: https://www.youtube.com/watch?v=Jib2AmRb_rk https://www.youtube.com/watch?v=Jib2AmRb_rk

quietbritishjim 5y ago

> 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