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.
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.
If there is an "obvious" improvement to database capabilities that seems to be mysteriously absent from all competent implementations, one should consider the hypothesis that it would make databases strictly worse across many dimensions people care about. One thing that can be said about the history of database software is that it has tended to exhaustively explore the known phase space of possible implementations. Novelty in database implementations is usually predicated on a material computer science advance at the architectural level; any rearrangement of existing parts and ideas has usually already been tried multiple times.
There are many good ideas for databases that no one implements because we don't know how to make them fast enough. People care greatly about database performance and scalability. You can implement almost any database feature you can imagine if you don't care about performance and scalability, you just won't have any users.