7 ms·

> PostgreSQL has taken a complex problem and solved it to such an effective degree that all of its competitors are essentially obsolete, perhaps with the except

> PostgreSQL has taken a complex problem and solved it to such an effective degree that all of its competitors are essentially obsolete, perhaps with the exception of SQLite. 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.

beermonster 5y ago
SQLite is literally on billions of devices and most people (even tech folk) are none the wiser.
pedrocr 5y ago
I'm always amazed at the amazing reviews SQLite gets, particularly in a thread about PostgreSQL. I get the angle of SQLite competing with fopen(). If what you need is a file format you can do a lot worse than SQLite. But I've worked on multiple desktop apps where the only reason SQLite gets used for the internal database is that PostgreSQL doesn't have a mode where you can ship it as part of the app easily and asking the user to setup a database is a non-starter. If not for that the performance benefits of the swap would be very significant. I suspect if PostgreSQL shipped such a mode a very large percentage of SQLite uses would switch over.
- donarb 5y ago
  On the Mac at least, there is PostgresApp. A double-clickable application that wraps a PostgreSQL server. Installation takes a minute or two without having to use the command line for compilation. https://postgresapp.com https://postgresapp.com
- deleted 5y ago
  [deleted]
- hitekker 5y ago
  The "mode" you wish for is a rearchitecture of PostgreSQL. Embedded and client-server DBs are two distinct systems.
  - 7952 5y ago
    Why though? Naively it would be possible to bundle postgres and have your app connect on localhost. What would be wrong with doing that?
    - handrous 5y ago
      You could do it, but you'd be paying (in complexity, resource use, and bundle size) for a bunch of features and optimizations you don't need, and it'd be tuned for a totally different use case than occasional access by one local process.
- Tostino 5y ago
  Completely agreed. I would love to be able to use an embedded version of Postgres. I have no interest in SQLite though because of the dynamic typing, reduced feature set, lack of advanced features, etc...
- marcosdumay 5y ago
  On Linux you don't actually need to install Postgres. For Windows there is this: https://garethflowers.dev/postgresql-portable/ https://garethflowers.dev/postgresql-portable/ I agree it could be better packaged, but it's actually not impossible to ship a Postgres with your program.
  - SahAssar 5y ago
    > On Linux you don't actually need to install Postgres. What do you mean? It's not like postgres is bundled by default with most distros.
    - marcosdumay 5y ago
      You can run it without installing. You can run almost anything on Linux without installing.
      - SahAssar 5y ago
        That's true for all OS'es (except iOS & android), if you have the executable you can run it. Installing usually just consists of placing the executable on a specific path and creating some directories to hold data/config/logs. You also often need to consider the dependencies of the application (unless it's statically compiled), so it's a lot easier to use your distros package manager.
        marcosdumay 5y ago
        That's really not true for all applications, and Windows applications tend to require being installed, while Mac ones are all over the place. 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 :)
        SahAssar 5y ago
        I still don't get what you mean (or exactly what you mean when you say "installing"), Postgres can be a simple executable on OSX and Windows and on linux it is usually "installed" because it needs to setup a data directory, user/group, config directory and so on. 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.
- anarazel 5y ago
  There's a decent bit of optimizations that sqlite can do by virtue of effectively being single-writer that postgres can't without compromising a lot of other workloads. That's e.g. one of (but not the only) the contributing factors to why the per-row overhead in sqlite is smaller than in PG. And of course something like PG won't be embeddable into the address space of an application, which invariably leads to higher latency (even if it still can be low). 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...
- sa46 5y ago
  It's possible but tediuos to run Postgres standalone. I packaged Postgres up for our Bazel build which requires a hermetic environment. The hard parts were: 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@postnewspapers.com.au https://www.postgresql.org/message-id/4E0DE1B6.6070600@postn...
  - mleonard 5y ago
    Very interesting. Is any of this open source? I'd love to take a look! 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_services_for_integration_tests_with/gfsmq6a?utm_source=share&utm_medium=web2x&context=3 https://www.reddit.com/r/bazel/comments/kcmbwb/how_to_run_se...
    - sa46 5y ago
      Sure, here's sketch of how it works: https://github.com/jschaf/bazel-postgres-sketch https://github.com/jschaf/bazel-postgres-sketch
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.
valbaca 5y ago
> but that aside strikes me as high praise for SQLite It's intended to be and is deserved.
pcr910303 5y ago
SQLite is an exceptional piece of software as well… 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. :-(
- password4321 5y ago
  Does https://duckdb.org https://duckdb.org enforce types?
- names_are_hard 5y ago
  Agreed on all points. I'd also add that I miss table-valued functions in sqlite.
  - DemocracyFTW 5y ago
    you can write your own table-valued, aggregate and window functions when you use better-sqlite3 (see https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/api.md; https://github.com/JoshuaWise/better-sqlite3/blob/master/doc... see Database#function(), Database#aggregate(), Database#table()) in JavaScript.
    - names_are_hard 5y ago
      This is really cool, thanks for pointing me to that. I'm still probably going to migrate to postgres because I'd like the functions to exist in the db and not just in code, so I can use them in ad-hoc queries. But this is worth considering as an option.
- open-source-ux 5y ago
  It's worth taking a look at the Firebird relational database. 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/ https://firebirdsql.org/en/features/
amedvednikov 5y ago
SQLite is perfect except for dynamic types.
smt88 5y ago
SQL Server and Oracle are also fantastic software. There may not be any good reason to use them today for a new product, but they're far from obsolete. 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.
orev 5y ago
To CoRecursive podcast has a great interview with Richard Hipp (author of SQLite). Very much worth a listen. https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/

beermonster 5y ago

SQLite is literally on billions of devices and most people (even tech folk) are none the wiser.

pedrocr 5y ago

I'm always amazed at the amazing reviews SQLite gets, particularly in a thread about PostgreSQL. I get the angle of SQLite competing with fopen(). If what you need is a file format you can do a lot worse than SQLite. But I've worked on multiple desktop apps where the only reason SQLite gets used for the internal database is that PostgreSQL doesn't have a mode where you can ship it as part of the app easily and asking the user to setup a database is a non-starter. If not for that the performance benefits of the swap would be very significant. I suspect if PostgreSQL shipped such a mode a very large percentage of SQLite uses would switch over.

donarb 5y ago
On the Mac at least, there is PostgresApp. A double-clickable application that wraps a PostgreSQL server. Installation takes a minute or two without having to use the command line for compilation. https://postgresapp.com https://postgresapp.com
deleted 5y ago
[deleted]
hitekker 5y ago
The "mode" you wish for is a rearchitecture of PostgreSQL. Embedded and client-server DBs are two distinct systems.
- 7952 5y ago
  Why though? Naively it would be possible to bundle postgres and have your app connect on localhost. What would be wrong with doing that?
  - handrous 5y ago
    You could do it, but you'd be paying (in complexity, resource use, and bundle size) for a bunch of features and optimizations you don't need, and it'd be tuned for a totally different use case than occasional access by one local process.
Tostino 5y ago
Completely agreed. I would love to be able to use an embedded version of Postgres. I have no interest in SQLite though because of the dynamic typing, reduced feature set, lack of advanced features, etc...
marcosdumay 5y ago
On Linux you don't actually need to install Postgres. For Windows there is this: https://garethflowers.dev/postgresql-portable/ https://garethflowers.dev/postgresql-portable/ I agree it could be better packaged, but it's actually not impossible to ship a Postgres with your program.
- SahAssar 5y ago
  > On Linux you don't actually need to install Postgres. What do you mean? It's not like postgres is bundled by default with most distros.
  - marcosdumay 5y ago
    You can run it without installing. You can run almost anything on Linux without installing.
    - SahAssar 5y ago
      That's true for all OS'es (except iOS & android), if you have the executable you can run it. Installing usually just consists of placing the executable on a specific path and creating some directories to hold data/config/logs. You also often need to consider the dependencies of the application (unless it's statically compiled), so it's a lot easier to use your distros package manager.
      - marcosdumay 5y ago
        That's really not true for all applications, and Windows applications tend to require being installed, while Mac ones are all over the place. 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 :)
        SahAssar 5y ago
        I still don't get what you mean (or exactly what you mean when you say "installing"), Postgres can be a simple executable on OSX and Windows and on linux it is usually "installed" because it needs to setup a data directory, user/group, config directory and so on. 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.
anarazel 5y ago
There's a decent bit of optimizations that sqlite can do by virtue of effectively being single-writer that postgres can't without compromising a lot of other workloads. That's e.g. one of (but not the only) the contributing factors to why the per-row overhead in sqlite is smaller than in PG. And of course something like PG won't be embeddable into the address space of an application, which invariably leads to higher latency (even if it still can be low). 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...
sa46 5y ago
It's possible but tediuos to run Postgres standalone. I packaged Postgres up for our Bazel build which requires a hermetic environment. The hard parts were: 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@postnewspapers.com.au https://www.postgresql.org/message-id/4E0DE1B6.6070600@postn...
- mleonard 5y ago
  Very interesting. Is any of this open source? I'd love to take a look! 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_services_for_integration_tests_with/gfsmq6a?utm_source=share&utm_medium=web2x&context=3 https://www.reddit.com/r/bazel/comments/kcmbwb/how_to_run_se...
  - sa46 5y ago
    Sure, here's sketch of how it works: https://github.com/jschaf/bazel-postgres-sketch https://github.com/jschaf/bazel-postgres-sketch

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.

valbaca 5y ago

> but that aside strikes me as high praise for SQLite It's intended to be and is deserved.

pcr910303 5y ago

SQLite is an exceptional piece of software as well… 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. :-(

password4321 5y ago
Does https://duckdb.org https://duckdb.org enforce types?
names_are_hard 5y ago
Agreed on all points. I'd also add that I miss table-valued functions in sqlite.
- DemocracyFTW 5y ago
  you can write your own table-valued, aggregate and window functions when you use better-sqlite3 (see https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/api.md; https://github.com/JoshuaWise/better-sqlite3/blob/master/doc... see Database#function(), Database#aggregate(), Database#table()) in JavaScript.
  - names_are_hard 5y ago
    This is really cool, thanks for pointing me to that. I'm still probably going to migrate to postgres because I'd like the functions to exist in the db and not just in code, so I can use them in ad-hoc queries. But this is worth considering as an option.
open-source-ux 5y ago
It's worth taking a look at the Firebird relational database. 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/ https://firebirdsql.org/en/features/

amedvednikov 5y ago

SQLite is perfect except for dynamic types.

smt88 5y ago

SQL Server and Oracle are also fantastic software. There may not be any good reason to use them today for a new product, but they're far from obsolete. 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.

orev 5y ago

To CoRecursive podcast has a great interview with Richard Hipp (author of SQLite). Very much worth a listen. https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/