3 ms·

If pg was olap there wouldn’t exist any competition out there. IMO hybrid oltp and olap is what most companies need for their user facing apps and whoever nails

If pg was olap there wouldn’t exist any competition out there. IMO hybrid oltp and olap is what most companies need for their user facing apps and whoever nails it will become #1 in zero time. Ps. I would like to test firebolt, sounds promising.

abraxas 4y ago
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.
kmac_ 4y ago
There's too wide gap between OLAP and OLTP DBs, but definitely hybrids will appear. Vanilla pg is great but it's far away from analytical workloads. I use Vertica at work, it's a OLAP DB, but it handles hundreds of concurrent users in a light transactional workload without any issues (even though its locking isn't as granular as the one in postgresql). I would say that there's a spectrum between the two types of DBs and there's a market for all.
ren_engineer 4y ago
there's a few projects that are creating something like that, most include some type of column based storage extension for OLAP stuff
hodgesrm 4y ago
> If pg was olap there wouldn’t exist any competition out there. What does this actually mean? If PG were an OLAP database it probably wouldn't have full ACID support, because pure OLAP workloads typically don't do a lot of updates. And it would have a lot of hooks around storage like compression codecs that don't give a lot of value in an OLTP database. Finally it would probably be eventually consistent because it's expensive to ensure data consistency across a large, distributed dataset. So it wouldn't really be PG at that point.

abraxas 4y ago

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.

kmac_ 4y ago

There's too wide gap between OLAP and OLTP DBs, but definitely hybrids will appear. Vanilla pg is great but it's far away from analytical workloads. I use Vertica at work, it's a OLAP DB, but it handles hundreds of concurrent users in a light transactional workload without any issues (even though its locking isn't as granular as the one in postgresql). I would say that there's a spectrum between the two types of DBs and there's a market for all.

ren_engineer 4y ago

there's a few projects that are creating something like that, most include some type of column based storage extension for OLAP stuff

hodgesrm 4y ago

> If pg was olap there wouldn’t exist any competition out there. What does this actually mean? If PG were an OLAP database it probably wouldn't have full ACID support, because pure OLAP workloads typically don't do a lot of updates. And it would have a lot of hooks around storage like compression codecs that don't give a lot of value in an OLTP database. Finally it would probably be eventually consistent because it's expensive to ensure data consistency across a large, distributed dataset. So it wouldn't really be PG at that point.