Caching a partition index's parent OID in the relcache?

Hello,

On 2026-Jul-17, Ayush Tiwari wrote:

While poking at INSERT ... ON CONFLICT on partitioned tables,
get_partition_ancestors() kept showing up in profiles, it scans
pg_inherits once per hierarchy level, and ExecInitPartitionInfo() calls
it once per leaf-partition index while sorting out arbiter indexes.
There's already an XXX in that loop wondering about a syscache "or some
other way to cache".

Yeah. We didn't measure the actual performance impact of that code (see
commit e6d6e32f4240) but it's obviously not great.

I tried the "other way": keep the immediate parent OID on the relcache
entry (a lazily-filled rd_partparent), then use the existing walker above
that. This avoids repeatedly scanning the leaf link and eliminates the
pg_inherits scan entirely for the common one-level hierarchy. Patch
attached (fairly small).

Hmm. Why not cache the entire list of ancestors instead of just the
immediate one? You could have a union that's either a single
OID (for the most common case where there's only one ancestor), or a
pointer to an array of an arbitrary number of ancestors. With such a
system, you only have to scan pg_inherits for a relation once per
invalidation, regardless of the number of ancestors.

(Looking at pahole's output for RelationData it's obvious that nobody
cares too much about how much memory that struct takes.)

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Hay dos momentos en la vida de un hombre en los que no debería
especular: cuando puede permitírselo y cuando no puede" (Mark Twain)

Hi,

On Fri, 17 Jul 2026 at 19:38, Álvaro Herrera <alvherre@kurilemu.de> wrote:

Hello,

On 2026-Jul-17, Ayush Tiwari wrote:

I tried the "other way": keep the immediate parent OID on the relcache
entry (a lazily-filled rd_partparent), then use the existing walker above
that. This avoids repeatedly scanning the leaf link and eliminates the
pg_inherits scan entirely for the common one-level hierarchy. Patch
attached (fairly small).

Hmm. Why not cache the entire list of ancestors instead of just the
immediate one? You could have a union that's either a single
OID (for the most common case where there's only one ancestor), or a
pointer to an array of an arbitrary number of ancestors. With such a
system, you only have to scan pg_inherits for a relation once per
invalidation, regardless of the number of ancestors.

(Looking at pahole's output for RelationData it's obvious that nobody
cares too much about how much memory that struct takes.)

The union is fine, but I don't think a full list cached on the index has
a valid invalidation. AFAICT it depends on the *ancestors'* links, and
re-parenting an intermediate index doesn't invalidate the leaf:
pg_inherits changes send no relcache inval (only
pg_class/pg_attribute/pg_index/pg_constraint do), and IndexSetParentIndex
flips only the re-parented index's own pg_class row.

-- 3-level r > m > l; warm l's index cache
INSERT INTO r VALUES (1) ON CONFLICT (i) DO NOTHING;
NOTICE: FILL l_pkey ancestors=[m_pkey r_pkey]
ALTER TABLE r DETACH PARTITION m;
ALTER TABLE r2 ATTACH PARTITION m ...; -- m_pkey now under r2_pkey
INSERT INTO r2 VALUES (2) ON CONFLICT (i) DO NOTHING;
NOTICE: HIT l_pkey cached=[m_pkey r_pkey] live=[m_pkey r2_pkey] <-STALE

l_pkey is never invalidated, so the cached list keeps r_pkey and
ExecInitPartitionInfo() would pick the wrong arbiter. The
immediate-parent cache dodges this: it stores only the leaf's own parent
(invalidated when it changes) and walks the rest live.

To make a full list safe we'd need a new invalidation path that, on any
index re-parent, invalidates the whole descendant-index subtree rather
than just the re-parented index, and it only helps 3+ level
hierarchies. Should I explore that?

Regards,
Ayush

On 2026-Jul-17, Ayush Tiwari wrote:

-- 3-level r > m > l; warm l's index cache
INSERT INTO r VALUES (1) ON CONFLICT (i) DO NOTHING;
NOTICE: FILL l_pkey ancestors=[m_pkey r_pkey]
ALTER TABLE r DETACH PARTITION m;
ALTER TABLE r2 ATTACH PARTITION m ...; -- m_pkey now under r2_pkey
INSERT INTO r2 VALUES (2) ON CONFLICT (i) DO NOTHING;
NOTICE: HIT l_pkey cached=[m_pkey r_pkey] live=[m_pkey r2_pkey] <-STALE

l_pkey is never invalidated, so the cached list keeps r_pkey and
ExecInitPartitionInfo() would pick the wrong arbiter. The
immediate-parent cache dodges this: it stores only the leaf's own parent
(invalidated when it changes) and walks the rest live.

I see.

To make a full list safe we'd need a new invalidation path that, on any
index re-parent, invalidates the whole descendant-index subtree rather
than just the re-parented index, and it only helps 3+ level
hierarchies. Should I explore that?

I think we should do that. Reparenting an index is a very uncommon
operation, so it doesn't matter if it emits several invalidation
messages. On the other hand, if we limit the caching to just the
immediate index, then we have good performance for the easy case of a
single partitioning level, but everyone using more than that will have
to pay a performance cost (on every INSERT ON CONFLICT) that's not
easily visible.

Thanks!

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

Hi,

On Fri, 17 Jul 2026 at 22:19, Álvaro Herrera <alvherre@kurilemu.de> wrote:

On 2026-Jul-17, Ayush Tiwari wrote:

To make a full list safe we'd need a new invalidation path that, on any
index re-parent, invalidates the whole descendant-index subtree rather
than just the re-parented index, and it only helps 3+ level
hierarchies. Should I explore that?

I think we should do that. Reparenting an index is a very uncommon
operation, so it doesn't matter if it emits several invalidation
messages. On the other hand, if we limit the caching to just the
immediate index, then we have good performance for the easy case of a
single partitioning level, but everyone using more than that will have
to pay a performance cost (on every INSERT ON CONFLICT) that's not
easily visible.

Thanks a lot for the direction and help.

v2 attached. It caches the full ancestor list on the index's relcache
entry using the single-OID-or-array union you suggested, and builds a
fresh list from it on each call.

For the invalidation we discussed, I had IndexSetParentIndex() invalidate
the whole descendant-index subtree via find_all_inheritors(), so a
re-parent can't leave a leaf's cached list stale. Does that feel like the
right place and granularity to you? And is NoLock there OK, given
ATTACH/DETACH already hold AccessExclusiveLock over the subtree, or is
there a path where that doesn't hold?

On performance I used pgbench. The test is INSERT ... ON CONFLICT (i) DO
NOTHING into a 64-leaf range-partitioned table with all keys pre-populated,
so
every statement goes through the arbiter mapping; pgbench -M prepared -c 8,
median of three 15s runs. The arbiter loop runs once per index on the
leaf, so the gain grows with the number of indexes per partition:

indexes/leaf master v2
4 34553 tps 40232 tps (+16%)
8 34128 tps 40246 tps (+18%)
15 29906 tps 39283 tps (+31%)

Looking forward to reviews.

Regards,
Ayush

Hello!

Have no cycles at the moment to dive into the patch, just for threads
interconnection - [0]/messages/by-id/CADzfLwWZjWqeX6fF5=iKq_PJiw7G+k01CBu5xB8X_Z+nN1gqqA@mail.gmail.com.

Best regards,
Mikhail.

[0]: /messages/by-id/CADzfLwWZjWqeX6fF5=iKq_PJiw7G+k01CBu5xB8X_Z+nN1gqqA@mail.gmail.com
/messages/by-id/CADzfLwWZjWqeX6fF5=iKq_PJiw7G+k01CBu5xB8X_Z+nN1gqqA@mail.gmail.com

Hi,

On Sat, 18 Jul 2026 at 01:57, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

v2 attached. It caches the full ancestor list on the index's relcache
entry using the single-OID-or-array union you suggested, and builds a
fresh list from it on each call.

For the invalidation we discussed, I had IndexSetParentIndex() invalidate
the whole descendant-index subtree via find_all_inheritors(), so a
re-parent can't leave a leaf's cached list stale. Does that feel like the
right place and granularity to you? And is NoLock there OK, given
ATTACH/DETACH already hold AccessExclusiveLock over the subtree, or is
there a path where that doesn't hold?

On performance I used pgbench. The test is INSERT ... ON CONFLICT (i) DO
NOTHING into a 64-leaf range-partitioned table with all keys
pre-populated, so
every statement goes through the arbiter mapping; pgbench -M prepared -c 8,
median of three 15s runs. The arbiter loop runs once per index on the
leaf, so the gain grows with the number of indexes per partition:

indexes/leaf master v2
4 34553 tps 40232 tps (+16%)
8 34128 tps 40246 tps (+18%)
15 29906 tps 39283 tps (+31%)

Looking forward to reviews.

More benchmarking, mostly the multi-level case that was open with v2, plus
the
ATTACH/DETACH cost and a profile. This was on an Azure VM (AMD EPYC 7763,
8 vCPU, 32 GB), with master c90c9678e53 vs v2, gcc 13.3 -O2, no asserts.

For the TPS tests, I used INSERT ... ON CONFLICT (i) DO NOTHING with all
keys
pre-populated, pgbench -M prepared -c1, and pinned the backend and pgbench
to
separate physical cores. The figures are means +/- 95% CI from five 30s
runs.

Indexes on the leaf (single level):

indexes/leaf master patched
4 14296 +/- 128 17244 +/- 354
8 11781 +/- 113 16635 +/- 138
15 8935 +/- 218 15725 +/- 258

Depth instead (single chain to one leaf, 8 indexes):

levels master patched
1 12179 +/- 59 17008 +/- 145
2 10453 +/- 30 16723 +/- 336
3 9380 +/- 44 16660 +/- 68
4 8442 +/- 17 16359 +/- 204

v2 stays roughly flat while master slides down as the ancestor walk grows.

perf of one backend looping INSERT ... ON CONFLICT with a leaf index having
four ancestors (20s at 999 Hz; perf record --call-graph dwarf; perf report
--children):

master:
Children Self Symbol
91.99% 0.05% ExecInsert
90.08% 0.54% ExecFindPartition
84.67% 0.17% get_partition_ancestors
80.97% 0.34% get_partition_parent_worker
23.79% 0.84% systable_beginscan

v2:
Children Self Symbol
48.16% 0.31% ExecInsert
34.07% 1.63% ExecFindPartition
1.49% 0.42% get_partition_index_ancestors

Separately, I measured ATTACH/DETACH of an empty 16-index partition with K
leaf
subpartitions. One cycle is ATTACH followed by DETACH; these are mean
latencies
from five 10s runs:

leaves K master patched
1 1.92 ms 2.14 ms
64 3.85 ms 4.79 ms
256 9.94 ms 30.77 ms

The added cost grows with the descendant count. IndexSetParentIndex() walks
the
descendant index subtree once per re-parented index, but re-parenting is
rare,
so I think the absolute cost is an acceptable trade.

Regards,
Ayush