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

On Mon, Apr 3, 2017 at 5:13 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:

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.

This would not work. The completely unbounded partition would overlap all other possible partitions. How would it decide which child table to put data in? Looks like this is stopped right from the start anyway.

keith@keith=# create table testing_range (id int, created_at timestamptz) partition by range (created_at);
CREATE TABLE
Time: 41.987 ms

keith@keith=# create table testing_range_default partition of testing_range for values from (unbounded) to (unbounded);
CREATE TABLE
Time: 8.625 ms

keith@keith=# create table testing_range_p2017_04 partition of testing_range for values from ('2017-04-01 00:00:00') to ('2017-05-01 00:00:00');
ERROR:  partition "testing_range_p2017_04" would overlap partition "testing_range_default"
Time: 4.516 ms
 

 

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.

Not sure I follow what you're saying here. With pg_partman, making the old table the parent and still containing all the data has caused no issues when I've migrated clients to it, nor has anyone reported any issues to me with this system. New data goes to the child tables as they should and old data is then moved when convenient. It makes things work very smoothly and the only outage encountered is a brief lock at creation time.

I've been watching that thread as well and as soon as a fix is posted about the latest concerns, I'll gladly look into reviewing it.