Problem Description

Currently, when using PostgreSQL on NixOS, I see two major annoyances when using Postgresql extensions.

Problem 1: Extensions must be explicitly overridden to refer to the right PostgreSQL package

By default, Nixpkgs sets the top-level postgresql attribute to an arbitrary version. At the time of this writing, this version is 9.6, while the latest stable version available is actually 10.2.

However, all packaged extensions (postgis, pg_hll, timescaledb, and more) are currently only built against the postgres attribute (equivalently: they are only built as 9.6 extensions.) This means that if I want to use PostgreSQL 10.2 on my NixOS machines, I must explicitly override the postgres argument to point to the right package. Otherwise, things aren't going to work when I run CREATE EXTENSION in postgresql v10 and it tries to load a .so file built for v9.6.

As an example, here's a service description, snipped from my nixos configurations:

  services.postgresql =
    let
      mkPlug = p: p.override { postgresql = config.services.postgresql.package; };
    in {
      enable = true;
      package = pkgs.postgresql100;
      dataDir = "/data/pgsql";

      extraConfig = "shared_preload_libraries = 'timescaledb'";
      extraPlugins = with pkgs; map mkPlug
        [ postgis
          timescaledb
          cstore_fdw
          pg_hll
          pg_cron
          pg_topn
        ];
    };

The problem here is that this is error prone (it's easy to forget), and even after that it's relatively non-obvious to a user how to properly configure the extensions unless they're already Nix-savvy. It's not unreasonable to want the most recent version of Postgres on a greenfield deployment, while the Nixpkgs default version may be different.

Ideally, extraPlugins really should do this attribute mapping itself, IMO. But at the same time...

Problem 2: The set of postgresql versions and extensions is not namespaced

If we are going to have multiple PostgreSQL versions, each with extensions, I strongly suggest we namespace them appropriately, yielding a top-level attribute set that can be used.

For example, we could have something like the following in all-packages.nix:

postgresql96Packages = ...;
postgresql10Packages = ...;

postgresqlPackages = postgresql96Packages;

Then, much like Linux kernel modules, a PostgreSQL extension for a particular version will be available under the corresponding attrset; e,g.

postgresql96Packages.timescaledb
postgresql96Packages.pg_hll
...

The motivation here is twofold, for me: one, these packages can't even be used without postgres, so having them in the top-level namespace seems wrong. Second, as a result of the first point, finding what extensions we support and updating them will be significantly easier IMO. Right now the only convenient thing tying all these packages together is that all related expressions are under ./pkgs/servers/sql/postgresql/. This should be reflected in the namespace, IMO.

Napkin sketch of new module API

If we had something like the above, we could simplify the above example of using Postgresql 10.x to something like:

  services.postgresql.enable = true;
  services.postgresql.dataDir = "/data/pgsql";
  services.postgresql.packages = pkgs.postgresql10Packages;
  services.postgresql.plugins = pgsqlPkgs: with pgsqlPkgs;
    [ postgis
      timescaledb
      cstore_fdw
      pg_hll
      pg_cron
      pg_topn
    ];
  services.postgresql.extraConfig = "shared_preload_libraries = 'timescaledb'";

Namely, the services.postgresql.plugins attribute contains a function which takes the attrset of all PostgreSQL extension packages as an argument, as specified by .packages. Essentially you can think of it like a kind of filter predicate that picks out the extensions you want, from the attrset containing every possible extension. By default this would be _: [] or const [].