4 ms·

The whole idea about the post is quite interesting. Definitely shows the high performance capabilities of the system. I really appreciate the addition of the pa

The whole idea about the post is quite interesting. Definitely shows the high performance capabilities of the system. I really appreciate the addition of the parallel insert via INSERT ... SELECT which is quite handy. I have a question about the topic of the post. The method presented could also be simulated with a MATERIALIZED VIEW? Are they supported in Citus? In any case, the technique presented is pretty cool :)

ozgune 10y ago
(Ozgun from Citus Data) We're planning on supporting VIEWS (but not MATERIALIZED VIEWs) in Citus 6.1: https://github.com/citusdata/citus/issues/442 https://github.com/citusdata/citus/issues/442 If you're interested in using Materialized Views in Citus, please feel free to comment on the issue or open a new one. We'd be happy to incorporate your feedback into upcoming releases. You're also correct in that the method presented in this blog post could be simulated with a distributed materialized view. One potential difference could be that materialized views in PostgreSQL currently refresh the entire data from source tables. This method provides flexibility to do rolling inserts or upserts.
- ahachete 10y ago
  +1 for VIEW support :) I agree that the suggested method is more flexible (which means more performance in this kind of environments/problems). Mat views provide simplicity. I'd say not a must, but they may be useful for users who don't want to dig deep into these techniques.
buremba 10y ago
Materialized view is already supported by Postgresql but it requires full table re-write: https://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html https://www.postgresql.org/docs/9.3/static/sql-createmateria... We're also experimenting incremental materialized view with PrestoDB. If you have a incremental column in a append-only table or a server_time column for each row. (https://rakam.io/doc/buremba/rakam-wiki/master/Analyze-Data#materializedtablesmaterialized https://rakam.io/doc/buremba/rakam-wiki/master/Analyze-Data#...) One possible way to implement incremental materialized views in Postgresql is logical decoding: https://www.postgresql.org/docs/9.4/static/logicaldecoding-explanation.html https://www.postgresql.org/docs/9.4/static/logicaldecoding-e...

ozgune 10y ago

(Ozgun from Citus Data) We're planning on supporting VIEWS (but not MATERIALIZED VIEWs) in Citus 6.1: https://github.com/citusdata/citus/issues/442 https://github.com/citusdata/citus/issues/442 If you're interested in using Materialized Views in Citus, please feel free to comment on the issue or open a new one. We'd be happy to incorporate your feedback into upcoming releases. You're also correct in that the method presented in this blog post could be simulated with a distributed materialized view. One potential difference could be that materialized views in PostgreSQL currently refresh the entire data from source tables. This method provides flexibility to do rolling inserts or upserts.

ahachete 10y ago
+1 for VIEW support :) I agree that the suggested method is more flexible (which means more performance in this kind of environments/problems). Mat views provide simplicity. I'd say not a must, but they may be useful for users who don't want to dig deep into these techniques.

buremba 10y ago

Materialized view is already supported by Postgresql but it requires full table re-write: https://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html https://www.postgresql.org/docs/9.3/static/sql-createmateria... We're also experimenting incremental materialized view with PrestoDB. If you have a incremental column in a append-only table or a server_time column for each row. (https://rakam.io/doc/buremba/rakam-wiki/master/Analyze-Data#materializedtablesmaterialized https://rakam.io/doc/buremba/rakam-wiki/master/Analyze-Data#...) One possible way to implement incremental materialized views in Postgresql is logical decoding: https://www.postgresql.org/docs/9.4/static/logicaldecoding-explanation.html https://www.postgresql.org/docs/9.4/static/logicaldecoding-e...