The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Superfast Table Copy

This blog post was authored by Jim Knicely.

Very often we need to make a copy of a very large table in order to do some development or quality assurance type of duties.

Typically we’ll use a CREATE TABLE AS SELECT (CTAS) statement to make that copy.


dbadmin=> SELECT COUNT(*) FROM big_number_table;
(1 row)
Time: First fetch (1 row): 1383.065 ms. All rows formatted: 1383.127 ms

dbadmin=> CREATE TABLE big_number_table_copy AS SELECT * FROM big_number_table;
Time: First fetch (0 rows): 393328.528 ms. All rows formatted: 393328.566 ms

But that took a very long time (~ 40 minutes)!

Fortunately Vertica has the lightweight, in-memory COPY_TABLE function!


dbadmin=> SELECT COPY_TABLE('big_number_table', 'big_number_table_copy_fast');
Created table big_number_table_copy_fast.
Copied table big_number_table to big_number_table_copy_fast
(1 row)
Time: First fetch (1 row): 206.614 ms. All rows formatted: 206.664 ms

It made the same copy in less than one second. Wow!

Have fun!


Sign In or Register to comment.