Source-linked AI summary
The Vertica Analytic Database: C-Store 7 Years Later
Andrew Lamb, Matt Fuller, Ramakrishna Varadarajan, Nga Tran, Ben Vandier, Lyric Doshi, Chuck Bear
TL;DR
The paper addresses how a relational database can support massive analytic workloads despite legacy systems’ transactional orientation. It describes Vertica’s commercialization of C-Store through distributed storage, execution, optimization, and physical design choices, and reports that modern architectural choices preserve a relational interface while delivering high analytic performance. The paper also identifies co-location assumptions in early query optimization and the scale-out constraints imposed by its distributed design.
Problem
Legacy RDBMS systems were designed for transactional workloads on older hardware, leaving the suitability of relational systems for massive analytic workloads as a central concern.
Method
The paper describes Vertica’s architecture and its deviations from C-Store, including distributed segmentation, pipelined vectorized execution, and automated physical design.
Results
Vertica provides positive proof that a modern RDBMS can retain a familiar relational interface while achieving high performance for analytic systems through appropriate architectural choices.
Takeaways & Limitations
Vertica’s commercialization shows that database research ideas can support widely deployed, large-scale distributed analytic systems.
Takeaways & Limitations
Early StarOpt optimization handled only queries with co-located projections, while the distributed design also required avoiding shared-disk and excessive-network bottlenecks.
Abstract
from arXiv · showhide
This paper describes the system architecture of the Vertica Analytic Database (Vertica), a commercialization of the design of the C-Store research prototype. Vertica demonstrates a modern commercial RDBMS system that presents a classical relational interface while at the same time achieving the high performance expected from modern "web scale" analytic systems by making appropriate architectural choices. Vertica is also an instructive lesson in how academic systems research can be directly commercialized into a successful product.
1. INTRODUCTION
Vertica commercializes C-Store’s ideas as a distributed, massively parallel RDBMS for large-scale analytic workloads. The paper presents its architecture, implementation lessons, and implications for future analytic-system research.
- System and motivation: Vertica is a distributed, massively parallel RDBMS that commercializes the C-Store research project.The system had over 500 production deployments, including at least three substantially larger than a petabyte.
- System and motivation: Legacy RDBMS systems were designed for transactional workloads on older hardware, whereas Vertica targets analytic workloads on modern hardware.
- System and motivation: Vertica demonstrates that distributed databases can retain fully ACID transactions while efficiently processing petabytes of structured data.
- Contributions: The paper overviews Vertica’s architecture, emphasizing deviations from C-Store.
- Contributions: It explains the implementation and deployment lessons that produced those architectural differences.
- Contributions: It reports real-world observations intended to inform future research on large-scale analytic systems.
2. BACKGROUND
Vertica was built from C-Store’s research lineage as a distributed analytic database for modern commodity hardware. Its design emphasizes scalable processing, fast loading, online operation, and reduced administration.
- Origins: Vertica directly commercialized C-Store and was founded in 2005 before HP acquired it in 2011.
- Workloads: Vertica targets analytic rather than transactional workloads, which examine substantial fractions of a table instead of modifying a handful of rows.
- Workloads: Growing databases make workload specialization increasingly important because analytic-focused systems can exceed one-size-fits-all systems by orders of magnitude.
- Design goals: Vertica uses shared-nothing scale-out architecture on commodity servers and avoids shared disk and excessive network traffic to support linear scaling as nodes are added.
- Design goals: The system must sustain high ingest rates while loading data in bulk without unduly slowing concurrent queries.
- Design goals: Vertica keeps operations online and automates physical design where possible, trading inexpensive CPU cycles for reduced administrative effort.
- Implementation: Vertica was written largely from scratch, with PostgreSQL-derived SQL parsing, semantic analysis, rewrites, and early client libraries among the exceptions.
3. DATA MODEL
Vertica presents tables through SQL while physically organizing data in sorted, encoded projections. These structures replace C-Store join indices and support alternative layouts for efficient analytic access.
- Projections: Vertica supports standard SQL data modification, bulk loading, and querying while physically storing table data through projections.
- Projections: Projections are sorted subsets of table attributes, and customers commonly use one super projection plus zero to three narrow non-super projections.
- Projections: Each projection is totally sorted and functions as a restricted materialized view, without joins, aggregation, or filtering constructs.
- C-Store differences: Vertica requires a super projection containing every anchoring-table column instead of implementing C-Store join indices.
- C-Store differences: Prejoin projections allow normalized schemas with physically denormalized storage, but are used less often because small-dimension joins execute efficiently.
- Projection layout: Figure 1 contrasts a date-sorted, sale-id-segmented super projection with a cust-sorted, cust-segmented projection containing only cust and price.
- Encoding: Vertica assigns encoding schemes per column and projection, including automatic selection, run-length encoding, delta methods, dictionaries, and compressed delta variants.
3.5 Partitioning
Vertica separates data physically within nodes and distributes it across nodes through projection segmentation. These choices accelerate deletion, pruning, joins, aggregation, and cluster changes.
- Partitioning: Vertica obtains intra-node parallelism by dividing on-disk structures into logical regions at runtime rather than physically separating them.
- Partitioning: Partitioning accelerates bulk deletion by allowing entire month- and year-based files to be removed and storage reclaimed immediately.
- Partitioning: Partitioning improves predicate pruning because minimum and maximum values in each ROS can exclude containers, while separated values avoid intermixed ranges.
- Segmentation: Projection segmentation deterministically maps tuples to computation nodes, enabling fully local distributed joins and efficient distributed aggregations.
- Segmentation: Projections may be replicated across nodes or segmented so each tuple resides on exactly one projection node according to a segmentation expression.
- Segmentation: Nodes receive ranges of segmentation-expression values through a classic ring-style scheme, commonly using HASH over high-cardinality, evenly distributed columns.
- Segmentation: Local segments let Vertica expand or contract clusters by transferring existing segment data wholesale without rearrangement or splitting.
3.7 Read and Write Optimized Stores
Vertica separates mutable ingestion from read-optimized storage and never modifies stored data in place. ROS containers use physically separate column files, while delete vectors represent modifications.
- Read Optimized Store: ROS stores complete sorted tuples across multiple containers, with separate data and position-index files for each column.The position index stores disk-block metadata such as start position, minimum value, and maximum value.
- Write Optimized Store: The WOS keeps data in memory to buffer small inserts, deletes, and updates until writes can amortize physical-writing costs.Its orientation changed over time without significant measured performance differences.
- Data Modifications and Delete Vectors: Vertica represents deletions and updates with delete vectors rather than modifying tuples in place.An update deletes the existing row and inserts a row containing the updated column values.
- Physical Storage Layout: The illustrated projection uses 14 ROS containers and two columns per container, yielding 28 user-data files across four month-year partition keys.The node is segmented by HASH(cid), and local segments further divide its assigned hash range.
4. TUPLE MOVER
The tuple mover asynchronously reorganizes Vertica’s physical storage while balancing ingestion efficiency against the costs of excessive small ROS containers. It performs moveout and mergeout without centralized coordination across nodes.
- Tuple Mover Functions: The tuple mover has two main functions: moveout transfers data from WOS to ROS, while mergeout combines multiple ROS files into larger ones.These operations oversee and rearrange physical data files during query processing.
- Moveout: Moveout runs asynchronously as WOS fills, but overflow can direct subsequent loads into new ROS containers until capacity returns.The system balances moveout to avoid both excessive small ROS containers and WOS overflow.
- Mergeout: Mergeout reduces ROS-container counts, improving compression opportunities and avoiding extra file handles, seeks, and sorted-file merges.It also removes tuples deleted before the Ancient History Mark when producing merged output.
- Mergeout: Mergeout organizes containers into exponential size strata and limits newly created ROS containers to a current maximum of 2TB.The output is planned to occupy at least one larger stratum than any input container.
- Distributed Coordination: The tuple mover preserves partition and local-segment boundaries, maximizes ingest rate, and keeps ROS layouts private to each node.Different nodes may have different layouts because of merging patterns, resources, failures, and recovery.
5. UPDATES AND TRANSACTIONS
Vertica combines immutable storage, epoch-based snapshots, quorum-oriented cluster coordination, replicated projections, and online recovery and backup to support transactional analytics.
- Epoch Management: Vertica assigns commit and delete epochs so epoch boundaries represent globally consistent snapshots while storage remains unmodified.Epochs are implicit 64-bit integral columns on projections or delete vectors.
- Locking: Vertica’s locking model supports serializable access, concurrent inserts, shared reads-and-inserts, exclusive updates and deletes, and short tuple-mover operations.Insert locks are compatible with themselves to support simultaneous inserts and bulk loads.
- Transactions: The cluster commits transactions without traditional two-phase commit: a commit succeeds on a quorum, while nodes failing during commit are ejected.Committed ROS or WOS data becomes visible after the commit completes.
- Epoch Management: Automatic epoch advancement on DML commits reduces visibility confusion for READ COMMITTED users and simplifies internal management.Vertica tracks the Last Good Epoch and Ancient History Mark for durability, recovery, and historical cleanup.
- Recovery: Buddy projections provide fault tolerance by storing each projection’s segments on different nodes, allowing missing rows to be sourced during node recovery.Historical and current recovery phases copy committed data from a buddy projection until the recovering projection reaches the current epoch.
- Online Operations: Recovery, refresh, rebalance, and backup remain online, affecting ongoing operations only through the computational and bandwidth resources they consume.Backup uses catalog snapshots and hard links to preserve read-only data files during copying.
6. QUERY EXECUTION
Vertica executes SQL through a multithreaded, pipelined, vectorized engine that exploits projections, segmentation, encoded data, adaptive algorithms, and parallel local and cluster processing. Its optimizer and Database Designer target varied workloads while managing physical-design trade-offs.
- Query Interface: Vertica supports standard SQL and proprietary extensions, with an SDK for extending system components rather than continually adding extensions.Queries can be submitted interactively or through JDBC, ODBC, and ADO.NET drivers.
- Query Operators and Plan Format: The execution engine runs operator trees with multiple concurrent threads and pipelines, including scans, grouping, joins, sorting, analytics, and data exchange.StorageUnion dispatches ROS-container work and local resegmentation, while ParallelUnion runs grouping and filtering in parallel.
- Query Operators: Operators apply predicates, grouping, joins, expression evaluation, sorting, windowed aggregates, and segmented or broadcast tuple exchange.Hash and merge joins can externalize when necessary, and grouping uses several hash-based and pipelined algorithms.
- Execution Techniques: Sideways Information Passing filters data early, while runtime algorithm switching and prepass operators adapt execution to observed data and available memory.A hash join can switch to sort-merge when its hash table will not fit in memory.
- Parallel Execution: Vertica parallelizes work across non-overlapping ROS regions and cluster nodes, sending alike values to the same node so operators can compute complete results locally.Operators are optimized for sorted, encoded data maintained by the storage system.
- Query Optimization: StarOpt initially required co-located projections, whereas StarifiedOpt and V2Opt broaden optimization toward non-star queries and on-the-fly inter-node data transfer.V2Opt’s extensible modules allow optimizer knowledge from end-user experience to be incorporated without extensive additional engineering.
- Physical Design: The Database Designer selects projections and encodings under workload, storage, and load-overhead constraints, providing a baseline that users rarely override for column encoding.Users sometimes adjust segmentation, select lists, or sort lists for large tables based on application-specific knowledge.
7. USER EXPERIENCE
Vertica’s commercial adoption was shaped by usability requirements alongside performance, including standard SQL, resource management, automated tuning, predictable behavior, and flexible loading. These priorities addressed organizational skill investments, operational complexity, and large-scale ingestion needs.
- SQL: Standard SQL was critical for commercial success because customer organizations had substantial investments in the language.
- Resource Management: Resource management for sharing cluster resources and reporting allocations across concurrent users was critical to real-world deployments.The authors initially underestimated this requirement and consider it understudied in academic data management research.
- Automated Tuning: Automated physical-design and storage management reduced the tuning burden placed on database users.Achieving this required interrelated changes to the storage system, execution engine, and tuple mover.
- Predictability vs. Special Case Optimizations: Special-case optimizations made query performance harder to predict, so users preferred consistent behavior over isolated speedups.Some queries became much faster while others did not, often because of low-level implementation details.
- Direct Loading to the ROS: Directing large bulk loads to the ROS avoided the memory costs of routing all newly inserted data through the WOS.Users could explicitly tag such loads for improved resource usage.
- Bulk Loading and Rejected Records: Handling schema-nonconforming records during distributed bulk loading was important and complex to implement.
8. PERFORMANCE MEASUREMENTS
Vertica’s commercial feature additions did not eliminate the performance and compression advantages demonstrated by its architecture. The measurements report roughly doubled single-core speed versus C-Store and substantially smaller encoded data representations for random and customer data.
- Performance: Vertica was roughly twice as fast as C-Store on a single-core machine.Vectorized execution and more sophisticated compression reclaimed performance lost to added product features; remaining overhead was negligible.
- Compression: Vertica’s storage engine achieved significant compression on both contrived random-integer data and real customer data.Table 4 summarizes the compression results for 1M random integers and customer records.
- Performance: The comparison used single-node Pentium 4 hardware with the queries and test harness from the C-Store paper.A modern multicore cluster comparison was considered unfair because C-Store was single-threaded and could not use MPP hardware.
- 1M Random Integers: 0.6 MB was Vertica’s encoded size for one million random integers, compared with 3.6 MB using gzip and 2.2 MB after sorting before gzip.The raw data size was 7.5 MB.
- 200M Customer Records: 418 MB was Vertica’s total compressed size for 200 million customer meter records, versus 6200 MB as CSV and 1050 MB with gzip.Sorting by metric, meter, and collection time exposed compression opportunities across columns.
9. RELATED WORK
Vertica and C-Store combine established design features for analytic workloads, while subsequent systems commercialize related columnar, in-memory, and distributed approaches. Vertica’s success also influenced legacy RDBMS vendors to add columnar storage options.
- Research Roots: Vertica and C-Store’s contribution is their combination of previously documented design features applied to a specific workload.
- Subsequent Systems: Other commercialized systems include InfoBright, Brighthouse, Vectorwise, and MonetDB/X100, while SAP HANA and Blink pursue different in-memory approaches.SAP HANA integrates columnar in-memory storage with business applications; Blink combines in-memory execution with distributed shared-nothing design.
- Industry Impact: The success of Vertica and other native column stores led legacy RDBMS vendors to add columnar storage options to existing engines.
10. CONCLUSIONS
The paper presents Vertica’s architecture as a commercial extension of C-Store that retains a relational interface while delivering high analytic performance through architectural choices. It concludes that such commercialization depended on transformational database research.
- Contributions: The paper describes Vertica’s architecture and identifies how its design differs from or extends C-Store.It also presents quantitative and qualitative advantages associated with that architecture.
- Conclusion: Vertica shows that a modern RDBMS can retain a familiar relational interface while achieving the high performance expected from modern analytic systems.The paper attributes this performance to architectural choices drawing on database research from the preceding 30 years.
- Research Implications: Vertica depended on new research innovations rather than only incremental changes to existing database paradigms.The authors argue that database research should focus on transformational and innovative engine designs for expanding system requirements.