Skip to content
Quantoros

What are the tradeoffs between row-based and column-based (columnar) storage formats?

ML System DesignLevel: FoundationData pipelines and distributed processing

What are the tradeoffs between row-based and column-based (columnar) storage formats?

Hints
  • Does the typical query need whole rows or only selected columns?
  • Compare bytes read, compression, analytical aggregation and individual-row updates.

Answer

Row-oriented storage keeps a record's fields together, which suits reading or writing complete records. Columnar storage groups values by column, helping analytical scans that need a few columns from many rows; similar values also permit effective compression and encoding. Parquet is a columnar file format, while Avro serializes records according to a schema.

Do not confuse a row-oriented file with a transactional row-store database. CSV or Avro alone does not provide indexes, locking, transactional isolation or efficient arbitrary in-place row updates. Those are storage-engine or table-layer features. Choose based on access patterns, projection needs, compression, append/update behavior and the surrounding system's transactional requirements.

Source and licence: Ishan Dutta — Awesome Data Science Interview Q&A · MIT · answer: Quantoros editorial

Related questions