From: John Scalia <jayknowsunix@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Technical guidance for a large partition table
Date: Sun, 9 May 2021 19:18:38 -0400
Message-ID: <EF56C0B6-A15B-4784-817F-60627BE46B96@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com
Subject: Re: Technical guidance for a large partition table
In-Reply-To: <EF56C0B6-A15B-4784-817F-60627BE46B96@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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