Re: migrating data from non-partitioned table to partitioned table in Aurora Postgresql

On Tue, Feb 13, 2024 at 10:44 AM hellen jiang <hellenjiang99(at)gmail(dot)com>
wrote:

> Hi All,
>
> We have 1522 non-partition tables in the Aurora postgres cluster, There
> are around 180,000,000 rows in each table, which is very big. We want to
> change each table into a partitioned table, so each table will be
> partitioned into around 200 partitioned tables: eventually we will get
> around 1522* 200 tables. Now we are trying to migrate from non-partitioned
> tables to partitioned tables by INSERT INTO NEW-TABLE SELECT * FROM
> OLD-TABLE WHERE XXX. The problem is this performance is very slow,
>

1. Are there indices on NEW-TABLE? (That _always_ slows things down.)
2. Have you tried COPY FROM / COPY TO? (It would require a separate
computer, though, and decent network bandwidth. Maybe spin up an EC2 VM
for that purpose.)

> is there any way that we can speed up the migration process? by whatever
> method like changing configuration. We don't care about database downtime
> at this moment.
>

That would depend on how you've currently configured the instance, which
you haven't mentioned (and might not be valid in vanilla Postgresql).