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

Поиск
Список
Период
Сортировка
От Ron Johnson
Тема Re: migrating data from non-partitioned table to partitioned table in Aurora Postgresql
Дата
Msg-id CANzqJaDtx3yv9Tjvu2kJhf7RcriotnC3vXr6NmGiNcgAroVVQg@mail.gmail.com
обсуждение исходный текст
Ответ на migrating data from non-partitioned table to partitioned table in Aurora Postgresql  (hellen jiang <hellenjiang99@gmail.com>)
Список pgsql-admin
Дерево обсуждения
On Tue, Feb 13, 2024 at 10:44 AM hellen jiang <hellenjiang99@gmail.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).


В списке pgsql-admin по дате отправления:

Предыдущее
От: hellen jiang
Дата:
Сообщение: migrating data from non-partitioned table to partitioned table in Aurora Postgresql
Следующее
От: Krishnaswamy
Дата:
Сообщение: Postgres upgrade from 9.6.9 to postgresql 16 version