Last updated: October 3 2024
PostgreSQL is my default choice whenever I need a relational database. In fact, it is one of my favorite pieces of technology, ever.
I’m not alone in this sentiment. It is consistently ranked as the most loved database in Stack Overflow’s yearly developer survey by a wide margin.
PostgreSQL offers excellent single node performance and can handle hundreds of thousands of transactions per minute. If it ever becomes a performance bottleneck for your application, you’re probably a victim of your own success.
That’s a great problem to have! In that situation, you can just adopt a PostgreSQL compatible NewSQL database. This future flexibility makes adopting the system a smart choice in both the short and long term.
It does have two performance downsides, compared to some competitors:
However, if you need to store data in a columnar format, you may want to consider using a dedicated data warehouse. And if you need to keep a table entirely in memory, you may want to consider a key value cache.
In other words, those features are nice to have, but can be easily replaced by supplementing with other systems. Those other, specialized systems will do a better job at their specific use cases, anyways.
PostgreSQL can perform basic sharding through its partitioning feature and even utilize multiple drives to store relatively large amounts of data. However, if you find your data creeping up toward the 1 terabyte mark, you may want to consider a NewSQL database with native sharding features.
PostgreSQL allows you to encrypt data at rest and in transit. It also implements a mature role based access control (RBAC) system that allows for granular control over who has access to what.
If anything, the only problem with PostgreSQL’s security model is how vast it is and how many options it grants you. Knowing what you can do and implementing it correctly is challenging.
PostgreSQL is free and open-source. As far as I can tell from the managing organization, the “PostgreSQL Global Development Group”… It always will be.
This sets it apart from data systems that are currently open-source, but owned by for-profit businesses. For example, MySQL is owned by Oracle and MariaDB is currently owned by “K1 Investment Management”. Who knows if those owning organizations will ever change the license to monetize that ownership?
There is no first party commercial support, although EnterpriseDB is an excellent option for this if you need it.
Every major cloud provider offers a managed PostgreSQL database. Because of this, it’s impossible to get “locked in” to a particular vendor. You will always have leverage over any given vendor by maintaining a plausible threat to switch providers.
This is increasingly important as large cloud providers gain more and more control over our digital infrastructure.
If you want to run the software yourself, it is readily available as a Docker image, through various package managers / official installers or can be built from source
A great tool ecosystem exists around PostgreSQL.
It isn’t quite as well polished as the walled garden Microsoft has built around SQL Server, but I put it in second place.
PostgreSQL gives you lots of options for creating highly available databases. If your objective is to maintain an SLA, you should be fine with what it provides.
However, if you have absolutely mission critical workloads that can’t fail, or need more advanced cluster control to increase throughput or scalability, you should consider one of the PostgreSQL compatible NewSQL databases.
PostgreSQL has top notch backup and recovery options. You can create full backups, incremental backups, and export all of those files in various formats.
For example, you can export a database as a file system dump, or portable SQL code.
PostgreSQL has the best community of any database in the world. There is no close competitor and everyone else is competing for second place in this aspect.
It has excellent documentation, an official Slack group, a community forum, and is fully open-source on Github.
Developers love PostgreSQL. As mentioned in the intro, It is consistently the most loved database technology on Stack Overflow’s developer survey by a wide margin.
Its nature as a popular and loved open-source database has encouraged the development of a vast extension ecosystem. You can find extensions that allow it to act as a data warehouse, time series database, geospatial database, and many other things.
The vibrant community around the project and the resulting ecosystem of extensions makes it unmatched in its flexibility. This flexibility has given rise to a somewhat humorous movement that recommends using PostgreSQL for everything.
Because PostgreSQL is so popular, basically every ETL, ELT, and any other data ecosystem provider will provide support for it in some capacity. It’s impossible to list all of them, but PostgreSQL is almost always one of the first connectors built for any data product.