← Browse the extensions catalog

pgfaceting

Source README ↗

pgfaceting

Fast facet counts using roaring bitmap indexes. Upstream documentation.

Follow the quick start to set up extension-update email alerts first, then use the CronJob to install and refresh the catalog. These examples inherit image references and runtime settings from the catalog. The Cluster YAML is an excerpt: replace imageCatalogRef: ... with your catalog reference and keep your existing instance and storage settings.

Supported images

PostgreSQL 18 on Debian Bookworm and Trixie, for amd64 and arm64.

Usage

1. Add the pgfaceting extension image to your Cluster

Include the required roaringbitmap extension image before pgfaceting. No shared preload configuration is needed.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-pgfaceting
spec:
  imageCatalogRef: ...
  postgresql:
    extensions:
    - name: roaringbitmap
    - name: pgfaceting

2. Enable the extension in a database

Once the extension's files are available in the cluster, you can install it in a database in either of two ways:

Platform teams may make extension containers available across their clusters without installing every extension in every database. Install only the extensions a database uses; letting users enable them through SQL alongside the rest of their DDL schema management code can be a convenient approach.

For extensions that normally require superuser privileges, platform teams can use pgextwlist to allow non-superusers to install an administrator-approved selection of extensions.

Install roaringbitmap, then pgfaceting in that order. Through SQL, run CREATE EXTENSION roaringbitmap; before CREATE EXTENSION pgfaceting;.

3. Verify installation

Connect to the database with psql and run \dx to check the installed extensions.

Use and maintain facets

After installing both extensions, use an administrator connection to the app database to grant the table owner access to the schema and metadata tables:

GRANT USAGE ON SCHEMA faceting TO app;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON faceting.faceted_table, faceting.facet_definition TO app;

These grants permit management of the shared facet metadata; reserve them for trusted table owners. Then connect to app as its owner:

CREATE TABLE items (id integer PRIMARY KEY, color text, size text);
INSERT INTO items VALUES (1, 'red', 'small'), (2, 'red', 'large'), (3, 'blue', 'small');
SELECT faceting.add_faceting_to_table('items', key => 'id',
  facets => ARRAY[faceting.plain_facet('color'), faceting.plain_facet('size')]);
SELECT * FROM faceting.count_results('items'::regclass,
  filters => ARRAY[ROW('color', 'red')]::faceting.facet_filter[]);
-- size=small: 1; size=large: 1
UPDATE items SET color = 'blue' WHERE id = 2;
SELECT faceting.merge_deltas('items'::regclass);
SELECT * FROM faceting.top_values('items'::regclass, n => 10);
-- color=blue: 2; color=red: 1

Facet indexes and pending changes are ordinary database tables, included in normal database backup/replication. Schedule CALL faceting.run_maintenance() through your SQL scheduler to merge deltas regularly. Keys must fit in 32-bit integers. No external daemon or writable extension-image directory is needed.

The PGDG copyright file calls its license Aasma; the complete text is the standard BSD-3-Clause license (including the non-endorsement clause), so metadata.hcl uses that SPDX identifier. The shared SBOM generator retains Debian's LicenseRef-Aasma label; it refers to the same reviewed license text. The unmodified notice is shipped at /licenses/postgresql-18-pgfaceting/copyright.

The image contains only pgfaceting SQL/control files and its copyright notice; no shared libraries, interpreter or build tools are shipped. The required roaringbitmap extension is a separate image with its own notices and SBOM. Both fit the top-level README's CNCF-compatible declared-license group.

Updates and validation

Renovate tracks Debian package pins and SQL versions in metadata.hcl. SQL versions are extracted from the verified package version shape; review the packaged control files and upgrade scripts when that shape changes. Rebuild and validate both distributions and architectures after package updates.

The extension-specific Chainsaw test exercises real behavior through the mounted image, in addition to the shared installation checks. Run:

task checks TARGET=pgfaceting
task e2e:test:full TARGET=pgfaceting