I've to duplicate values from one table to another (identical table schemes). What is better (performance):
Update: I've made a small test on table with almost 3k rows. Drop and create gives about 60ms vs Delete and insert - about 30ms.
I see four useful ways to replace the contents of the table. None of them is "obviously right", but it depends on your requirements.
(In a single transaction) DELETE FROM foo; INSERT INTO foo SELECT ...
Pro: Best concurrency: doesn't lock out other transactions accessing the table, as it leverages Postgres's MVCC.
Con: Probably the slowest if you measure the insert-speed alone. Causes autovacuum to clean up dead rows, thus creating a higher I/O load.
TRUNCATE foo; INSERT INTO foo SELECT ...
Pro: Fastest for smaller tables. Causes less write I/O than #1
Con: Excludes all other readers -- other transactions reading from the table will have to wait.
TRUNCATE foo, DROP all indexes on table, INSERT INTO foo SELECT ..., re-create all indexes.
Pro: Fastest for large tables, because creating indexes with CREATE INDEX is faster than updating them incrementally.
Con: Same as #2
The switcheroo. Create two identical tables foo and foo_tmp
TRUNCATE foo_tmp;
INSERT INTO foo_tmp SELECT ...;
ALTER TABLE foo RENAME TO foo_tmp1;
ALTER TABLE foo_tmp RENAME TO foo;
ALTER TABLE foo_tmp1 RENAME TO foo_tmp;
Thanks to PostgreSQL's transactional DDL capabilities, if this is done in a transaction, the rename is performed without other transactions noticing. You can also combine this with #3 and drop/create indexes.
Pro: Less I/O performed, like #2, and without locking out other readers (locks taken only during the rename part).
Con: The most complicated. Also you cannot have foreign keys or views pointing to the table, as they would point to the wrong table after renaming it.
Use TRUNCATE instead of DROP TABLE or DELETE when you have to get rid of all records in a table. With TRUNCATE you can still use triggers in PostgreSQL and permissions are easier to set and maintain.
Like a DROP, TRUNCATE also needs a table lock.
Here is the (comparative) timings for the intgr's answer (see the code below):
drop index/truncate/insert/create index - 13 sec.
-- preparations
drop table if exists temp_refresh_experiment;
-- million random strings
create table temp_refresh_experiment as
select
upper(substr(md5(random()::text), 0, 25)) as some_column
FROM
generate_series(1,1000000) i;
-- create index
create index temp_refresh_experiment_ix on temp_refresh_experiment(some_column)
;
-- 1. delete/insert
delete from temp_refresh_experiment;
insert into temp_refresh_experiment(some_column)
select
upper(substr(md5(random()::text), 0, 25)) as some_column
FROM
generate_series(1,1000000) i;
-- 36 secs
-- 2. truncate/insert
truncate temp_refresh_experiment;
insert into temp_refresh_experiment(some_column)
select
upper(substr(md5(random()::text), 0, 25)) as some_column
FROM
generate_series(1,1000000) i;
-- 19 sec
-- 3. drop index/truncate/insert/create index
drop index if exists temp_refresh_experiment_ix;
truncate temp_refresh_experiment;
insert into temp_refresh_experiment(some_column)
select
upper(substr(md5(random()::text), 0, 25)) as some_column
FROM
generate_series(1,1000000) i;
create index temp_refresh_experiment_ix on temp_refresh_experiment(some_column)
;
-- 13 sec
In case you are talking about executing the INSERTs manually, one by one, then DROP/CREATE will be much faster. Also, when using CREATE TABLE AS, it will only copy the column definitions. Indices, and other constraints will not be copied. This will speed up the copy process enormously. But you'll have to remember to re-create these on the new copy once you're finished.
The same goes for SELECT INTO. They are functionally identical. They just have different names.
In any case. When copying large tables, always disable triggers, indices, and constraints to gain performance.
