Vertica Quick Tip: Superfast Table Copy

Posted February 7, 2018 by Soniya Shah, Information Developer

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!