Vertica is a distributed analytics DBMS. It can be deployed on various platforms like AWS,GCP,Azure... Vertica is designed to a achieve a high performance on OLAP compared with DBMS for large workloads. High availability and good scalability can be achieved as well on commodity hardware. Also, it provides good integration with Hadoop, Spark, Kafka, which makes users select where they would like to analyze their data freely.
Vetica was founded by Michael Stonebraker and Andrew Palmer in 2005. It is derived from C-Store, which is a prototype developed by MIT, Brown, and few other universities in 2005. It was acquired by Hewlett Packard in 2011 and joined Micro Focus in 2017 due to the merger between HP and Micro Focus .
In Vertica, each node maintains checkpoints and transaction logs separately. The synchronization duration can be tuned by users as well. For a single-node failure, it can be recovered from other nodes. If the entire cluster fails, it can be recovered up to the earliest checkpoints when all nodes are good. New transaction log cannot be appended when a new checkpoint begins.
Both Run-Length Encoding and Delta encoding are used in Vertica. RLE encoding is only used when the number of repetitions is large. Delta encoding works for INTEGER/DATE/TIME/TIMESTAMP/INTERVAL type, where the variations from the smallest value are stored instead of the real values to save more space.
Vertica supports MVCC to achieve data consistency. Both current and previous statuses are stored and visible to transactions. Transaction isolations can be achieved since no conflict between the read and write operations exist. A shared-nothing MPP (Massively Parallel Processing) architecture is used in Vertica, which can avoid the overheads caused by locks.
Columnar store is used in Vertica to improve the performance of sequential access by sacrificing the performance of single access. Compared with row-oriented databases which has to scan the whole table, only few needed columns are retrieved based on given queries in Vertica, which can improve throughput by reducing disk I/O costs.
Vertica allows users to use foreign key constraints. Foreign keys should be defined when tables are created or "ALTER TABLE" is used.
Indexes are not support in Vertica. Projections are used to improve query performance in Vertica.
Read Committed and Serializable are supported in Vertica. Read Committed is the default isolation level. Read Uncommitted and Repeatable Read are treated automatically as Read Committed and Serializable respectively in vertica.
Both merge join and hash join are supported in Vertica. Merge join is faster in general and requires less memory, but data is required to be sorted before. Hash join requires more memory, but it is faster if the inner table can fit in the memory.
There are two kinds of logs at each node in Vertica cluster. The dblog is used to keep track of the status only when the database boots. The other type called vertica.log is used to record the status of the node and the status of the whole cluster when Vertica runs.
Projections in Vertica have been used for query execution. Query optimizer is responsible for designing and selecting the suitable projections based on the given query plan. Various projections have different influence on query performance in terms of memory, CPU utilization, I/O, Network bandwidth ...
Hybrid data store are chosen in Vertica. Write Optimized Store(WOS) is about storing data in memory, which does not support sorting, compression and indexing. WOS is mainly designed for OLAP. Read Optimized Store(ROS) is about storing data on disk, where data is sorted and indexed, and compressed.
Data is stored in Vertica in columnar format to improve query performance, since a lot of disk I/O can be avoided.
Stored Procedures are not support in Vertica. External Procedures such as R,C++ can be used in Vertica.
Shared-nothing architecture is used in Vertica, where all nodes don't share anything in terms of memory and disk storage. Shared-nothing architecture are easier to scale, since there is no race or contention caused by locks. Moreover, Massively MPP(Massive Parallel Processing) architecture is used in Vertica, which can improve query performance such as increasing the throughput of large joins when multiple machines are involved.
C++, Java, Perl, Python, R