pg_kpart

Overview

Planner hook must be loaded through shared_preload_libraries or session_preload_libraries; CREATE EXTENSION is optional.

Version

Build

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

pig build pkg pg_kpart         # build RPM / DEB packages

Install

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

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

dnf install -y pg_kpart_18       # PG 18
dnf install -y pg_kpart_17       # PG 17
dnf install -y pg_kpart_16       # PG 16
dnf install -y pg_kpart_15       # PG 15
dnf install -y pg_kpart_14       # PG 14

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

Preload:

shared_preload_libraries = 'pg_kpart';

Usage

Sources:

• Official v1.0 README
• v1.0 control file

pg_kpart prevents accidental queries that would scan every leaf partition of a partitioned table without effective partition pruning. Its planner hook can raise, warn, or log before execution. The functional unit is the preloaded library; there are no SQL objects to create, and upstream describes CREATE EXTENSION only as optional catalog registration.

Enable and Roll Out

For cluster-wide enforcement, preload the library and restart PostgreSQL:

shared_preload_libraries = 'pg_kpart'

It can also be loaded for selected sessions or databases without a server restart:

session_preload_libraries = 'pg_kpart'

Start in audit mode before enforcing errors:

ALTER SYSTEM SET pg_kpart.message_level = 'warning';
SELECT pg_reload_conf();

Once the observed queries are understood, set pg_kpart.message_level = 'error'.

Scope and Behavior

-- Check only these tables and their sub-partitions.
ALTER SYSTEM SET pg_kpart.blacklisted =
    'public.measurement, public.orders';

-- Or check all partitioned tables except selected hierarchies.
ALTER SYSTEM SET pg_kpart.whitelisted = 'public.audit_log';
SELECT pg_reload_conf();

-- Partition key is logdate.
SELECT * FROM measurement WHERE city_id = 5;              -- violation
SELECT * FROM measurement WHERE logdate = DATE '2026-07-01'; -- pruned, allowed
SELECT * FROM measurement WHERE logdate = $1;             -- runtime pruning, allowed

Violations use SQLSTATE FS001, which applications can trap when message_level is error.

Configuration Index and Caveats

• pg_kpart.enabled: master switch; default on.
• pg_kpart.message_level: error, warning, notice, log, and other PostgreSQL message levels.
• pg_kpart.min_partitions: minimum leaf-partition count to check; default 2.
• pg_kpart.check_superuser: superusers bypass checks by default.
• pg_kpart.blacklisted: when nonempty, only named hierarchies are checked and whitelisted is ignored.
• pg_kpart.whitelisted: hierarchies exempt from checking when no blacklist is set.
• A predicate whose range still includes every partition is treated as a full scan and rejected, even if it mentions the partition key.
• The hook also applies to UPDATE, DELETE, and EXPLAIN without ANALYZE. It relies on PostgreSQL’s planned pruning result, not textual inspection of WHERE clauses.
• Upstream v1.0 is tested on PostgreSQL 14 and newer.