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