7 ms·

Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics: - Instead of updating tables build their replacements und

by efxhoy 5y ago

Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics: - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant. Works even for schemas: rebuild a schema as schemaname_next rename the current to schemaname_old then rename schemaname_next to schemaname. - Keep all the source data raw and disable WAL, you don't need it for ETL. - Set memory limitis high. And lots of other good tips for doing ETL/DW in postgres. It's here: https://www.youtube.com/watch?v=whwNi21jAm4 https://www.youtube.com/watch?v=whwNi21jAm4 I really appreciate having data in postgres. It's often easy to think that a specialised DW tool will solve all your problems, but that often fails to consider things like: - Developer experience. Postgres runs very easily on a local machine, more specialized solutions often don't or are tricky to setup. - Learning another tool costs time. A developer can learn postgres really well in the time it takes them to figure out how to use several more specialised tools. And many devs already know postgres because it's pretty much the default DB nowadays. - Analytics queries often don't need to run at warp speed. Bigquery might give you the answer in a second but if postgres does it in a minute and it's a weekly report, who cares? - Postgres is boring and has been around for many years now, it will probably still be here in 10 years so time spent learning it is time well spent. More niche systems will probably be superseded by fancier, faster replacements. I would go so far as to say don't necessarily need to split out your DW from your prod DB in every case. As soon as you start splitting out a DW to a separate server you need some way to keep it in sync, so you'll probably end up duplicating some business logic for a report, maintaining some ingestion app, shuffling data around S3 or whatever. Keeping your analytics in your prod DB (or just a snapshot of yesterdays DB) is often good enough and means you will be more likely to avoid gnarly business-rules going out of sync between your app and your DW.

Guthur 5y ago
And if say that if you are in the position where you can run DW workloads on your prod database you probably don't need a data warehouse in the first place. Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it's a price to pay to isolate these two very different workloads.
- chii 5y ago
  DW is a capability, not a scale problem. DW lets you gain insight from your available data, whether there's a lot of data, or not. DW with postgres is probably the right choice if your data isn't petabytes level.
  - Guthur 5y ago
    DW is a scale problem, what you are describing is data analytics. DW has always described the storage solution not the workload.
    - inopinatus 5y ago
      From a company perspective, DW is an implementation detail for the utility it provides, which is analysis and reporting. These activities have such different lifecycles, accountabilities, and controls, to operational data stewardship, that the pattern of a BI team operating independently from the corporate IT unit may arise by governance pressures alone. Differences in storage architecture and data schema aren't considered when it comes to carving up the org chart, but they're certainly enabled by the consequences: in my experience of large-to-very-large enterprises it's common for BI teams to take practically vertical control of their entire stack, from the racks to the apps (or in public clouds, one or more separated accounts), and all the infrastructure in between, a classic sighting of Conway's law in action. More than once I've pointed at such teams as an explainer when pitching a service-team approach to standing up a new application. (Caveat, lest it backfire if you try the same, check who you're talking to first; they're not always fans of "the BI gang")
      - pm90 5y ago
        I hate to admit this, but I’m definitely one of the folks who isn’t a fan of the BI gang that operates in my workplace. They seem to have a very superficial understanding of how anything works. Their stuff constantly breaks on schema changes, and their response is never “hey, this seems like a broken workflow. How do we improve it?”. Its always “@here our DW may be broken but we will get it back up soon” and radio silence as they try to privately figure out how to fix something they don’t quite understand. Not to mention that they have hardly any monitoring/alerting, which means that the data in the DW is not reliable, which can be really frustrating for users. Overall I get the sense that these are unskilled folks who oversold what they could do but ultimately have very little clue what’s really going on.
beckingz 5y ago
And if you use a replica for analytics the overhead on performance is tiny!
- tomnipotent 5y ago
  This should be the first step every company does as it scales out. If you can no longer run analytics against your production database for whatever reason, fire up a secondary replica with a 12/24 hour delay and you're golden.
harikb 5y ago
> Instead of updating tables build their replacements under a different name then rename them. This works really well, although a few minor warnings. If you run backups, they will block DDL changes like creation and rename of a table. Now this might block all reads on your table until backup completes. Make sure you do this on a schema that isn’t being backed up or do other workarounds. Other than that, this trick is really nice for instant replace and can also reduce disk space churn / fragmentation.
- stadeschuldt 5y ago
  In the above mentioned scenario there is no need for backups as the database (or more precisely the schema) is recreated at each run.
- atwebb 5y ago
  This works well until it doesn't and you're incrementally adding time to the loads. Some DBs handle it better than others. What most are looking for is performant, less constrained materialized views. If you're running ELT then MVs are a big deal. Additionally some DBs (SQL Server) don't have an option for pure "no logging" so, even with simple, there's overhead to the large writes. I like schema swaps too for the tables rather than renames but the pattern is the same.
thrav 5y ago
You’ve described the ELT approach that is why dbt is taking the valley by storm.
zukzuk 5y ago
That first point is exactly what dbt (https://www.getdbt.com/ https://www.getdbt.com/) does. It's an amazing tool, with extremely well thought out developer ergonomics.
- sails 5y ago
  Snowflake + dbt is great for this. Snowflake have a swap database function so instead of doing this at a table level you can create an entirely new database and do a blue/green style deployment. Not sure how this works with pgsql, but I suppose more complex.
diehunde 5y ago
Why would developers need to learn another tool? They are all just SQL at the end.
stadeschuldt 5y ago
The tooling behind the approach has been built as a set of python package named Mara. It is available at GitHub: https://github.com/mara/mara-pipelines https://github.com/mara/mara-pipelines And additional packages can be found at the Mara org: https://github.com/mara https://github.com/mara
m_mueller 5y ago
> - 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.
candu 5y ago
Re: renaming tables - yay! It's essentially double-buffering for databases. I've used a slight variant of this in the past: I'll have a table (e.g. my_schema_new.my_table) that gets updated by an ETL pipeline. I'll then also have a matview (e.g. my_schema.my_table) that's just SELECT * FROM my_schema_new.my_table. As long as I can give this matview a UNIQUE index, I can then REFRESH MATERIALIZED VIEW my_schema.my_table CONCURRENTLY for zero-downtime updates. (Of course, if you're using this in a non-interactive data warehouse context, you might not care so much about zero-downtime updates; this is more for application-facing views. The REFRESH ... CONCURRENTLY pattern can be faster for incremental updates, but often struggles with larger changes to the underlying table, as it's essentially applying a diff between the two versions. Also, it only works in cases where your users can tolerate data that's as stale as the scheduled time between REFRESHes.)

by efxhoy 5y ago

Guthur 5y ago

And if say that if you are in the position where you can run DW workloads on your prod database you probably don't need a data warehouse in the first place. Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it's a price to pay to isolate these two very different workloads.

chii 5y ago
DW is a capability, not a scale problem. DW lets you gain insight from your available data, whether there's a lot of data, or not. DW with postgres is probably the right choice if your data isn't petabytes level.
- Guthur 5y ago
  DW is a scale problem, what you are describing is data analytics. DW has always described the storage solution not the workload.
  - inopinatus 5y ago
    From a company perspective, DW is an implementation detail for the utility it provides, which is analysis and reporting. These activities have such different lifecycles, accountabilities, and controls, to operational data stewardship, that the pattern of a BI team operating independently from the corporate IT unit may arise by governance pressures alone. Differences in storage architecture and data schema aren't considered when it comes to carving up the org chart, but they're certainly enabled by the consequences: in my experience of large-to-very-large enterprises it's common for BI teams to take practically vertical control of their entire stack, from the racks to the apps (or in public clouds, one or more separated accounts), and all the infrastructure in between, a classic sighting of Conway's law in action. More than once I've pointed at such teams as an explainer when pitching a service-team approach to standing up a new application. (Caveat, lest it backfire if you try the same, check who you're talking to first; they're not always fans of "the BI gang")
    - pm90 5y ago
      I hate to admit this, but I’m definitely one of the folks who isn’t a fan of the BI gang that operates in my workplace. They seem to have a very superficial understanding of how anything works. Their stuff constantly breaks on schema changes, and their response is never “hey, this seems like a broken workflow. How do we improve it?”. Its always “@here our DW may be broken but we will get it back up soon” and radio silence as they try to privately figure out how to fix something they don’t quite understand. Not to mention that they have hardly any monitoring/alerting, which means that the data in the DW is not reliable, which can be really frustrating for users. Overall I get the sense that these are unskilled folks who oversold what they could do but ultimately have very little clue what’s really going on.

beckingz 5y ago

And if you use a replica for analytics the overhead on performance is tiny!

tomnipotent 5y ago
This should be the first step every company does as it scales out. If you can no longer run analytics against your production database for whatever reason, fire up a secondary replica with a 12/24 hour delay and you're golden.

harikb 5y ago

> Instead of updating tables build their replacements under a different name then rename them. This works really well, although a few minor warnings. If you run backups, they will block DDL changes like creation and rename of a table. Now this might block all reads on your table until backup completes. Make sure you do this on a schema that isn’t being backed up or do other workarounds. Other than that, this trick is really nice for instant replace and can also reduce disk space churn / fragmentation.

stadeschuldt 5y ago
In the above mentioned scenario there is no need for backups as the database (or more precisely the schema) is recreated at each run.
atwebb 5y ago
This works well until it doesn't and you're incrementally adding time to the loads. Some DBs handle it better than others. What most are looking for is performant, less constrained materialized views. If you're running ELT then MVs are a big deal. Additionally some DBs (SQL Server) don't have an option for pure "no logging" so, even with simple, there's overhead to the large writes. I like schema swaps too for the tables rather than renames but the pattern is the same.

thrav 5y ago

You’ve described the ELT approach that is why dbt is taking the valley by storm.

zukzuk 5y ago

That first point is exactly what dbt (https://www.getdbt.com/ https://www.getdbt.com/) does. It's an amazing tool, with extremely well thought out developer ergonomics.

sails 5y ago
Snowflake + dbt is great for this. Snowflake have a swap database function so instead of doing this at a table level you can create an entirely new database and do a blue/green style deployment. Not sure how this works with pgsql, but I suppose more complex.

diehunde 5y ago

Why would developers need to learn another tool? They are all just SQL at the end.

stadeschuldt 5y ago

The tooling behind the approach has been built as a set of python package named Mara. It is available at GitHub: https://github.com/mara/mara-pipelines https://github.com/mara/mara-pipelines And additional packages can be found at the Mara org: https://github.com/mara https://github.com/mara

m_mueller 5y ago

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

candu 5y ago

Re: renaming tables - yay! It's essentially double-buffering for databases. I've used a slight variant of this in the past: I'll have a table (e.g. my_schema_new.my_table) that gets updated by an ETL pipeline. I'll then also have a matview (e.g. my_schema.my_table) that's just SELECT * FROM my_schema_new.my_table. As long as I can give this matview a UNIQUE index, I can then REFRESH MATERIALIZED VIEW my_schema.my_table CONCURRENTLY for zero-downtime updates. (Of course, if you're using this in a non-interactive data warehouse context, you might not care so much about zero-downtime updates; this is more for application-facing views. The REFRESH ... CONCURRENTLY pattern can be faster for incremental updates, but often struggles with larger changes to the underlying table, as it's essentially applying a diff between the two versions. Also, it only works in cases where your users can tolerate data that's as stale as the scheduled time between REFRESHes.)