3 ms·
Materialized views vs. Rollup tables in Postgres
- andreygrehov 8y agoNot to confuse with ROLLUP grouping operation: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/current/queries-table-expres...
- deleted 8y ago[deleted]
- jomendoz 8y agoIs it just me or this article is very poorly written?
- TrolTure 8y agoI thought it was me but maybe it was you too.
- hnmonkey 8y agoCan anyone speak to whether MySQL is comparable to Postgres in this area? With how quickly Postgres has been getting improvements I can't seem to tell anymore if they have feature parity or one is way out ahead of the other.
- gpapilion 8y agoThis does not exist in MySQL.
- truth_seeker 8y agoPipelineDB is much appropriate extension to use internet this use case.
- zerd 8y agoYou have to be careful if you use the "rollup" way, and the event_id is assigned by a sequence. If you have two transactions, T1 and T2, and T1 inserts event_id 1000, T2 inserts event_id 1001, but T2 commits first and you run the rollup before T1 has committed you'll miss the row. We store the txid_current() on each row, and when getting new rows we get from the previous txid up to txid_snapshot_xmin(txid_current_snapshot()).
- ehfeng 8y agoThey actually link to another blog post (https://www.citusdata.com/blog/2018/06/14/scalable-incremental-data-aggregation/ https://www.citusdata.com/blog/2018/06/14/scalable-increment...) that solves this with pg_sequence_last_value(). I personally couldn't find this function, but I believe they might be talking about lastval (https://www.postgresql.org/docs/current/functions-sequence.html https://www.postgresql.org/docs/current/functions-sequence.h...).