3 ms·

I'm not that familiar with the requirements of a big OLAP setup but can you explain why they can't be met by creating a logical replica using something like pg_

I'm not that familiar with the requirements of a big OLAP setup but can you explain why they can't be met by creating a logical replica using something like pg_logical? I understand that the shape of the schema has to be somewhat different for analytics (though I think that point is overstated and many use cases could probably get accomplished with the same table layout but just different index placements)

sa46 4y ago
Because transactional workloads (many small reads and writes that require consistency) look different from analytical workloads (giant reads with a small number of columns with looser consistency guarantees). OLAP databases typically use a column store which is amazing for reading a subset of columns because of much better compression and use of vectorized execution as opposed to Postgres’ tuple-at-a-time execution. The tradeoff is it’s expensive to update a column store since you have to rewrite a chunk of the column at least. Postgres is moving towards better OLAP. AlloyDB is a recent commercial DB that swaps out the Postgres storage engine to better support OLAP. That said, you can coerce Postgres into doing a reasonable job at OLAP for a surprising amount of data.