Alternative to drop index, load data, recreate index?
От
Jason L. Buberel
Тема
Alternative to drop index, load data, recreate index?
Дата
Msg-id
46E5DC0B.9020205@buberel.org
Список
Дерево обсуждения
Alternative to drop index, load data, recreate index? "Jason L. Buberel" <jason@buberel.org>
Re: Alternative to drop index, load data, recreate index? Jeff Davis <pgsql@j-davis.com>
Re: Alternative to drop index, load data, recreate index? Tom Lane <tgl@sss.pgh.pa.us>
Re: Alternative to drop index, load data, recreate index? hubert depesz lubaczewski <depesz@depesz.com>
Re: Alternative to drop index, load data, recreate index? "Jason L. Buberel" <jason@buberel.org>
Re: Alternative to drop index, load data, recreate index? "Scott Marlowe" <scott.marlowe@gmail.com>
When loading very large data exports (> 1 million records) I have found it necessary to use the following sequence to achieve even reasonable import performance:
1. Drop all indices on the recipient table
2. Use "copy recipient_table from '/tmp/input.file';"
3. Recreate all indices on the recipient table
However, I now have tables so large that even the 'recreate all indices' step is taking too long (15-20 minutes on 8.2.4).
I am considering moving to date-based partitioned tables (each table = one month-year of data, for example). Before I go that far - is there any other tricks I can or should be using to speed up my bulk data loading?
Thanks,
jason
1. Drop all indices on the recipient table
2. Use "copy recipient_table from '/tmp/input.file';"
3. Recreate all indices on the recipient table
However, I now have tables so large that even the 'recreate all indices' step is taking too long (15-20 minutes on 8.2.4).
I am considering moving to date-based partitioned tables (each table = one month-year of data, for example). Before I go that far - is there any other tricks I can or should be using to speed up my bulk data loading?
Thanks,
jason
В списке pgsql-general по дате отправления