Can I put a limit on number of projections per table

Natasha_2

I know there is no hard limit to number of projections we can have but can I put a limit on how many projections get created for each table?

Vertica_Curtis

There's nothing configurable that would control how many projections get created. Are you finding yourself in a position where people are creating a lot of projections? Can you elaborate a little on why you would need such functionality?

Vertica_Curtis

Sounds like more of a process problem than a technical one.

In general, I would suggest that having multiple projections isn't really an issue, especially if the table is small. Lots of projections will affect load performance, but if load performance isn't really a concern, then it's not really a problem. More projections also don't contribute to license capacity, but will eat up extra disk space, but if you're on Eon mode, then that's not really an issue, either.

But this query might help:
select tablename, column_count, first_sort, last_sort, hash_segment, count()
from (
select distinct projection_schema || '.' || p.anchor_table_name as tablename
, projection_schema || '.' || p.projection_basename as basename
, count(projection_column_name) OVER (w) as column_count
, first_value(projection_column_name) over (w order by sort_position asc) as first_sort
, first_value(projection_column_name) over (w order by sort_position desc) as last_sort
, hash(segment_expression) hash_segment
from projections p join projection_columns pc using (projection_id)
join tables t on p.anchor_table_id = t.table_id
where p.is_segmented and pc.sort_position is not null
and not t.is_flextable
and not t.is_system_table
window w AS (PARTITION BY p.projection_schema, p.projection_name) ) as foo
group by 1,2,3,4,5 having count() > 1
order by 1 ;

It will help you identify duplicate projections. Obviously, having more than one projection on a table be defined in the same way isn't helpful. This will help you identify those (though it can produce false positives).

You might also consider looking into using zstd_fast_comp as the compression type. Z-standard compression is really amazing for load performance, and compression performance. If you use it in the place of AUTO compression (which is LZO), you're going to see about 2x load performance and up to 60% better compression. So, if your folks are creating lots of projections, they might as well make good ones.

Natasha_2

Thank you for information. Yes we have internal employee problem with too many people giving permission to admin Vertica that they should not touch. They keep create too many projections. I working to get management to stop them but wanted to turn on configuration parameter or something they dont know about to stop them if exist.

Natasha_2

Thank you Vertica_Curtis for excellent information.