pg_kpart PostgreSQL extension

As a DBA, you have almost certainly run into queries that hit a partitioned table without using the partition key. On small tables it goes unnoticed. On tables holding hundreds of millions or billions of rows, it is a disaster: PostgreSQL has no choice but to scan every partition, the server's I/O subsystem saturates, and overall performance collapses for everyone connected to the instance.

The frustrating part is that this rarely happens on purpose. It usually comes down to one of two things:

• The developers writing the queries are not aware that the table is partitioned, or do not fully understand the consequences of a full scan across every partition.
• They were aware at some point, but the table grew, the team changed, and that knowledge quietly faded over time.

Either way, the database is left exposed. A single careless query can bring a high-traffic production server to its knees.

At HexaCluster, we run into this pattern regularly on customer clusters with very large partitioned tables. Whenever a missing safeguard like this can be solved with a PostgreSQL extension, we build it so the whole community can benefit — and that is exactly the problem pg_kpart was created to solve.

What pg_kpart does

pg_kpart is a PostgreSQL extension that rejects queries which would scan every partition of a partitioned table without a usable predicate on the partition key. In other words, it prevents accidental full-hierarchy scans caused by a missing condition on the partition key in the WHERE clause or in a join.

The rule it enforces is simple: if a query against a protected partitioned table cannot prune any partitions, it is not allowed to run. The author gets a clear error instead of a server-wide I/O storm, and the query has to be rewritten to filter on the partition key — which is exactly the access pattern partitioning is meant to encourage.

How it works

pg_kpart installs a planner_hook. After the standard planner has produced a plan, the extension walks the resulting plan tree. For every Append or MergeAppend node sitting on top of a partition hierarchy, it makes a single decision:

• If the node carries run-time pruning information (part_prune_info — for example a parameterized predicate on the partition key), the key is being used, so the query is allowed.
• Otherwise, it compares the number of surviving leaf partitions against the total number of leaf partitions of the queried table. If they are equal, no pruning happened: the partition key was not restricted, and pg_kpart raises its configured message.

A predicate that prunes only some partitions — say, a range on the key spanning a handful of partitions — leaves fewer surviving leaves than the total and is accepted. The check works the same way one level down: a direct query on a partition that has its own sub-partitions is rejected if it would scan all of those sub-partitions.

Because this happens in the planner, the protection naturally covers SELECT, UPDATE, DELETE, and even EXPLAIN (without ANALYZE) — anything that goes through planning.

A quick illustration

Take the classic measurement table partitioned by range on logdate. With pg_kpart active, a query that filters only on a non-key column would scan every partition, so it is rejected:

-- partition key is logdate
SELECT * FROM measurement WHERE city_id = 5;            -- ERROR: would scan all N partitions

Filtering on the partition key lets the planner prune, and the query is allowed:

SELECT * FROM measurement WHERE logdate = '2024-03-01';   -- OK (pruned to 1 partition)
SELECT * FROM measurement WHERE logdate >= '2024-06-01';  -- OK (key restricted)
SELECT * FROM measurement WHERE logdate = $1;             -- OK (run-time pruning)

The same logic applies to sub-partitions. If m_2025 is a partition of measurement that is itself sub-partitioned, querying it directly is fine when it prunes, and rejected when it would scan the whole sub-tree:

SELECT * FROM m_2025 WHERE logdate = '2025-03-01';        -- OK (pruned to 1 sub-partition)
SELECT * FROM m_2025
WHERE logdate >= '2025-01-01' AND logdate < '2026-01-01'; -- ERROR: would scan all N sub-partitions

Violations are raised with the custom SQLSTATE FS001, so applications can trap them explicitly rather than treating them as generic errors:

DO $$
BEGIN
  PERFORM count(*) FROM measurement;
EXCEPTION WHEN SQLSTATE 'FS001' THEN
  RAISE NOTICE 'caught a full-partition-scan attempt';
END $$;

Why this matters: the I/O difference

To make the cost concrete, picture measurement partitioned monthly into 24 partitions with a few hundred million rows each. The numbers below are illustrative, but the behavior they show is exactly how PostgreSQL handles these two cases.

Without a usable predicate on the partition key, PostgreSQL builds an Append over every partition and reads each one end to end:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM measurement WHERE city_id = 5;

 Finalize Aggregate  (actual time=84211.0 rows=1)
   ->  Gather  ...
         ->  Partial Aggregate  ...
               ->  Parallel Append  (actual rows=...)
                     ->  Parallel Seq Scan on measurement_2024_07  ...
                     ->  Parallel Seq Scan on measurement_2024_08  ...
                     ->  ... (all 24 partitions scanned) ...
                           Filter: (city_id = 5)
   Buffers: shared read=41875320
 Planning Time: 1.4 ms
 Execution Time: 84233.7 ms

Every partition is touched, tens of gigabytes are pulled through shared buffers, and the query runs for well over a minute — saturating storage for everyone else on the server while it does. This is precisely the plan pg_kpart refuses at planning time.

Now the same aggregate, but filtering on the partition key so the planner can prune:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM measurement
WHERE logdate >= DATE '2026-06-01'
  AND logdate <  DATE '2026-07-01'
  AND city_id = 5;

 Aggregate  (actual time=312.5 rows=1)
   ->  Index Scan using measurement_2026_06_city_id_idx on measurement_2026_06  ...
         Index Cond: (city_id = 5)
   Buffers: shared read=1842
 Planning Time: 0.9 ms
 Execution Time: 313.1 ms
 Subplans Removed: 23

The Subplans Removed: 23 line tells the whole story: 23 of the 24 partitions were pruned away before execution even began. The query reads a few thousand buffers instead of tens of millions and finishes in milliseconds instead of minutes. That difference — roughly four orders of magnitude in both I/O and latency in this example — is exactly what pg_kpart keeps you from losing.

Building and enabling the extension

Building pg_kpart requires the PostgreSQL server development files, with pg_config on your PATH:

make
make install      # may need sudo

The functional part is a planner hook installed when the library is loaded, so the library must be preloaded. Load it cluster-wide in postgresql.conf (this needs a restart):

shared_preload_libraries = 'pg_kpart'

Or load it per session or per database without restarting the server:

session_preload_libraries = 'pg_kpart'

-- or scope it to a single database:
ALTER DATABASE mydb SET session_preload_libraries = 'pg_kpart';

Running CREATE EXTENSION pg_kpart; is optional — it simply registers the extension in pg_catalog.pg_extension. The protection itself comes from the preloaded hook. pg_kpart has been tested on PostgreSQL 13 and later.

Configuration

pg_kpart is controlled by the following GUCs:

GUC	Default	Description
pg_kpart.enabled	on	Master switch for the check.
pg_kpart.message_level	error	Severity of a violation: error, warning, notice, log, and so on. Use warning to audit before enforcing.
pg_kpart.min_partitions	2	Only check tables with at least this many leaf partitions.
pg_kpart.check_superuser	off	When off, superusers bypass the check. Set to on to hold them to the same rule.
pg_kpart.blacklisted	(empty)	Comma-separated list of partitioned tables the check applies to (and their sub-partitions). When set, only these tables are checked.
pg_kpart.whitelisted	(empty)	Comma-separated list of partitioned tables exempt from the check (and their sub-partitions).

Audit mode for a safe rollout

A practical way to introduce pg_kpart on an existing system is to start in audit mode — set the message level to warning so offending queries still run but are logged. Once you have flushed out and fixed the bad queries, switch back to error to enforce:

-- roll out in audit mode first
ALTER SYSTEM SET pg_kpart.message_level = 'warning';
SELECT pg_reload_conf();

Scoping the check to specific tables

By default — with both lists empty — every partitioned table is checked. You can narrow or widen that scope with two complementary lists:

• pg_kpart.blacklisted restricts the check to only the tables you name. Use this to protect just your largest or most critical partitioned tables and leave everything else untouched.
• pg_kpart.whitelisted does the opposite: it exempts the tables you name and keeps the check active on all the others.

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

-- alternatively: police everything except a few audit tables
ALTER SYSTEM SET pg_kpart.whitelisted = 'public.audit_log';
SELECT pg_reload_conf();

A few rules worth keeping in mind:

• The blacklist wins. If pg_kpart.blacklisted is set, pg_kpart.whitelisted is ignored entirely — only the blacklisted tables are checked.
• Names may be schema-qualified (schema.table). An unqualified name is resolved through the current search_path.
• Listings cover the whole hierarchy. Naming a partitioned table also covers any sub-partitioned tables beneath it, so a sub-partition queried directly is matched whenever one of its ancestors is listed. Membership is decided from the partitioned table referenced in the query.

By default a superuser is not subject to the restriction, which keeps maintenance, migrations, and ad-hoc administrative work from being blocked. If you would rather hold superusers to the same standard, set pg_kpart.check_superuser = on.

Notes and limitations

• A predicate that happens to match all partitions — for example logdate > '1900-01-01' — is treated as a full scan and rejected, because in practice that is exactly what it is.
• The check covers UPDATE and DELETE as well as SELECT, and also EXPLAIN without ANALYZE, since all of these go through the planner.
• pg_kpart has been tested on PostgreSQL 13 and later.

Conclusion

Partitioning is one of the most effective tools PostgreSQL gives you for handling very large tables — but only if queries actually use the partition key. The moment they stop doing so, partitioning turns from an asset into a liability, and a single bad query can saturate your I/O and degrade the entire server.

pg_kpart closes that gap. It turns "please always filter on the partition key" from a fragile convention that depends on every developer remembering it into a guarantee enforced by the database itself. With an audit mode to ease the rollout, blacklist/whitelist scoping to target exactly the tables that matter, and a dedicated SQLSTATE so applications can react to violations cleanly, it slots neatly into a production workflow. If you run large partitioned tables and you would rather not be at the mercy of the next query that forgets the partition key, this extension is a genuine must-have.

The code is available at github.com/darold/pg_kpart, released under the PostgreSQL License.

---

Need help designing, tuning, or securing partitioning on your PostgreSQL clusters? Reach out to HexaCluster for personalized expertise to elevate your database performance and meet your business needs. Subscribe to our newsletter and stay tuned for more deep dives into PostgreSQL.

About the author — Gilles Darold is the CTO of HexaCluster and one of the major PostgreSQL contributors. He is the creator of Ora2Pg, pgBadger, and many other popular PostgreSQL tools and extensions.