Superfast Table Copy

[Deleted User]

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!

sreeblr

wow . any export schema - all objects with data option to simply data copy option?

Jim_Knicely

@sreeblr - Sorry, not sure I understand your question

Are you asking how to "super fast" copy all tables in a schema?

sreeblr

copy schema from one server to another do we have any "super fast schema copy. "

Jim_Knicely

@sreeblr - Not that I am aware of Usually its the network that's the bottle neck. There is a great discussion here that might help you: https://forum.vertica.com/discussion/comment/239767#Comment_239767
Basically zipping the data prior to sending it over the network will help make the copy "faster".