Vertica Partitioning Concept

[suhridghosh.01]

Hello ,

I am very new to this forum. I am looking for some clarity on vertica partitioning concept.I have a aggregate table which is of daily granularity. This table is partitioned on Date Column which present in this aggregate table. So as an when any data comes for some X day we drop the partition for day X from this aggregate table and then rerun the aggregation module to populate for day X.

So when i keep 3 year data in this aggregate table , i end up having 1095 daily partitions. But vertica is recommending only max of 1024 partitions.

Also vertica suggests that a table should have 10 -12 partitions to get optimal performance.

So my confusion here lies how i deal with daily partition tables and get the most optimal performance out of it.

Please suggest me some ways !!! Hope to look for some expertise suggestions !!!

Thanks,
Suhrid Ghosh

[adrian.oprea]

You will need to redesign you partition design or maybe get rid of it.
You have way to many partitions.
It is important to understand that Vertica is "WRITE ONCE" once a ROS container is written it will never be re-written, they just get consolidated.
Each partition is stored in it's own ROS container(you can also store more then one partition in one container by using the merge_partition function), so is ideal you stick to recommended limits,
Why ?
- to many open files - there might be times where Vertica will have to open all ROS containers so this will may need more resources(Make Vertica slow). In each container you will have a file per each column in your table(Vertica is a Columnar Database) imagine the number of open files in 2 columns table with 1095 partitions where you run a query that will read half of your table data ! that will require
you Operational System to open a lot of ROS containers(data files).

I suggest you try to see how it works without partitioning, Vertica is quite fast !