Skip to content

pg_improv

Incremental multidimensional models and formula functions over PostgreSQL tables.

Overview

PackageVersionCategoryLicenseLanguage
pg_improv0.13.0OLAPApache-2.0 OR MITRust
IDExtensionBinLibLoadCreateTrustRelocSchema
2580pg_improvNoYesNoYesNoNoimprov

Commit/logical modes need preload; policies bypassed by owners.

Version

TypeRepoVersionPG VerPackageDeps
EXTPIGSTY0.13.01817161514pg_improv-
RPMPIGSTY0.13.01817161514pg_improv_$v-
DEBPIGSTY0.12.21817161514postgresql-$v-pg-improv-
OS / PGPG18PG17PG16PG15PG14
el8.x86_64N/A
el8.aarch64N/A
el9.x86_64N/A
el9.aarch64N/A
el10.x86_64N/A
el10.aarch64N/A
d12.x86_64N/A
d12.aarch64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
d13.x86_64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
d13.aarch64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
u22.x86_64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
u22.aarch64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
u24.x86_64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
u24.aarch64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A
u26.x86_64N/A
u26.aarch64
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
PIGSTY 0.12.2
N/A

Build

You can build the RPM / DEB packages for pg_improv using pig build:

pig build pkg pg_improv         # build RPM / DEB packages

Install

You can install pg_improv 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:

Install
pig install pg_improv;          # Install for current active PG version
pig
pig ext install -y pg_improv -v 18  # PG 18
pig ext install -y pg_improv -v 17  # PG 17
pig ext install -y pg_improv -v 16  # PG 16
pig ext install -y pg_improv -v 15  # PG 15
dnf
dnf install -y pg_improv_18       # PG 18
dnf install -y pg_improv_17       # PG 17
dnf install -y pg_improv_16       # PG 16
dnf install -y pg_improv_15       # PG 15
apt
apt install -y postgresql-18-pg-improv   # PG 18
apt install -y postgresql-17-pg-improv   # PG 17
apt install -y postgresql-16-pg-improv   # PG 16
apt install -y postgresql-15-pg-improv   # PG 15

Create Extension:

CREATE EXTENSION pg_improv;

Usage

Sources:

pg_improv 0.13.0 binds PostgreSQL tables to multidimensional models, evaluates formulas and materializes results. It supports PostgreSQL 15–18 and requires superuser installation. This example uses manual synchronization, which works without preloading the library.

Core Workflow

CREATE EXTENSION pg_improv;
CREATE TABLE sales (quarter text, price float8, qty float8);
INSERT INTO sales VALUES ('Q1', 10, 100), ('Q2', 12, 200);
SELECT improv.create_model('plan');
SELECT improv.set_sync_mode('plan', 'manual');
SELECT improv.bind_category('plan', 'Time', 'sales', 'quarter');
SELECT improv.bind_measure('plan', 'Price', 'sales', 'price');
SELECT improv.bind_measure('plan', 'Quantity', 'sales', 'qty');
SELECT improv.define('plan', 'Revenue = Price * Quantity');
SELECT improv.recalc('plan');
SELECT * FROM improv_plan.revenue;
SELECT * FROM improv.status('plan');

Bind categories before their measures. Duplicate coordinates require an explicit aggregate; null values do not create model cells. Result tables live in a model-specific schema. improv.define accepts replace => true to replace a formula, and improv.recalc returns the number of changed result rows.

Synchronization Modes

improv.set_sync_mode chooses manual, trigger, commit or logical synchronization. Trigger mode works without preloading; commit mode needs session or server preloading. The default logical mode needs shared_preload_libraries, a server restart, wal_level=logical and the appropriate improv.database worker setting. Logical results arrive asynchronously; use explicit recalculation when a transaction must immediately see current results.

Monitor improv.status and cap retained WAL with max_slot_wal_keep_size. Logical replication slots can retain WAL while a worker stalls. Binding tables can create publications and change replica identity; account for those effects before enabling logical mode.

Formula and Maintenance Boundaries

The improv language supports scalar formulas as well as model expressions, but not arbitrary SQL calls or external I/O. Formula errors normally become null; improv.raise_errors changes that policy. Numeric values and large integers pass through floating-point evaluation, so precision beyond 2^53 needs care. improv.max_cells limits loaded input cells.

Back up model definitions and result state with database dumps and rehearse restoration; logical mode needs a fresh slot and recomputation after restore. Library upgrades require restarting preloaded processes and updating the extension SQL. Model computation is distinct from an application transaction’s source-of-truth tables.

Model Access Policies in 0.13.0

Models execute with their owner’s privileges. Grant improv_user to model creators and give them the required source-table privileges. Version 0.13.0 adds Cedar policies through improv.set_policies, inspection through improv.policies, and decision explanations through improv.explain_access.

SELECT * FROM improv.policies('plan');
SELECT * FROM improv.explain_access('plan', 'Read');

Grant improv_reader (or improv_user) to restricted users and separately grant access to result tables. Policies filter result rows and the improv.categories, improv.items, improv.measures and improv.cells functions. Superusers, the owner and roles allowed to act as the owner bypass these policies. A model without policies retains its ordinary grant behavior; configured policies deny by default, and invalid policies deny access to restricted users.

Derived values are calculated over all owner-visible inputs before filtering. Hiding an input does not remove its contribution from an allowed total. Model policies do not restrict direct access to source tables. Permitted cell edits and policy changes are recorded in improv.policy_audit; rejected operations are logged by the server. Review grants and totals before exposing a model.

Upgrading to 0.13.0

Install matching files and restart PostgreSQL when the library is preloaded, then update each database:

ALTER EXTENSION pg_improv UPDATE TO '0.13.0';

The 0.12.2-to-0.13.0 migration adds the policy objects and functions without changing model rows. The release also fixes an upgrade deadlock with the logical-sync worker. Back up roles and model data and rehearse the update. Upstream now uses pgrx 0.19.3; this does not establish availability of a matching distribution package.

Was this page helpful?

Table reader