Data Modeling hard
Why do Columnar Databases (like Snowflake/Redshift/ClickHouse) achieve much better compression than Row-based DBs?
- A They utilize a proprietary ZIP-based file format that is natively optimized by the physical disk controller to compress data as it is written to the hardware.
- B They implement a specialized 'Lossy Storage' model that automatically removes all NULLs and redundant whitespace characters from the dataset before indexing.
- C Columnar data is homogeneous (same type), allowing for aggressive, domain-specific schemes like Bit-Packing and Run-Length Encoding (RLE) to collapse repeating values.
- D They strictly limit the total volume of data that can be stored in each partition to ensure that the compression dictionary never exceeds the size of the CPU cache.
Answer: C. Columnar data is homogeneous (same type), allowing for aggressive, domain-specific schemes like Bit-Packing and Run-Length Encoding (RLE) to collapse repeating values.
Row-based databases store diverse data types together (e.g., an Int, a Varchar, and a Date), which creates high entropy and makes compression difficult. In a columnar database, every block contains values of the same type (e.g., thousands of 'Active' statuses in a row). This low-entropy environment is perfect for Run-Length Encoding (RLE), where the system can store '"Active" repeated 50,000 times' in just a few bits, resulting in 10-50x better compression.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?