Partition big table

ChenSha

Hi all

Vertica 7.1.1-10, 20 node cluster.

We have big table (27TB) on production environment, and I want to partition this table so I could run large deletions and get this table smaller.
- What do I need to know about partitioning big table?
- What are the preparations\ checks that I need to make?
- What is the overhead?
- Anything that can help

 

Thanks
Chen

K_Krutiy

 

Sounds like you already have table in vertica with data in it and table unpartitioned.

 

I also assume you will need to do deletes on regular basis.

 

 

To have partitioned table data needs to be re-written.

 

You have 2 options.

 

Option 1 is to create new table, which is partitioned and copy data into partitioned table from unpartitioned table.

 

Option 2 is to run command like ALTER TABLE Sales PARTITION BY Month REORGANIZE;

 

Eaither way you need extra 27 Tb of free space to transition to partitioned table.

 

 

Generally people partition on some DATE field and define data retention policy.

 

You can do something like quarterly partitions and 12-24 month data retention.

Or what ever your business think is apropriate.

 

Keep in mind that Vertica has limit of 1024 partiotns, but recommend to keep low number of those (10-20) for good performance.

 

You also want to make sure that you use DROP_PARTITION().

Running DELETE on data of one partition is not different then running DELETE on non-partitioned table.