pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTechnical guidance for a large partition table
4+ messages / 3 participants
[nested] [flat]
* Technical guidance for a large partition table
@ 2021-05-09 23:18 John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: John Scalia @ 2021-05-09 23:18 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Technical guidance for a large partition table
@ 2021-05-10 00:44 Keith <keith@keithf4.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Keith @ 2021-05-10 00:44 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Sun, May 9, 2021 at 7:18 PM John Scalia <jayknowsunix@gmail.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Technical guidance for a large partition table
@ 2021-05-10 01:14 Keith <keith@keithf4.com>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 4+ messages in thread
From: Keith @ 2021-05-10 01:14 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
On Sun, May 9, 2021 at 8:49 PM John Scalia <jayknowsunix@gmail.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-partitioni...
Keith
> On May 9, 2021, at 8:44 PM, Keith <keith@keithf4.com> wrote:
>
>
>
>
> On Sun, May 9, 2021 at 7:18 PM John Scalia <jayknowsunix@gmail.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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Technical guidance for a large partition table
@ 2021-05-10 05:45 Laurenz Albe <laurenz.albe@cybertec.at>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2021-05-10 05:45 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
On Sun, 2021-05-09 at 19:18 -0400, John Scalia wrote:
> 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.
> [...]
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-05-10 05:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-09 23:18 Technical guidance for a large partition table John Scalia <jayknowsunix@gmail.com>
2021-05-10 00:44 ` Keith <keith@keithf4.com>
2021-05-10 01:14 ` Keith <keith@keithf4.com>
2021-05-10 05:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox