Bulk load data from one table to another

From: Nikola <XLPizza(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Bulk load data from one table to another
Date: 2008-06-23 19:40:04
Message-ID: eeaff5d2-9faa-4791-9dab-88f379c3f494@s50g2000hsb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message salman 2008-06-23 19:40:15 Re: dbsize command
Previous Message Mike Gould 2008-06-23 19:37:31 Re: Data Types