Columnar Databases Are Incredibly Fast. Here's Why.

EnglishالعربيةDeutschEspañolFrançaisहिन्दीBahasa Indonesia日本語한국어PortuguêsРусский中文
BBetter Stack
Computing/Software

Transcript

00:00:00whilst i absolutely love postgres it's not the best for everything there's a different sort of
00:00:04database called a columnar database that can run certain queries up to 40 times faster these
00:00:10databases work by storing columns together rather than rows which means you get massive benefits
00:00:15for certain use cases like analytics platforms now of course everything comes with a trade-off
00:00:20but today we'll look at exactly what columnar databases are and compare a few queries against
00:00:25postgres to see the benefits and the drawbacks we'll compare postgres with clickhouse and duckdb
00:00:31so stick around and by the end you'll have a solid understanding of column-based databases
00:00:36and when to reach for the right technology
00:00:43so at the start i said you can run certain queries 40 times faster and that is true if we take a
00:00:49database table here i've loaded in a hundred million rows and i'll run a group by query with
00:00:55postgres this takes roughly 9.7 seconds to execute because we're aggregating revenue from every
00:01:00single row if we run the same query across the same data set using a columnar database and that's
00:01:06running literally the exact same sql both clickhouse and duckdb have postgres-like sql syntax so you'll
00:01:13already feel familiar clickhouse comes in at 0.28 seconds and duckdb at 0.24 seconds that is over 40 times faster
00:01:22how is this possible it seems unbelievable right the same 100 million rows in as little as 0.24 seconds
00:01:30while columnar databases are just doing orders of magnitude less work to give us the same results
00:01:36let's see another query in action and just a quick note guys we release content on ai and tech constantly
00:01:42so if this is something you enjoy then why not subscribe to better stack this time we're counting up the number
00:01:47of events between two time stamps where the country code is gb in our case we're looking at the data in
00:01:53march the results postgres at 5.9 seconds clickhouse 0.06 seconds and duckdb at 0.03 seconds march contains
00:02:02around 13 percent of the total events here so postgres still needs to look at millions of rows we could
00:02:09add aggressive index in here to help things but that won't get us close to clickhouse and duckdb so why
00:02:15are column stores so much faster here well clickhouse doesn't index individual rows at all when you create
00:02:20a table you give it a sort key and as is sorted by timestamp then it splits the sorted data into blocks of
00:02:32and it just keeps a note of the first time stamp in each one for 100 million rows that's about 12 000 notes
00:02:39and that's small enough to sit in memory so when we ask for march it does a quick search through those
00:02:44notes finds the blocks that could contain march and reads only those in our case that's 1633 blocks out
00:02:52of 12 208 and everything else on disk gets skipped duckdb does something similar because it keeps a min
00:03:00and max for every chunk of a column so we can look at a chunk see the timestamps run from january to
00:03:06february and skip the whole thing without reading it add to that the trick from before where it only
00:03:10opens the columns it needs and you get 0.03 seconds but things take a complete u-turn when you want to
00:03:17select a single row by id this simple query reveals a lot postgres takes two milliseconds clickhouse 168
00:03:26milliseconds and duckdb interestingly still at two milliseconds postgres has a binary tree index on the
00:03:32id so it walks down the tree lands on the one page that holds the row and it's done in just two
00:03:37milliseconds but with click house we have a problem its only index is that sort key and this table is
00:03:43sorted by timestamp first and id second so when we ask for a single id it has no idea which block that id
00:03:51lives in and it ends up checking every one of the 12 208 blocks and once it's found the row it still
00:03:57has to open all eight column files and stitch that row back together which is a lot of work for such a
00:04:03simple query so how does duckdb get away with it it got lucky with our data the ids were inserted in
00:04:09order so the min and max on each chunk line up neatly with the id and duckdb can skip straight to the right
00:04:16one if the ids were shuffled it would be scanning the whole column just like click house let's now look
00:04:21at updating a row we've got one query for postgres and duckdb and a slightly different version for
00:04:26click house postgres came in at five milliseconds click house at 5.8 seconds and duckdb again just tens
00:04:34of milliseconds postgres just rewrites one row and updates one index entry in click house the data files
00:04:40are immutable they never get edited in place so to change one revenue value it has to rewrite the
00:04:46entire revenue column file for that chunk of the table click house even makes you write this as an
00:04:51alter table because it treats it as a mutation of the whole table there is a lighter weight update in
00:04:56beta but it's only meant for small numbers of rows about 10 of the table at most duckdb sits in between
00:05:03the two because its file can be changed in place so a single row update comes in at a few tens of
00:05:08milliseconds and finally let's count up the number of distinct users in our table again a slight
00:05:14variation of the query for click house and the results are postgres 38.4 seconds click house 0.78 seconds
00:05:22and duckdb under one second again postgres suffers aggregating over an entire table this large just
00:05:28requires a huge amount of work now you'd often want to use columnar databases for things like analytics
00:05:34where aggregating data across various columns is a common task here we looked at two options click house
00:05:41is a hosted server open source and available on most major platforms to run this locally you need to run
00:05:47a click house server on your machine much closer to how postgres works duckdb however is more like sql lite
00:05:54it's a library that runs inside its own process and the whole database lives in a single file on disk so
00:06:00that file can be locked while one update is in progress which kills concurrency both are columnar
00:06:06databases but take very different approaches so which database you end up using comes down to much more
00:06:12than i could assume here it's not just about raw query speed when running demos on your local machine
00:06:17it's about scalability redundancy and extensibility of course postgres has a rich plugin ecosystem to
00:06:24extend its feature set in many ways which you can see in this next video

Key Takeaway

Columnar databases like ClickHouse and DuckDB accelerate analytical queries up to 40 times faster than row-based systems like Postgres by scanning only required columns and data blocks, though they trade away single-row lookup speed and update flexibility.

Highlights

  • Columnar databases execute group by queries over 100 million rows in 0.28 seconds compared to 9.7 seconds for Postgres.

  • ClickHouse and DuckDB run analytics queries up to 40 times faster than Postgres by storing data by columns instead of rows.

  • Postgres outperforms columnar databases on single-row lookups by ID, retrieving the record in 2 milliseconds while ClickHouse takes 168 milliseconds.

  • ClickHouse treats data files as immutable, requiring a full table mutation or file rewrite to update a single value.

  • DuckDB operates as an embedded in-process library inside a single file, while ClickHouse requires a dedicated server process.

Timeline

Performance Comparison on Group-By Queries

  • Columnar databases store data by columns rather than rows to optimize analytical workloads.
  • Postgres takes 9.7 seconds to execute a group by query over 100 million rows.
  • ClickHouse executes the same SQL query in 0.28 seconds and DuckDB in 0.24 seconds.

Row-based databases read entire rows into memory even when aggregating specific columns, creating massive overhead for analytics. Columnar storage architecture allows databases to perform orders of magnitude less work for identical aggregate results.

Filtering Ranges and Skipping Data Blocks

  • ClickHouse and DuckDB sort data by timestamps and divide entries into memory-resident blocks containing min and max values.
  • Postgres takes 5.9 seconds to count events across a specific month, while ClickHouse takes 0.06 seconds and DuckDB takes 0.03 seconds.
  • Columnar databases skip irrelevant disk blocks entirely without reading unneeded data.

ClickHouse maintains a sorted timestamp index split across roughly 12,000 blocks for 100 million rows, keeping index notes entirely in memory. When querying a specific month, the database isolates relevant blocks and skips the rest on disk. DuckDB utilizes similar min and max chunk tracking to bypass irrelevant ranges.

Single-Row Lookups and Data Immutability Trade-Offs

  • Postgres retrieves a single row by ID in 2 milliseconds using a binary tree index.
  • ClickHouse takes 168 milliseconds for single-row lookups because it lacks an ID index and must scan all blocks before stitching columns together.
  • ClickHouse files are immutable and require rewriting entire column files for updates, whereas DuckDB allows in-place modifications.

Columnar databases excel at scanning large ranges of specific columns but struggle when fetching isolated records. Because ClickHouse sorts data primarily by timestamp, searching for a specific ID forces a full scan across all 12,208 data blocks. Mutating data in ClickHouse requires a heavy table-level rewrite operation.

Distinct User Counts and Architectural Differences

  • Postgres requires 38.4 seconds for distinct user aggregations, while ClickHouse takes 0.78 seconds and DuckDB takes under one second.
  • ClickHouse runs as a hosted server process, whereas DuckDB functions as an in-process library operating out of a single file.
  • Database selection requires balancing raw analytical query speed against scalability, concurrency limits, and extension ecosystems.

Distinct user counts require heavy aggregation across large datasets, exposing the performance limits of row-based engines. Architectural distinctions dictate deployment choices, with DuckDB storing data in a single file that locks during writes, while Postgres offers a mature plugin and extension ecosystem.

Community Posts

No posts yet. Be the first to write about this video!

Write about this video