Stop Nesting Database Systems

The idea of embedding fast data processing engines into other systems to speed up analytics is not new. In fact, DuckDBs started out as SQL Analytics on Pandas. Meta had a similar vision for its Velox project, which takes a slightly different approach by building an embeddable execution engine distributed as a C++ library. If you’re considering building your own analytics engine, embedding an existing engine is almost always a better choice. Don’t roll your own crypto analytics engine.

Recently, however, there has been a trend to embed database systems in other database systems for various reasons, which I find surprising and worrying. It started with pg_duckdb, and now there is omni-sqlite, pg_clickhouse, and, just this week, a Planetscale to MotherDuck pipeline over pg_duckdb. With this pipeline, you end up nesting four systems: PlanetScale packs PostgreSQL, which packs the MotherDuck-built extension pg_duckdb, which packs DuckDB and MotherDuck.

Solutions like pg_duckdb and pg_clickhouse promise to solve your PostgreSQL analytics issues with HTAP-like capabilities. They allow you to run transactions in PostgreSQL and claim to seamlessly hand over analytical workloads to Clickhouse and DuckDB with a sleek interface directly from PostgreSQL. But only at first glance. To me, this approach is similar to opening a random closet and throwing all your clutter in there to hide it before guests arrive. Does it look clean? Sure! But does it solve your issues? Probably not.

To find out if these scientists were too preoccupied with whether they could, let’s take a look at how these extensions work and what they can and can’t do for you.

How the Nesting of Database Systems Works

What They Can Do Better than the Outer Engine

Analytical query processing has certainly come a long way since PostgreSQL was designed. Consequently, using an analytical system provides a wealth of functionality not available in PostgreSQL. Unlike PostgreSQL, DuckDB’s optimizer was designed for complex analytical workloads, so it can find much more efficient plans. This allows DuckDB to fully parallelize queries and utilize system resources much more effectively. While PostgreSQL assumes memory is scarce, analytical engines can productively saturate powerful servers. This has the potential to reduce execution times significantly. You also gain access to features of the embedded engine that are not available in PostgreSQL. For example, you can query remote Parquet files and combine them with local subresults.

Where They Are On-Par

Of course, nesting another engine does not magically solve all performance issues. There are parts where the embedded engine can only be as fast as the outer system allows. In order to run queries on data managed by the host system, the embedded engine must retrieve the data from the host. Retrieving this data requires a regular scan, which PostgreSQL must perform. pg_duckdb employs additional optimizations to reduce the impact of this process. It pushes filters to PostgreSQL scans where possible and runs scans in parallel. However, it still depends on PostgreSQL to fetch rows from disk using its buffer manager. It has no access to columnar layout or advanced techniques such as scan pruning. Therefore, while the upper parts of the query plan can leverage DuckDB’s full performance, all scans are expected to run at PostgreSQL speeds.

Where They Hold You Back

Creating a Frankenstein system comes at a cost. For one thing, you increase the complexity of your stack. Rather than running PostgreSQL in isolation, you now need two engines that aren’t designed to work together to do so. There are other limitations as well.

While you can perform many analytical tasks, some things fundamentally do not work. The most obvious issue is that you cannot mix and match functionality. For instance, you can’t run DuckDB plans that require PostgreSQL functions in the query plan. However, this is not only a surface-level issue of available functions. What if the systems have different sort orders or comparisons, e.g., through collations? Suddenly, the results could differ depending on which system executes the query, and you would not know.

The last downside is the missing upsides in the current state of these solutions. While there are obvious theoretical opportunities for performance improvements, neither pg_duckdb nor pg_clickhouse can reliably outperform PostgreSQL on TPC-H in their current forms.¹ So, you need to carefully decide which queries to use them for, as well as when sticking to PostgreSQL is the better choice.

Nesting Systems vs ETL

Although nesting systems have their drawbacks, there are definitely use cases where they are preferable to typical ETL setups.

First, if you need to perform analytics, they must occur somewhere. If you don’t want to adopt a full HTAP engine like CedarDB right away, nesting systems can be a good stopgap solution. For many PostgreSQL users, the alternative to such integrations is setting up and maintaining a full ETL pipeline for infrequent, analytical “heavy hitter” queries, such as monthly reporting. In that case, having the option to spin up an embedded engine at the click of a button and have it disappear without a trace is ideal. However, if you need to continuously run analytics, the benefits of a dedicated analytical or HTAP solution will quickly outweigh the cost.

This does not hold true for approaches like pg_clickhouse or the MotherDuck component for pg_duckdb, which act more like foreign data wrappers. Rather than being fully embedded within the host, they provide access to query results on external systems through a single, convenient interface. However, they don’t help with moving data to the analytical system or keeping it up to date. Therefore, you still need to set up a data pipeline from PostgreSQL to Clickhouse or MotherDuck. While these solutions are more convenient, they are not fundamentally different from traditional ETL stacks.

These extensions’ main promise is HTAP, a workload that ETL pipelines will never be able to cover. Running analytics within a transaction and persisting results with transactional guarantees is only possible if a single system controls both analytical reads and transactional writes. These workloads are much more common than one might think. ² To run HTAP workloads, your analytical queries require a consistent view of your transactional data and the ability to persist results with the same guarantees. However, is an analytical engine nested in a transaction system worthy of being called “HTAP”?

Is This HTAP?

The MotherDuck post that inspired this semi-rant gives a “maybe yes” but I want to stick to a clear “no.” The setup can be useful in the above scenarios, but the only part that manages to even scratch the surface of HTAP is the last one: a local, embedded analytical engine within a transactional host.

And even a full embedding still has to delegate scans to the host system, meaning it can not overcome the most crucial bottleneck for analytics: scans. The majority of time in analytics is spent on table scans, even in systems that heavily optimize their scans for analytical workloads. ³ Both MotherDuck and Clickhouse have such scans as well, but they are only useful on data they manage themselves, which does not come with the same transactional guarantees as your PostgreSQL query.

The only way to have your cake and eat it too is to use a system built for HTAP that can optimize the entire process, from scan to result, while still guaranteeing transactional integrity. Getting this right takes more than simply embedding an analytical engine.

Here is a short list of what we had to build at CedarDB to achieve full performance for a mix of analytics and transactions:

• A storage layout that works for both point accesses and analytical scans.
• Optimistically synchronized data structures to reduce lock contention between parallel write and read operations.
• Optimistic snapshot isolation for transactions to prevent long reads from blocking writers.
• A scheduler that prioritizes small writes without starving long reads.

These features are in addition to optimizations for the analytical and transactional parts in isolation, many of which we highlight in other blog posts. Building an HTAP system is definitely a much larger project than nesting two database systems, but I believe the results justify the effort.

If you want to experience the performance and convenience of a true HTAP solution for yourself, try out our Community Edition with an HTAP workload.