pg_kpart

pg_kpart : Reject full partition scans that omit the partition key

Overview

Packages

Source

pig build pkg pg_kpart;		# build rpm/deb

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_kpart;		# install via package name, for the active PG version

pig install pg_kpart -v 18;   # install for PG 18
pig install pg_kpart -v 17;   # install for PG 17
pig install pg_kpart -v 16;   # install for PG 16
pig install pg_kpart -v 15;   # install for PG 15
pig install pg_kpart -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_kpart';

This extension does not need CREATE EXTENSION DDL command

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.