RFC: A constraint-enforcement layer, decoupling cross-partition constraints from indexing

Hello hackers,

I'd like to float an idea and gauge appetite for exploring it. This is a request for comments, not a patch.

The problem, stated narrowly:

A UNIQUE or PRIMARY KEY constraint on a partitioned table must today include every partition-key column, because each per-partition index can only police uniqueness inside its own partition. The most-wanted feature this blocks is a cross-partition unique constraint on a non-partition-key column (e.g. partition by created_at, but keep email globally unique).

Prior art (and what each pays for it)
* The 2019 "Proposal: Global Index" [1] - a single parent-level index over storage-less parents.
* The 2022 HighGo "Global Unique Index" POC [2][3][4] - RELKIND_GLOBAL_INDEX, a merge-sort across all partitions at build, and on every write a probe into other partitions' indexes.
* Dilip Kumar's ongoing "Global Index" patch set [5] - a partition identifier embedded in each index tuple, a pg_index_partitions catalog, planner paths for the partitioned relation, and (currently) locking the partition hierarchy during DML.
* Postgres Pro's shipped gbtree AM [6][7] - a non-MVCC global structure carrying the PK as INCLUDE-style columns, with hash-bucket locking for uniqueness; reportedly ~1.6–1.7× slower writes with 100 partitions.

These are all full global indexes: they have to serve index scans, carry per-row entries with visibility semantics, expose planner paths, and be vacuumed. That is a large surface, and I think it's why the feature has been "almost there" for the better part of a decade.

The observation:

In the recent thread [8][9], the discussion already split the problem in two: for SELECTs we'd like to avoid locking every partition, and for uniqueness the conflict is found by probing one structure, after which only the single owning partition needs touching. In other words, enforcing a cross-partition constraint is a strictly smaller problem than maintaining a cross-partition index for reads. A unique constraint needs only: "does this key already exist anywhere, and if so where, transactionally?" It does not need ordered range scans, planner paths, or visibility-aware read access for arbitrary queries.

The proposal: a Constraint Management layer (two-tier)

Formalize constraint enforcement as its own abstraction, separate from indexing - call it a constraint method (CM), analogous to but distinct from the index AM. The planner/executor consult the CM before the row reaches the table AM and index AMs, or in cases where we are using the index for constraint enforcement as we do now.

Tier 1 - reuse an index when one already does the job. When the constraint's columns include the partition key (today's supported case) the CM simply delegates to existing local-index.

Tier 2 - a purpose-built enforcer when full indexing is overkill. For cross-partition uniqueness on a non-key column, register a lightweight enforcement structure that maintains just enough logged state to answer the membership question and locate the one conflicting partition. It is keyed only by the constraint columns; it stores no per-row scan payload, exposes no planner path, and is checked/updated from an ExecInsert/Update/Delete hook ahead of the heap and index inserts. Conceptually this is a global membership map (key -> owning-partition + heap TID) rather than a global secondary index.

The win is that Tier 2 lets us defer the hard parts the full-index proposals struggle with. Because the enforcer is consulted at the executor level (not from inside index AM code), we know which single partition to lock at exactly the moment we have a candidate conflict — addressing the "we only discover the partition while inside the AM" objection raised in the current thread. And because it carries no MVCC scan data, it sidesteps much of the vacuum/bloat conversation.

What it might look like in code:

A pg_constraint_method catalog and a CM descriptor with a handful of callbacks, roughly:
cm_check(values, snapshot) -> {ok | conflict(partition, tid)}
cm_insert(values, partition, tid, xid) / cm_delete(...)
cm_build(partitioned_rel) for initial population
cm_vacuum(...) / WAL redo for the enforcer's own state.

A reference Tier-2 enforcer: a WAL-logged, MVCC-aware ordered/hash relation keyed on the constraint columns, holding (partition_id, heap_tid, xmin/xmax) — large enough to enforce and to resolve the visibility of a would-be conflict, small enough to avoid being a second copy of the data.

Executor wiring: ExecInsert/ExecUpdate call cm_check after forming the tuple but before/around the heap insert (mirroring how speculative insertion + ON CONFLICT already work), locking only the partition returned by a positive cm_check.

Recovery: the enforcer is just another WAL-logged relation/fork, so crash recovery and physical replication come along for free; logical replication and ON CONFLICT are explicit follow-ups.

Why I think this is worth exploring over "just finish the global index":

The full-global-index patches are valuable and I'm not proposing to abandon them - Tier 1 can adopt them when they land, but the motivation for global indexes should be a requirement for index scans across partitioned tables, not constraint management. Based on what I understand, the real demand is for the constraint enforcement over partitioned tables, not the index scan. A CM layer lets us ship cross-partition uniqueness (and cross-partition exclusion constraints, partition-spanning FKs) sooner, with a smaller and more reviewable surface providing a "global" constraints system which should enable a wider adoption of partitioned tables.

Open questions I'd like input on:

- Is a separate constraint-method abstraction warranted, or should this be modeled as a degenerate "constraint-only" mode of the proposed global index (no read paths)?

- Concurrency: is a speculative-insertion-style protocol against the Tier-2 structure sufficient, or do we need a dedicated predicate-locking scheme for SSI correctness?

- Catalog and DDL: how should ALTER TABLE … ADD CONSTRAINT choose Tier 1 vs Tier 2, and should users be able to force it?

- Does deferring partition locks to executor-level CM checks interact badly with prepared plans / AcquireExecutorLocks?

- Is there appetite for a minimal prototype (uniqueness only, no FK/exclusion/etc.) as a discussion vehicle/proof-of-concept?

I'm happy to put together a WIP prototype of the uniqueness enforcer if there's interest in the direction. Feedback, especially on whether this should be folded into the existing global-index effort rather than standing alongside it, very welcome.

best,

-greg

[1] https://www.postgresql.org/message-id/CALtqXTcurqy1PKXzP9XO%3DofLLA5wBSo77BnUnYVEZpmcA3V0ag%40mail.gmail.com
[2] https://www.postgresql.org/message-id/184879c5306.12490ea581628934.7312528450011769010%40highgo.ca
[3] https://www.highgo.ca/2022/10/28/cross-partition-uniqueness-guarantee-with-global-unique-index/
[4] https://www.highgo.ca/2022/12/16/global-unique-index-attach-support-and-its-potential-deficiency/
[5] https://www.postgresql.org/message-id/CA%2BHiwqG%2BEizej9nCNcxSOfFyZ2i9Mhv%3Dzn%2Ba%2B4o-gwsvFz6EqQ%40mail.gmail.com
[6] https://postgrespro.com/docs/enterprise/current/pgpro-gbtree
[7] https://habr.com/en/companies/postgrespro/articles/948428/
[8] https://www.postgresql.org/message-id/CA%2BHiwqG%2BEizej9nCNcxSOfFyZ2i9Mhv%3Dzn%2Ba%2B4o-gwsvFz6EqQ%40mail.gmail.com
[9] https://www.postgresql.org/message-id/CAFiTN-tu1f0TL4C1CgRzBYkTrrhcYscc7Nz_LJ3xJDOZJGA6kA%40mail.gmail.com