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.

Example:

dbadmin=> SELECT COUNT(*) FROM big_number_table;
   COUNT
------------
1000000000
(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;
CREATE 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!

Example:

dbadmin=> SELECT COPY_TABLE('big_number_table', 'big_number_table_copy_fast');
                                               COPY_TABLE
--------------------------------------------------------------------------------------------------------
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!