pg_table_range

pg_table_range : Planning-time partition pruning by per-partition data ranges, including non-key columns.

Overview

Packages

Source

pig build pkg pg_table_range;		# build rpm

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_table_range;		# install via package name, for the active PG version

pig install pg_table_range -v 18;   # install for PG 18
pig install pg_table_range -v 17;   # install for PG 17
pig install pg_table_range -v 16;   # install for PG 16

Create this extension with:

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.