Show HN: pgxman – npm for Postgres extensions | Hacker News Reader

Show HN: pgxman – npm for Postgres extensions

pgxman.com

81 pointscoatue21 comments

pgxman is npm for Postgres extensions, simplifying the discovery and use of extensions so you can easily enhance your applications.

Installing and updating Postgres 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].

For example, to build parquet_s3_fdw manually, you'd need to:

1. Download the parquet_s3_fdw source code;

2. 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.

3. 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

4. Build the extension targeting the correct OS, CPU architecture and Postgres version.

5. Determine and build to the right path /usr/lib/postgresql/15/lib - otherwise, Postgres wouldn’t be able to find them.

6. 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.

Using pgxman, you can just do:

  pgxman install s3_parquet_fdw

pgxman integrates with your 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.

To install pgxman, you can do

  brew install pgxman/tap/pgxman

or, if you don't mind pipe-to-shell,

  curl -sfl https://install.pgx.sh | sh -

If you'd like to learn more, we have an extensive blog post here: https://www.hydra.so/blog-posts/the-design-of-postgres-exten....

Open articleView on HN

Other related projects:

* PGXN (https://pgxn.org/ - the original Postgres community extension repository, though more "old school")

* Trunk by Tembo (https://github.com/tembo-io/trunk - very similar to pgxman, but using the more liberal Postgres BSD-style license)

Additionally its important to mention that many popular extensions are already packaged as part of the Postgres YUM and Apt repositories (https://wiki.postgresql.org/wiki/Apt and https://yum.postgresql.org/).

I would generally recommend using the upstream repository packages directly, since they are tested/updated with each major release, and their maintainers are proactive in reaching out to extension maintainers that need to update their extension due to upstream code changes.

> I would generally recommend using the upstream repository packages directly, since they are tested/updated with each major release, and their maintainers are proactive in reaching out to extension maintainers that need to update their extension due to upstream code changes.

Not all extensions have an upstream repository and built debian/yum packages. At Hydra, we test all published in https://github.com/pgxman/buildkit which is available from our registry.

Also database.dev, specifically for Trusted Language Extensions:

https://supabase.com/blog/dbdev

We have some exciting news re: Trunk to announce in January — taking the project to the next level.

Thanks for mention :)

I didn't even know I wanted this, but after seeing it, makes 100% sense!

What an incredibly good idea.

Edit: Looks like there is some existing (partial) competition from pgxn: https://manager.pgxn.org/about

pgxn is a network of extensions but is far from being an extension manager. An extension manager would IMHO include building the extensions, distributing built artifacts, and managing their lifecycle (upgrade/delete etc.) This is the gap that pgxman tries to fill. When you run `pgxman install EXTENSION`, it installs the prebuilt extension to your system.

A core part of npm is that the dependencies are installed in the folder/project you are working in (unless you use the --global flag which is discouraged for most use-cases).

Can I use this in any way without a global system state? Like with https://github.com/zonkyio/embedded-postgres-binaries or similar that don't have a global install? Or with a distro that supports multiple versions/instances of postgresql?

In my opinion, we plan on accomplishing this by using a container; it's not quite something we have today, but this is good feedback. :)

On Ubuntu/Debian, Postgres doesn't typically work this way, so it's not the way that pgxman works. pgxman works on top of the existing `postgresql` packages and with the existing package manager (apt) in order to install extensions -- which is also how it handles runtime dependencies, whether libraries or even other extensions.

So, that said, we have a container feature I could see using to effectively isolate for a single project. Right now there is only one single "global" container (per Postgres version) that pgxman will manage for you, but this is just a MVP of this feature. I could definitely see something like `pgxman c dev` or similar which will read a local pgxman pack file (pgxman.yaml) in your project and boot a "local" Postgres for you just for that project.

The pgxman pack is already a thing and is how the local container config is maintained, but we haven't tied it together in the way described above... yet. For more on both pgxman pack and the container feature, check out our docs.

I'm not sure what is `npm` your meaning, is it https://www.npmjs.com/ ?

Is pgxman an extension manager for Postgres like apt for Debian?

Yes, that's right- npm was in reference to npmjs.com. The analogy was made since pgxman handles version and dependency management, but for Postgres extensions.

> pgxman’s automated build system creates [APT] packages

Could these packages be put in a repository online, so that they can be installed without using pgxman?

Interesting - does it work with the existing postgres apt/yum repos?

Does it work with the postgres Docker image? Can I:

    FROM postgres
    (...get pxman)
    pgxman install ...

> does it work with the existing postgres apt/yum repos?

We only support apt for now but plan to support other package managers in the future. It works with existing Postgres apt packages, we recommend using PGDG but the default system packages on Debian/Ubuntu work as well.

> Does it work with the postgres Docker image?

yes, in fact this how our `container` feature works. https://docs.pgxman.com/container

ooof, that render on the homepage consistently brings my Chrome browser to its knees.

Runs nice and smooth for me on Firefox

Same (FF on iOS)

Same on Brave, 700% CPU usage before I closed the page.

Dumb question but those any percent over 100 mean?

I know 100% is full cpu usage, but what does 700% mean?

It means that your cpu has 7x more work to do than it is capable of executing. 100% is a full load of a single core, 700% will be 7 cores working at 100% each.

Thanks

Reply on news.ycombinator.com