DuckDB Is Quietly Killing Pandas (SQLite for Analytics)

[00:00:00]You point pandas at a big CSV and Python dies. Out of memory, kernel gone. If you work with data, you have hit this exact wall. Now change one line. Hand that same file to duct DB and the query comes back in about a second. That is duck DB.

[00:00:16]Think of it as ESQLite but rebuilt for analytics instead of transactions. Same laptop, same file, same query. One tool crawls for a minute, the other answers before you blink. And it is taking over the job Pandas has owned for 15 years.

[00:00:29]the everyday number crunching. It runs right inside your Python process. No server to start, no account, one pip install and it is there. The pitch is simple, faster than pandas on a fraction of the memory and it chews through data bigger than your RAM, nearly 40,000 GitHub stars, and it came out of a public research lab in Amsterdam. It is fast for one big reason. It reads your data in columns, a batch at a time, never rowby row. It also uses every core on your machine at once. Pandas famously uses exactly one. Here is the whole story. The memory wall pandas hits, why duct DB is faster, and where Pandas still wins. First, some history because it matters. Wes McKini built pandas in 2008 while working at a hedge fund. And it basically turned Python into the language of data. For 15 years, it has been the default for loading, cleaning, and crunching a table. But it was designed for a world where your data comfortably fit in memory. And that world is gone. Here's the first crack.

[00:01:28]Your laptop probably has 8, 12, 16 CPU cores. Pandas uses exactly one of them.

[00:01:34]Run a heavy group by and the other 15 sit completely idle while you stare at a progress bar. All that hardware you paid for doing nothing. The bigger problem is memory. Pandas loads the entire data set into RAM, then needs room to copy it around. Mckin's own rule of thumb, keep 5 to 10 times your data set size in memory. So a 2 GB CSV can demand 15 or 20 gigs of RAM before it will even finish a join. And when you cross that line, there is no graceful failure.

[00:02:02]Pandas throws a memory error. The Python kernel dies. Your notebook wipes every variable you had loaded and the analysis is just over. No flag, no chunk size inside Pandas, gets you past your own RAM. There is also nothing planning your work. A real database has a query optimizer that reorders and skips work.

[00:02:20]Pandas does exactly what you type line by line. 10 chained operations means 10 full scans of the data even when it could have filtered first and touched a tenth of the rows. So one core everything jammed into RAM and no optimizer on small data. You never notice any of it. On real analytical work, millions of rows and a few joins, those three limits stack up fast and the weight becomes your whole afternoon.

[00:02:44]This is the exact gap duct DB was built to fill and it fills it well. Last October, KDN Nuggets ran all three head-to-head on a million row table, summing a column, grouping by category, filtering, multiffield aggregates. Duck DB was fastest on nearly every query.

[00:03:00]Pandas trailed and SQLite, the transactional one, was slowest by a wide margin. That matches what data engineers keep reporting on their own real workloads. Independent tests are even rougher on pandas. On heavier generate and aggregate workloads, one widely shared benchmark clocked duct DB at around five times faster than Pandas.

[00:03:19]And once a Pandanda's pipeline is forced to spill, the gap gets embarrassing.

[00:03:23]Same data, same laptop, but raw speed is only half the win. Watch the memory.

[00:03:28]Panda swings between the lightest and the single heaviest tool depending on the query. Wildly unpredictable. DUTDB stays low and flat across all of them.

[00:03:37]Predictable memory is what lets you actually trust it on a big job. Why is it this much faster? The engine pandas and SQLite process data one row at a time. Duct DB is columner and vectorized. It pulls your data through in vectors, chunks of about 2,000 values and runs each operation across the whole chunk while it stays in the CPU cache.

[00:03:57]Far less overhead per value, far more throughput. Even just reading a file, it wins. Pandas parses a CSV in Python on one core all in memory. DuckDB has a parallel CSV and Parkquet reader that streams across every core. So loading a big file is often several times faster before you have run a single query.

[00:04:16]Joins show it best. Merging two big tables in pandas is where notebooks go to die. It materializes both sides and memory explodes. Duck DB runs a proper streaming hash join, keeps only what it needs, and joins files that are far larger than the RAM you actually have.

[00:04:32]Which brings us to the part that genuinely ends the fight. When your data outgrows RAM, duct DB does not fall over. It spills to disk and keeps going.

[00:04:40]Querying a 100 gigabyte parquet file on a 16 GB laptop is a completely normal thing to do. Pandas cannot do that, not slowly at all. It works out of core by design. It reads in chunks, uses your SSD as overflow, and sorts and aggregates without ever holding the full data set at once. There are peer-reviewed papers behind the external hash aggregation that makes it work.

[00:05:02]Pandas has no equivalent. The whole thing fits or it dies. And the interface for all of this is plain SQL, not a new data thing dialect with a thousand methods to memorize. The same select where and group by that basically every analyst and engineer already knows. You already speak the API. It also reads your files directly. Point Duck DB at a parquet, CSV or JSON file and just query it. Select star from the file name.

[00:05:28]Done. No separate load step, no import, no defining a schema by hand. It figures out the columns and the types by reading the file itself. Here is the move that makes adoption painless. Duct DB can query a Panda's dataf frame that is already sitting in memory by name in place with zero copying. So you do not rewrite your project. You drop one DuckDB query into the slow part of an existing Panda script and keep everything else exactly as it is. It also fixes ESQL's worst paper cuts.

[00:05:57]DuckDB ships what they call friendly equal group by all select star exclude a column trailing commas that do not error out. Small things but together they make writing a query feel modern instead of like it is 1999. Need to keep your results? Duck DB is a full database too.

[00:06:14]Everything lives in a single file on disk with real acid transactions and secondary indexes. The exact one file no server model that made SQLite the most deployed database on earth. And it goes everywhere. The same way the same engine runs on your laptop on a 100 core server on a tiny edge device and because it has zero dependencies right inside a browser tab through web assembly one binary no install anywhere you have a CPU. It also reaches across the network. Duck DB can query a parquet file sitting in an S3 bucket and pull down only the columns and rows the query needs, not the whole file. You can run real analytics on a cloud data set without ever fully downloading it to your machine. And it is extensible, a tiny core, plus loadable extensions for HTTP and S3, for JSON, for full text search, even geospatial queries. The community publishes their own through a package index. So, the same little file grows into whatever your analysis actually needs. This is not a science experiment anymore. In September 2025, DuckDB shipped its first long-term support release, version 1.4. 4 with database encryption, a merge statement, and a full year of guaranteed support. It has already moved on to version 1.5. This is production software now. The rest of the stack notice fast. DBT, Python, R, Rust, Java, and Node all speak duck DB now.

[00:07:37]And a Seattle startup called Mother Duck raised $und00 million at a $400 million valuation to build a managed cloud right on top of the same engine. And it is already under the hood of tools you use.

[00:07:48]Hugging face lets you run SQL across 200,000 data sets right in your browser.

[00:07:53]That SQL console is DuckDuck DB compiled to web assembly. Tools like DBT, real, and evidence build on it. You have probably queried DuckDB without even knowing it. Mother Duck's founder is worth listening to here. He helped build Google BigQuery, the poster child for big data, then wrote an essay titled Big Data is dead. his argument. Most companies data is only a few gigabytes and a single machine running DuckDB handles it fine. The giant cluster you were told you needed was for most teams oversold. So is pandas actually dead?

[00:08:25]Honestly, no. And anyone selling you that is exaggerating. Pandas is still the best glue in Python for plotting, for feeding scikitlearn and pietorrch and for quick little reshapes on a small table. The install base is enormous and it is not going anywhere. The honest split looks like this. Let DuckDB do the heavy lifting. Load the files, filter, join, and aggregate the big data down.

[00:08:48]Then hand the small result to pandas with a single DF call for charts and models. Duck DB is the engine. Pandas is the last mile. Together, they beat either one alone. A simple rule for your next project. Reach for DuckDB when the data is big, the work is analytical, or you just want SQL. Stay in pandas when the data is small, and the logic is row-byrow Python. Most real pipelines end up wanting both. The cost to try this is basically nothing. Pip install duct DB, import it, and one line runs SQL directly over your CSV or your dataf frame. No server, no account, no config file. Take the slowest Panda script you own and point DuckDB at it this week.

[00:09:27]Here's where I land. For the heavy analytical work, the loading, the joining, the aggregating, duct DB has become the new default. Pandas finally has the fast columner backend it always deserved sitting underneath it. If you write data code, this is worth an afternoon. That is the duck DB and Panda story. If this was useful, subscribe. I break down the tools, reshaping how we actually build software.