Materialized views vs. Rollup tables in Postgres

citusdata.com

40 points·lfittl··8 comments

Not to confuse with ROLLUP grouping operation: https://www.postgresql.org/docs/current/queries-table-expres...

You 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()).

They actually link to another blog post (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.h...).

Is it just me or this article is very poorly written?

I thought it was me but maybe it was you too.

Can 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.

This does not exist in MySQL.

PipelineDB is much appropriate extension to use internet this use case.

Reply on news.ycombinator.com