Pre-aggregation is still a tool in the toolbox, but you should first consider switching to a purpose-built column-store like Redshift/BigQuery/Snowflake.
Pre-aggregation is less common in data warehouses use-cases than it is in real-time use-cases. Data warehouses are generally used by a small number of users that run ad-hoc reporting queries and can take anywhere from several seconds to a day to complete. With queries changing frequently and high query times being acceptable, pre-aggregation does not provide that much benefit.
Citus is meant for applications with a large number of users (e.g. dashboards [1]). Investing in pre-aggregation is often worth doing because it makes for more responsive applications and lower hardware cost.
> It's fragile: in this example, it depends on your data coming in exactly-once, in-order.
The solution proposed in the blog post does allow out-of-order writes since it only looks at the database clock. Exactly-once delivery depends on a) the database clock not going backwards, b) writes taking less than a minute. However, it is also possible to guarantee exactly-once delivery with a more complex solutions (e.g. create staging table, or use a version number that gets increased atomically through some locks).
[1] https://blog.cloudflare.com/scaling-out-postgresql-for-cloud...
True, unless you have regulatory requirements that you can't store your data off-site.
https://www.microsoft.com/en-us/sql-server/sql-server-editio...