Inside the engine: how a columnar database reads so little

Ask Pondside's warehouse for two years of café sales by month and the answer is back before you have let go of the return key. DuckDB is not reading two years of payments very quickly. It is finding a way to read almost none of them.

In part two we took that speed on trust. This part opens the engine. You do not need any of it to use a warehouse, the way you do not need to understand a gearbox to drive. But a few minutes here will change how you design tables and write queries, and every idea transfers to the other analytical systems you will meet, from Parquet files to the big cloud warehouses. The examples use Pondside's sample data and a million synthetic rows you can generate yourself.

Two ways to lay out a table

A table looks like a grid, but a disk is a long line of bytes. Every database has to decide how to flatten the grid into the line, and there are two natural choices.

Row-oriented

Write the first row’s values side by side, then the second row’s, and so on. Postgres, MySQL and SQLite store tables this way.

Column-oriented

Write every value of the first column together, then every value of the second. DuckDB, Parquet files and nearly every analytical database do this.

Neither is better in general. Each is better at something. If your job is to show customer 4,812 their order, you want all of that order's values in one place, and a row store fetches the lot in a single read. If your job is to total the amount column across ninety thousand payments, you want all the amounts in one place, and a row store makes you read every other column to get at them.

That is the whole difference between the database behind an app and the database behind a report, which part one called OLTP and OLAP. Everything else in this part follows from choosing columns.

Why analytics touches so few columns

Real business tables are wide. Pondside's square.payments has seventeen columns as synced; a real Stripe or Shopify table has dozens, plus a JSON copy of the full record. Yet look at the questions people ask: sales by month needs a date and an amount. Tips by barista needs a staff member and a tip. Rarely does a question need more than four or five columns, whatever the width of the table.

A column store makes the cost of a query depend on the columns it asks for, not the width of the table it asks them of.

So when DuckDB runs Pondside's monthly sales query from part two, it reads four of the seventeen columns: created_at, status, amount and tip_amount. The other thirteen stay on disk, untouched. This is called projection pushdown, and it is the reason a SELECT * over a wide table is the slowest habit in analytics: it switches off the single biggest saving the engine has.

Compression: columns are repetitive, and repetition compresses

Storing values by column has a second effect. The values next to each other are now the same kind of thing, and often nearly the same thing. A status column is COMPLETED ninety-nine times in a hundred. A currency column is USD every time. Amounts sit in a narrow range. Dates arrive in order. That sameness is what compression feeds on.

DuckDB does not use one general-purpose compressor for everything. For each column, in each block of rows, it tries a set of lightweight methods and keeps whichever makes that block smallest. “Lightweight” matters: these methods are cheap enough to decode that reading compressed data is usually faster than reading it uncompressed, because the bottleneck is how many bytes come off the disk, not how many the processor has to unpack.

You can see what DuckDB chose. pragma_storage_info lists every segment of every column, with its compression and the statistics DuckDB keeps about it.

SELECT column_name, compression, count(*) AS segments
FROM pragma_storage_info('square.payments')
WHERE segment_type <> 'VALIDITY'
GROUP BY ALL
ORDER BY column_name;

On a pondside.duckdb file built with DuckDB 1.5, the café's payments come out like this, and the choices read like common sense:

Skipping what cannot match: row groups and zone maps

Reading four columns instead of seventeen is a big saving. The next one is reading only part of those four.

DuckDB splits every table into row groups of 122,880 rows. For each column in each row group it keeps a few statistics, most importantly the smallest and largest value. These are often called zone maps, or min-max indexes, and DuckDB creates them automatically for every table, with no CREATE INDEX needed. When a query filters on a column, DuckDB first checks the filter against each row group's minimum and maximum. If a group's dates all fall before September, a query for September cannot find anything there, and the group is skipped without being read.

Pondside's payments fit in a single row group, so to watch this happen we need something bigger. Here is a million rows, one per cup of coffee poured, in the order they were poured:

CREATE OR REPLACE TEMP TABLE cups_poured AS
SELECT
  i AS cup_id,
  DATE '2025-01-01' + CAST(i // 1570 AS INTEGER) AS poured_on,
  CAST(300 + i % 7 * 25 AS SMALLINT) AS price_cents
FROM range(1000000) t(i);

Each row has a position, its rowid, and rows are stored in row groups in that order. Grouping by rowid // 122880 shows what the zone map of each row group holds for poured_on:

SELECT
  rowid // 122880 AS row_group,
  min(poured_on) AS first_day,
  max(poured_on) AS last_day,
  count(*) AS cups
FROM cups_poured
GROUP BY ALL
ORDER BY row_group;

Each group covers a tidy, separate stretch of dates. A question about September only has to open the last two. Now shuffle the same million rows, so they are stored in no particular order, and look again:

CREATE OR REPLACE TEMP TABLE cups_shuffled AS
FROM cups_poured
ORDER BY (cup_id * 7919) % 1000003;

SELECT
  rowid // 122880 AS row_group,
  min(poured_on) AS first_day,
  max(poured_on) AS last_day
FROM cups_shuffled
GROUP BY ALL
ORDER BY row_group;

Every group now spans every date, so every group might contain September, and every group has to be read. Same data, same query, nine times the work. Zone maps are only as good as the order the data is stored in.

The grouping above is a stand-in, so you can see the idea with ordinary SQL. The real statistics are in pragma_storage_info, in its stats column, one line per segment:

SELECT row_group_id, stats
FROM pragma_storage_info('cups_poured')
WHERE column_name = 'poured_on' AND segment_type = 'DATE'
ORDER BY row_group_id;

Vectors: a few thousand values at a time

Once DuckDB has decided what to read, it still has to compute the answer, and here too it does something different from a traditional database.

The classic way to run a query, used since the 1990s, passes one row at a time through a chain of operators: scan hands a row to filter, filter hands it to sum, sum asks scan for the next row. It is elegant and easy to build. It is also slow on modern processors, because the time goes on the handing over, not the arithmetic. Each row pays for several function calls to have one number added.

DuckDB's operators exchange vectors instead: up to 2,048 values of one column at a time. The filter receives 2,048 dates and returns which of them pass. The sum receives 2,048 amounts and adds them in a tight loop. The overhead of handing over is paid once per 2,048 values instead of once per value, and a tight loop over an array of numbers is exactly what processors are built to run quickly, using their cache and, where they can, instructions that work on several values at once.

Every core, and past the edge of memory

A modern machine has many cores, and DuckDB uses all of them for a single query. It splits the work into small pieces, called morsels, roughly a row group's worth of rows each, and lets every thread take the next morsel as soon as it finishes one. Fast threads simply take more. Nobody waits for a slow worker to finish a big fixed share, which is what made older ways of parallelizing queries uneven.

Memory is the other limit. By default DuckDB allows itself 80 percent of the machine's memory. When a sort, join, window or grouping needs more than that, it writes the excess to temporary files on disk and keeps going, more slowly, rather than stopping with an out-of-memory error. This is what lets a small machine answer a big question eventually instead of not at all.

-- Leave room for other programs on a shared machine.
SET memory_limit = '4GB';
-- Where spilled data goes, if not beside the database file.
SET temp_directory = '/tmp/duckdb_spill';

The same ideas, in a file: Parquet

Everything above also describes Apache Parquet, the open file format most analytical data now travels in. It was created in 2013, and it is column-oriented for the same reasons DuckDB is. A Parquet file is divided into row groups; each row group holds one column chunk per column; each chunk is split into compressed pages. At the very end of the file sits a footer with the schema, the location of every chunk, and the minimum, maximum and null count of each column chunk: a zone map, written into the file.

That is why DuckDB can query a large Parquet file on a web server or in a bucket without downloading it. It reads the footer first, works out which row groups could match the filter and which column chunks the query names, and fetches only those byte ranges. You can read the footer yourself:

SELECT row_group_id, path_in_schema, stats_min, stats_max, compression
FROM parquet_metadata('cafe_payments.parquet')
WHERE path_in_schema = 'created_at';

The same habit applies, too: a Parquet file written in time order has row groups with narrow date ranges, and filters on time skip most of the file.

Habits that follow

None of this asks you to tune anything. It asks for a few habits, each of which follows from the above.

• Name the columns you need. SELECT * is fine for a look at ten rows. In a query that runs every day, or in a view that others build on, list the columns, so the engine can skip the rest.
• Filter on the column itself. WHERE sold_on >= DATE '2026-09-01' can be checked against zone maps directly. Wrap the column in a calculation and the engine may have to compute it for every row before it knows what to skip.
• Keep data in a useful order. Time order, usually. It is free when data arrives that way and one ORDER BY when you rebuild a table.
• Use the right type. Dates as DATE, not text; money as integer cents or DECIMAL; small numbers in small integer types. Right-sized types compress better, compare faster, and give zone maps something meaningful to hold.
• Ask the engine. When a query is slower than you expect, EXPLAIN ANALYZE shows what DuckDB did, step by step, with rows and time for each.

EXPLAIN ANALYZE
SELECT count(*), sum(price_cents)
FROM cups_poured
WHERE poured_on >= DATE '2026-09-01';

At the bottom of the plan is the TABLE_SCAN of cups_poured. Its Filters line shows the date condition pushed down into the scan itself, where the zone maps can use it, and its Projections line shows price_cents as the only other column it reads. Above it, the row counts show how little made it through: 45,440 cups out of a million.

Where the remaining time goes

For a small warehouse, almost all of this happens below the threshold of noticing. The engine is rarely the slow part. The slow part is upstream: getting data out of a dozen tools, on time, complete, without double counting, which is what part four is about. A fast engine fed unreliable copies gives you wrong answers quickly.