This trade off doesn't really need to exist in a SQL database today on modern hardware with its extremely high storage bandwidth. There are other models that can satisfy both OLTP and OLAP use cases satisfactorily in a single coherent system with modern internals. The SQL database market is extremely conservative, so even if you built it no one would adopt it for a decade.
ok, which are these.
the one place where you do start to get into fundamental issues is consistency. the planner can certainly identify read-only transactions and statically remove some conflicts. but whatever scheme you are using (locks, mvcc, optimistic) is going to struggle the more concurrent overlapping transactions there are. since OLAP transactions are very long lived and touch alot of things - they are pretty hostile co-residents with the OLTP traffic.
Like... Vertica is a fork of Postgres. I'm curious why they chose to fork and implement column-oriented storage and indexes etc. rather than simply add them as options to stock Postgres.
Obviously joins across the two different worlds would be highly problematic, and perhaps query execution, data materialization, query planning etc. could look significantly different.
But the potential advantage to the end user seems high, if moving from "transactional" to "analytical" workloads is a matter of moving data from one table-type to another, within the same underlying database system.
Again, I know there are reasons why this approach has not been successful. I'm curious what they are.