Ardent Performance Computing

Latest Post

Major Developments in Postgres Extension Discovery and Distribution

PostgreSQL “extensions” are a big part of what makes this database special.

The developers building the core Postgres database are amazing. But many people don’t realize just how much of a “data platform” Postgres is (borrowing this phrase from something Craig Kerstiens recently posted online) and just how decentralized the development is for PostgreSQL’s capabilities.

Extensions are independent software projects that extend the PostgreSQL core database server’s capabilities. As with a GNU-Linux based operating system, you can build and install everything yourself alongside the core database, or you can find someone who creates a bundle/stack/distribution containing both the core database and some popular extensions. Example database server capabilities you might use, without realizing your friendly upstream maintainer does not live at postgresql.org:

• query hints
• additional data type support (like extra UUID functionality)
• advanced encryption
• scheduled jobs that run inside the database
• geospacial support
• vector support
• automatic time-based child partition creation
• custom logical replication connectors (like JSON)
• … and lots more

An extension has deep integration with the database. With most software, this kind of integration requires patching and recompiling. But the magic of Postgres is a reasonable openness to hooks that enable this deep integration without recompilation. Extensions can have libraries (think dll or so), but some don’t. Extensions can have sql/schema components, but some don’t. Extensions aren’t even required to be written in C (like Postgres is) – many languages can be used, from tcl to rust to python to perl to javascript… and even PL/pgSQL.

This means that independent developers can build and maintain extended server capabilities – and these developers can more easily organize their software projects independently from Postgres core database development. Better definition of APIs means there’s less risk of breakage at integrations points after Postgres upgrades. These extensions can be installed alongside the standard pre-compiled RPMs and DEBs provided on official community Postgres repositories.

The extension model isn’t perfect but it has existed in the Postgres world for many many years. Something else has existed for many years as well: the PostgreSQL Extension Network (PGXN), a hub for discovering and distributing extensions. It’s not perfect either, and it doesn’t have every extension, but it has the most and it’s the oldest. Also, the pgxn client is already in the standard repositories of major linux distributions. (apt install pgxnclient)

Postgres has been around for a really long time, and there’s been a lot of advancement around public software distribution over this time. The go module index, crates.io, perl modules on CPAN/PAUSE, rubygems and PyPI have each advanced the field. Over the years, a handful of people have started working to fill the gaps around Postgres extensions. We’ve seen pgpm, dbdev & trusted language extensions (TLE), pgxman and trunk.

It’s pretty clear that the time has come for an updated open standard of Postgres extension discovery and distribution.