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

Problem statement

The same question, asked of the same 13,472,195 rows, in two file formats (Example 4):

                                     TSV (1.79 GB)   Parquet (446 MB)
count distinct values in one column       0.56 s          0.01 s
aggregate two columns into a grid         0.42 s          0.07 s

Seventy-four times and six 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:

Question shape, columns read, compression and layout

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.19 MB      24.24%
count(*), max(length(alternatenames))     116.11 MB      26.01%

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.26 MB
requests                        5             221
query time                  0.01 s          1.50 s

Shuffling the rows made the file 66% larger and the same filtered query read 39 times more bytes and took 150 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,231,941 populated places with an index on the lookup column:

store             per lookup     lookups/s
python dict         0.0005 ms    2,022,337
SQLite + index      0.0093 ms      107,969
DuckDB + index      0.46 ms          2,186

DuckDB is 49 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.

Measuring both layouts on GeoNames

The data used on this page

The points are GeoNames' full allCountries dump — every feature, about 13.5 million, rebuilt daily at download.geonames.org, so counts drift a little between runs — kept both as the 1.79 GB text file and as places.parquet (also linked as geonames.parquet) with name, latitude, longitude, feature class, country and population. The polygons are Natural Earth's 1:10m admin-1 states and provinces, 4,596 of them, as provinces.shp.

import os
import zipfile

import duckdb
import requests


def fetch(url, path):
    if not os.path.exists(path):
        with requests.get(url, stream=True, timeout=600) as reply:
            reply.raise_for_status()
            with open(path, "wb") as out:
                for chunk in reply.iter_content(1 << 20):
                    out.write(chunk)


fetch("https://download.geonames.org/export/dump/allCountries.zip", "allCountries.zip")   # about 420 MB
if not os.path.exists("allCountries.txt"):
    zipfile.ZipFile("allCountries.zip").extract("allCountries.txt")
if not os.path.exists("places.parquet"):
    duckdb.sql("""
        copy (select column01 as name, column04 as lat, column05 as lon, column06 as feature_class,
                     column08 as country, column14 as population
              from read_csv('allCountries.txt', delim = '\\t', header = false, quote = '',
                            all_varchar = false, sample_size = -1))
        to 'places.parquet' (format parquet)""")
if not os.path.exists("geonames.parquet"):
    os.link("places.parquet", "geonames.parquet")
fetch("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_1_states_provinces.zip", "provinces.zip")
if not os.path.exists("provinces.shp"):
    with zipfile.ZipFile("provinces.zip") as z:
        for member in z.namelist():
            if member.startswith("ne_10m_admin_1_states_provinces."):
                open("provinces" + os.path.splitext(member)[1], "wb").write(z.read(member))
n = duckdb.sql("select count(*) from read_parquet('places.parquet')").fetchone()[0]
print(f"{n:,} GeoNames points; places.parquet {os.path.getsize('places.parquet') / 1e6:.0f} MB, "
      f"allCountries.txt {os.path.getsize('allCountries.txt') / 1e9:.2f} GB")

13,472,195 GeoNames points; places.parquet 306 MB, allCountries.txt 1.79 GB

For the layout comparisons the setup also writes all 19 GeoNames columns to full.parquet in the order the dump lists them, a copy with the rows shuffled, and starts a small HTTP server in its own process that serves the working directory and logs the bytes of every range request — so a remote read can be measured exactly.

import atexit
import subprocess
import sys

COLUMNS = ("geonameid BIGINT, name VARCHAR, asciiname VARCHAR, alternatenames VARCHAR, lat DOUBLE, lon DOUBLE, "
           "feature_class VARCHAR, feature_code VARCHAR, country VARCHAR, cc2 VARCHAR, admin1 VARCHAR, "
           "admin2 VARCHAR, admin3 VARCHAR, admin4 VARCHAR, population BIGINT, elevation VARCHAR, dem INTEGER, "
           "timezone VARCHAR, modified DATE")
TSV = ("read_csv('allCountries.txt', delim = '\\t', header = false, quote = '', columns = {"
       + ", ".join(f"'{c.split()[0]}': '{c.split()[1]}'" for c in COLUMNS.split(", ")) + "})")
if not os.path.exists("full.parquet"):
    duckdb.sql(f"copy (select * from {TSV}) to 'full.parquet' (format parquet, compression zstd)")
if not os.path.exists("shuffled.parquet"):
    duckdb.sql("select setseed(0.42)")
    duckdb.sql("copy (select * from read_parquet('full.parquet') order by random()) "
               "to 'shuffled.parquet' (format parquet, compression zstd)")

SERVER = """
import http.server, os
class Counting(http.server.SimpleHTTPRequestHandler):
    def do_GET(self):
        path = self.translate_path(self.path.split("?")[0])
        size = os.path.getsize(path)
        first, last = 0, size - 1
        if "Range" in self.headers:
            a, b = self.headers["Range"].split("=")[1].split("-")
            first, last = int(a), min(int(b) if b else size - 1, size - 1)
            self.send_response(206)
            self.send_header("Content-Range", f"bytes {first}-{last}/{size}")
        else:
            self.send_response(200)
        self.send_header("Content-Length", str(last - first + 1))
        self.send_header("Accept-Ranges", "bytes")
        self.end_headers()
        with open(path, "rb") as f:
            f.seek(first)
            self.wfile.write(f.read(last - first + 1))
        with open("requests.log", "a") as log:
            log.write(f"{last - first + 1}\\n")
    def do_HEAD(self):
        size = os.path.getsize(self.translate_path(self.path.split("?")[0]))
        self.send_response(200)
        self.send_header("Content-Length", str(size))
        self.send_header("Accept-Ranges", "bytes")
        self.end_headers()
    def log_message(self, *args):
        pass
server = http.server.ThreadingHTTPServer(("127.0.0.1", 0), Counting)
print(server.server_port, flush=True)
server.serve_forever()
"""
proc = subprocess.Popen([sys.executable, "-c", SERVER], stdout=subprocess.PIPE, stderr=subprocess.DEVNULL, text=True)
atexit.register(proc.terminate)
BASE = f"http://127.0.0.1:{proc.stdout.readline().strip()}"


def fetched(sql_template, path):
    """Requests and bytes the server sent while DuckDB ran the query against path over HTTP."""
    con = duckdb.connect()
    con.execute("install httpfs; load httpfs; set enable_external_file_cache = false")
    open("requests.log", "w").close()
    start = time.perf_counter()
    con.execute(sql_template.format(f"read_parquet('{BASE}/{path}')")).fetchall()
    seconds = time.perf_counter() - start
    sizes = [int(line) for line in open("requests.log")]
    return len(sizes), sum(sizes), seconds


import time

print(f"full.parquet {os.path.getsize('full.parquet') / 1e6:.0f} MB, "
      f"shuffled.parquet {os.path.getsize('shuffled.parquet') / 1e6:.0f} MB, served at {BASE.split(':')[0]}")

full.parquet 446 MB, shuffled.parquet 741 MB, served at http

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}×")

On the GeoNames dump — a tab-separated file with no header, so the reader is told its columns:

import shutil

if not os.path.exists("allCountries.csv"):
    duckdb.sql(f"copy (select * from {TSV}) to 'allCountries.csv' (header true)")
compare_layouts("allCountries.csv", "full.parquet")

csv        1,799 MB
parquet      446 MB  (0.25×)
count all rows         csv   0.62s   parquet   0.00s     244×
one column distinct    csv   0.45s   parquet   0.00s     111×
two-column aggregate   csv   0.40s   parquet   0.05s       8×

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}×")


parquet_layout("full.parquet")

13,472,195 rows in 109 row groups (123,598 rows each)

column                       compressed          raw  ratio
alternatenames                3,872,616   11,101,118    2.9×
name                            764,326    2,187,545    2.9×
asciiname                       712,892    1,990,072    2.8×
lon                             458,530      991,287    2.2×
lat                             452,878      991,287    2.2×
dem                             212,987      221,507    1.0×
geonameid                       131,448      991,287    7.5×
feature_code                     85,447      139,245    1.6×
admin2                           71,057       97,991    1.4×
modified                         70,467      174,234    2.5×

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")


write_sorted(duckdb.connect(), "read_parquet('shuffled.parquet')", "resorted.parquet", ["country"])

wrote resorted.parquet sorted by country
  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.

Example 4 — the measurements quoted on this page

import sqlite3

con = duckdb.connect()
con.execute("set enable_progress_bar = false")
for label, template in (("count distinct values in one column", "select count(distinct country) from {}"),
                        ("aggregate two columns into a grid", "select floor(lon), floor(lat), count(*) from {} group by 1, 2")):
    t = time.perf_counter(); con.execute(template.format(TSV)).fetchall(); tsv = time.perf_counter() - t
    t = time.perf_counter(); con.execute(template.format("read_parquet('full.parquet')")).fetchall()
    pq_s = time.perf_counter() - t
    print(f"{label:36} TSV {tsv:5.2f} s  Parquet {pq_s:5.2f} s  ({tsv / pq_s:.0f}×)")
print(f"TSV {os.path.getsize('allCountries.txt') / 1e9:.2f} GB, Parquet {os.path.getsize('full.parquet') / 1e6:.0f} MB; "
      f"{con.execute('select count(distinct country) from read_parquet(\'full.parquet\')').fetchone()[0]} distinct countries")

size = os.path.getsize("full.parquet")
for label, template in (("count(*)", "select count(*) from {}"),
                        ("count(distinct country)", "select count(distinct country) from {}"),
                        ("group by floor(lon), floor(lat)", "select floor(lon), floor(lat), count(*) from {} group by 1, 2"),
                        ("count(*), max(length(alternatenames))", "select count(*), max(length(alternatenames)) from {}")):
    n, sent, _ = fetched(template, "full.parquet")
    print(f"{label:40} {sent / 1e6:8.2f} MB  {sent / size:7.2%}  ({n} requests)")

FILTER = "select count(*) from {} where country = 'IS'"
for path in ("full.parquet", "shuffled.parquet"):
    n, sent, secs = fetched(FILTER, path)
    print(f"{path:17} {os.path.getsize(path) / 1e6:5.0f} MB  filter read {sent / 1e6:6.2f} MB in {n} requests, {secs:.2f} s")

places = con.execute("select geonameid, name from read_parquet('full.parquet') where feature_class = 'P'").df()
keys = places.geonameid.sample(2000, random_state=0).tolist()
lookup = dict(zip(places.geonameid, places.name))
t = time.perf_counter(); [lookup[k] for k in keys]; d = (time.perf_counter() - t) / len(keys)
lite = sqlite3.connect("places.sqlite")
if not lite.execute("select count(*) from sqlite_master where name = 'p'").fetchone()[0]:
    places.to_sql("p", lite, index=False)
    lite.execute("create index p_id on p(geonameid)")
t = time.perf_counter()
for k in keys:
    lite.execute("select name from p where geonameid = ?", (k,)).fetchone()
q = (time.perf_counter() - t) / len(keys)
db = duckdb.connect("places.duckdb")
if not db.execute("select count(*) from duckdb_tables() where table_name = 'p'").fetchone()[0]:
    db.execute("create table p as select * from places")
    db.execute("create index p_id on p(geonameid)")
t = time.perf_counter()
for k in keys[:200]:
    db.execute("select name from p where geonameid = ?", [k]).fetchone()
k_ = (time.perf_counter() - t) / 200
print(f"{len(places):,} populated places, one lookup by id:")
for label, secs in (("python dict", d), ("SQLite + index", q), ("DuckDB + index", k_)):
    print(f"  {label:16} {secs * 1000:9.4f} ms  {1 / secs:12,.0f} lookups/s")
print(f"load average {os.getloadavg()[0]:.1f}")

count distinct values in one column  TSV  0.56 s  Parquet  0.01 s  (74×)
aggregate two columns into a grid    TSV  0.42 s  Parquet  0.07 s  (6×)
TSV 1.79 GB, Parquet 446 MB; 253 distinct countries
count(*)                                     0.26 MB    0.06%  (1 requests)
count(distinct country)                      0.27 MB    0.06%  (110 requests)
group by floor(lon), floor(lat)            108.19 MB   24.24%  (110 requests)
count(*), max(length(alternatenames))      116.11 MB   26.01%  (110 requests)
full.parquet        446 MB  filter read   0.26 MB in 5 requests, 0.01 s
shuffled.parquet    741 MB  filter read  10.26 MB in 221 requests, 1.50 s
5,231,941 populated places, one lookup by id:
  python dict         0.0005 ms     2,022,337 lookups/s
  SQLite + index      0.0093 ms       107,969 lookups/s
  DuckDB + index      0.4575 ms         2,186 lookups/s
load average 4.2

Why a row store reads everything

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.

GeoParquet, row groups and select *

• 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.

Internal links

• DuckDB spatial explained: a spatial database that is just a file — the engine on top of this
• GeoParquet and columnar storage explained — the spatial columnar format
• How to query remote GeoParquet over HTTP with DuckDB — where the byte measurements come from
• How to query hive-partitioned GeoParquet in DuckDB — pruning at the file level
• DuckDB's memory model: streaming, spilling and limits — what happens above memory
• How to build an offline geocoder from open address data — where the single-row measurement comes from
• GIS file formats compared — the wider format landscape
• How to reduce GIS file size — compression in practice

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,472,195 rows: a one-column distinct count took 0.56 s from a 1.79 GB TSV and 0.01 s from a 446 MB Parquet file — 74 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.