It’s interesting that I never considered why OLTP and OLAP are basically orthogonal technologies. Are there any major players that have an integrated solution for both?
I guess it makes sense because the infra is so different, but I’m not sure whether it need be.
I think they have, they are just not that well known. E.g. SQL Server - https://learn.microsoft.com/en-us/sql/relational-databases/i... - you can also find quite a lot of papers by microsoft employees on the designs and capabilities (starting around 2016 I believe, so "pretty new"). I have used it with TPC-H and it worked wonders, never got around to using it in a production workload though.
Effecient OLAP queries need a different shape of data - some combination of columnar storage for efficient scanning, and roll-up tables with pre-aggregated measures. Even in an integrated scenario, behind the scenes there will need to be a bunch of copying to transpose and / or refresh roll-ups.
The pre-aggregation is one too many try and skip. People seem to think they can build a single schema to rule all things, and then assume they can quickly calculate any aggregation on demand.
I guess it makes sense because the infra is so different, but I’m not sure whether it need be.