Partitioning Best Practices

Recommendations

Minimize number of partitions

Vertica supports up to 1024 ROS containers per partition. In general, a ROS contains only one partition (although a partition can sometimes span multiple ROS containers). Each partition typically spans multiple ROS containers. Be aware that any delete operation requires Vertica to open all ROS containers, so a large number of partitions can adversely affect performance.

In practice, it is highly unlikely that you will approach this maximum. For optimal performance, Vertica recommends that the number of partitions range between 10 and 20, and not exceed more than 50. This range is typically compatible with most business requirements.

Avoid partitioning by LONG data types
For maximum performance, do not partition table data on LONG VARBINARY and LONG VARCHAR columns.

Tip: Partitioning by Year and Month

To partition by both year and month, the partition clause should pad the month out to two digits with an expression like this:

PARTITION BY EXTRACT(year FROM tdate)*100 + EXTRACT(month FROM tdate)

This expression formats partition keys as follows:

201101
201102 
201103 
... 
201111 
201112