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.
Another Postgres tradition: for many years, the conference called “pgcon” in Ottawa was one of the largest annual in-person gatherings of PG core hackers. It’s not the only gathering, but it’s an important one. It also usually happens around the time that features for the next major version are getting nailed down.
Two very important announcements happened in recent months:
- This year (2024), the conference will move to Vancouver.
- This year, the conference will include a “mini-summit” for everyone building and using extensions. (To be clear, that’s most of you!)
The mini-summit idea was spear-headed by David Wheeler, the guy who I think chiefly carries the blame for PGXN. 😀 As co-conspirators we have myself, David Christensen, Keith Fiske and Devrim Gündüz. Importantly, this represents collaboration across multiple countries and multiple companies.
Leading up to this summit, every 2 weeks on Wednesday, there’s an open public zoom call with a short presentation and then discussion. At the meetings we had on March 6 and March 20, the presentations were great and the discussions were very engaging. We’re getting into specifics about why this is a hard problem to solve and how we can chart the path forward.
There are four more meetings coming up. Schedule and topics are here: https://justatheory.com/2024/02/extension-ecosystem-summit/
That blog has the link to sign up with EventBrite for zoom dial-in info, or alternatively you can jump on the public community slack and just ask David for the zoom link 😉 (find us on the #extensions channel)
The past zoom calls can be viewed on this YouTube playlist, and future calls will be posted here as well: https://www.youtube.com/watch?v=6o1N1-Eq-Do&list=PL11N188AYb_aT6ulbJfrQJJziWb012jG3&index=1
Over the past two months, David’s been publishing blog posts so fast I can barely keep up! You can see a full list at https://justatheory.com/archive/
Here are a few that I think are the most important:
- Top Read – What does “great” look like? What’s our north star? Learning from go/rust/perl/python/ruby… solving binary packaging, supply chain vulnerabilities, APIs, CLIs, standards: https://tembo.io/blog/pgxn-ecosystem-jobs
- Top Read – Reasoning behind first proposal for updating
META.json(the heart of PGXN)… this will be the foundation for solving everything above: https://justatheory.com/2024/03/rfc-pgxn-metadata-sketch/ …please give feedback!- You can optionally leave comments about the blog at https://github.com/theory/justatheory/pull/4/files if you like this format. Alternatively, find us on the community slack #extensions channel.
- Gaps in PGXN today: https://justatheory.com/2024/01/pgxn-challenges/
- Reasoning behind proposal to stick with Semantic Versioning requirement: https://justatheory.com/2024/02/extension-versioning/ …please give feedback, especially if you disagree
- Reasoning behind proposal to use go-style namespacing (
pgxn install github.com/example/pew): https://justatheory.com/2024/03/extension-namespace-rfc/ …still under discussion – please give feedback
If you are using extensions – and especially if you’re involved in building or bundling extensions – please join us on these zoom calls. Whether you’re an individual or a company. Join the zoom calls (please!), comment on blog posts, join discussion on discord or slack (#extensions channel), register for the mini-summit in Vancouver.
It would be especially helpful if you could look at the proposed new META.json spec, and try to write a META definition for your extension. Share your draft with us! Are we missing some metadata? Does it work? Is there some field that just doesn’t quite fit for how you’d want to list and distribute your extension?
One more way you can help: these days the social media landscape seems a bit more fragmented than it used to be. I’m not on all the social media places. So please re-share this article and David’s articles, and help us spread the word about the mini-summit. We rely heavily on this word-of-mouth assistance to get the word out, now more than ever.
Today Postgres database servers are running in every industry, private and public and personal, everywhere from the largest iron in the world to raspberry pi’s and tiny embedded IoT devices. We know that a significant percentage of these installations involve independently developed extensions (not coming from postgresql.org). We also know there’s still a lot of room for growth. We want to hear feedback and thoughts from everyone, large and small – to make the extension experience better for both users and developers alike.
Ardent Performance Computing 
Discussion
No comments yet.