Is REPLICA IDENTITY inherited in non-native partitioning (PG13)

[fbattke]

We are using pg_partman's non-native partitioning on postgres 13 and need to specify a custom REPLICA IDENTITY USING INDEX for the partitions, based on a UNIQUE CONSTRAINT on the parent table.

The UNIQUE CONSTRAINT is inherited by the children, but I can find no mention if the REPLICA IDENTITY is also inherited (is it part of the constraint definition?) or if I need a custom maintenance job to confiure the REPLICA IDENTITY for newly generated partitions.

Sorry if I missed an obvious place to look for an answer.

[keithf4]

For non-native partitioning, I have not made a specific inheritance for replica identity, so it likely is not. I'm currently working on phasing out non-native partitioning support in pg_partman, so I likely will not investigate adding that for non-native.

As part of the next release development, I'll test and see if native partitioning is doing replica identity inheritance. If it's not, I'll see what can be done.

[keithf4]

Is there a specific reason you are using non-native partitioning?

[fbattke]

Thank you for the quick reply!

We are using non-native because we are coming from a postgres 9.2 setup that we have migrated to pg 13. The partitioned table is in the terabyte range and needed in everyday production.

I have seen your migration doc but we have not yet investigated the upgrade path in detail. We are creating new partitions ahead of time with pg_partman so I can easily write a cron job that sets the replica identity on new partitions before they are used.

If my memory is correct I once saw a comparison between native and non-native that contained one reason why we didn't want to change when upgrading to pg13, can't find it now, though.

[fbattke]

Perhaps alternatively I can promote an existing unique index to a primary key constraint on the parent?
ALTER TABLE part_parent ADD CONSTRAINT part_pkey PRIMARY KEY USING INDEX ...
Would that be applied to new partitions automatically?

[keithf4]

From what I'm reading in the docs, a primary key is always, automatically a REPLICA IDENTITY. Since there can be multiple unique keys, it doesn't know which one you want to be the IDENTITY so in that case you need to tell it which one. So that could solve the issue for you if you're able to set one.

For non-native partitioning, that should automatically apply that primary key to the children if you set it on the parent. That may not be the case with native partitioning, though, so keep that in mind. You'll likely have to use the template table in that case.

The question as to why you were still using non-native was just to see if there was some use-case I hadn't come across why it still may be valid. I would definitely recommend looking at migrating to native at some point. The performance benefits are tremendous. At this point I haven't come across any reasons to stay on non-native myself so it's why I'm looking at deprecating it.

[fbattke]

I'm interested in learning more about the performance benefits. Over the years, we have made sure that wherever we know which partition an INSERT would be directed to, we directly insert into that partition. And with constraint exclusion, SELECTs seem to be pretty well optimized. Would we still expect performance gains by switching to native? Basically, in our case, the partitions are only used to reduce the size of indexes and be able to phase out old data more efficiently.

And thank you so much for developing pg_partman!

[keithf4]

If you are directly inserting to the child partitions, then there is no benefit either way because you're entirely by passing all tuple routing anyway.

Version 12+ actually make use of partition pruning, not just constraint exclusion. Constraint exclusion still has to at least visit every child table to see if the constraint excludes the data before it can skip over it. With native partitioning and pruning, PG is able to simply eliminate the child partitions up front without having to visit each child table (might not be exactly what it's doing, but it's something close to that). If you have less than 100 partitions or so, it's not going to be a big difference there. But when you start getting into the higher child table numbers, that can make a notable difference in read planning time.