History

Vetica was founded by Michael Stonebraker and Andy Palmer in 2005. It was based on the C-Store prototype developed by MIT, Brown, and Brandeis.

It was acquired by Hewlett Packard in 2011. HP then sold it to MicroFocus in 2017.

Checkpoints[05][06]

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.

Compression[07]

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. Also there are various types of dictionary encoding - distinct column values are compiled into a dictionary and then the dictionary and a list of indexes to represent the data block are stored. Finally simple LZO, GZIP and ZSTANDARD encodings are available for character datatypes.

Concurrency Control[08]

Vertica supports MVCC to achieve data consistency. Both current and previous statuses are stored and visible to transactions. There is Ancient History Mark(configurable), which represents how long history is available.

Data Model[09]

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.

Foreign Keys[10]

Vertica allows users to use foreign key constraints. Foreign keys should be defined when tables are created or "ALTER TABLE" is used. Constraints are disabled by default except CHECK constraints, so they are not evaluated synchronously, can be analyze using SELECT ANALYZE_CONSTRAINTS() statement asynchronously. They can be enabled by keyword ENABLE, so they are evaluated synchronously.

Indexes[11]

Indexes are not support in Vertica. Projections are used to improve query performance in Vertica. There can be more projections on top of single table. Sort order and segmentation can be specified to define by which columns data are presorted and distributed in database cluster. If query joins or aggregates data and underlying projections are presorted / segmented in compatible way, Vertica can use optimized operators, so much less memory is used and performance is significantly better. Projections can also be "UNSEGMENTED", so all table data are stored on all nodes in cluster. It is obviously recommended for smaller tables.

Isolation Levels[12]

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.

Joins[13]

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. In multi-node cluster, if join key is not compatible with segmentation of underlying projections, RESEGMENT / BROADCAST operators must be executed and performance significantly degrades. UNSEGMENTED projections on small tables are recommended to prevent RESEGMENT / BROADCAST.

Logging

Query Compilation

Query Interface[14][15]

Vertica supports query via SQL and its custom API (GeoSpatial, Timeseries, Event Series Joins, Machine Learning, ...). Vertica also provides connectors for external services, such as Hadoop, Spark, Kafka, S3, Google Cloud Storage. Moreover, Vertica also supports C++, Java, Python, R SDK.

Storage Architecture[16][17]

Enterprise (legacy) mode supports both Read and Write Optimized Storage (WOS, ROS). WOS is in memory, optimized for fast writes, ROS is on disk, encoded/compressed, for fast reads. EON mode stops supporting WOS. Persistent storage is separated (S3 like), but there is local cache called "Depot" (LRU).

Storage Model[16]

Data is stored in Vertica in columnar format to improve query performance, since a lot of disk I/O can be avoided.

Storage Organization[18]

Different from other DBMS, containers are used in Vertica to store data, WOS (Write Optimized Storage) and ROS (Read Optimized Storage) are two existing types. Database files (ROS) are immutable. Each file consists of blocks of fixed size. Vertica keeps statistics(e.g. min/max) for each block as well as for each file. Vertica is capable to prune files or even blocks to significantly reduce I/O. Moreover files can be split by using (hierarchical) partitioning and partitions can be pruned. Finally when running in EON mode, files are stored in separated storage like S3, so compute is separated from storage and can be scaled independently. Files are also cached on local storage(LRU). Update is split into delete / insert, delete vectors are stored. Obsolete data are purged asynchronously. There is internal tool called Tuple Mover, which is responsible for: - Immutable files are defragmented - WOS is moved to ROS

Stored Procedures[19]

Stored Procedures are not support in Vertica. External Procedures such as R,C++ can be used in Vertica.

System Architecture[09][20][21]

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. Newly EON mode has been developed to separate compute from storage, so they can be scaled independently. S3 like storage can be used.

Views[22][06]

The projections in Vertica are similar to materialized view in other DBMS. Various projections can be created on the same table so that some optimizations such as sorting data can be done for some specific queries in advance. Moreover there is support for pre-aggregated projections.