Re: pg_partman 3.0.0 - real-world usage of native partitioning and a case for native default

On Mon, Apr 3, 2017 at 10:45 PM, Keith Fiske <keith(at)omniti(dot)com> wrote:

>
> On Mon, Apr 3, 2017 at 5:13 AM, Ashutosh Bapat <
> ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>
>>
>>
>> On Fri, Mar 31, 2017 at 9:00 PM, Keith Fiske <keith(at)omniti(dot)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(at)keith=# create table testing_range (id int, created_at timestamptz)
> partition by range (created_at);
> CREATE TABLE
> Time: 41.987 ms
>
> keith(at)keith=# create table testing_range_default partition of
> testing_range for values from (unbounded) to (unbounded);
> CREATE TABLE
> Time: 8.625 ms
>
> keith(at)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
>
>

Hmm, looks like default partition for range would be helpful 1. in these
case and 2. to hold data in the holes in the existing partitioning scheme.

>
>>
>>>
>>>
>>> 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.
>

In partitioning, partitioned table doesn't have any storage. Data that
belongs to a given partition is expected to be there from day one.

>
> 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.
>

Thanks.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company