Projections in HP Vertica - 1

What  are Projections in HP Vertica? 

Lets try to understand by comparing with traditional databases like - Oracle, MySQL, SQL Server etc.. 

• In traditional database architecture, data is physically stored in table. Additionally, secondary tunning structure such as index and materialized view structure are created to improve query performance. 

• In contrast, table donot occupy any physical storage atallin vertica. 

• Physical storage consists of collection of table columns called projections.

• Projections store data in a format that optimize query execution. They are simmilar to MVs in that they store result set on disk rather then compute them each time they areused in a query. The result set are automatically refreshed whenever data values are inserted, appended or changed. 

• Projections are not aggregated but rather store row in a table e.g. full atomic detail

Definition: 

Optimized collection of table columns that provide physical storage for data. A projection can contain some or all the columns of one or more tables. A projection that contains all of the columns of a table is called super-projection. A projection that contain one or more tables is called pre-join projection.

What are the benefits of Projections?

• Projections allow for the sorting of data in any order ( even if different from the source tables). This enhances query performance and compression.

• Projections delivers high availability optimized for performance, since the reduntant copies of data are always actively used in analytics. We have the ability to automatically store the redundant copy using a different sort order. This provides the same benefits as a secondary index in a more efficient manner.

• Projections do not require a batch update window. Data is automatically available upon loads.

• Projections are transparent to end-users and SQL. The Vertica query optimizer automatically picks the best projections to use for any query.

• Projections are dynamic and can ve added/changed at ay time without stopping the database.

Note: Vertica's projections represent collections of columns (so you can say- It's a table), but they are optimized for analytics at the physical storage structure level and are not constrained by the logical schema.

As we have covered the simple concept of Vertica's projections. Now as we have a basic understanding of that projections are. Lets go in more detailin the next post.  Vertica Projections -2

Please share it among your friends. Lets learn together!!