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

On Mon, Apr 3, 2017 at 11:33 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

>
>
>>>
>>>>
>>>>
>>>> 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.
>
>
>> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

I understand that with native, the parent has no data and never will. I'm
just using pg_partman's method as an example. The DEFAULT would take the
place of the parent table in this situation. Yes, in an ideal world, all
the data would be in the children right from the beginning when you declare
the parent. But that hardly seems realistic, especially when you have to
partition an existing billion+ row table and keep it running at the same
time. Yes, the basic purpose of the default is to catch data that gets
inserted outside the current child existence. That's what I also do with
the parent in pg_partman. But it can also serve as a method to ease
migration, as the parent also does in pg_partman's trigger-based method.

Keith