Bulk load data from one table to another

Started by Nikalmost 18 years ago2 messagesgeneral
Jump to latest
#1Nik
XLPizza@gmail.com

I have a partitioned table that uses monthly temporal partitions.
Every night I want to load the last 10 weeks of data into a separate
non-partitioned table.

Currently, I truncate the destination table, drop the indexes,
execute:
"INSERT INTO destination SELECT * FROM source_partition"
and recreate indexes.

This loads about 66.5 million rows into the destination table in about
1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is
there a more efficient way to perform this procedure?

Thanks.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nik (#1)
Re: Bulk load data from one table to another

Nikola <XLPizza@gmail.com> writes:

Currently, I truncate the destination table, drop the indexes,
execute:
"INSERT INTO destination SELECT * FROM source_partition"
and recreate indexes.

This loads about 66.5 million rows into the destination table in about
1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is
there a more efficient way to perform this procedure?

Do you have maintenance_work_mem set to a suitable value for building
the indexes?

regards, tom lane