11–20 of 32 posts
Postgres is an amazing database. It’s only significant weakness now is in Materialized views, with their lack of incremental refresh. Was disappointing to see there was no progress towards this in v15.
Postgres is an amazing database. It’s only significant weakness now is in Materialized views, with their lack of incremental refresh. Was disappointing to see there was no progress towards this in v15.
I also wish this were in core posgres, but if you use a build tool like DBT you can enable this type of thing.
Earlier quoted context omitted.
I also wish this were in core posgres, but if you use a build tool like DBT you can enable this type of thing.
Interestingly, DBT does not support creating materialized views.
Postgres is an amazing database. It’s only significant weakness now is in Materialized views, with their lack of incremental refresh. Was disappointing to see there was no progress towards this in v15.
> If I’d written out the query, I might have seen that I didn’t need the JOIN (or never written it in the first place). Whereas the view hides that complexity. So they can make things easier, but that can lead to performance pitfalls if we’re not careful. you can avoid this particular pitfall by using left joins for views like this (that join stuff in for convenience that you might not select) - postgres will elimina…
> commercial engines will use the presence of fk constraints What, postgres doesn't do FK join removals? Like I tried it right now and it didn't remove the hash-join http://sqlfiddle.com/#!17/073747/2 Should've been just a count off the PK Employees table. Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run somethin…
Earlier quoted context omitted.
> commercial engines will use the presence of fk constraints What, postgres doesn't do FK join removals? Like I tried it right now and it didn't remove the hash-join http://sqlfiddle.com/#!17/073747/2 Should've been just a count off the PK Employees table. Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run somethin…
How would that work? Your schema allows an employee to be assigned to multiple departments, and the query must count them.
Earlier quoted context omitted.
> commercial engines will use the presence of fk constraints What, postgres doesn't do FK join removals? Like I tried it right now and it didn't remove the hash-join http://sqlfiddle.com/#!17/073747/2 Should've been just a count off the PK Employees table. Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run somethin…
How would that work? Your schema allows an employee to be assigned to multiple departments, and the query must count them.
I do have one question: how does the algorithm described in the article work when the source table is spread across multiple servers, say in multiple shards? Can TimescaleDB maintain materialized views on each shard and then run a query that reconciles them?
Edited: clarification
This is an excellent article. I like the way the author builds up in steps to eventual consistency between source tables and their materialized views. It was fun to guess the next step. I do have one question: how does the algorithm described in the article work when the source table is spread across multiple servers, say in multiple shards? Can TimescaleDB maintain materialized views on each shard and then run a que…
So we thought about doing something like that with multinode where each of the nodes would maintain their own materialization but abandoned it for that very reason it’s very, very difficult to maintain any sort of consistency guarantees in that case, or even to reason about it.
Instead we use the access nodes as coordinators to do the materialization. right now the materialization only exists on the access node but there’s no reason we couldn’t send it back out to the data nodes, you just need a coordination point to start a distributed transaction to have some semblance of a guarantee.