3 ms·

> - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant isn’t this

> - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant isn’t this what materialized views are for?

efxhoy 5y ago
I agree that a materialized view is a lot more ergonomic and probably the correct choice in most cases. Refreshing a materialized view blocks selects from it though, rendering it inaccessible while the refresh is taking place. There is the CONCURRENTLY option but that may be more costly and requires a unique index on the view. There's also the issue of rebuilding several dependent views. Say you have 3 dependent views that are all being shown in some dashboard: 1, 2 and 3. You refresh view 1 OK but view 2 fails for some reason. Now view 1 and view 3 are out of sync and view 2 is broken. Encapsulating the 3 dependent views in a schema and only doing the name swap once all steps are OK prevents you from ending up in this state. If view 2 breaks you wont do the name swap so you'll be serving stale data, which is probably better than no data for 2 and out-of-sync data for 1 and 3, until you can fix it.
- m_mueller 5y ago
  Thank you for the elaborate response. So I think the question is how near time your view data needs to be. Especially for reporting purposes (which is probably the most common use case of data lakes) it’s often acceptable to have data ready per T-1 (in terms of business days). For financial reports, last day’s close prices of assets is usually what’s being used. So, being able to relax requirements in this manner can reduce a lot of complexity.
  - efxhoy 5y ago
    Absolutely. Complexity in our own system pretty much exploded when we wanted to serve stats at near "real-time" (a couple of seconds delay). If business could just chill out and wait until next day, or even like an hour, we could have saved countless engineering hours. Such is life.

> - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant isn’t this what materialized views are for?

efxhoy 5y ago

I agree that a materialized view is a lot more ergonomic and probably the correct choice in most cases. Refreshing a materialized view blocks selects from it though, rendering it inaccessible while the refresh is taking place. There is the CONCURRENTLY option but that may be more costly and requires a unique index on the view. There's also the issue of rebuilding several dependent views. Say you have 3 dependent views that are all being shown in some dashboard: 1, 2 and 3. You refresh view 1 OK but view 2 fails for some reason. Now view 1 and view 3 are out of sync and view 2 is broken. Encapsulating the 3 dependent views in a schema and only doing the name swap once all steps are OK prevents you from ending up in this state. If view 2 breaks you wont do the name swap so you'll be serving stale data, which is probably better than no data for 2 and out-of-sync data for 1 and 3, until you can fix it.

m_mueller 5y ago
Thank you for the elaborate response. So I think the question is how near time your view data needs to be. Especially for reporting purposes (which is probably the most common use case of data lakes) it’s often acceptable to have data ready per T-1 (in terms of business days). For financial reports, last day’s close prices of assets is usually what’s being used. So, being able to relax requirements in this manner can reduce a lot of complexity.
- efxhoy 5y ago
  Absolutely. Complexity in our own system pretty much exploded when we wanted to serve stats at near "real-time" (a couple of seconds delay). If business could just chill out and wait until next day, or even like an hour, we could have saved countless engineering hours. Such is life.