Columnstore Index Compression in a Nutshell
summary
Explains how SQL Server columnstore compresses by treating each column as a segment per row group (up to 2^20 ≈ 1M rows), then applying three steps: dictionary/value encoding, vertipack reordering for run‑length or bit‑array compression, and final xVelocity compression; shows DMV queries (sys.column_store_segments, sys.column_store_dictionaries) to inspect dictionaries, base/magnitude for value encoding, and which segments lack optimization; warns about a 16 MB dictionary limit and reports real-world compressions of 10–100+:1 and when rebuilds are required.
AI-written summary · about
chapters (10)
- 0:00Introduction to speaker
- 1:00Columnstore index basics
- 2:43Fundamentals of compression
- 3:56Dictionary encoding process
- 5:57Value encoding
- 7:48Vertipaq optimization
- 10:01Physical compression methods
- 12:30Optimizing for compression
- 14:55DMV monitoring demonstration
- 18:33Conclusion and Q&A
related talks
20mColumnstore Index Compression in a Nutshell- 20mColumnstore Index Compression in a Nutshell
PolarStore: High-Performance Data Compression for Large-Scale Cloud-Native Databases
45mClickHouse: what is behind the fastest columnar database
26mColumnar Storage: Redefining Data Management for the Modern Era
16mTVStore: Automatically Bounding Time Series Storage via Time-Varying Compression
37mBenefits of columnar data layout
46mKEEPING UP IN A DATA-INTENSIVE WORLD WITH SINGLESTORE