RFC: A constraint-enforcement layer, decoupling cross-partition constraints from indexing - Mailing list pgsql-hackers

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-partitionindex can only police uniqueness inside its own partition. The most-wanted feature this blocks is a
cross-partitionunique 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_partitionscatalog, planner paths for the partitioned relation, and (currently) locking the partition hierarchy
duringDML. 
 * Postgres Pro's shipped gbtree AM [6][7] - a non-MVCC global structure carrying the PK as INCLUDE-style columns, with
hash-bucketlocking 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,
exposeplanner paths, and be vacuumed. That is a large surface, and I think it's why the feature has been "almost there"
forthe 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
everypartition, and for uniqueness the conflict is found by probing one structure, after which only the single owning
partitionneeds touching. In other words, enforcing a cross-partition constraint is a strictly smaller problem than
maintaininga cross-partition index for reads. A unique constraint needs only: "does this key already exist anywhere,
andif so where, transactionally?" It does not need ordered range scans, planner paths, or visibility-aware read access
forarbitrary 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),
analogousto but distinct from the index AM. The planner/executor consult the CM before the row reaches the table AM and
indexAMs, 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'ssupported 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
questionand 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
indexinserts. Conceptually this is a global membership map (key -> owning-partition + heap TID) rather than a global
secondaryindex. 

The win is that Tier 2 lets us defer the hard parts the full-index proposals struggle with. Because the enforcer is
consultedat the executor level (not from inside index AM code), we know which single partition to lock at exactly the
momentwe have a candidate conflict — addressing the "we only discover the partition while inside the AM" objection
raisedin 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,
smallenough 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
(mirroringhow 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
forfree; 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
constraintmanagement. 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
systemwhich 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"
modeof 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
dedicatedpredicate-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,
especiallyon whether this should be folded into the existing global-index effort rather than standing alongside it,
verywelcome. 

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