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 c onsists 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 proj
The below mentioned diagram illustrates the basic system architecture of Vertica on a single node. Pic: Vertica.com Queries are issued in SQL to a front end that parses and optimizes queries. Vertica is internally organized into a hybrid store consisting of two storage structures: WOS and ROS Write-Optimized Store (WOS) is a data structure that generally fits into main memory and is designed to efficiently support insert and update operations. The data within the WOS is unsorted and uncompressed. Read-Opytimized Store (ROS) contains the bulk of the data in the database, and is both sorted and compressed, making it efficient to read and query. A background process called the Tuple Mover , moves data out of the WOS into ROS. As it operates on the entire WOS, the tuple mover can be very efficient, sorting many records at a time and writing them to disk as a batch. Both WOS and ROS are organized into columns, with each columns representing one attributes o