THE DESIGN OF POSTGRES EXTENSION MANAGER “PGXMAN”

Introducing the Postgres Extension Manager, which we fondly call 'pgxman' or 'xman' for short. In the following sections, we’ll cover an introduction to pgxman, extensions, the problems pgxman attempts to solve, and how it works. Check out pgxman.com to learn more.

pgxman is npm for Postgres extensions. pgxman simplifies the discovery and use of extensions so modern hackers can easily enhance the capabilities of their applications.

Instead of managing extension versions, build & run dependencies, operating system and platform architecture, pgxman automatically detects and streamlines extension operations (xOps) based on the local development environment.

Without pgxman, installing and updating extensions is an uphill battle. You're left searching for the right build tools and grappling with often unclear and incomplete compiling instructions to even try one out. But with pgxman, we've streamlined the process to one simple step: pgxman install [extension name]. Jump ahead for a practical example with & without pgxman.

pgxman integrates with the system package manager, ensuring the correct versions are installed without extra packages from any shared dependencies between extensions. pgxman’s automated build system creates [APT] packages for each Postgres version, platform, and OS supported by the extension. Extensions are built from a “buildkit” formula, written in YAML, and are contributed through GitHub.

INTRODUCTION TO EXTENSIONS

In their 1986 paper "The Design of Postgres," authors Michael Stonebraker and Lawrence Rowe prioritized user extendibility, writing “[Postgres should] provide user extendibility for data types, operators, and access methods.”

If you’re unfamiliar with Postgres extensions, they are 3rd party open-source plugins that add new capabilities to Postgres. Extension authors create remarkably novel database capabilities by modifying Postgres’ low level hooks, such as parser hooks, executor hooks, planner hooks.

‍Here are examples of popular Postgres extensions:

Extension	Description
postGIS	Storing, indexing, querying geospatial data in Postgres
pgvector	Adds vector similarity search to Postgres
hydra_columnar	enables columnar tables for 500X faster analytical / aggregate queries

View more extensions

Amazingly, extensions remain compatible with Postgres’ future version releases and new core features. Modifications to Postgres’ low level hooks can be quite powerful - it’s like gene splicing a database. In the eternal words of Professor Charles Xaviar, “mutation is the key to evolution.”

*The xmen: x-gene mutants with superhuman abilities led by Professor X*

DISCUSSION OF DESIGN GOALS

While there are over 1000 Postgres extensions today, modern hackers rarely discover them. During our monthly “SF Analytics Night” club, I ask how many people have used Postgres extensions — only one or two hands raise in the large group.

Extensions are underutilized for several reasons: poor discoverability, restricted support from managed Postgres providers, the need for existing knowledge or study before use, and complex management of extension versions and dependencies. By lowering the friction to use Postgres extensions with pgxman, modern hackers can enhance applications more easily.

1. Discoverability & freedom of choice — pgxman has LLM-enabled search: "pgxman.ai” Search with personalized use case questions to discover relevant extensions. pgxman.ai explains why extensions are recommended to add helpful context. Modern hackers have the freedom to install and run any trusted extension in their environment with pgxman.

2. Clean install, upgrade, uninstall anywhere in “the way a machine expects” — Integrating with the system package manager solves several problems. pgxman handles build and runtime dependencies that extensions need - including Postgres and any shared dependencies between extensions. pgxman ensures correct versions are installed without extra packages. Packages are uninstalled directly with your system package manager - no unmanaged files. There’s no longer a need to expand a tarball into your filesystem.‍

3. Automated build system — Each extension is built from a “buildkit” formula, written in YAML. Buildkits are contributed through GitHub (homebrew model) and supports extensions written in all languages. The automated build system creates [APT] packages for each Postgres version, platform, and OS supported by the extension.

MADE FOR MODERN HACKERS


   > curl -sfl https://install.pgx.sh | sh -
   > pgxman install pgvector

Available Commands	Description
build	Build extension according to the configuration file
bundle	Manage PostgreSQL extensions from a bundle file
completion	Generate the autocompletion script for the specified shell
help	Help about any command
init	Create a buildkit file
install	Install PostgreSQL extensions
search	Search for PostgreSQL extensions
upgrade	Upgrade PostgreSQL extensions

‍

Without pgxman:

Steps to build parquet_s3_fdw manually:

Download the parquet_s3_fdw source code
Figure out how to build it by looking at README. When unclear, look at the source code how to build it. The README of parquet_s3_fdw says it needs libarrow, libparquet & aws_sdk. Readme doesn’t say where to get them.
Make sure all dependencies are available to build the project. Either install them from apt, if available, or build them manually if not. For parquet_s3_fdw aws_sdk has to be built manually — it’s not available in any apt repos.
Build the extension targeting the correct OS, CPU architecture and Postgres version
Determine and build to the right path /usr/lib/postgresql/15/lib - otherwise, Postgres wouldn’t be able to find them.
Repeat across all relevant Postgres instances. Hopefully, these Postgres versions are recent or else you’ll have to update postgres, set maintenance window, etc.
** Added friction: Since parquet_s3_fdw is not designed to use in a cloud environment, and we forked it to make changes to make it possible to use.

With pgxman:


   > curl -sfl https://install.pgx.sh | sh -
   > pgxman install s3_parquet_s3_fdw

*modern hackers at SF Analytics Night [Sept. 2023] hosted by Hydra at Ridge Ventures.*

AFTERWORD

My good friend and cofounder, JD, and I first crossed paths at Heroku in 2014. Any late-evening app builder or dev tools guru will vouch for the transformative and obvious impact of git push heroku master. By reducing the friction to deploy apps, developers could tinker and share webapps more easily. If you look closely, you’ll find the same transformational ethos at OpenAI, Replit, and Vercel today.

I find that these companies, like all great companies, are built with the hacker spirit described in one of my favorite books, “The Cathedral and The Bazaar” by Eric S. Raymond. “Hackers,” a term frequently misused to mean cybercriminals, must be free to think in unusual and creative ways. Empowering strange and wonderful people to tinker and share is a really good thing for the world. The hacker spirit, the culture of creating, is the best shot we have at making a richer society for everyone.

JD, Owen Ou (our lead hacker), and I, along with our entire team, are excited to share the Postgres Extension Manager, which we fondly call 'pgxman' or 'xman' for short. pgxman helps modern hackers quickly enhance apps with Postgres extensions.

CLOSING COMMENT

If you want to build advanced applications, start with pgxman and make it so.

*Patrick Stewart, who portrayed Professor X, has published his memoir “Making It So”*

FUNCTIONAL SOURCE LICENSE

pgxman is released under the Functional Source License (FSL). Open to all users, source available, for Permitted Purpose: “any purpose other than a Competing Use. A Competing Use means use of the Software in or for a commercial product or service that competes with the Software or any other product or service we offer using the Software as of the date we make the Software available.” You can do anything with FSL software except strip-mine the software and undermine the continued development. You can read it, learn from it, run it internally, modify it, and propose improvements. After 2 years, the license for pgxman converts to Open Source software under Apache 2.0. Converting to Apache 2.0 offers community protection if pgxman development slows. We encourage pgxman adoption broadly and welcome any service provider to contact us to obtain a license. ensure the service can scale to meet your needs.

ACKNOWLEDGEMENTS

Thank you Hydra team for excellent work on pgxman, and especially, thank you to Owen Ou, Simon Wijckmans, James Lovern, and my cofounder Jonathan Dance “JD”.

A big thank you to my wife, Rebecca, for her years of loving support and thanks to our tiny, fluffy dog, Titus (”Postgres pet of the week”) too.

Share this post