In Praise of PostgreSQL(drewdevault.com)
drewdevault.com
In Praise of PostgreSQL
https://drewdevault.com/2021/08/05/In-praise-of-Postgres.html
138 comments
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.
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.
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
(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
> 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.
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.
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.
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.
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.
I would definitely not try using this in a write heavy workload but could be interesting to consider in a write occasionally scenario.
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.
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.
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.
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]
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
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
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.
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.
> but that aside strikes me as high praise for SQLite
It's intended to be and is deserved.
It's intended to be and is deserved.
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. :-(
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. :-(
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/
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/
> embedded DB
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.
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.
I worked with a company a long time ago that was using Interbase as the backend for a few websites. It had the interesting property that if you were using the command line client to connect to the server, and you used ^Z to pause the client, then the server would hang for all clients until you resumed it.
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?
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?
Nothing beats the nimbleness, simplicity and speed of SQLite, but it is not a client/server database engine (although it is often used for that purpose due to its speed).
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.
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.
Does https://duckdb.org enforce types?
Agreed on all points. I'd also add that I miss table-valued functions in sqlite.
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/doc...; see Database#function(), Database#aggregate(), Database#table()) in JavaScript.
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.
But this is worth considering as an option.
Javascript does exactly wat it does but I don't see anybody rushing to praise it
SQLite is literally on billions of devices and most people (even tech folk) are none the wiser.
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.
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@postn...
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...
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_se...
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...
We pull Postgres from Nix using rules_nixpkgs for our Bazel tests. It works pretty well. I wrote some additional infrastructure that makes it easy to test against different versions of postgres (11, 12, 13 - all available from Nix) using simple Bazel command line options.
Sure, here's sketch of how it works: https://github.com/jschaf/bazel-postgres-sketch
Amazing thanks so much!
> 1. Postgres doesn't really didn't want to be statically compiled, according to [1].
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.
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.
I ended up skipping static compilation for the reasons you outlined (not necessary). I packaged everything up in a directory and used LD_LIBRARY_PATH. I linked to a sketch on GitHub in a sibling comment.
On Linux you don't actually need to install Postgres. For Windows there is this: 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.
I agree it could be better packaged, but it's actually not impossible to ship a Postgres with your program.
> 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.
What do you mean? It's not like postgres is bundled by default with most distros.
You can run it without installing. You can run almost anything on Linux without installing.
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.
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 :)
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 :)
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.
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.
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]
The "mode" you wish for is a rearchitecture of PostgreSQL. Embedded and client-server DBs are two distinct systems.
Why though? Naively it would be possible to bundle postgres and have your app connect on localhost. What would be wrong with doing that?
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.
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...
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...
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...
SQLite is perfect except for dynamic types.
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.
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.
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/
I'll second the praise for the Postgres documentation. I learned a lot about Postgres simply by looking through the documentation and reading the parts that looked interesting.
Same here. I feel like I built several years of my career on reading that documentation and knowing where to find things when other folks didn't, so they'd come and ask me and I'd just send them to the right place.
Parts of the documentation are very good.
For example https://www.postgresql.org/docs/13/wal-configuration.html is done properly: it describes what that part of the program actually does, with the settings that affect it introduced at the appropriate point, separately from the reference documentation which lists the same settings in alphabetical order.
But not all aspects of Postgres get this quality of documentation. It would be good to see a similar treatment of the checks the planner or executor performs to decide whether to permit access to a particular table and column and row, rather than having to deduce it from the separate descriptions of the security features.
(For example, the basic fact that a VIEW accesses a table using the privileges of the view's owner, not those of the user executing the query, is very hard to find in the documentation.)
For example https://www.postgresql.org/docs/13/wal-configuration.html is done properly: it describes what that part of the program actually does, with the settings that affect it introduced at the appropriate point, separately from the reference documentation which lists the same settings in alphabetical order.
But not all aspects of Postgres get this quality of documentation. It would be good to see a similar treatment of the checks the planner or executor performs to decide whether to permit access to a particular table and column and row, rather than having to deduce it from the separate descriptions of the security features.
(For example, the basic fact that a VIEW accesses a table using the privileges of the view's owner, not those of the user executing the query, is very hard to find in the documentation.)
Not just about postgres internals, but generally about SQL in my case.
Interestingly enough the competition for "biggest impact on my SQL Knowledge"... Is a book about MS Access 97 which taught me enough about how SQL actually works that I could understand Postgres docs.
Interestingly enough the competition for "biggest impact on my SQL Knowledge"... Is a book about MS Access 97 which taught me enough about how SQL actually works that I could understand Postgres docs.
It is great that you can switch on any page in the documentation to the documentation of another release of PostgreSQL. That is extremely helpful if you are working with different versions.
> all of its competitors are essentially obsolete
For most companies I've worked at I've used either SQL Server or Oracle and from what I've seen when looking at job posts recently the demand is only increasing. Although I live in Los Angeles and not Silicon Valley.
Additionally Facebook and other large tech companies still use MySQL and outside of silicon valley many large companies still run IBM databases.
I don't see the other major databases being obsolete at all.
I've used PostgresSQL before for learning (~3 years ago) but didn't really like the web based UI compared to SQL Server or Oracle query tools.
Does anyone have recommended PostgresSQL query interfaces?
For most companies I've worked at I've used either SQL Server or Oracle and from what I've seen when looking at job posts recently the demand is only increasing. Although I live in Los Angeles and not Silicon Valley.
Additionally Facebook and other large tech companies still use MySQL and outside of silicon valley many large companies still run IBM databases.
I don't see the other major databases being obsolete at all.
I've used PostgresSQL before for learning (~3 years ago) but didn't really like the web based UI compared to SQL Server or Oracle query tools.
Does anyone have recommended PostgresSQL query interfaces?
Regarding SQL Server at least (I can't speak to Oracle), they've created something of a network effect.
SQL Server is really expensive. But, the more Microsoft stack you have in house the more discounts you get. The more Microsoft certified staff you have, the more discounts you get. This biases companies to hiring more developers with Microsoft certs, which leads to more developers getting Microsoft certs, which leads to more discounts and a feeling of investment at higher levels of the company that lead them to be unwilling to switch.
I spent 5 years at a company that was using SQL Server as the backbone of it's flagship product. They pushed it as far as they could, but ultimately the biggest issues with the product at the company revolved around either limitations of SQL Server, lack of features with SQL Server or licensing being prohibitive of different types of beneficial scaling strategies. I pushed for them to make the switch to PostgreSQL for probably my last 3 years there as part of the architecture group, including migration and training plans, etc but it was like talking to a wall. Nobody could acknowledge the SQL Server issues. Everyone who had a part in considering solutions was too invested as far as I could tell. Eventually they tried moving to Couchbase (NoSQL) before finally making the switch to PostgreSQL from what I heard from people I know who still work there.
As far as query interfaces go, you can use just about any tool you like. I prefer Navicat myself but I know a lot of people like DBeaver and DataGrip.
SQL Server is really expensive. But, the more Microsoft stack you have in house the more discounts you get. The more Microsoft certified staff you have, the more discounts you get. This biases companies to hiring more developers with Microsoft certs, which leads to more developers getting Microsoft certs, which leads to more discounts and a feeling of investment at higher levels of the company that lead them to be unwilling to switch.
I spent 5 years at a company that was using SQL Server as the backbone of it's flagship product. They pushed it as far as they could, but ultimately the biggest issues with the product at the company revolved around either limitations of SQL Server, lack of features with SQL Server or licensing being prohibitive of different types of beneficial scaling strategies. I pushed for them to make the switch to PostgreSQL for probably my last 3 years there as part of the architecture group, including migration and training plans, etc but it was like talking to a wall. Nobody could acknowledge the SQL Server issues. Everyone who had a part in considering solutions was too invested as far as I could tell. Eventually they tried moving to Couchbase (NoSQL) before finally making the switch to PostgreSQL from what I heard from people I know who still work there.
As far as query interfaces go, you can use just about any tool you like. I prefer Navicat myself but I know a lot of people like DBeaver and DataGrip.
Thanks for the info!
I've been using Navicat for SQLite and like it. If I ever get a PostgresSQL job I'll try it there since it works well for SQLite.
At my current job we are on IBM AS/400 for ERP (legacy and replacing it would be very high risk as it would change the business drastically so that won't happy any time soon).
For all other stuff we are using SQL Server and several of our key 3rd party vendors only use SQL Server. I think the fact too that some many companies run Microsoft Network and Servers that it naturally lends toward the network effect as you mention. Plus while it's expensive it's 10x cheaper than Oracle in many cases.
I've been using Navicat for SQLite and like it. If I ever get a PostgresSQL job I'll try it there since it works well for SQLite.
At my current job we are on IBM AS/400 for ERP (legacy and replacing it would be very high risk as it would change the business drastically so that won't happy any time soon).
For all other stuff we are using SQL Server and several of our key 3rd party vendors only use SQL Server. I think the fact too that some many companies run Microsoft Network and Servers that it naturally lends toward the network effect as you mention. Plus while it's expensive it's 10x cheaper than Oracle in many cases.
I assume you are referring to pgAdmin, which isn't even a part of PostgreSQL. I work usually with a well configured psql cli tool, or IntelliJ IDEAs database console. They even turned it into its own product. But conflating a database UI/console with the database is big misunderstanding. Nobody pays for Oracle DB because of SQL Developer. The strength of PostgreSQL is in the flexibility and maturity of the features of the database core and its extensibility. At least those are the reason, why I have recommended PostgreSQL in the past.
Obsolete as in, postgres is free/open/libre and as good or better than other options for 95% of projects, and growing. It's a similar situation to the old adage about "skating to where the puck is going," rather than where it is now.
I don't use Web DB UIs, but I like dbeaver and pgcli a lot. You may find them useful.
I don't use Web DB UIs, but I like dbeaver and pgcli a lot. You may find them useful.
Thanks! Once I used dbeaver for SQL Server (can't remember why) but I can try it again in the future next time I use Postgres.
I think for Enterprise SQL Server is increasing if anything (due in part because so many Companies run windows Servers internally) and developers can install it for free.
I do like the power of PL/pgSQL although I've never worked with it in detail. I reminds me of the power of Oracle's syntax without the huge cost that only large companies can afford.
I think for Enterprise SQL Server is increasing if anything (due in part because so many Companies run windows Servers internally) and developers can install it for free.
I do like the power of PL/pgSQL although I've never worked with it in detail. I reminds me of the power of Oracle's syntax without the huge cost that only large companies can afford.
Lots of older companies got Oracle and aren't changing that. I seem to recall that SQL server works really well with .net, in a way that most others don't.
I think that explains the jobs. Then you have the people who make suboptimal choices, such as buying Oracle today, because nobody got fired for doing that.
I think that explains the jobs. Then you have the people who make suboptimal choices, such as buying Oracle today, because nobody got fired for doing that.
For me I've worked with the following databases with .NET (SQL Server, Oracle, SQLite, IBM AS/400) and probably others that I don't remember.
.NET divers work great. As for Oracle when companies have been around for decades and rely on it I can see it's almost impossible to change without a huge risk to the business. Bu t if someone were starting a business today Oracle doesn't feel the way to go in most cases. Of course I'm sure they have some really good sales people that make it happen lol
.NET divers work great. As for Oracle when companies have been around for decades and rely on it I can see it's almost impossible to change without a huge risk to the business. Bu t if someone were starting a business today Oracle doesn't feel the way to go in most cases. Of course I'm sure they have some really good sales people that make it happen lol
> I seem to recall that SQL server works really well with .net, in a way that most others don't.
my company uses postgresql and dotnet efcore and it works really really really well. npgsql/efcore.pg is really awesome and has stuff built-in that works. kudos to roji which basically built it (as far as I know does he work for microsoft to bring npgsql forward). but maybe it works equally well, because microsoft bought citus and thus many psql developers.
my company uses postgresql and dotnet efcore and it works really really really well. npgsql/efcore.pg is really awesome and has stuff built-in that works. kudos to roji which basically built it (as far as I know does he work for microsoft to bring npgsql forward). but maybe it works equally well, because microsoft bought citus and thus many psql developers.
Do you mean: https://github.com/dotnet/efcore ?
Are you talking about a client application? PgAdmin is the goto for postgres administration. But some ppl like DBeaver and I really like Datagrip.
However, I also really like using clis when they are available. In that sense psql is quite good and pgcli is really cool.
However, I also really like using clis when they are available. In that sense psql is quite good and pgcli is really cool.
> Does anyone have recommended PostgresSQL query interfaces?
Nothing beats JetBrains DataGrip for me. I’ve tried a whole bunch of alternatives, but it’s most well connected and useful in terms of functionality.
Nothing beats JetBrains DataGrip for me. I’ve tried a whole bunch of alternatives, but it’s most well connected and useful in terms of functionality.
That's for the tip!
I've used JetBrains for Development before and consider their quality exception so next time I use PostgresSQL I'll give it a try.
I've also used Navicat for SQLite and like it so I'll probably try that one as well.
I've used JetBrains for Development before and consider their quality exception so next time I use PostgresSQL I'll give it a try.
I've also used Navicat for SQLite and like it so I'll probably try that one as well.
I'm using DBeaver. And still using even for non PG loads
> For a start, Postgres is simply an incredibly powerful, robust, and reliable piece of software, providing the best implementation of SQL.
Why is it better than MySQL? This article wasted my time. Is there a better article which explains why Postgres is better than other free and opensource SQL implementations?
Why is it better than MySQL? This article wasted my time. Is there a better article which explains why Postgres is better than other free and opensource SQL implementations?
MySQL and MariaDB are REALLY great software. The real difference is not one of quality (both are proven performers, and both have survived the test of time, both are used at scale by some pretty giant customers), it is one of quantity. Postgres simply has more features by a pretty wide margin than does MySQL. For many DBAs and developers, Postgres is a more flexible choice due to the additional capability.
> Postgres simply has more features
And yet lacks the most important one. A natively supported clustering and HA solution.
Especially important with CitusData being acquired by Microsoft and its future now at risk.
And yet lacks the most important one. A natively supported clustering and HA solution.
Especially important with CitusData being acquired by Microsoft and its future now at risk.
pg_auto_failover is pretty great.
a) It's maintained by CitusData which was acquired by Microsoft and so is now both unsupported and based on history at risk of being unmaintained.
b) Is not part of the core PostgreSQL codebase and so you are forever having to worry about compatibility.
c) PostgreSQL still lacks multi-master HA which is needed when you want to HA but continue to scale out.
b) Is not part of the core PostgreSQL codebase and so you are forever having to worry about compatibility.
c) PostgreSQL still lacks multi-master HA which is needed when you want to HA but continue to scale out.
FWIW, pg_auto_failover was released post-acquisition and is most certainly not unsupported; it's actively developed and new feature releases have been solid. It would be nice for this functionality to be built into Postgres, though.
Ahem there is also https://repmgr.org
An actively developed FOSS solution backed by EnterpriseDB also one of the main company supporting PostgreSQL.
But err... And ahem ahem... there are more https://www.postgresql.org/download/products/3-clusteringrep...
It's a choice of the PostgreSQL development team to only include low level api for HA in core project. But it's fairly harsh to say there is no active support for HA. Many maintainers of the core are also invested in HA related project build around PostgreSQL.
An actively developed FOSS solution backed by EnterpriseDB also one of the main company supporting PostgreSQL.
But err... And ahem ahem... there are more https://www.postgresql.org/download/products/3-clusteringrep...
It's a choice of the PostgreSQL development team to only include low level api for HA in core project. But it's fairly harsh to say there is no active support for HA. Many maintainers of the core are also invested in HA related project build around PostgreSQL.
Natively supported means part of the core product.
Dealing with third party solutions means having to worry about incompatibility, enterprise support, ongoing maintenance etc.
Dealing with third party solutions means having to worry about incompatibility, enterprise support, ongoing maintenance etc.
Not being in part of core mean multiple solutions to fit different needs. Also a lot of people don't really need HA so it keep core feature more focused. (Again HA is supported at low level, you could totally achieve HA without third party, it just require more internal ressources)
There are a lot of companies out there which provide enterprise support for PostgreSQL + HA.
There are a lot of companies out there which provide enterprise support for PostgreSQL + HA.
Transactional DDL changes is a huge one. Postgres allows me to modify the table schema in a transaction, and rollback changes as if it was a mere data transaction, if something fails mid-migration. Makes maintaining production databases through upgrades and schema changes much less stressful.
Geez, I can't imagine running migrations without this.
You'd just run your migration statements one after the other and pray it doesn't fail midway? Or have a contingency plan for every possible point of failure?
Postgres has given me a sheltered life.
You'd just run your migration statements one after the other and pray it doesn't fail midway? Or have a contingency plan for every possible point of failure?
Postgres has given me a sheltered life.
Transactional DDL changes cannot be understated - it’s the most outrageously useful database feature out there.
I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.
I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.
I've always wondered about this in the scope of REALLY big tables. Migrating a busy table with a few billion rows will take 10+ hours, doesn't the redo log fill up? The amount of work that needs to be undone could easily take another 10+ hours, is there enough storage space for that?
Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.
Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.
What kind of DDL migration are you thinking about that would take 10+ hours? Adding a column is instant, even if your table is multiple TB's large. Same for dropping and renaming columns.
Adding a column can be instant on multi TiB tables, but that depends on the details. If is a non-null column with a non-stable default value, like the current time, it will require a full rewrite of all rows of the table.
Indeed, i was talking about multi-TB tables. From the postgres v13 docs (https://www.postgresql.org/docs/current/sql-altertable.html):
> Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.
> Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.
Yes, but generally that could be handled in a multi-stage process. First you would create the new column with no constraint, then periodically start updating the rows, once nearly all the rows are updated with the new default, in a transaction you update the rest of the rows, update the table with the constraint, and set the default. Not very much downtime is required with this approach (although of course it is more involved).
>doesn't the redo log fill up?
It's not a redo/undo log. It's more like (simplifying things here) having multiple copies of things and different sessions/connections "see" different sets of data. That's why you need to vacuum the database periodically, to garbage collect old rows that are no long accessible to any sessions/transactions.
See https://devcenter.heroku.com/articles/postgresql-concurrency
>is there enough storage space for that?
You do need to make sure you aren't too close to filling your storage, postgres needs some breathing room for this reason.
It's not a redo/undo log. It's more like (simplifying things here) having multiple copies of things and different sessions/connections "see" different sets of data. That's why you need to vacuum the database periodically, to garbage collect old rows that are no long accessible to any sessions/transactions.
See https://devcenter.heroku.com/articles/postgresql-concurrency
>is there enough storage space for that?
You do need to make sure you aren't too close to filling your storage, postgres needs some breathing room for this reason.
One is not better they are just different.
If you value more expressive data types and constructs to read/write your data then PostgreSQL is better. If you don't need those but do need to be able to scale to very large data volumes then MySQL is better.
I don't understand why people keep wanting to make this a competition. It's been going for decades and it was stupid back then as it is today. The databases are both popular, both high quality but serve different needs.
If you value more expressive data types and constructs to read/write your data then PostgreSQL is better. If you don't need those but do need to be able to scale to very large data volumes then MySQL is better.
I don't understand why people keep wanting to make this a competition. It's been going for decades and it was stupid back then as it is today. The databases are both popular, both high quality but serve different needs.
For me it’s their extensive selection of foreign data wrappers and extensions which allow for it to be incredibly integrated and work with novel paradigms. It was the first by several years to support JSON fields and window functions, but I was pleased to see those in MySQL 8. For extremely write-heavy use cases I think MySQL wins as Postgres tends to want every materialized view current before completing an insert. It’s license has never been in question, where MySQL, Percona, and MariaDb have been a source of concern/confusion over the years.
https://wiki.postgresql.org/wiki/Foreign_data_wrappers IMAP, GitHub, BigQuery, Faker and CSV have been awesome for me, but don’t tend to be 100% supported by public cloud managed DB services. I’ve heard some can be flaky.
INSERT INTO public.people SELECT ssn, name, phone_number FROM faker.people;
That is a way to generate a ton of fake records for integration and load testing. Very elegant!
https://wiki.postgresql.org/wiki/Foreign_data_wrappers IMAP, GitHub, BigQuery, Faker and CSV have been awesome for me, but don’t tend to be 100% supported by public cloud managed DB services. I’ve heard some can be flaky.
INSERT INTO public.people SELECT ssn, name, phone_number FROM faker.people;
That is a way to generate a ton of fake records for integration and load testing. Very elegant!
Nailed it - except one massive feature missed IMO is pl/pgsql.
As a language it’s a bit clunky* but once you learn it, and you start moving database logic into the database, suddenly everything is 25% LOC, often literally 100x faster, and easier to understand.
As an added bonus, when I’ve moved very complex data transforms into pl/pgsql, the non-programmer technical people can now read the logic and have more than once pointed out problems (with solutions).
* it helps a lot if you write your SELECT and other statements in lowercase. Feels barbaric I know, but reading shoutycode all day gives me a headache.
As a language it’s a bit clunky* but once you learn it, and you start moving database logic into the database, suddenly everything is 25% LOC, often literally 100x faster, and easier to understand.
As an added bonus, when I’ve moved very complex data transforms into pl/pgsql, the non-programmer technical people can now read the logic and have more than once pointed out problems (with solutions).
* it helps a lot if you write your SELECT and other statements in lowercase. Feels barbaric I know, but reading shoutycode all day gives me a headache.
Yeah I haven't used pl/pgsql but it reminds me of Oracle's pl/sql which is very powerful.
At first the Oracle version felt clunky to me too but then after taking time to learn it I also found moving complex app logic to SQL allowed me to make complex app changes much quicker. I have use that method of development now for years in a variety of apps and databases and it works great.
At first the Oracle version felt clunky to me too but then after taking time to learn it I also found moving complex app logic to SQL allowed me to make complex app changes much quicker. I have use that method of development now for years in a variety of apps and databases and it works great.
I used Sybase and SQL Server for a long time. PostgresSQL I found much tougher than expected to figure out. Just seems built for functionality over ease of use. I guess I got to get used to it.
Versus Sybase!? Not my experience (but its so long ago now). I feel this may be a just a comfort-zone switch for you. Like, Sybase is known and therefore already figured out and "better". All big systems take some ramp-up / comfort-building time. As others have said, the documentation, the community (still on IRC and email!) are excellent. And heck, digging in the code for information is not so terrible either (and it's huge, and C (and I don't do either of those things) and I still found what I needed). I'm sure you'll be used to it in short order. I've already poured you some Kool-Aid :)
I found the o'Reilly book, Postgres: Up and Running approachable if you have some db experience.
Been a big fan of PG since the late 90s. I always joke that the only time I’ve lost data with PG is when it was my own damn fault!
Agreed. The last and only time I saw a database corruption with PG was in 1999 and the culprit was a memory fault in a raid array.
Cross fingers now that my hubris does not come and bite me.
Cross fingers now that my hubris does not come and bite me.
Could anyone recommend a good and current book on Postgres?
This one's great:
https://theartofpostgresql.com/
https://theartofpostgresql.com/
As a counterpoint, I did not like this book and learned much more efficiently from the postgres docs
Can you say what you didn't like about it? It's a little pricey so I would be curious to hear your feedback.
It's been a couple years but I found it very verbose and not very well edited. It may have improved since then. I did appreciate their push towards doing more in the DB and less in the app layer, though, and I wish more devs would hear that message!
I liked the o'Reilly book, Postgres: Up and Running approachable if you have some db experience. It is short and to the point.
1. Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13 - Luca Ferrari, Enrico Pirozzi is current, and covers a lot of Postgres specific grounds in a structured manner. The examples are sometimes too succinct and hard to follow though, but I find it good for quickly looking things up, specifically the management side of things.
2. PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries - Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova is quite new, and exclusively focuses on query side of things, but goes deep into this topic. I haven't read it though, only flipped through the pages but it seems good.
2. PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries - Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova is quite new, and exclusively focuses on query side of things, but goes deep into this topic. I haven't read it though, only flipped through the pages but it seems good.
I am also interested.
If anyone is looking for the Alpine article linked in the first paragraph, it's only available via Gemini, or via a proxy like here https://portal.mozz.us/gemini/drewdevault.com/2021/05/06/Pra...
I want to use PostgreSQL, because there seems to be a lot of technical discussion around why it's so great, but the tooling I have available to me seems to be immature compared to MySQL's.
And so, I find it really difficult to justify investing time in building up infrastructure to support it for my clients' work, because I need to be effective from day one.
Realistically that has absolutely nothing to do with PostgreSQL but whether or not some little third-party company or one or two individual developers have built something solid like Sequel Ace. That's really troubling for me to think about, but these CLI tools are insufficient for being productive.
This a great example to me as to why a business can't use a potentially superior product because some ecosystem feature is non-existent, or is hard to find.
That being said, I'm not too upset because modern MySQL seems perfectly fine, and still more widely used than PostgreSQL.
And so, I find it really difficult to justify investing time in building up infrastructure to support it for my clients' work, because I need to be effective from day one.
Realistically that has absolutely nothing to do with PostgreSQL but whether or not some little third-party company or one or two individual developers have built something solid like Sequel Ace. That's really troubling for me to think about, but these CLI tools are insufficient for being productive.
This a great example to me as to why a business can't use a potentially superior product because some ecosystem feature is non-existent, or is hard to find.
That being said, I'm not too upset because modern MySQL seems perfectly fine, and still more widely used than PostgreSQL.
if you use psql you will be way more productive than using any gui tool. also Sequel Ace is so barbones that using a cli is way way faster.
I understand that, but I'm not interested in writing everything by hand. I'd rather have tooling work for me instead, and verify correctness by reading the manual for this-or-that. It also helps my staff when I have a recommended tool for those less SQL inclined.
IntelliJ is decent and has drivers for pretty much all database systems. It can be downloaded has a stand-alone too (DataGrip)
That is for IntelliJ Ultimate if I am not mistaken: https://www.jetbrains.com/help/idea/connecting-to-a-database... Otherwise as you mention DataGrip https://www.jetbrains.com/datagrip/ seems to be really sleek.
Thank you for the insight!
You need you some Postico*! Absolutely fantastic GUI for Mac.
* Postico 1 is great, but the soon-to-be released Postico 2 is in beta atm and even better.
* Postico 1 is great, but the soon-to-be released Postico 2 is in beta atm and even better.
Thank you for sharing this!
PGAdmin is really fine for a GUI. Sure it's not beautiful but it's good enough.
There's also https://tableplus.com/ - which supports an impressive list of databases and is all round amazing
I am still surprised that no large company exists around PostgreSQL, similarly to Oracle, the (defunct/acquired) MySQL AB, or Redis Labs.
Wondering whether this is a missed opportunity. This is not to bash the free software part of PostgreSQL, but rather to focus on something that could drive more enterprise adoption, I guess to the detriment of primarily Oracle and Microsoft SQL Server.
Wondering whether this is a missed opportunity. This is not to bash the free software part of PostgreSQL, but rather to focus on something that could drive more enterprise adoption, I guess to the detriment of primarily Oracle and Microsoft SQL Server.
What a bizarre love letter to a piece of software. Some of this looks cribbed from the postgres.org site.
"After 25 years of persistence, and a better logo design, Postgres stands today as one of the most significant pillars of profound achievement in free software, alongside the likes of Linux and Firefox." The better logo really does it. All three of the "pillars" the author names are clones or derivatives of commercial products (Ingres, Unix, Netscape Navigator). Postgres development was partly funded by DARPA at Berkeley, and then through licensing to IBM, before it was open-sourced. Only Linux can be called a "profound achievement" of free software if the author means the cathedral and bazaar model, and then just barely.
"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." Uh, no. The whole notion of "competitors" in the world of open source software is flawed. PostgreSQL has not made Oracle, SQL Server, and DB/2 obsolete, nor has it made MySQL obsolete, or any of the other database managers we have to choose from. PostgreSQL is about half as "popular" as MySQL (https://db-engines.com/en/ranking), and both are below Oracle.
"... providing the best implementation of SQL" and in the footnote "No qualifiers. It’s straight-up the best implementation of SQL." I can't tell what the author means by "best," it's like writing that Oreos are the "best" cookies. In terms of standards compliance DB/2 is better. In terms of performance and scalability Oracle, SQL Server, and DB/2 are all objectively "better." In terms of compared to the main open source alternative, MySQL, the two are nearly neck-and-neck with features, compliance, and performance.
"SQL is usually the #1 bottleneck in web applications, and Postgres does an excellent job of providing you with the tools necessary to manage that bottleneck." Not in my experience. Sometimes excessive database traffic causes performance issues in web apps, but many other problems show up, like Apache/nginx contending for resources with MySQL or PostgreSQL (hint: don't run those on the same server/VPS). If the code makes too many database queries, or badly-written queries (WordPress and pretty much every ORM) that's a fault in the application code, not the database, and PostgreSQL doesn't magically fix it.
"After 25 years of persistence, and a better logo design, Postgres stands today as one of the most significant pillars of profound achievement in free software, alongside the likes of Linux and Firefox." The better logo really does it. All three of the "pillars" the author names are clones or derivatives of commercial products (Ingres, Unix, Netscape Navigator). Postgres development was partly funded by DARPA at Berkeley, and then through licensing to IBM, before it was open-sourced. Only Linux can be called a "profound achievement" of free software if the author means the cathedral and bazaar model, and then just barely.
"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." Uh, no. The whole notion of "competitors" in the world of open source software is flawed. PostgreSQL has not made Oracle, SQL Server, and DB/2 obsolete, nor has it made MySQL obsolete, or any of the other database managers we have to choose from. PostgreSQL is about half as "popular" as MySQL (https://db-engines.com/en/ranking), and both are below Oracle.
"... providing the best implementation of SQL" and in the footnote "No qualifiers. It’s straight-up the best implementation of SQL." I can't tell what the author means by "best," it's like writing that Oreos are the "best" cookies. In terms of standards compliance DB/2 is better. In terms of performance and scalability Oracle, SQL Server, and DB/2 are all objectively "better." In terms of compared to the main open source alternative, MySQL, the two are nearly neck-and-neck with features, compliance, and performance.
"SQL is usually the #1 bottleneck in web applications, and Postgres does an excellent job of providing you with the tools necessary to manage that bottleneck." Not in my experience. Sometimes excessive database traffic causes performance issues in web apps, but many other problems show up, like Apache/nginx contending for resources with MySQL or PostgreSQL (hint: don't run those on the same server/VPS). If the code makes too many database queries, or badly-written queries (WordPress and pretty much every ORM) that's a fault in the application code, not the database, and PostgreSQL doesn't magically fix it.
[deleted]
How does it compare to MariaDB?
It's better.
This [1] seems to be a more discerning answer
[1] https://hevodata.com/learn/mariadb-vs-postgresql/
[1] https://hevodata.com/learn/mariadb-vs-postgresql/
FYI, the section on partitioning is wrong there.
Postgres supports table partitioning perfectly fine[1].
1. https://www.postgresql.org/docs/current/ddl-partitioning.htm...
Postgres supports table partitioning perfectly fine[1].
1. https://www.postgresql.org/docs/current/ddl-partitioning.htm...
Less West Side Story more Jungle Book.
They are both very impressive systems.
I have done many projects in both PostgreSQL and MariaDB/MySQL
I do tend to get attached to the DB of my last project because I discover new cool things. And there are some differences.
But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL.
I have re-implemented systems that used non open-opensource DBMS's on Windows and the convenience and performance (on the same hardware) were just amazing when using PostgreSQL or MariaDB.
I do tend to get attached to the DB of my last project because I discover new cool things. And there are some differences.
But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL.
I have re-implemented systems that used non open-opensource DBMS's on Windows and the convenience and performance (on the same hardware) were just amazing when using PostgreSQL or MariaDB.
Is there a MySQL equivalent for "FOR UPDATE SKIP LOCKED"? That's a feature that I've fallen in love with because it has completely eradicated the need to have a message queue like RabbitMQ or whatever. For example, for distributing jobs to background workers, I just insert them into a jobs table and have the workers poll for jobs with:
DELETE FROM jobs WHERE id = (
SELECT id FROM jobs FOR UPDATE SKIP LOCKED
) RETURNING *;
MySQL 8 supports FOR UPDATE SKIP LOCKED [1], but does not have a RETURNING clause.
MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles.
[1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-an...
MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles.
[1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-an...
> But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL.
Can MySQL do GIS related features like PostGIS? Also IIRC to index JSON in MySQL you need to add generated columns for all the properties/paths you want to index, which can get pretty hairy.
Can MySQL do GIS related features like PostGIS? Also IIRC to index JSON in MySQL you need to add generated columns for all the properties/paths you want to index, which can get pretty hairy.
Can MySQL do GIS related features like PostGIS?
It can[1] for most of the common cases, but it lacks many of the more advanced features PostGIS has.
So if you have a MySQL database up and running, you probably don't have to switch to PostGIS just because you want to add some geospatial data. However if starting a new geospatial project then you should almost certainly choose PostGIS.
[1] https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
It can[1] for most of the common cases, but it lacks many of the more advanced features PostGIS has.
So if you have a MySQL database up and running, you probably don't have to switch to PostGIS just because you want to add some geospatial data. However if starting a new geospatial project then you should almost certainly choose PostGIS.
[1] https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
Postgres has always prioritized correctness, and more sophisticated datatype and query features ... mysql/mariadb have had more consistent performance and easier/more-flexible replication. I'm thinking about auto-vacuum and logical replication to upgraded versions... I've had much less trouble with mysql/mariadb around these topics many years ago, than I've had with Postgres relatively recently. But also, I don't care so much about fancy data types and query features, I stick to the basics.
[deleted](2)
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.