Can I put a limit on number of projections per table

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?

Best Answers

  • Vertica_CurtisVertica_Curtis Employee
    Answer ✓

    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_CurtisVertica_Curtis Employee
    Answer ✓

    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. :)


  • 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.

  • Thank you Vertica_Curtis for excellent information.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file