back
3 comments
Traditionally, many OLTP operational databases are connected via ETL to an OLAP data warehouse; they are not mutually exclusive. PingCap is the company behind TiDB, an HTAP NewSQL engine that competes with CockroachDB and YugabyteDB; the OP is content marketing for TiDB.

TiDB distinguishes itself with HTAP; transparently incorporating OLTP/ETL/OLAP in a single cluster. You have to specify the ETL layer and data warehouse in addition to PostgreSQL to make an apples to apples comparison; that is the core of HTAP positioning.

SAP HANA is the poster child for HTAP, a data warehouse with good enough OLTP performance to replace Oracle RDBMS; a single system is used for both SAP app tiers, Business Suite and Business Warehouse. The same value proposition applies to cloud apps. Independent OLTP/ETL/OLAP is still robust and is more modular while HTAP is more tightly integrated and simpler to operate.

We tried to deploy HANA with BW in a larger-ish life sciences company (200k employees) and so far it was a huge waste of money. It's not actually performing well and the support team had to stop replicating some of our most important data becase "there's too much".

I'm not convinced HTAP can actually work - the they OLTP and OLAP works internally seems too different.

That’s interesting and makes sense. SAP introduced HANA NLS (nearline storage) based on Sybase IQ to address your use case (I think). HANA HTAP is in-memory while IQ works with shared storage clusters so it’s ideal for offloading massive amounts of historical BW data that doesn’t fit in memory.

In-Memory HANA is freakishly good for running OLAP BW queries against fresh data. Column stores like HANA and IQ are both good at this, but to be honest, I don’t know how BW systems were typically configured before HANA/IQ.

I have to admit I'm not super involved with the system in question, and it's possible the team is underfunded or just not that great.

But we definitely ended up in a situation where it was hard to get to some important data, since the team couldn't SLT it to BW because "it's too many transactions" and trying to get it from Sidecar was blowing up, because it's too much data. And that was already with S/4.

But again, I don't know why it was the case. It would have been great and saved us a lot of work if it worked out and we could've done stuff in HANA directly, instead of copying data to different OLAP system daily. Especially now, when everyone is trying to get on the realtime-train.

A rough figure : 100gb is still fine with postresql but even then the problem is not the database (query) but rather your pipeline to get data into it. At this point you'll probalby optimize both.
OLAP is kind of a different world - none of the typical Postgres "competition" (like MySQL or Oracle) works really any better in this domain.

There really isn't a very good free one part solution here - so either you pay big bucks for the likes of Google BigQuery or Snowflake so they can become gatekeepers to your own data, or you end up burning a lot of engineering time to get the likes of Hadoop or Spark on K8s or Trino working.

In common usage does OLAP automatically imply data that is too large for a single node to handle? I'm wondering if a lot of OLAP workloads couldn't be handled with some parquet files (or some other column based storage)?

I realize parquet is used by the hadoop/spark ecosystem, but do you really need those systems? I'm thinking that a lot of companies reach for a hadoop cluster when some parquet files in a regular for system would be much simpler. I've done things like this and in my experience it works quite well. But only for personal projects.

No, of course you're right, I'm just bit too comfortable in my own domain.

For smaller data (and here that would mean few hundreds GB which is pretty huge by normal standards) OLTP databases would cover you pretty well. Oracle has some fancy bitmap indexing, MS SQL even has columnar tables, and pretty much anything has partitioning.

Parquet is really cool - especially there's like 30 x ratio between "generic oracle table" and gzip compressed parquet file so scan time are really in a different world.

But by itself parquet doesn't solve a whole lot - where are the files stored and what scans them? What happens if someone is updating the files while someone else is reading them?

Yeah the storage size was a real clincher for me, as much as I appreciate a SQL database.

Re: concurrent reading and writing, for my use case the files are immutable so that isn't a concern. But I agree, I don't think pure parquet is a good fit there.