Best Practice For Rebuilding Table

simongo9550

Hello,

Fairly new to Vertica here so I apologize in advance.

We have a table that needs to be available as much as possible for query purposes. Every day we need to rebuild the data set within this table.

Currently we build a staging table and run insert commands to load the data. Then once completed we alter table rename swapping our staging table in place of our primary table and then finally dropping our old primary table.

ALTER TABLE primary_table , staging_table RENAME TO staging_table, primary_table;

This appears to be working Is this the technique that we should be using or is there something else?

Bryan_H

Hi, this method is probably the best way to completely replace contents of primary_table with contents of staging_table. I see two issues:

• are there ever operations other than SELECT on the primary_table? Any other transaction that locks the table will prevent rename so timing issues might appear if the workload ever changes.
• have you optimized primary_table with a projection? If so, be sure the create statement for staging_table creates the same projection. If not, consider running DataBase Designer to create a projection on primary_table, since this will improve storage compression and query efficiency. Then CREATE TABLE staging_table LIKE primary_table INCLUDING PROJECTIONS; to copy the projection definition each time you rebuild the staging table.