Reports are the bread and butter of any business. ERPNext has realtime reports - they use live ledger as it's source of truth.

Early last year we got a ticket from a customer that their Trial Balance report wasn't working. Upon investigation we found they have 300 million ledger entries. It was such a surprise as it's far beyond what ERPNext can handle. Turns out they are in fintech space and they make particularly heavy use of accounting module. They regularly post 800,000 to 1 million ledger entries in a day which eventually adds up and by the time they raised the ticket, they already amassed 300 million entries.

Report were run with extremely high timeout value (couple hrs) with a lot of memory provisioned. Even then it was barely usable. Large scale data processing isn't something new and the industry has been solving them for couple decades now. It comes in the form of OLAP databases.

OLTP vs OLAP

There are 2 different architectures of relational databases - Row based (OLTP) and Column based (OLAP). Fundamentally they differ in how data is physical arranged in disk and this gives them inhert advantage in certain workloads.

1. OLTP databases (MariaDB, PostgreSQL, SQLite, MySQL) store each row of a table sequentially. This architecture is highly efficient in inserting rows.
2. OLAP databases (Clickhouse, DuckDB etc) store each column of all rows of a table sequentially. This is highly efficient for aggregation.

Integrating DuckDB

For such high volume, the only choice is OLAP. The problem is, Frappe Framework has always been a single DB web app. Adding a second database and maintaining a real time sync requires lot of manual configuration and upkeep. Customer also was ok with a snapshot approach. So we decided against it. We needed a snapshot sync with no additional server setup and DuckDB fit that bill perfectly. It is an embedded DB, so it can run in the same application server. This also plays very nicely with "everything is a doctype" philisophy of Frappe Framework.

A Doctype DuckDB Sync has been introduced. This takes care of replicating the schema and syncing all data in that doctype.

Once DuckDB has been integrated, we rewrote core financial statements - Trial Balance, Balance Sheet, P&L Statement and General Ledger to be DuckDB aware. So with just a toggle, reports can switch their source of truth from MariaDB -> DuckDB and back.

Some numbers