Columnar Databases Explained: Why Analytics Queries Read Less Data

The reports get slow, and they slow the app down with them. A row database like PostgreSQL or MySQL is built to fetch and change a few rows fast. Columnar databases are built for the opposite job: scanning lakhs of rows.

Picture a common setup. A food delivery app keeps every order in one orders table with seven columns. The setup is made up, but the numbers below come from real test runs on generated data.

The seven columns are order_id, order_time, city, status, amount_paise, customer_id and address. Amounts are stored in paise (one rupee is 100 paise) as whole numbers. September had 10 lakh (1 million) orders.

The app reads and writes one order at a time, and that works well. Then the finance team asks for revenue per city, every morning, for the whole month.

That query needs only two of the seven columns. A row store's plain table scan still reads every row in full, address text included, to pick out two small values. This guide follows that one query from a row store to a column store.

You need five words. Each gets a plain meaning here, tied to the orders table.

• Row: one order, with all seven of its values. A row store keeps those seven values side by side on disk.
• Column: one field across all orders, such as every city value. A column store keeps those values side by side instead.
• Scan: reading through many rows to answer a query, as opposed to jumping to one row by its key.
• OLTP: online transaction processing. Many small reads and writes, each touching a few rows, like placing one order.
• OLAP: online analytical processing. Fewer, bigger queries that read a large share of the table, like revenue per city.

Columnar databases store all values of one column together, then the next column, and so on. A row database does the opposite and stores each row's values together.

PostgreSQL is a clear row store. Its docs describe each table as fixed-size pages, usually 8 kB. Each row sits in a page as a small header followed by that row's column values.

So in PostgreSQL, one order's city, amount and address sit next to each other. That is perfect when the app loads order 101 to show it on screen. Once the database finds the right page, that page holds the whole order.

A column store flips this. Every city value of a batch of orders sits in one stretch of the file. Every amount sits in another stretch. The address text sits in a third.

Here is an everyday picture. A row store is a box of order slips, one slip per order, every field on each slip. A column store is a set of ledgers, one ledger per field.

To total revenue per city, with slips you lift every slip. With ledgers you open just the city ledger and the amount ledger. Adding one new order is the reverse: one slip, or one line in each of seven ledgers.

Where the picture breaks: real column stores do not write one ledger line at a time. They collect many rows first, then write each column in large compressed blocks.

Column storage is not new. A 2005 research paper on a system called C-Store described a read-optimised database that stores data by column rather than by row. Parquet and DuckDB, used in this guide, follow the same basic idea.

You will meet column storage in two forms. Some are full databases with their own files, such as DuckDB. Others are open file formats, such as Apache Parquet, that many engines can read. This guide uses both.

Because the query only opens the columns it names, and the rest of the file is never read. In our test, revenue per city read about one sixth of the file.

We wrote the 10 lakh orders to a Parquet file with pyarrow 26.0.0, the Python library for Apache Arrow, using its default settings. Then we wrapped the file in a counter that adds up every byte the reader asks for.

Reading all seven columns pulled 27.82 MB, about the whole 27.76 MB file. Reading only city and amount_paise pulled 4.69 MB. That is about 17 percent of the file.

DuckDB 2.0.0 told the same story. We counted the bytes its process read from the file during the query, and it read 4.66 MB.

Notice which column is the biggest. The address text is a third of the file, and finance never asks about it. In a row store, that text rides along with every row the query scans.

Values in one column are the same type and often repeat, so simple tricks shrink them a lot. Our CSV file was 97.20 MB, and the default Parquet file was 27.76 MB, about 3.5 times smaller.

Three layers do the work. Each layer is a separate setting, so we turned them on one at a time.

Dictionary encoding: store each distinct value once

Dictionary encoding builds a short list of the distinct values in a column. Each row then stores only its position in that list, a small whole number.

Our city column has 8 distinct cities. Stored as plain text, it took 10.84 MB. With a dictionary, it took 0.38 MB, about 28 times smaller.

Eight entries need only 3 bits each, because 3 bits can count from 0 to 7. Storing each number in just the bits it needs is called bit-packing. Ten lakh rows at 3 bits is 3,75,000 bytes, which matches the 3,79,751 bytes we measured.

Dictionaries only pay off when values repeat. The Parquet spec says a writer falls back to plain encoding if the dictionary grows too big, in size or in number of distinct values.

Run-length encoding: store a repeated value once with a count

Run-length encoding, or RLE, stores a run of equal values as one value and a count. Ten thousand Bengaluru values back to back become, in effect, Bengaluru times 10,000.

RLE only helps when equal values sit next to each other. Our orders are in time order, so cities are mixed, and runs are short.

Pause and predict: what happens to the city column if you sort the table by city before writing it? We tried it.

The city column fell from 3,79,751 bytes to 2,904 bytes, about 130 times smaller. Parquet stores dictionary positions with a mix of RLE and bit-packing, so long runs collapse to almost nothing.

General-purpose codecs on top

After encoding, Parquet can squeeze each page with a general-purpose codec, meaning a compression method that works on any bytes, such as snappy or zstd. Snappy is pyarrow's default codec. Zstd made our file 16.18 MB instead of 27.76 MB.

DuckDB's own table files take a different route. Its 2022 compression blog says DuckDB uses only lightweight compression, picked for each column segment after scanning its data. The blog notes that general-purpose methods are expensive to decompress. DuckDB 2.0.0 also accepts zstd for table data if you force it with the force_compression setting, but it picked only lightweight methods for our table.

A row group is a horizontal slice of the table, such as one lakh rows, stored column by column. A zone map records the smallest and largest value of each column in each row group.

Zone maps are also called min-max statistics. Before reading a row group, the engine checks those two numbers. If the filter value cannot fall between them, it skips the whole row group.

Parquet keeps these numbers in the file footer, which is written after the data. DuckDB creates zone maps automatically for columns of all general-purpose data types, according to its indexing guide.

A filter on a sorted column

Finance now wants revenue per city for 15 September only. We wrote the file again with row groups of 1,00,000 rows, giving 10 row groups.

Because orders arrive in time order, each row group covers about three days. Row group 0 runs from 1 September to just after midnight on 4 September. Only row groups 4 and 5 contain 15 September.

The one-day query read 2.37 MB of the 33.65 MB file with pyarrow, and 2.45 MB with DuckDB. That is about 7 percent of the file.

The same query on the default file, which had only one row group, read 8.99 MB. With a single row group, there is nothing to skip, so the whole order_time column had to be read too.

A filter on an unsorted column

Now filter for orders of at least 1,499.90 rupees instead. Amounts are random, so every row group had a minimum near 99 rupees and a maximum near 1,500 rupees.

Every range overlapped the filter, so no row group could be skipped. The reader read the whole amount column to find 76 matching orders.

What just happened? Zone maps are only as good as the order of your data. DuckDB's indexing guide says the same: the more ordered the data in a column, the more useful its zone maps.

Vectorised execution means the engine runs each step of a query on a batch of values at once, not one row at a time. DuckDB's docs give its default batch, called a vector, as 2,048 values.

The idea comes from a 2005 research paper on a system called MonetDB/X100. It found that handling one row per function call caused high interpretation overhead and kept modern processors from working efficiently.

Working on about a thousand values per call spread that overhead out. The batch also stayed small enough to fit in the processor's fast cache memory.

You can feel the same effect in Python. We summed the 10 lakh amounts with a plain for loop, then with one NumPy call on the whole column.

The loop took 25.1 ms and the single call took 0.30 ms, about 83 times faster on our machine. The loop runs Python code for every value, while the single call runs one compiled loop over the whole array.

This demo shows the principle, not DuckDB's internals. DuckDB describes its own engine as columnar-vectorised, and contrasts it with systems such as PostgreSQL, MySQL and SQLite that process rows one by one.

Column storage and vectorised execution fit together. A column chunk is already a tight array of one type, which is exactly what a batch operation wants.

Because one row is spread across many places, and each place is packed and compressed in blocks. Adding one row touches every column, and every small statement pays a fixed setup cost.

Parquet files are written once

The Parquet spec says file metadata is written after the data, so a writer can produce the file in one pass. pyarrow offers no call to edit a value inside an existing file.

So we changed the status of one order by rewriting the file. That meant reading the 27.76 MB file and writing a new 27.75 MB file, which took about one second. One changed value cost a whole file.

DuckDB pays a cost per statement

DuckDB's docs warn that parsing and processing each INSERT statement has significant overhead. They advise against many row-by-row INSERTs when you insert more than a few rows.

We tested 1,000 single-row INSERTs inside one transaction on both engines. SQLite, a row store, took 9 to 24 ms over three runs. DuckDB 2.0.0 took 1.5 to 1.9 seconds.

Then DuckDB loaded 1,00,000 rows in one INSERT statement in 0.87 seconds. That is about 9 microseconds a row, against roughly 1.5 milliseconds a row one at a time.

Single-row lookups by primary key also favoured the row store. SQLite answered 1,000 of them in about 10 ms, and DuckDB took 0.7 to 0.8 seconds. These timings come from a 2-CPU cloud container; the ratios matter, not the exact numbers.

How column stores live with writes

Column stores batch writes instead. The C-Store paper paired a small writeable store for fast inserts with a large read-optimised store, and a background tuple mover shifting rows across in batches.

In practice, many teams do the same thing by hand. They keep the app's writes in a row database and copy changes in batches to the column store every few minutes or hours.

Move analytics out when big scans start to compete with the app's small queries, or when reports take too long. Keep the app's writes in the row database either way.

We loaded the same 10 lakh orders into SQLite and DuckDB on the same machine. Both files were on disk, and each test ran more than once.

Neither engine wins everywhere. The row store was about 30 to 40 times slower on the report, and the column store was about 70 to 90 times slower on single-row lookups.

That is why a common design runs both. The app talks to PostgreSQL or MySQL, and analytics reads a copy held in a column store or in Parquet files.

1. 1

   Measure the pain first

   Find the slow reports and check whether they scan most of a big table. Indexes rarely help a query that needs every row.

2. 2

   Try a read replica

   A replica, a read-only copy of the database, keeps report scans away from the app. It is still a row store, so scans stay slow.

3. 3

   Copy data out in batches

   Export changed rows every few minutes or hours into Parquet files or a column store. Big batches suit how column stores write.

4. 4

   Sort by the main filter

   Write the copy ordered by time or by whatever finance filters on most. That gives the min-max statistics something to skip.

5. 5

   Point reports at the copy

   Run dashboards against the column store, and keep the app on the row database. Agree how fresh the copy must be.

Our DuckDB 2.0 storage format guide covers what changed in DuckDB's own file format. Our Bigtable guide covers a different kind of store that is often confused with this one.

Read the Parquet footer with pyarrow, read DuckDB's query plan, the engine's step-by-step plan, with EXPLAIN, and list compression with pragma_storage_info, a built-in function. Each check takes a few lines.

These samples use orders.parquet, our 10 lakh orders written with row_group_size=100_000. They ran on pyarrow 26.0.0 and DuckDB 2.0.0.

import pyarrow.parquet as pq

md = pq.ParquetFile("orders.parquet").metadata
print(md.num_rows, md.num_row_groups)

col = md.row_group(0).column(2)  # column 2 is city
print(col.path_in_schema, col.encodings)
print(col.total_compressed_size, "bytes")

t = md.row_group(4).column(1).statistics  # order_time
print(t.min, t.max)

# 1000000 10
# city ('PLAIN', 'RLE', 'RLE_DICTIONARY')
# 38092 bytes
# 2026-09-12 23:49:55 2026-09-15 23:53:05

RLE_DICTIONARY in the encodings means dictionary encoding is on. The last line is row group 4's zone map for order_time. If you write a table with default settings, expect a single row group for up to 10,48,576 rows.

That default comes from pyarrow 26.0.0's source: a row group holds at most 1024 times 1024 rows unless you set row_group_size. The Parquet docs suggest large row groups of 512 MB to 1 GB, so tune for your engine and query pattern.

import duckdb

q = """
SELECT city, sum(amount_paise) / 100 AS rupees
FROM read_parquet('orders.parquet')
WHERE order_time BETWEEN TIMESTAMP '2026-09-15 00:00:00'
                     AND TIMESTAMP '2026-09-15 23:59:59.999999'
GROUP BY city
ORDER BY rupees DESC
LIMIT 3
"""
print(duckdb.sql(q).fetchall())
print(duckdb.sql("EXPLAIN " + q).fetchall()[0][1])

# [('Bengaluru', 5787155.53), ('Mumbai', 5275170.15), ('Delhi', 4759372.69)]
# The Read Parquet box in the plan shows:
#   Projections: city, amount_paise
#   Filters: order_time range for 15 September

In the plan, Projections lists the only columns DuckDB will read from the file. Filters shows the date range pushed down into the reader, where row-group statistics can use it. BETWEEN includes both ends, hence the last microsecond of the day.

import duckdb

con = duckdb.connect("orders.duckdb")
con.sql("CREATE OR REPLACE TABLE orders AS FROM 'orders.parquet'")
con.sql("CHECKPOINT")
con.sql("""
SELECT column_name, compression, count(*) AS segments
FROM pragma_storage_info('orders')
WHERE segment_type != 'VALIDITY'
GROUP BY ALL
ORDER BY column_name
""").show()

# address DICT_FSST, amount_paise BitPacking, city DICT_FSST,
# customer_id BitPacking, order_id BitPacking,
# order_time BitPacking, status DICT_FSST

CHECKPOINT makes sure the table is written to the database file before you look. The same function also lists each row group. In DuckDB 2.0.0, each row group of our table held at most 1,22,880 rows.

Lead with the workload, then the layout, then the cost. Interviewers want to hear why you would keep two stores, not only what a column store is.

1. 1

   Split the workloads

   Say which queries are OLTP, like placing an order, and which are OLAP, like revenue per city for a month.

2. 2

   Explain the layout

   A column store keeps each column together, so a query reads only the columns it names. Our revenue query read about 17 percent of the file.

3. 3

   Explain compression

   Similar values compress well with dictionary and run-length encoding. Sorting by a low-variety column makes runs long.

4. 4

   Explain skipping

   Row groups carry min and max values per column. Sorting by time lets a one-day query skip most of the file.

5. 5

   Name the cost and the fix

   Single-row writes are slow, so copy data from the row database in batches. State how fresh the copy needs to be.