🧭 Concept DuckDB & analytic SQL Performance & scale DuckDB for Spatial Data · step 2 of 20 Updated

Columnar or Row Storage: Why DuckDB Is Fast on Wide Tables

Problem statement

The same question, asked of the same 13,464,017 rows, in two file formats:

                                     TSV (1.79 GB)   Parquet (446 MB)
count distinct values in one column       1.30 s          0.03 s
aggregate two columns into a grid         1.01 s          0.06 s

Forty-three times and seventeen times. The data is identical, the machine is identical, and the query is identical. The only difference is how the bytes are arranged on disk.

That difference — rows together, or columns together — decides most of what an analytical engine can and cannot do quickly, and it explains both why DuckDB is fast on a wide table and why it is slow at fetching a single row.

Quick answer

Row storage keeps a whole record together; columnar storage keeps a whole column together.

ROW-ORIENTED  (CSV, shapefile .dbf, Postgres heap)
  [id=1, name=Paris, lat=48.8, lon=2.3, pop=2138551, geom=…]
  [id=2, name=Lyon,  lat=45.7, lon=4.8, pop=513275,  geom=…]
  → reading one column means touching every record

COLUMN-ORIENTED  (Parquet, GeoParquet, DuckDB's own storage)
  id   [1, 2, 3, …]
  name [Paris, Lyon, …]
  lat  [48.8, 45.7, …]
  pop  [2138551, 513275, …]
  → reading one column means reading one contiguous run of bytes

The consequences follow mechanically:

Operation Row store Column store
select * where id = 42 fast slow
select avg(pop) from 13M rows slow fast
adding a row cheap expensive
compressing a column poor excellent
Two panels showing row-oriented records against column-oriented storage.
A query touching three columns of nineteen reads three columns.

Step-by-step solution

1. Recognise which shape your question has

Two questions, and almost everything is one or the other:

  • Analytical (OLAP) — "what is the average population per province?", "how many points fall in each polygon?", "which category has the most rows?". Touches few columns and many rows.
  • Transactional (OLTP) — "give me the record with this id", "update this feature's attribute". Touches one row and all its columns.

A GIS workflow is usually analytical, which is why a columnar engine fits it. A GIS application — a web map fetching one feature per click — is transactional, and needs a row store.

2. Count the columns your query actually reads

Columnar formats let the engine skip columns entirely. Measured against a 19-column, 446 MB Parquet file served over HTTP with every byte counted:

query                                   bytes read   share of file
count(*)                                    0.26 MB       0.06%
count(distinct country)                     0.27 MB       0.06%
group by floor(lon), floor(lat)           108.11 MB      24.24%
count(*), max(length(alternatenames))     115.62 MB      25.92%

The two-column aggregation read a quarter of the file. The one-column count read a sixteen-hundredth. No row-oriented format can do that, because the columns are interleaved.

3. Understand why columns compress so well

A column holds one type of value, and values in one column are similar to each other. That gives compression algorithms far more to work with:

  • Dictionary encoding — 253 distinct country codes across 13.4 million rows become 253 strings plus a small integer per row.
  • Run-length encoding — sorted or clustered values collapse to (value, count) pairs.
  • Bit packing — an integer column whose values fit in 12 bits stores 12 bits, not 64.

Measured: the same data was 1.79 GB as TSV and 446 MB as Parquet with zstd — a factor of four. And the country column, dictionary-encoded across 109 row groups, read back in 270 kB.

4. Know that layout is part of the format

Compression depends on the data being arranged well, not merely on the format. The same file, shuffled:

                       sorted file   shuffled file
file size                  446 MB        741 MB
bytes read for a filter    0.26 MB        10.25 MB
requests                        4             222
query time                  0.01 s          2.28 s

Shuffling the rows made the file 66% larger and the same filtered query read 39 times more bytes and took 200 times longer. Sorting on the column you filter by is a storage decision with a query-time payoff.

5. Accept the trade-off at the single-row end

The same properties that make a columnar engine fast on aggregates make it slow on point lookups. Measured on 5,226,942 rows with an index on the lookup column:

store             per lookup     lookups/s
python dict         0.0007 ms    1,476,388
SQLite + index      0.058 ms        17,217
DuckDB + index      13.4 ms             75

DuckDB is 230 times slower than SQLite here, and it is not a defect: reconstructing one row means visiting every column's storage, and the whole vectorised machinery is set up for one value.

6. Choose the format from the access pattern

  • Parquet / GeoParquet — analytical reads, archival, anything remote. The default for a modern GIS pipeline.
  • GeoPackage / SQLite — mixed reads and writes, single-row lookups, an application's data store.
  • Shapefile — because somebody sent you one.
  • PostGIS — many writers, transactions, a system of record.
Bar chart of query times from a TSV and from Parquet for two queries.
The row store has to parse every field of every row to reach one column.

Code examples

Example 1 — measuring the two layouts on your own data 

import duckdb
import os
import time


def compare_layouts(csv_path, parquet_path=None):
    """Convert once, then time the same queries against both."""
    con = duckdb.connect()
    con.execute("set enable_progress_bar = false")
    parquet_path = parquet_path or csv_path.replace(".csv", ".parquet")

    if not os.path.exists(parquet_path):
        started = time.perf_counter()
        con.execute(f"""copy (select * from read_csv('{csv_path}'))
                        to '{parquet_path}' (format parquet, compression zstd)""")
        print(f"converted in {time.perf_counter() - started:.1f}s")

    print(f"csv     {os.path.getsize(csv_path) / 1e6:8,.0f} MB")
    print(f"parquet {os.path.getsize(parquet_path) / 1e6:8,.0f} MB  "
          f"({os.path.getsize(parquet_path) / os.path.getsize(csv_path):.2f}×)")

    queries = {
        "count all rows": "select count(*) from {}",
        "one column distinct": "select count(distinct country) from {}",
        "two-column aggregate": ("select floor(lon), floor(lat), count(*) "
                                 "from {} group by 1, 2"),
    }
    for label, template in queries.items():
        row = f"read_csv('{csv_path}')"
        col = f"read_parquet('{parquet_path}')"
        t0 = time.perf_counter(); con.execute(template.format(row)).fetchall()
        row_secs = time.perf_counter() - t0
        t0 = time.perf_counter(); con.execute(template.format(col)).fetchall()
        col_secs = time.perf_counter() - t0
        print(f"{label:22} csv {row_secs:6.2f}s   parquet {col_secs:6.2f}s   "
              f"{row_secs / max(col_secs, 1e-9):5.0f}×")

Example 2 — inspecting a Parquet file's internal layout 

import pyarrow.parquet as pq


def parquet_layout(path):
    """Row groups, column sizes and how much each column costs to read."""
    meta = pq.read_metadata(path)
    print(f"{meta.num_rows:,} rows in {meta.num_row_groups} row groups "
          f"({meta.num_rows // max(1, meta.num_row_groups):,} rows each)")

    group = meta.row_group(0)
    rows = []
    for i in range(group.num_columns):
        column = group.column(i)
        rows.append((column.path_in_schema, column.total_compressed_size,
                     column.total_uncompressed_size, str(column.encodings)))

    rows.sort(key=lambda r: -r[1])
    print(f"\n{'column':26} {'compressed':>12} {'raw':>12} {'ratio':>6}")
    for name, comp, raw, _ in rows[:10]:
        print(f"{name:26} {comp:12,} {raw:12,} {raw / max(comp, 1):6.1f}×")

The ratio column is where the story is: a low-cardinality string column often compresses a hundredfold, while a high-entropy one — a free-text name, a WKB geometry — barely compresses at all.

Example 3 — sorting for the queries you actually run 

def write_sorted(con, source, target, sort_columns, compression="zstd"):
    """Clustering rows by the column you filter on makes row-group pruning work."""
    order = ", ".join(sort_columns)
    con.execute(f"""
        copy (select * from {source} order by {order})
        to '{target}' (format parquet, compression {compression})
    """)
    print(f"wrote {target} sorted by {order}")
    print("  filters on those columns can now skip whole row groups")

Row-group statistics record the minimum and maximum of each column in each group. When the file is sorted on the filter column, most groups can be skipped without being read — which is exactly the 0.26 MB versus 10.25 MB difference measured above.

Explanation

Why the row store has to read everything

In a CSV or a .dbf, the fields of one record are adjacent and the records follow one another. To read the tenth field of every record, the reader must walk the entire file, parsing delimiters, because there is no way to know where field ten of record 4,000,000 begins without having read everything before it.

That is the 1.30 s in the opening measurement: the engine parsed 1.79 GB to count 253 distinct country codes. The columnar version read 25 MB of one column's storage and did the same work in 0.03 s.

Why compression is a query optimisation, not just storage

Every byte not read is a byte not decompressed, not transferred and not parsed. A dictionary-encoded country column that compresses 100× is 100× less I/O for any query touching it.

This is why the columnar file was both smaller and faster: 446 MB against 1.79 GB on disk, and 43× faster on a one-column query. Storage efficiency and query speed are the same property viewed twice.

Why sorting matters as much as the format

Parquet stores per-column, per-row-group minimum and maximum values. A query with where country = 'AD' can consult those statistics and skip any group whose range excludes the value.

That only works if the values are clustered. In the measured comparison, the sorted file answered the filter by reading 4 chunks totalling 0.26 MB; the shuffled file — same rows, same schema — had to read 222 chunks totalling 10.25 MB, because every group contained some rows matching every value.

Why single-row lookups are the price

Reconstructing one complete row from columnar storage means locating and decoding that row's position in every column. Each column is a separate read, and none of the vectorised machinery helps when the vector has one element in it.

Row stores do the opposite: one seek, one contiguous read, the whole record. Hence the measured 13.4 ms against 0.058 ms. The right response is not to tune DuckDB, it is to use a row store for that access pattern — which is exactly what GeoPackage and SQLite are for.

Grid comparing bytes, requests, time and file size for a sorted and a shuffled Parquet file.
Row-group statistics can only prune when the values are clustered.

Edge cases or notes

  • GeoParquet is Parquet with geometry stored as WKB plus a geo metadata key, so all of this applies to it.
  • Row-group size is a tuning knob. Too large and pruning is coarse; too small and the metadata dominates.
  • Geometry columns compress badly — WKB is high-entropy. Expect the geometry to dominate a spatial Parquet file's size.
  • select * gives up the main advantage. Name the columns you need.
  • Appending to Parquet means writing a new file; it is an archival format, not a working store.
  • Shapefile .dbf is row-oriented and has a 2 GB limit and a 10-character field-name limit besides.
  • Sorting costs time once and saves it on every subsequent query.
  • A columnar file over HTTP turns column pruning into bandwidth saved, which is the whole cloud-native argument.

FAQ

What is columnar storage?

A file layout that keeps each column's values together instead of each row's. A query touching three columns of a nineteen-column table reads three columns' worth of bytes.

How much faster is Parquet than CSV?

Measured on 13,464,017 rows: a one-column distinct count took 1.30 s from a 1.79 GB TSV and 0.03 s from a 446 MB Parquet file — 43 times faster, on the same data.

Why is DuckDB slow at fetching a single row?

Because reconstructing one row means visiting every column's storage. Measured, an indexed point lookup took 13.4 ms in DuckDB and 0.058 ms in SQLite.

Does sorting my Parquet file matter?

Substantially. The same filter read 0.26 MB from a sorted file and 10.25 MB from a shuffled one, and the shuffled file was also 66% larger.

Should I convert all my GIS data to Parquet?

Convert what you analyse. Keep a row-oriented store — GeoPackage, SQLite, PostGIS — for anything that fetches or updates individual features.

Does GeoParquet compress geometry?

Poorly. WKB is high-entropy, so geometry usually dominates a spatial Parquet file's size while the attribute columns compress heavily.