Re: Technical guidance for a large partition table

On Sun, May 9, 2021 at 8:49 PM John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> I’m planning on using the built-in method, and it worked very well in all
> the non-production environments, but none of those had an API updating
> these tables. Each table will be using a list of string values to decide
> which partition to use, so no integer operations will be used.
>
> Sent from my iPad
>
>

Ok, pg_partman does not support string-based partitioning at this time.
However, the document on using the old table as the default to attempt live
partitioning may help provide some guidance to a method that could work for
you. It's not doing anything proprietary and you can view the source to see
all the commands that pg_partman uses in the background with its creation
and data migration functions.

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#online-partitioning

Keith

> On May 9, 2021, at 8:44 PM, Keith <keith(at)keithf4(dot)com> wrote:
>
> 
>
>
> On Sun, May 9, 2021 at 7:18 PM John Scalia <jayknowsunix(at)gmail(dot)com> wrote:
>
>> Hi all,
>>
>> I have a dev team requesting that I partition 3 very large tables with
>> more 2 billion rows each. I’ve already got the partition key decided for
>> each with a script which will create a copy of original with the partition
>> declaration, along with the partition child tables. The problem will be
>> that each table has an API which updates each table fairly regularly, and
>> the devs say it cannot be turned off. So, I’m looking for the best way to
>> do this so that the partitioned version will match the original table. FYI,
>> this is production instance that is regularly updated by field technicians.
>> Quite literally, I cannot perform two sequential select count(*) from any
>> of these tables which will return the same row counts. I’m thinking of
>> installing a trigger on insert on the original tables, which would write
>> any insert to a third table, then copy the original table to another table
>> name, do the partitioning on it to yet another table name, move the
>> partition table into the original table name, kill the trigger, then apply
>> the updates from the table where the insert trigger was being applied. Is
>> there a better way to do this, or am I missing something?
>> —
>> Jay
>>
>> Sent from my iPad
>>
>>
> Are you planning on using the built in, native partitioning or using the
> old method combining trigger, constraints and inheritance? That should be
> the first thing that's decided since it will greatly influence the method
> you use. I'd highly recommend going with the native, built-in if at all
> possible since it has significantly better write performance and better
> read performance on PG12+.
>
> If you're using time or integer based partitioning, the pg_partman
> extension has a lot of utilities and methods to help manage and migrate to
> a partitioned table. Check out the howto documentation for some examples of
> doing the kind of live migrations similar to what you may be looking for.
>
> https://github.com/pgpartman/pg_partman/tree/master/doc
>
> Keith
> @keithf4
>
>