
Materialized views vs. Rollup tables in Postgres - lfittl
https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/
======
andreygrehov
Not to confuse with ROLLUP grouping operation:
[https://www.postgresql.org/docs/current/queries-table-
expres...](https://www.postgresql.org/docs/current/queries-table-
expressions.html#QUERIES-GROUPING-SETS)

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

~~~
ehfeng
They actually link to another blog post
([https://www.citusdata.com/blog/2018/06/14/scalable-
increment...](https://www.citusdata.com/blog/2018/06/14/scalable-incremental-
data-aggregation/)) 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...](https://www.postgresql.org/docs/current/functions-
sequence.html)).

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

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

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

~~~
gpapilion
This does not exist in MySQL.

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

