partitions limit per projection

nirka

Hi,

According to the documentation the partition limit per projection is 1024, but as stated in this page: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Partitions/MinimizingPartitions.htm
the best practice is to keep partition count under 50.

We're designing new ETL flow which may load data in parallel to same table.
To support it we need to add to the partition expression also the ID of the process that loads the data to the table, so it would be possible to do partition swap.

The new design requires ~ 200 partitions for projection.
I've checked the performance with such configuration and didn't see any downgrade in terms of select / insert statements.

So my questions are:
1) Why the best practice is to have up to 50 partitions?
2) If I use only 2-3 tables with 200+ partitions does it matter? (I read someplace the number of partitions can effect catalog size, but maybe only few tables with above 50 wont have negative effect on performance)

Thanks for the help,
Nir

Jim_Knicely

Take a look at the article "Tuple Mover Best Practices" here:

https://www.vertica.com/kb/Tuple-Mover-Best-Practices/Content/BestPractices/Tuple-Mover-Best-Practices.htm

In particular:

Create 50 or fewer partitions per table because Vertica does not merge ROS containers across partitions. Thus, tables with hundreds of partitions can hit ROS pushback quickly.

nirka

Thanks, on the same page the "active partitions" are described:

If your tables receive frequent data into the current and the most recent inactive partition, change the ActivePartitionCount parameter to 2 from the default of 1. Vertica expects partitions to be time-based, with one active partition receiving data and other inactive partitions rarely or never receiving data. The Tuple Mover merges ROS containers for inactive partitions into a single ROS container.

As I understand, the active partitions are merged according to strata algorithm, but what happens when inactive partition is changed? It means that partition will have more than 1 file. When those files will get merged? Immediately without any algorithm / logic?