At 22 years old, Postgres might just be the most advanced database yet

As a techy, many of the debates I engage in can be boiled down to just one question: Should we pick the new thing or the proven one? As passionate as this question is discussed, there are a handful of technologies where the answer is easy: Why not both?

Postgres is such a technology. Originally released in 1996 (and effectively developed since 1982), it is now 22 years old - yet in many respects, it is the most modern database management system there is. Not only does it come with a simply mindboggling set of features, but it also transcends being a pure database and evolved into a fully programmable, integrated data environment, complete with its own programming language PL/pgSQL.

There's any number of marvels one can discuss in regards to Postgres - but for this article, I'd like to shed light on the five extraordinary features that made it our backend of choice for Arcentry:

Pub/Sub Messaging

Postgres can be used as a clusterable message broker. Granted, it doesn't come with the featureset that purpose-built solutions like RabbitMQ or Kafka offer, but its integration of event-based messaging into the wider data-context makes it extremely valuable. Arcentry's on-premise version, for instance, makes use of this pattern. We use Postgres-Messaging as the backbone for horizontally scalable deployments:

Whenever a user makes a change to any diagram, Arcentry issues a request to a server which merges the update into a binary JSON document stored in Postgres. Once the write is confirmed, a trigger emits an event that all other connected servers are subscribed to which in turn forwards the update to its active users.

This gives us an easy way to provide horizontally scalable realtime updates with strong consistency - all from a single external dependency.

Triggers

Triggers are functions that run before or after data is manipulated. They are a fantastic way to build validation, transformation and derived logic directly into the database.

Triggers also present a simple way to extend existing database functionality. For example, take customers asking for an immutable audit log of changes to Arcentry's account table.

Rather than writing an additional query or service endpoint, we simply programmed a trigger into Postgres that runs whenever a row in the accounts table is altered and writes a copy of the current row, complete with timestamp and the userId that initiated the change to a separate audit table.

Foreign Data Wrappers

Sometimes, it is nice to integrate a user's existing Database into Arcentry - whether to query user accounts or to store document data within an established structure. There is - of course - any number of ways to achieve this, but a particularly convenient one are Postgres' Foreign Data Wrappers.

These are endpoints that connect Postgres to any number of other data sources, say MongoDB, Redis, MySQL or even CSV or JSON files. As far as the query statement is concerned, these sources are just regular Postgres tables that can be joined, searched, referenced and become an organic part of the database - making Postgres a powerful integration tool and potential access point for data-lake setups.

JSONB

Many databases store JSON or its binary representation, JSONB - and I get that this won't spark too much excitement anymore. But Postgres' manipulation functions make JSON a first class citizen within a table based, relational database. Whether its outputting query results as a nested JSON structure or parsing JSON on the fly, Postgres can handle it beautifully.

Add-ons

At times, however, neither PL/pgSQL nor Triggers are enough to achieve the functionality one needs. With many other databases this would simply be the end of it - but Postgres is impressively extendable. Writing Postgres Addons is not an easy task (trust me, I tried), but fortunately, many talented engineers have done the work for me and built extensions that turn Postgres into an entirely different product.

Take for instance PostGIS that turns the Postgres server into a fully fledged spatial database for geographic information systems (GIS).

Or how about PipelineDB that turns Postgres into a timeseries store/ stream processor.

There's any number of Postgres extensions, tools and GUIs, an overview over which can be found here

A granddad-hipster

What makes all this truly stand out is the example that Postgres sets: Remaining relevant for 22 years is an almost impossible feat for any software - but keeping a strong focus and investing decades into improving, refining and optimizing an already strong core can create a piece of technology that today is as relevant as it was back in the 1990s.