pg_table_range

Overview

Experimental. 0.1.2 fixes missing rows and backend crashes. From 0.1.1: install new binaries, restart all backends, UPDATE extension, REINDEX each table_range index (parent for partitioned tables); old summaries are ignored until rebuilt.

Version

Build

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

pig build pkg pg_table_range         # build RPM packages

Install

You can install pg_table_range 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 pg_table_range;          # Install for current active PG version

pig ext install -y pg_table_range -v 18  # PG 18
pig ext install -y pg_table_range -v 17  # PG 17
pig ext install -y pg_table_range -v 16  # PG 16

dnf install -y pg_table_range_18       # PG 18
dnf install -y pg_table_range_17       # PG 17
dnf install -y pg_table_range_16       # PG 16

Create Extension:

CREATE EXTENSION pg_table_range;

Usage

Sources:

• Upstream README
• Version and PostgreSQL feature matrix
• Planner-hook and GUC registration
• 0.1.2 upgrade and correctness fixes

pg_table_range is an experimental PostgreSQL 16–18 extension for planning-time partition pruning on columns that are not partition keys. Its table_range index access method stores each partition’s actual scalar minimum and maximum, range extent, or PostGIS geometry extent. A planner hook removes a partition only when its summary proves that the partition cannot match.

Basic use

Build the summary index on a partitioned table and compare plans with representative predicates:

CREATE EXTENSION pg_table_range;

CREATE INDEX events_tr
ON events USING table_range (val, created_at);

EXPLAIN (COSTS OFF)
SELECT *
FROM events
WHERE val >= 250;

SET table_range.enable_pruning = off;
EXPLAIN (COSTS OFF)
SELECT *
FROM events
WHERE val >= 250;
RESET table_range.enable_pruning;

The index is never selected to scan rows; it owns compact summaries used by the planner. Inserts widen summaries automatically. Deletes remain safe but can leave them wider than live data until REINDEX tightens them; VACUUM does not rebuild summaries.

Supported pruning predicates include scalar comparisons, BETWEEN, null tests, constant IN or ANY lists, and overlap for range and PostGIS geometry types. Unsupported expressions conservatively keep the partition.

Upgrade from 0.1.1

Version 0.1.2 fixes missing rows and backend crashes in 0.1.1. Install the new binaries and restart PostgreSQL so no backend still runs the old code. Then update the extension and rebuild every table_range index, using the parent index for partitioned tables:

ALTER EXTENSION pg_table_range UPDATE TO '0.1.2';
REINDEX INDEX events_tr;

Old summaries are ignored until rebuilt, keeping all partitions rather than risking incorrect pruning. Partial indexes are rejected. REINDEX is also the way to tighten summaries after deletes.

Caveats

• Upstream explicitly labels the project experimental and heavily AI-driven. Qualify correctness, crash recovery, upgrades, and planner behavior on production-shaped data before adoption.
• Planning cost is linear in the partition count. Native partition-key pruning is far cheaper; use pg_table_range only for selective non-key predicates where the execution savings justify planning work.
• Index creation and non-key planning lock every partition. With thousands of partitions, the default max_locks_per_transaction can fail with out-of-shared-memory errors; increasing it consumes shared memory and requires a restart.
• Deletes can reduce selectivity until maintenance rebuilds or tightens summaries. An invalid table_range index silently disables its pruning.
• The control file requires superuser installation. Limit table and index DDL privileges and test the Rust/pgrx build for the exact PostgreSQL major version.