Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: John Scalia <jayknowsunix@gmail.com>
Mime-Version: 1.0 (1.0)
Date: Sun, 9 May 2021 19:18:38 -0400
Subject: Technical guidance for a large partition table 
Message-Id: <EF56C0B6-A15B-4784-817F-60627BE46B96@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/EF56C0B6-A15B-4784-817F-60627BE46B96%40gmail.com>
Precedence: bulk

Hi all,

I have a dev team requesting that I partition 3 very large tables with more 2=
 billion rows each. I=E2=80=99ve already got the partition key decided for e=
ach with a script which will create a copy of original with the partition de=
claration, along with the partition child tables. The problem will be that e=
ach table has an API which updates each table fairly regularly, and the devs=
 say it cannot be turned off. So, I=E2=80=99m looking for the best way to do=
 this so that the partitioned version will match the original table. FYI, th=
is is production instance that is regularly updated by field technicians. Qu=
ite literally, I cannot perform two sequential select count(*) from any of t=
hese tables which will return the same row counts. I=E2=80=99m thinking of i=
nstalling a trigger on insert on the original tables, which would write any i=
nsert to a third table, then copy the original table to another table name, d=
o 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?
=E2=80=94
Jay

Sent from my iPad=