upvote
There are always tradeoffs. Databases are fundamentally about managing optimizations, and the question is whether those tradeoffs make sense for your workload.

HTAP has historically been niche because the cost of analytics on fresh data wasn't worth the squeeze (sooner or later the MBAs step in). Separating the OLTP and OLAP layers gives you better workload isolation and the ability to optimize each independently.

Databricks Lakebase/LTAP uses Postgres for transactions and lakehouse engines for analytics, with a shared storage layer. They also use Spark to build Postgres pages in parallel and write them directly to storage, avoiding the single-writer bottleneck when loading large volumes of data.

SingleStore takes a different approach: a distributed database based on MySQL with an in memory rowstore for transactions tightly coupled with a merger to columnstore for analytics.

I don't think HTAP DBs will become the default for every use case. But I do think the space will grow as we move towards agents and automated decision making and optimizations.

reply
Agree with everything here. Workload isolation and independent scaling in particular are the reasons I never see HTAP work out in practice, even beyond expected loss from not using the more specialized solution. A very low latency storage handoff between two engines always seems like 80% of value with 20% of operational headache.
reply
The workloads are different. An almost -in-sync analytics replica is a decent tradeoff (copy, lag etc).

I'm working on one such solution right now,

reply
> HTAP has historically been niche because the cost of analytics on fresh data wasn't worth the squeeze (sooner or later the MBAs step in). Separating the OLTP and OLAP layers gives you better workload isolation and the ability to optimize each independently.

That feels like something HTAP databases could offer as a choice!

- Things you can always do

- Things you can do when optimizing for fresh data

- Things you can do when optimizing for analytics on old data

- Things you can do, but with poor performance

This feels like one of those cases where people choose to make software simple instead of, and at the cost of (not) making operations simple.

reply
You are correct! And sadly the vast majority of people don't want to make choices or think about that tradeoff.

If it can't outperform pure OLTP and it can't outperform pure OLAP then there is no pit of success for such an HTAP system.

The unfortunate part is that the vast majority of software out there in the world would be served perfectly with an HTAP solution.

The fortunate part is that for a lot of problems you can just run Postgres and then when you need analytics you can still use Postgres, until you hit scaling issues (if ever).

I've never seen a project choose to solve OLAP and OLTP upfront and succeed.

reply
It is certainly possible. I built a highly optimized KV store, originally designed to add tags (key-value pairs) for the object store I invented a few years ago. This tagging system turned out to resemble a columnar store database, so I started using it to create relational tables.

As predicted, it was very good at performing analytic operations; but it also was very fast at traditional OLTP operations too. I benchmarked a large number of queries against other databases and in the majority of cases, it was actually faster. In some cases it was much faster. It was able to do all this using just one copy of the data without needing to create and maintain separate indexes.

There are several short demo videos on my YouTube channel (listed in my profile).

reply
I think there's never going to be a point where you can have the kind of performance you can get by optimising a datastructure for a 'fixed' dataset. Nor can you get the maximal performance updating data if you need to maintain a useful datastructure.

A simple case is a sorted list, you can sort it after every update, you can ignore the ordering and sort it as needed, or you can use a datastructure that is typically O(log(N)) at both updates and retrieval. Better datastructurs help, but they cannot remove the tradeoff entirely (in fact O(log(N)) inserts are kind of theoretically minimal, though a trie can do it in O(1) for fixed length datatypes).

reply
There's a general trick which combines the time complexities.

In the case of a sorted list, it's keeping a sorted list, along with a growing small unsorted list that you will query or sort when needed.

This works for kernel timer trees, database storage, and indexes.

Keep your "fixed" data up to some checkpoint in an efficient form, and your "new" data since the checkpoint in a faster-updating, slower-querying form. When doing queries, read both and merge, and maybe sort parts of the new data on demand. The new data will be slower to query, but it should also be smaller.

At carefully chosen times, transfer data from the new form to the "fixed" form, either with an incremental update if you can, or by rewriting the fixed form entirely. It doesn't have to stop at two layers, and shouldn't if the fixed form is large, or if you want unlimited scale.

Choosing the right times and how much to update (the whole key range, intervals, predicate subsets, ertc) is an interesting algorithm design problem. When you get it just right, you can get excellent performance in fast-queryable compression and continuous fast-updates at the same time. (I'm building a database engine that does this in my spare time.)

reply
There's also TiDB which has decent adoption. It allows for columnar replicas of transactional data and exposes both over the same MySQL-compatible interface.
reply
Lakebase is just cheating, they superglued a transactional database to an analytical one.

That said, it works wonderfully and I love it.

reply
[flagged]
reply