From a Fan: On the Ascendance of PostgreSQL
The Stack Overflow 2022 developer survey has been out for a couple of weeks now, and it’s jam-packed with good information to help end users and tech vendors alike get a better handle on what software developers are into these days. The survey is based on responses from more than 70,000 participants representing a global community of developers with diverse backgrounds, interests and levels of experience. You’ll find sections exploring everything from which technologies they love and are interested in using to their favorite asynchronous collaboration tools and how much they make.
One section of the report I’m particularly excited about is a topic near and dear to my heart (and brain!): databases.
Pro Devs ♥️ Postgres
The survey polled more than 63,000 developers about which databases they most prefer. In total, MySQL won out (46.85% to 43.59% across all respondents). But, importantly, among those who self-identify as professionals (as opposed to students), PostgreSQL won 46.48% to 45.68%. Barely a win, but a win, nonetheless. What a remarkable thing. Our long nightmare is over: People are giving PostgreSQL the love it deserves. Finally!
Admittedly, I’m biased, and not only because the company I work for has been active in Postgres for decades. I think PostgreSQL is just the better tool. So, yes, I might have leaped for joy when Oracle bought and then tried to sabotage MySQL some years ago, because it gave people a good reason to take a second look at PostgreSQL.
Databases Endure
As one of the most complex pieces of the stack, databases are notoriously difficult to migrate. I can only hazard that this “database durability” is the reason why MySQL won out among those who self-identify as learning to code. Twenty years ago, MySQL was everywhere. It’s the “M” in the “LAMP” stack, for Pete’s sake! It was easy to install on every operating system, and it was open source. In contrast, PostgreSQL didn’t even work on Windows until version 8.0 in 2005, a full 13 years after it debuted. It’s not hard to imagine why PostgreSQL’s upkeep was so much slower, and PostgreSQL ceded a lot of ground. So while MySQL’s inertia means it’s still the first thing those learning to code reach for, I hope that will change.
PostgreSQL ♥️s Containers
PostgreSQL is a fantastic tool, more so in container-based architectures. Meanwhile, the Kubernetes operator model has made it easy to spin up, provision and manage multiple database instances in a cluster.
In today’s world, with Docker containers, it’s trivially easy to get a working PostgreSQL or MySQL instance up and running. Here’s a definition you could use for your docker-compose.yaml file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
```yaml version: '3' services: Postgres: image: postgres:latest environment: - POSTGRES_USER=postgres - PGUSER=postgres - POSTGRES_NAME=postgres - POSTGRES_PASSWORD=postgres ports: - "5432:5432" ``` |
Then just run:
|
1 2 3 |
```shell docker-compose up ``` |
Assuming you’ve installed the psql CLI, you can connect with:
|
1 2 3 |
```shell PGPASSWORD=postgres psql -U postgres -h localhost postgres ``` |
Easy! I can see why professional devs love PostgreSQL.
Here is another example of how easy it is to do using the VMware Postgres operator.
Some easy setup:
Install the operator:
Deploy Postgres. Within a few seconds, you have a running Postgres server.
Enterprises ♥️ PostgreSQL
PostgreSQL has all the usual features: ACID compliance, scalability, data integrity, excellent security, etc. Now, I know you’re not all that impressed with this stuff today, when plenty of SQL (and some No/NewSQL!) databases purport to offer it, but it is essential.
Remember, there was a time when MySQL’s (v3 and earlier) default backend engine, MyIASM, would happily accept DDL (Data Definition Language) foreign key references enforcing data integrity and then quietly ignore them, letting users put in whatever they wanted. If I can’t trust my database to hold correctly — literally hold on to it — then it’s hardly a database, it’s more just… a base.
Real Talk
Why do I really love PostgreSQL? To start, because of how well it works in Spring and Java applications. So whether you’re using reactive APIs or plan to use the new project Loom with the traditional JDBC (Java database connectivity) APIs, you can bet your bottom dollar PostgreSQL offers a great developer experience, performs well and most likely meets your use cases. Going a step further, PostgreSQL was one of the early databases to support MVCC (multiversion concurrency control), a pattern designed to keep consistency for both reads and writes without blocking each other.
Now, would I say it does everything you’d ever need, as well as the most expensive databases, like Oracle? Probably not. But that’s not the value proposition. It’s the fact that it does most of what I want, and the exhaustive ecosystem fills in the gaps.
I remember learning in 2008 about something called Compiere, an open source ERP system written using Java (and good ol’ Swing) that made extensive use of the Oracle database. Meanwhile, somebody introduced compatibility features to PostgreSQL such that this sprawling, purpose-built ERP system could be made to run on PostgreSQL. Amazing! That’s way beyond compliance with just the specs!
There are a ton of projects aimed at building on PostgreSQL and adding Oracle compatibility (including my own employer). And this, my friends, speaks to the heart of why I love PostgreSQL so much: It’s extensible! There are a ton of projects that change the paradigms supported by PostgreSQL:
- Citrus gives you distributed tables for PostgreSQL.
- Greenplum, now a part of VMware, debuted in 2005 and is a big data database based on the MPP (massively parallel processing) architecture and PostgreSQL.
- TimescaleDB is a time series database, like Netflix Atlas, Prometheus or DataDog, built into PostgreSQL.
- Neon Cloud provides bottomless storage for PostgreSQL.
- YugabyteDB, whose founders created Apache Cassandra and HBase, among other things, provides a horizontally scalable engine for PostgreSQL.
- EnterpriseDB provides an industrial-grade distribution of PostgreSQL.
pgbounceris a lightweight connection pooler for PostgreSQL.pgbackrestprovides trivial, automated backup and restore
There are tons of extensions to PostgreSQL’s type system, too:
- PostGIS provides geospatial queries and capabilities for PostgreSQL.
btree_ginandbtree_gistadd new BTREE types.citessupports case-insensitive string values.cubeadds multidimensional cube types.dblinkmakes it possible to treat a remote PostgreSQL database as local.- The
earthdistancemodule provides two different approaches to calculating great circle distances on the surface of the Earth. file_fdwis a foreign data wrapper (of which there are many) to access data files in the server’s file system.httpmakes it possible to issue HTTP requests from PostgreSQL.isnprovides data types for the following international product-numbering standards: EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials).pg_cronsupports running periodic jobs in PostgreSQL.pg_trgmmakes it possible to determine the similarity of alphanumeric text based on trigram matching and index operator classes that support fast searching for similar strings.pg_auditprovides detailed session and object audit logging via the standard logging facility supplied by PostgreSQL.pgcryptoprovides cryptographic functions for PostgreSQL. No bitcoin is required, mercifully!
And there are even extensions to the languages by which you can wield PostgreSQL’s type system! For example, PostgreSQL supports several alternatives to PG/PSQL as languages in which you can write functions:
-
plcoffee— run CoffeeScript in PostgreSQL with PL/Coffeescript (v8). pljavasupports using the Java language in PostgreSQL.pllssupports using the LiveScript language.plv8lets you run JavaScript using the Google V8 OSS JavaScript engine (on which Node.js was initially based).
And nowadays, it is trivial to spin up a PostgreSQL instance anywhere you would like it, on any major cloud vendor. Indeed, all cloud vendors have horizontally scalable, bottomless integrations for their various PostgreSQL instances.
PostgreSQL is ubiquitous, multipurpose, robust, extensible, fast, scalable and more. The Stack Overflow survey results are surprising, in my mind, only because the results were that close. PostgreSQL is amazing! If you haven’t tried it yet, you should.
Jinali Sheth contributed to this article.
