Can I put a limit on number of projections per table

Best Answers

• Options

  Vertica_Curtis Employee

  June 2022 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?

  1
• Options

  Vertica_Curtis Employee

  June 2022 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.

  1