Loading...
System Design and AI Engineering, by Roni Das. 980 interactive lessons.
Start LearningIntermediate · Database Types & Storage
Columnar storage keeps each column's values together on disk instead of each row's. Analytics queries then read only the columns they need and compress well, but single-row writes are slower.
In short
Columnar storage keeps all the values of one column together on disk, instead of all the values of one row. ClickHouse, Amazon Redshift and Google BigQuery are well-known columnar databases. They answer an analytics query, a question about many rows at once such as total sales per city, by reading only the few columns it names. Those columns also compress well, which means they fit in fewer bytes. The cost is slower writes and lookups of single rows.
In short
Columnar storage keeps all the values of one column together on disk, instead of all the values of one row. ClickHouse, Amazon Redshift and Google BigQuery are well-known columnar databases. They answer an analytics query, a question about many rows at once such as total sales per city, by reading only the few columns it names. Those columns also compress well, which means they fit in fewer bytes. The cost is slower writes and lookups of single rows.
Learn it in the course
This page is the free part.
₹499 in India$49 everywhere elseonce, for the whole course
The System Design course covers Columnar Storage across a run of lessons, not one page. These 4 alone are about 89 minutes of step-by-step reading, every one with a quiz.
Store data by column instead of by row, the secret behind modern analytics engines.
How real-time analytics engines query fresh data in under a second. Shows how Pinot ingests from Kafka and compares Druid, ClickHouse and Rockset.
Your database already cached the disk, so what a query cache saves is CPU, and that varies by 430 times between queries.
Each microservice owns its data, no shared databases, no secret backdoor queries, no coupled schemas.
all part of
System Design Masterclass
770 lessons · about 283 hours · 18 free to read
₹499in India, by UPI
$49everywhere else, by PayPal
The concepts were explained in a clear and structured way, with practical examples that made even complex system design topics easier to understand. I especially liked the focus on real-world architecture, scalability, trade-offs. The content was well designed, engaging, and highly useful for anyone looking to strengthen their system design skills. Highly recommended for software engineers preparing for system design interviews or wanting to build a stronger foundation in designing scalable systems.
You own this course
Continue with Columnar Storagepay once,
yours for life
A table has rows and columns. A disk stores bytes in one long line, so a database must choose an order for them. That choice is the storage layout.
A row store, also called row-oriented storage, writes one whole row, then the next whole row. PostgreSQL and MySQL, two popular databases, store tables this way. A column store, also called columnar storage or a column-oriented database, writes every value of the first column, then every value of the second column, and so on. Same table, same data, different order on disk.
Here is a small sales table with four rows:
Drawn from the lesson Columnar Storage.
A row store lays it out as: 1, Pen, 10, Pune, 2, Cup, 20, Goa, 3, Ink, 15, Goa, 4, Mug, 30, Pune.
A column store lays it out as: 1, 2, 3, 4, then Pen, Cup, Ink, Mug, then 10, 20, 15, 30, then Pune, Goa, Goa, Pune.
Now ask for the average price. In SQL, the language most databases understand, you write that question as SELECT AVG(price) FROM sales. The row store must read every row, because the prices are mixed in between the other values. The column store reads only the price values, 10, 20, 15, 30, and skips the rest. That one difference is the whole idea. My columnar storage lesson writes a real table both ways and measures it, so you see real bytes, not a sketch. If row storage is new to you, read row-oriented storage first.
This kind of work is called OLAP (online analytical processing). It means reading many rows to answer one big question. OLAP queries usually touch millions or billions of rows, but only a few columns. Columnar storage helps them in three ways.
First, it reads less. A disk reads data in fixed-size chunks called blocks, not one value at a time. In a row store, the block that holds a price also holds names and cities. The disk must read all of it to get the price. In a column store, a block of the price column holds only prices. I/O means reading from and writing to the disk. Amazon's Redshift documentation says columnar storage "drastically reduces the overall disk I/O requirements" for this reason.
A worked example, pure arithmetic: take a table with 100 columns and 1 billion rows. Assume every column takes 8 bytes per row. One row is then 800 bytes, and the table is 800 billion bytes, about 800 GB. A query that needs 3 columns reads 3 times 8 bytes times 1 billion, which is 24 GB. That is 3 percent of the table. Real columns have different widths. So the real saving can be bigger or smaller. The lesson measures it on a real table.
Second, it compresses well. Compression means storing data in fewer bytes. Values from one column have the same type and often repeat, so simple tricks work very well. Three are common:
A worked example of run-length encoding, again arithmetic: sort 1 billion sales rows by country. If there are 200 countries, the sorted country column has about 200 runs (a few more, because each file block starts its runs again). One billion values shrink to about 200 (value, count) pairs. On an unsorted column it does little, because neighbouring values rarely match. So column stores care a lot about sort order. Apache Parquet, a common file format for column data, uses all three ideas. Its documented encodings include dictionary encoding and a combined run-length and bit-packing encoding.
Third, the computer's processor does less wasted work. A traditional database handles one row at a time and pays a small extra cost for every row. A vectorised database handles a batch of values from one column in a single step. This style comes from a 2005 research paper by Boncz, Zukowski and Nes about a system called MonetDB/X100. The paper gives about 1,000 values as an example batch. DuckDB says its own engine is "columnar-vectorized" and was inspired by that paper.
The other kind of database work is OLTP (online transaction processing): place this order, update this user's email, show me customer 42. Each request touches one row or a few, but needs all of that row's columns. There are many requests per second. My comparison of OLTP vs OLAP puts the two side by side.
Columnar storage is a poor fit here. Take a table with 30 columns. To add one row, the database must write one value into each of 30 separate column files. A row store writes the whole row in one place. Reading is similar. SELECT * means "give me every column". To fetch customer 42 with SELECT *, a column store must find the 42nd value in every column and join those values back into one row. A row store finds the row in one place.
Updates are harder still, because compressed column blocks are usually not changed once written. ClickHouse's own page on columnar databases says column stores "are generally optimized for inserting or updating records in larger batches rather than making many small updates."
So the simple rule is this. Use a row store for the application's live data, and a column store for analysis over that data.
Real systems avoid many tiny writes. They collect rows first and write them in large sorted batches, stored column by column. Later, a program running in the background joins small batches into bigger ones.
C-Store, a research column store described by Stonebraker and others in 2005, set out this design. It had a small Writeable Store for fast inserts and updates, and a much larger Read-optimized Store for queries. A process the paper calls the tuple mover (tuple is the paper's word for a row) moved records from the first to the second in batches.
ClickHouse's MergeTree engine works in a similar way. Its documentation says every insert creates a new data part: a sorted, immutable piece of the table, with columns stored separately. Immutable means it is never changed after it is written. A background process keeps merging small parts into larger ones, which is where the name MergeTree comes from. So ClickHouse asks clients to insert in bulk, for example 20,000 rows at once. Clients can also turn on asynchronous inserts. Then ClickHouse collects rows from many small INSERTs in a memory area called a buffer. It writes a part only when the buffer is full enough or a set time has passed.
Another storage design, the LSM tree, uses the same idea: write sorted batches, merge them later. The ClickHouse docs say its storage layer works "similarly to LSM trees". My LSM trees lesson covers that design in detail.
Drawn from the lesson Columnar Storage.
You do not need a columnar database to get columnar storage. A columnar file format brings the same layout to plain files. These are often kept in a data lake, a large store of plain files, usually in cloud storage such as Amazon S3.
Apache Parquet is the most common. A Parquet file is split into row groups, which are slices of rows. Inside each row group, each column's data sits in its own column chunk. Each chunk is stored in one unbroken piece of the file. The file ends with metadata, a small summary that says where every column chunk starts. So a reader can jump straight to the columns it needs.
Apache ORC is similar. It was built to speed up Apache Hive, a tool for running SQL over Hadoop files. ORC files are split into stripes of about 64 MB by default, and the columns inside each stripe are stored apart. ORC also keeps small summaries of each stripe, so a reader can skip stripes that cannot hold the rows it is looking for.
Columnar databases build the same idea into a full system. The common ones:
What about PostgreSQL? Its normal tables are row stores. Add-ons called extensions can give PostgreSQL a columnar table type. Citus is one example: it adds a USING columnar option for analytics tables.
A data lakehouse keeps Parquet files in a lake and adds database features on top, so you can run normal SQL over those files. My data warehousing lesson shows where a column store sits in a full analytics system.
People often confuse these two. Apache Cassandra and Apache HBase are often called column databases, column-family stores or wide-column stores. They are not columnar storage in the sense on this page.
In a column-family store, data is still organised around rows. Each row is found by its key, a unique name for that row. A column family is a named group of columns that the database keeps together. The HBase guide says tables are "physically stored by column family", so a row's values within one family sit together. Cassandra's docs call its design "a partitioned wide-column storage model". They say fast queries give the partition key, the part of the key that decides which server holds the row. These systems are built for fast writes and lookups by key.
A columnar database is the reverse. It is built to scan one column across billions of rows. ClickHouse's page on columnar databases says it plainly: wide-column databases like Cassandra and HBase "are not the same as columnar databases." The column-family stores lesson comes straight after the columnar lesson in the course.
Use it when the work is mostly reading and summarising large amounts of data.
Avoid it as the main store for an application's live data. That work means many small inserts, frequent updates of single rows, and fetching one row by its unique ID (its primary key). A row store with an index, a lookup structure that finds a row fast, suits these much better. The usual design keeps both: a row store for the application and a column store for analysis, with data copied across.
This page used simple arithmetic. My Columnar Storage lesson builds a real 500,000-row table both ways and measures it, so you see real bytes, not a sketch. It measures compression for each column. It shows why the number of different values in a column decides how well it compresses. It has step-by-step diagrams and a quiz.
ClickHouse
Open source, and also sold as a cloud service. Its docs describe analytics queries over billions of rows that often must return in under a second.
Amazon Redshift
Records you load are converted to columnar storage automatically, column by column, so teams load rows and query columns.
Google BigQuery
Its columnar storage format is called Capacitor, according to Google's BigQuery storage overview.
Snowflake
Keeps metadata for every micro-partition and uses it to prune: a query scans only the micro-partitions and columns it needs.
Apache Parquet
The usual file format in data lakes. It writes its metadata after the data, so a file can be written in a single pass.
A columnar database stores each column's values together on disk instead of each row's values together. Analytics queries then read only the columns they need, and those columns compress well. ClickHouse, Amazon Redshift, Google BigQuery, Snowflake and DuckDB are columnar databases.
Row storage writes one whole row after another, which is fast for reading or writing single rows (OLTP). Columnar storage writes one whole column after another, which is fast for scanning a few columns across many rows (OLAP), but slower for single-row inserts and lookups.
No. Cassandra and HBase are wide-column or column-family stores. They organise data around rows, each found by its key, and are built for fast writes and key lookups. A columnar database stores each column separately to scan it quickly across many rows.
Values in one column have the same type and often repeat, such as country codes or status values. That suits dictionary encoding, run-length encoding and bit packing.
No. Apache Parquet is a columnar file format. It stores each column's data in its own unbroken chunk of the file. Engines such as Spark, DuckDB, BigQuery and Snowflake can read Parquet files and fetch only the columns a query needs.
Not by default. Standard PostgreSQL tables are row-oriented. Extensions can add a columnar table type; for example, Citus lets you create a table with USING columnar for analytics data.
Checked against these on .
Column-Family Store
A NoSQL database that groups columns into families, optimized for reading and writing large amounts of data across many machines. Cassandra and HBase use this model.
Data Warehouse
A central repository of structured, cleaned data optimized for analytical queries. Snowflake, BigQuery, and Redshift are purpose-built data warehouses.
LSM Tree
Log-Structured Merge Tree: a write-optimized data structure that buffers writes in memory and periodically flushes sorted runs to disk. Used by Cassandra, RocksDB, and LevelDB.
Data Lakehouse
A design that keeps data as open files, usually Parquet, in cheap object storage and adds a log on top that makes changes all-or-nothing, checks column types and keeps old versions readable.
Redis
An in-memory data store used as a cache, message broker, and database. Blazing fast because everything lives in RAM.
Memcached
A simple, high-performance distributed memory caching system. Stores key-value pairs in RAM. Simpler than Redis but less feature-rich.
This page explains the idea. The "Columnar Storage" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Columnar Storage lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Interview prep packs
Our System Design interview pack is being written and is not on sale yet. Until it is out, the System Design course teaches this end to end, and these interview packs are on sale now, each question with a full answer and a diagram.
System Design and Distributed Systems: about 200 questions, coming soon. Not on sale yet.
