Why we waited to move our analytics to DuckDB
How Mat Plus moved from Metabase and PostgreSQL to a DuckDB service, and why the timing mattered as our data and customer needs grew.
We knew PostgreSQL would not be our analytical engine forever. We kept using it while we built the data and the product that would make the move worthwhile.
At Mat Plus, our first priority was collecting useful data and understanding what we could build around it. PostgreSQL let us focus on that work while serving our early analytical needs.
Building the analytics engine
Our first analytical work wasn’t even based on our own receipt data. It used our platform to analyse other datasets, and we used Metabase to build dashboards and reports.
We were developing an AI analytics engine, and we needed to use the analysis in more ways than displaying it in a dashboard.
The engine worked through a curated set of tools, each with a defined purpose, description and structured inputs. The model selected which tool to call for a given analytical task, while the queries and calculations remained under our control.
We did not want an LLM inventing calculations over raw data. We wanted it to help people explore the data using analyses we could define and verify.
Those tools needed reliable access to the underlying datasets and the relationships between them.
Metabase had served us well, but its API did not expose everything in the form we needed. Connecting results across different sources meant reconstructing relationships ourselves.
We began moving that analytical logic out of Metabase and into PostgreSQL views and materialized views. That gave us more control over the queries and allowed us to keep developing the engine without rebuilding everything around it.
PostgreSQL was doing its job. We had no reason to move yet.
When the workload grew
Then our own receipt data started growing, and customers began asking more demanding questions.
Our November 18 export contained about 5.6 million receipt records
and 40.9 million line items.
Product matching and cleanup generate additional evidence across different matching paths and historical windows.
Analytics builds further datasets from those processed results. As a context, one dataset tracking what shoppers bought each week held about 23 million rows on its own.
Behind the analysis customers see is a much larger amount of data to prepare, maintain and query. That work was growing along with the complexity of their questions.
We could have continued tuning PostgreSQL. I decided to put that effort into an infrastructure designed for this kind of analysis at this time.
Why DuckDB fit
I initially considered a data lake with Apache Spark. It could have handled the workload. We could bear the infrastructure cost. Engineering time was the more important constraint.
We already had an analytics engine, a UI and a body of SQL built around PostgreSQL. I wanted to carry that work forward. Compatibility mattered because it determined how much of our effort could go into improving the analysis rather than rebuilding the system around it.
DuckDB gave us a practical way to do that.
We could prepare the datasets needed for heavier analysis in Parquet and query them alongside data still in PostgreSQL. We could move the analytical workload while preserving the investment in our engine and the product around it.
MotherDuck was a serious candidate and an inspiration for the approach. I chose to operate DuckDB ourselves because I wanted direct control over how we deployed it and allocated resources as customer requirements developed.
Our customers do not all need the same analysis. Some have straightforward requirements. Others need complex queries involving many different data points.
I wanted room to give a demanding customer dedicated capacity without treating every customer as if they had the same workload. DuckDB’s small footprint made that a useful direction to keep open.
We built a DuckDB service and connected it to our existing analytics engine. That also meant taking responsibility for operating the service, refreshing its data and handling failures.
We chose that work along with the control it gives us.
We chose a daily refresh because it matches what our customers currently need. It also gives our cleaning pipeline time to work through data arriving throughout the day. Producing dependable analytical inputs is a substantial part of the system, and the refresh schedule reflects that.
The numbers
After the move, I wanted to put some numbers to the performance difference I expected.
DuckDB is built for analytical workloads. I expected it to be faster. What interested me was how much faster it would be on our own data and queries.
I ran a controlled local comparison using the same 2.45 million analytical records and identical SQL on PostgreSQL and DuckDB. PostgreSQL had covering indexes, and its execution plans confirmed index-only scans.
Across three representative analytical queries, DuckDB was 3.3 to 9.7 times faster.
Every returned value matched. Those figures are medians from five warm runs per query, with both engines running under the same CPU and memory limits.
Why the timing mattered
The benchmark came after the decision. It gave me a measure of the performance difference I expected. The decision to move had already been made based on the data we were processing, the questions our customers were asking and where I wanted to spend our engineering time.
Moving earlier would have taken that time away from the data and product without giving us anything we needed at the time.
I would make the same decision again.