The pedigree of PostgreSQL

When commentators talk about PostgreSQL, it is often in the same breath as MySQL, or MariaDB, or SQLite. They are open source databases, with no licence costs, and popular with developers. Got it.

But they are not the same. If you dig into the history of PostgreSQL you will see that it has a pedigree as illustrious as that of proprietary databases like Oracle or DB2. In the world of open source databases, it is in a league of its own, and forty years later, it should be the default choice for new systems.

Where it all started

Before relational databases came along, a database was an enormous cascading linked list of data items. How do I know this? I was there. I started my career writing Cobol programmes to crawl across an IDMS database on an ICL mainframe that recorded every pint of milk and ounce of cheese produced in England and Wales. Every few weeks, there was a herculean effort to export the data, tweak the relationships between the data items, then load the data back in again.

That came to an end when Oracle, DB2, Ingres and others arrived in the 1980s. They were all based on a ground-breaking paper by Edgar F. Codd in 1970 titled "A Relational Model of Data for Large Shared Data Banks." His insight was to take a declarative approach to define the relationships between data entities and then use relational algebra to define the data queries. IBM started a project called System R based on his ideas and released a series of papers describing the system they were building.

These papers inspired Michael Stonebraker and Eugene Wong at Berkeley to start a relational database research project of their own in 1973 called Ingres. Ingres was released under a BSD licence, and its code seeded a whole family of commercial databases, including Sybase and, by way of Sybase, Microsoft SQL Server.

Then in the mid-1980s, Stonebraker started the post-Ingres project that became Postgres.

Laying the foundations

A key objective of the Postgres project was to let users define their own data types, operators and functions, and plug them into the query engine as if they were built in. This extensibility and the BSD licence gave Postgres a firm foundation, and a series of later events propelled it forward to become PostgreSQL:

• SQL: Postgres originally used a query language called QUEL. When IBM released DB2, the commercial world standardised on SQL, and two Berkeley graduate students wrote a SQL parser for Postgres in their spare time. The result was released as Postgres95 and renamed PostgreSQL in 1996.
• Community involvement: Because the code was released under a permissive licence, a group of volunteer engineers picked it up when Berkeley put it down, and they have been supporting and improving it ever since.
• Oracle bought MySQL: When Oracle acquired Sun, and MySQL with it, many developers got squeamish about building on a database owned by the largest commercial database vendor in the world. PostgreSQL was the obvious alternative.
• The advent of the cloud: Heroku built its platform on PostgreSQL, and the Rails community followed. Amazon had no plans to support PostgreSQL until customers got tired of asking why Heroku had it and AWS did not. Today every major cloud vendor offers managed PostgreSQL, and many of the new cloud-native databases speak the PostgreSQL wire protocol, so that existing drivers and tools just work.

Much of the database world is PostgreSQL in disguise. Because the licence allowed it, PostgreSQL was the natural starting point for anyone building a new database. Netezza, Greenplum and Amazon Redshift were all built on forks of the PostgreSQL code base. If you have used a data warehouse in the last twenty years, there is a good chance you have used PostgreSQL without knowing it.

The secret sauce: extensibility

Like its heavyweight contemporaries, PostgreSQL is ACID-compliant, reliable and stable. But, unlike the others, extensibility is baked into the core.

The ecosystem of extensions that has grown up around this is remarkable:

• PostGIS turns PostgreSQL into a fully-fledged geographic information system, and it is the reference implementation that other spatial databases are measured against.
• pgvector adds a vector data type and similarity search, so you can store embeddings for AI applications next to the rest of your data instead of standing up a separate vector database.
• TimescaleDB adds time-series partitioning and continuous aggregates.
• Citus turns PostgreSQL into a sharded, distributed, horizontally scalable database.
• pg_cron schedules jobs inside the database, so you do not need a separate cron server to run your housekeeping.
• Foreign data wrappers such as postgres_fdw and tds_fdw let you query other databases, including SQL Server, as if their tables were local.

The extensions also act as a proving ground. When an idea is proven and stable, it tends to make its way into the core. JSON support is a good example. PostgreSQL 9.2 stored JSON as validated text; version 9.4 introduced jsonb, a binary format that can be indexed and queried efficiently. Today PostgreSQL is a perfectly good document database as well as a relational one, which removes one more reason to add MongoDB to your stack.

You can even skip writing an API layer altogether. You can use PostgREST to serve a RESTful API that is generated directly from your database schema. It has matured enormously and it is the engine behind the Supabase platform.

One database for everything?

While there are certainly workloads that justify a specialised engine, you can really use Postgres for everything: relational data, documents, queues, full-text search, geospatial data, time series and vector embeddings.

Every additional database in your architecture is another thing to secure, back up, monitor, patch and hire for. The simplest architecture is the one with the fewest moving parts.

This is the reason that PostgreSQL overtook MySQL in the Stack Overflow Developer Survey in 2023 as the most used database among professional developers, and it has topped the "most admired" list for several years running. DB-Engines has named it DBMS of the Year more often than any other database.

No vendor lock-in

The most important difference between PostgreSQL and its peers is not technical. It is that it is free of vendor lock-in.

PostgreSQL is released under the PostgreSQL Licence, a short, permissive licence similar to BSD and MIT, and it is developed by a global community of contributors. Companies like EDB, Crunchy Data and Microsoft employ many of the core contributors, but no single company controls the project. Outside of the Linux kernel, it is hard to think of another piece of critical infrastructure run in quite the same way.

For an architect, that matters. A database is one of the hardest components in an architecture to replace. When you choose PostgreSQL, you are not betting on the business model of a single vendor. You are betting on a project that has been in continuous development for nearly forty years, has a new major release every year, and is not going to be bought, relicensed, or abandoned.

If you need enterprise support, it is available from several competing vendors. EDB, for example, offers an Oracle-compatible distribution to ease the migration from Oracle, and every hyperscaler will run PostgreSQL for you.

Why PostgreSQL?

PostgreSQL did not come out of nowhere. It is the direct descendant of the research that created the relational database industry, and it has been the engine driving commercial data warehouses and cloud platforms for decades.

So when you are choosing a database for a new system, the question is no longer "why PostgreSQL?", but "why not PostgreSQL?"

Archton helps organisations design, migrate and run open source data platforms, including migrations from Oracle and SQL Server to PostgreSQL. Fire off a message to find out more.