pgfaceting

Overview

Version

Build

You can build the RPM packages for pgfaceting using pig build:

pig build pkg pgfaceting         # build RPM packages

Install

You can install pgfaceting directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pgfaceting;          # Install for current active PG version

pig ext install -y pgfaceting -v 18  # PG 18
pig ext install -y pgfaceting -v 17  # PG 17
pig ext install -y pgfaceting -v 16  # PG 16
pig ext install -y pgfaceting -v 15  # PG 15
pig ext install -y pgfaceting -v 14  # PG 14

dnf install -y pgfaceting_18       # PG 18
dnf install -y pgfaceting_17       # PG 17
dnf install -y pgfaceting_16       # PG 16
dnf install -y pgfaceting_15       # PG 15
dnf install -y pgfaceting_14       # PG 14

apt install -y postgresql-18-pgfaceting   # PG 18
apt install -y postgresql-17-pgfaceting   # PG 17
apt install -y postgresql-16-pgfaceting   # PG 16
apt install -y postgresql-15-pgfaceting   # PG 15
apt install -y postgresql-14-pgfaceting   # PG 14

Create Extension:

CREATE EXTENSION pgfaceting CASCADE;  -- requires: roaringbitmap

Usage

pgfaceting: fast faceted search using inverted indexes with roaring bitmaps

The pgfaceting extension enables rapid facet counting via inverted indexes built with roaring bitmaps. Requires the pg_roaringbitmap extension.

CREATE EXTENSION pgfaceting;

Facet Types

• plain_facet(column): Use column values directly as facets
• datetrunc_facet(column, precision): Apply date truncation (e.g., monthly/yearly buckets)
• bucket_facet(column, buckets): Assign continuous variables to predefined ranges

Key Functions

-- Create facet infrastructure for a table
SELECT pgfaceting.add_faceting_to_table(
    'products',
    'id',
    ARRAY[
        plain_facet('color'),
        plain_facet('size'),
        bucket_facet('price', ARRAY[0, 10, 50, 100, 500])
    ]
);

-- Run maintenance to merge incremental changes
SELECT pgfaceting.run_maintenance();

-- Merge deltas for a specific table
SELECT pgfaceting.merge_deltas('products');

-- Get top N facet values
SELECT pgfaceting.top_values('products', 10);

-- Count results with facet filters
SELECT pgfaceting.count_results('products', filters);

Architecture

The extension maintains two auxiliary tables per indexed table: a main facets table with roaring bitmaps mapping facet values to row IDs, and a delta table for incremental changes between maintenance runs.

Currently supports only 32-bit integer ID columns.