Your Database Is 40x Too Slow

Your analytics queries likely run significantly slower than necessary. Consider an aggregate query on 100 million rows: with Postgres, this operation takes a staggering 9.7 seconds. DuckDB, a column-store database, completes the identical query in just 0.24 seconds, demonstrating over a 40x speed improvement. This stark difference reveals a fundamental architectural bottleneck inherent in many traditional databases.

Traditional relational databases like Postgres are row-stores. They organize data on disk by row, meaning an entire row is retrieved from storage even if a query only needs a single column's value from that row. For analytical workloads that aggregate data across many rows but few columns, this design forces the database to read vast amounts of irrelevant data, wasting valuable I/O bandwidth and CPU cycles.

Column-stores like DuckDB and ClickHouse reverse this paradigm. They store data grouped by column. When an aggregate query targets a specific column, the database only reads the data for that particular column from disk, entirely skipping all other columns in the table. This drastically reduces the amount of data processed, leading to the observed performance gains.

Columnar databases achieve their stunning speed by performing orders of magnitude less work. Instead of scanning every row, they employ clever indexing and metadata strategies to skip irrelevant data. This efficiency stems from their column-oriented storage, which groups data by column rather than row, optimizing for analytical queries that often aggregate subsets of columns.

ClickHouse, for instance, does not index individual rows; it relies on a sparse primary key defined on a sorted column like a timestamp. As data is ingested, it's sorted and split into fixed-size blocks of approximately 8,192 rows, known as granules. ClickHouse stores only the first timestamp of each granule, creating about 12,000 small, in-memory notes for a 100 million-row dataset.

When a query requests data for a specific period, like March, ClickHouse quickly scans these in-memory notes, identifying and reading only the relevant granules from disk. In our benchmark, this meant reading just 1,633 out of 12,208 blocks, ignoring over 86% of the dataset. DuckDB uses a similar, highly effective trick: it stores min/max metadata for each column chunk. This allows DuckDB to prove a chunk is irrelevant to the query—for example, a chunk spanning January to February—and skip its contents entirely without reading.

Columnar databases excel at aggregate queries, but their architecture introduces significant trade-offs for other common operations. A point lookup query, retrieving a single row by ID, reveals this stark contrast. with Postgres, this takes a mere 2 milliseconds thanks to its binary tree index, which efficiently navigates to the exact data page containing the requested row.

ClickHouse, however, struggles with such queries, clocking in at 168 milliseconds. Its sparse primary key, sorted by timestamp and then ID, offers no direct path to an arbitrary ID without scanning all 12,208 data blocks. Even after locating the row, it must reconstruct the full record by opening and stitching together all eight column files, a considerable overhead for a seemingly simple fetch.

Updating a single row further exposes the architectural divergence. with Postgres, an update completes in 5 milliseconds, rewriting one row and updating one index entry. ClickHouse, conversely, takes a staggering 5.8 seconds for the same operation. This is due to its immutable data files; modifying a single value necessitates rewriting the entire revenue column file for that affected data chunk.

These performance characteristics are not design flaws but inherent specializations. Row-oriented databases like Postgres are optimized for transactional workloads (OLTP), where frequent single-row inserts, updates, and lookups are paramount. Columnar databases, by design, prioritize analytical workloads (OLAP), where aggregate queries over vast datasets dominate, making their strengths and weaknesses distinct based on the use case.

Two distinct columnar options exist for accelerating analytical queries: ClickHouse and DuckDB. whilst both deliver impressive performance gains over with Postgres for aggregate operations, their architectural philosophies diverge significantly, dictating disparate ideal applications.

ClickHouse operates as a full-featured, server-based system, purpose-built for production data warehouses. It functions much like Postgres, requiring a running server instance and supporting concurrent multi-user access for robust, shared analytics environments.

DuckDB, conversely, provides an embedded library experience, akin to SQLite for analytical workloads. It runs in-process with your application, storing its entire database within a single file on disk, making it ideal for local, client-side data manipulation.

For a scalable, shared analytics backend supporting multiple users and large datasets, ClickHouse is your weapon of choice. It handles high-throughput ingestion and complex aggregate queries across petabytes of data in a production setting.

Choose DuckDB to supercharge local data analysis, power single-node applications, or accelerate lightning-fast ETL jobs. Its in-process nature and single-file database simplify deployment for individual data scientists or smaller-scale internal tools.

If you need a robust, multi-user analytics platform, ignore DuckDB. If your needs are confined to local, single-user processing, ClickHouse's server overhead is unnecessary.

What is the main difference between a columnar and a row-based database?

A row-based database (like Postgres) stores all data for a single record together. A columnar database (like ClickHouse) stores all values for a single column together, which is far more efficient for analytical queries.

When should I use a columnar database?

Use a columnar database for analytical workloads (OLAP) that involve aggregating or filtering a few columns across millions or billions of rows. They are ideal for dashboards, business intelligence, and analytics platforms.

Why are columnar databases slow for single-row updates?

Their data files are optimized for bulk ingestion and are often immutable. Updating a single value can require rewriting an entire large chunk of a column file, making it much slower than transactional databases.

Is DuckDB a replacement for Postgres?

No, they serve different primary purposes. DuckDB is an embedded analytical database (like SQLite for analytics), ideal for in-process data analysis. Postgres is a general-purpose transactional database (OLTP) designed to be a system of record.