Re: pg_partman 3.0.0 - real-world usage of nativepartitioning and a case for native default - Mailing list pgsql-hackers

On Fri, Mar 31, 2017 at 9:00 PM, Keith Fiske <keith@omniti.com> wrote:

I've gotten pg_partman working with native partitioning already so I can hopefully have things ready to work when 10 is released. I've got a branch on github with this version for anyone to test and I'll hopefully have this released in the next few weeks after I finish some more testing myself. Any feedback would be appreciated!

https://github.com/keithf4/pg_partman/tree/v3.0.0

There's already a proposal to support default partition as [1]. That proposal talks about default partition in list partitioned tables. For range partitioned tables, we expect that a single partition with unbounded bounds would serve as default partition.

 

Thankfully since native partitioning still uses inheritance internally for the most part, pg_partman works pretty well without nearly as much change as I thought I would need. The biggest deficiency I'm seeing has to do with not having a "default" partition to put data that doesn't match any children. The fact that it throws an error is a concern, but it's not where I see the main problem. Where this really comes into play is when someone wants to make an existing table into a partitioned table. There's really no easy way to do this outside of making a completely brand new partition set and copying/moving the data from the old to the new.

If there are multiple partitions, there is likely to be more data that needs to be moved that is retained in the old table. So, creating complete brand new partitioning and copying/moving data is annoying but not as much as it sounds. Obviously, if we could avoid it, we should try to.

 

Yes there are technically ways to do this fairly seamlessly to the user of the partitioned table, but the complexity of those methods seems absurd in the face of just allowing a default partition.

A default would basically allow a method similar to what pg_partman does prior to native. Before I would just make the old table the parent and then the user could move data to the children as needed, eventually leaving the parent empty. All data is still accessible during this period and new writes go to the new children. The old table can't be made the parent with native obviously, but being able to define a default partition would allow defining the old table as the default and pretty much give the exact same, easy migration path. Without defining it as a default, you really can't attach the old table since it would require a constraint interval that would likely interfere with other children.

Yes, I agree that default partition would make it easy to migrate the data. But, I think, we need ability to SPLIT the partition along-with default partitioning to convert an unpartitioned table to a partitioned one. We don't have a SPLIT partition support right now.

 

I recall reading before that there are performance implications of having the default. I think as long as those performance issues are clearly documented and have no affect if there is no default, it shouldn't be a concern that would hold this feature up. I believe the benefit of the default partition to the migration process makes it more than worth it and I'm hoping it's possible to get into 10 so users can more easily use this new feature without having to wait for the next major version. Spoke with several core members at PGConf this year and they asked me to send an email to hackers making my case, so here it is!

I have not followed that mail thread closely, but it will help if you (if interested) review/update the patch on thread and help the patch to get into a committable state.

[1] https://www.postgresql.org/message-id/CAH2L28tbN4SYyhS7YV1YBWcitkqbhSWfQCy0G=apRcC_PEO-bg@mail.gmail.com
--

Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company