columnar database

A database stores information in tables – with rows for individual entries and columns for properties like name, price, or date. Most classic databases store these tables row by row: first all values of row 1, then all of row 2, and so on. A columnar database flips this around. It lays down all the values of a column one after another – first all the prices, then all the dates, then all the names. On a normal hard drive or a fast flash storage, related values stored together like this can be read much faster. The difference sounds small, but it has an enormous impact on how fast an analysis runs.

Why the order on disk matters

Imagine you want to calculate the average price from a table with a million sales. For this, you only need the “price” column – not the customers' names, not their addresses, not the date. A row-oriented database still reads every complete row from storage, because the prices are scattered throughout. This costs time and energy.

A columnar database, in this case, only reads that one column. That’s a fraction of the data volume. In practice, this can mean: an analysis runs in seconds instead of minutes. For companies that evaluate billions of data points daily, this makes an enormous difference – both in speed and in server costs.

There’s also a second advantage: values in a column often share the same type and similar magnitudes. This can be compressed well, meaning encoded in a space-saving way. A column containing the value “EUR” a thousand times takes up barely more space than a single “EUR” after compression. Row-oriented databases barely benefit from this kind of compression, because the values within a row are too different from one another.

How reading and writing work internally

When reading, the database only retrieves the file segments that belong to the requested columns. Modern systems read these segments in parallel – meaning several columns at once. This works particularly well on servers with many processor cores.

There is a downside when writing. Anyone inserting a single new record has to write each of its values into the respective matching column file – meaning many small write operations at different locations. This is slower than with a row-oriented database, which simply appends a new row at the end. Columnar databases are therefore better suited to scenarios where large amounts of data are loaded in bulk and afterward read much more often than they are modified. This is also called a “write-light, read-heavy” use case.

Many systems solve this problem with a trick: new data first lands in a fast in-memory buffer that operates row by row. Only later is it transferred into the columnar structure. This way, the system combines the strengths of both approaches.

Columnar databases in products and news

The term mainly appears in reports about data analytics, business intelligence, and AI infrastructure. Well-known systems include Google BigQuery, Amazon Redshift, Snowflake, and the open-source project DuckDB. They are all columnar and designed for analytical queries on very large datasets.

They are also relevant in the AI context. Anyone training a language model or running analyses on user data works with tables that can have billions of rows. Columnar databases make it possible to ask questions over such data in a reasonable amount of time – for example: “Which users have queried the model more than a hundred times in the last 30 days?” This would be significantly slower with a classic row-oriented database.

A common misconception is equating columnar databases with NoSQL databases. That’s not the same thing. NoSQL refers to databases that don’t use a classic table format – for example, those that store data as documents or graphs. A columnar database, on the other hand, can manage completely normal tables and even speak the SQL query language. The difference lies solely in how the data is internally arranged on storage.