Re: Partial indexes on system catalogs

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2026-10-01 18:49:21 -0300, Manu wrote:
>> In the ATTACH PARTITION thread [1] Álvaro suggested exploring partial
>> indexes on system catalogs. The open question there was how to evaluate
>> the predicate during catalog maintenance without running the full
>> executor, while still representing the restriction in the catalogs.

> Is the gain from that really substantial enough to warrant introducing this?

Yeah, I'm skeptical of that too, especially if the answer to "we can't
allow arbitrary code to execute during catalog updates" is to restrict
the set of allowed predicates to a tiny number. Then we don't have
partial indexes, just a hack with a small number of potential use-cases.

The fact of the matter is that if you just need to do pg_constraint
lookups by confrelid, you could simply add a non-unique index on that
column, paralleling the one on contypid (for which we already accepted
that there'd be a bunch of useless zero entries at one end of the
index).

More generally, the real problem here is that pg_constraint is
misdesigned and in need of a refactoring. I could imagine doing
something like

(a) have a "core" catalog that stores the OID, name/namespace, and contype
of each constraint, and maybe a few other fields if we don't feel like
implementing this breakout idea fully. OID is constrained unique, the
name/namespace have a nonunique index.

(b) for each contype, have a breakout catalog that has the OID and the
columns needed for that contype. This would be pretty analogous
to the way that pg_aggregate extends pg_proc for aggregate functions.
We could put unique constraints on the breakout catalogs for each
uniqueness property we want, at the cost that we'd likely have to
duplicate conname into each such catalog (but I suspect we'd choose
to do that anyway). For example, "domain constraint names are
unique per-domain" could be enforced by a unique index on (contypid,
conname) in a breakout index for type-related constraints.

(c) for backwards compatibility, make a view pg_constraint on these
catalogs to avoid breaking what clients see.

This is pretty handwavy; in particular maybe the breakouts should
be designed along some other principle than "what's the contype".
But I would rather go in some such direction than implement
something as messy as partial indexes just to keep propping up a
poor catalog design.

regards, tom lane