I want to write an appreciation post for PostgreSQL! why?! A few days ago, an article Things I hate about PostgreSQL was trending. I think the author is right about the limitations of PostgreSQL in some of the cases. Personally, I have also faced issues because of vacuum and XID-wraparound at Gojek[1]. But I feel there is a need to mention(repeatedly) how awesome Postgres is. Personally, I have been using PostgreSQL for over a decade and I really(really) enjoy working with it. In this appreciation post for PostgreSQL, I am going to talk about few things that I like about it. I am going to focus both on technical and non-technical aspects.
PostgreSQL is 30 years old database
Originated at the University of California, Berkeley in 1992, PostgreSQL is currently one of the most feature rich relational database systems on the planet. It supports numerous data types ranging from arrays to json to user-defined objects. It has a very rich query language that supports most of the major features of SQL:2016. Its newer versions support declarative table partitioning. It works on the majority of operating systems. Currently, at 13 version, postgres has more than 400 releases.
PostgreSQL is a batteries included database
PostgreSQL comes in a single package with everything you need to run a database system. Its defaults work for almost all use-cases if you are starting a project. If defaults don’t work, you can modify the settings/configs through 100s of parameters exposed by PostgreSQL. It is also bundled with utilities like psql, pg_dump. You can use psql to interact with your database or use pg_dump to take backups. Also, there is a client library for all major programming languages, so you can start using the database in your application in few minutes. Also, PostgreSQL is available as managed service on all major cloud providers so you can start using it with a click of a button.
PostgreSQL has a solid open source community
PostgreSQL has been in active development for almost 3 decades. So far there have been over 50K commits on the postgresql repository. It has a very active community on slack and irc. Also, there is an active mailing list where you can get answers to your questions and clarify your doubts. Also, there are User Groups all over the world. Apart from this, there are various events organized around the world every month. Also the community is backed by big companies. Also Postgres license is no nonsense license.
PostgreSQL is being used by almost every giant on the planet
We loved PostgreSQL at Gojek. We had hundreds of PostgreSQL production instances spanning across 18+ products at Gojek. According to stackshare 4000+ companies & 40,000+ developers reported that they use PostgreSQL. The giants like Uber, Netflix, Instagram, Stackshare are using or has used PostgreSQL to server millions of their customers. Not just this, hundreds of big companies have built sufficiently big businesses by providing services/products on top of PostgreSQL. Also according to the Stackoverflow 2020 Survey, 36% of the developers said they are using postgresql for their database needs.
PostgreSQL has very mature transaction support
Transactions are one of the most important features of any database. Transaction helps you reduce your application code by providing guarantees(ACID) at database level. Snapshot Isolation(I in ACID) of PostgreSQL prevents applications from concurrency problems like dirty/non-repeatable/phantom reads. The application developer can trust the postgresql mature transaction capabilities which have evolved over decades.
PostgreSQL is highly extensible
PostgreSQL by design is pretty extendible with extensions. There are hundreds of open source extensions built by the community which are already available for production use cases. The extensions range from monitoring to adding new data types to modifying storage mechanism. PostGIS is one of the widely used extensions which “spatially enables” the postgres server. The extension ecosystem is so mature that you can add functionalities at almost any level of the database – be it at network, sql, storage or even at transaction level.
PostgreSQL has awesome documentation
If there was a prize for best documentation for a sufficiently large project, I think PostgreSQL would be the winner. The documentation is so good and easily navigable. Also the documentation is SEO friendly so you just need to google something like server configuration postgresql and you would certainly get the official documentation link as one of the top results. The documentation is verbose and is tagged with the postgresql version. For example this is the documentation for server configuration of PostgreSQL v13.
PostgreSQL supports non-relational data types
PostgreSQL supports json datatype natively. It supports many operators and functions to query/manipulate the nested json structures. Most of the power of PostgreSQL comes with its relational data modeling but in some special cases storing json structures in a column comes in handy.
Shameless plug
I am currently working on an interactive exercise based PostgreSQL course. The course is aimed at application developers who want to level up their PostgreSQL skills. It is purely about solving real-world challenges we face when working with PostgreSQL. There are more than 100+ real-world scenarios. The scenarios are of moderate to high difficulty ranging from querying huge tables to fixing indexed of a high throughput application to tuning vacuum. You would solve the challenges from your browser in the Interactive Learning Environment(ILE) designed specifically for the course. ILE will have live PostgreSQL databases with programmatically generated traffic to give you a taste of high throughput applications. Feeling interested? Do signup below for early access(no spam guaranteed).
[1] – I no longer work at Gojek
Deven Bhooshan 