5 ms·

This is a needlessly complex solution. You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical datab

This is a needlessly complex solution. You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical database (Snowflake and BugQuery are both good choices). Setting up multiple Postgres database and linking them together with foreign data wrappers is interesting to blog about, but it’s an extremely roundabout way to solve this problem.

chatmasta 6y ago
It seems difficult to make the argument that replicating data to a warehouse is "simpler" than leaving the data in its original place (which could actually be a warehouse) and querying it through an FDW. In your scenario you need to maintain and monitor an ETL pipeline, pay storage costs for the warehouse, and likely pay bandwidth costs for moving your data into it. In the blog post scenario, the only cost is a Postgres instance and bandwidth for the data your queries return. There are many cases where replication to a warehouse makes sense, but there are also plenty where it doesn't. Our philosophy at Splitgraph, where we're building something just like this blog post, is that querying should be cheap and easy in the experimentation phase. Analysts shouldn't need to ask engineers to setup a data pipeline just so they can query some data they might not even want. So why not start with a "data lake" (or "data mesh")? You can query any data connected to the mesh without any setup or waiting time. If you eventually decide that you don't want the tradeoffs of federation, then you can selectively warehouse only the data you need to optimize certain queries. (I might go even further and suggest that the data industry has the idea of the "modern data stack" completely wrong. Replicating data to a warehouse is the ultimate act of centralization. Every other aspect of software is trending toward decentralization; it's the only real way to deal with increasing complexity and sprawl. It's only a matter of time before this paradigm shift hits the data stack too.)
- nunie123 6y ago
  I agree with much of what you said. However, I'm disinclined to believe there will be a trend away from a centralized data warehouse (or data lake). There is inherent value in having a single source of truth for analytics. With modern cloud tools abstracting away the complexity distributed analytical databases, data warehousing is getting easier and more powerful. It's true that there is added complexity in centralizing the data. But as the author of this article suggested, you're in a bad spot if your marketing team and your sales team can't agree on last month's revenue. I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized.
  - michael_j_ward 6y ago
    > There is inherent value in having a single source of truth for analytics. > I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized. Isn't the entire point of this article that the author achieved a single source of truth without moving the data by using postgres's Foreign Data Wrappers?
    - nunie123 6y ago
      The data is being moved, it's just that the mechanism to move it was the FDWs, instead of e.g. an Airflow DAG. The FDWs are interesting because they're update-on-query as opposed to updating on a fixed schedule or update on a source system event, which is more common.
      - michael_j_ward 6y ago
        > The data is being moved My understanding is that FDW's act as a proxy. I don't believe the tables are materialized. Can you link me where it says otherwise? I don't see anything in the docs [1]. [1] https://www.postgresql.org/docs/current/postgres-fdw.html https://www.postgresql.org/docs/current/postgres-fdw.html
- sa46 6y ago
  > decentralization; it's the only real way to deal with increasing complexity and sprawl Decentralization can help with sprawl (aka scale) but it makes software more complex so it’s not a way to tackle complexity. Decentralization is only tolerated because, at some point, it’s the only method left to scale. As an example, distributed databases are significantly more complex to build than a single node database. You’ve also ignored all of the downsides of using your OLTP database for OLAP queries. The most important is that adhoc queries can impact production. In my experience, that cost dwarfs the costs of maintaining an ETL pipeline as soon as you have your first outage. Another downside is that with a federated, pull based model you need to keep the mapping (FDW definition) up to date. In essence, the maintenance cost moves from an ETL pipeline to an FDW definition.
  - chatmasta 6y ago
    Re: ad-hoc queries impacting production, you can mitigate this by pointing your data mesh to a read replica of the prod DB (which you can colocate with it to avoid bandwidth costs). Admittedly at that point you might be approaching a warehousing use case. Re: maintaining the mapping. Sure that's a tradeoff. But I'd rather manage declarative FDW definitions than a programmatic ETL pipeline. Yes, handling schema changes is non-trivial (a naive starting point is periodic introspection of the remote table), but that's true in an ETL pipeline too. As you say, you're moving the cost from the ETL pipeline to the FDW definition. But what if we can lower the cost of the "FDW definition" (a.k.a. setting up and maintaining the data mesh)? From our perspective, at least with the product we're building, we think we can make a data mesh that's low maintenance enough that it makes sense as a first default before ETL. But the two aren't mutually exclusive. A data mesh can connect multiple warehouses just as easily as it can your production databases. For example, you could move data (ETL) from your data sources to your Snowflake and BigQuery warehouses, and then connect them both to your data mesh. Also, a mesh can add value as a unified access layer that proxies queries and applies data governance rules (e.g. sharing data, access control, column masking, query whitelisting, rewriting, rate limiting, auditing, firewalling, etc.). It would be hard to do this in your warehouse, because as soon as you have another one, or there's some data that you can't ETL into it, you lose any benefits of its access layer. (e.g. Maybe you have two warehouses because you haven't fully migrated to one, or for cost reasons like storing FireBase clickstream data in BigTable that you can't move to Snowflake with the rest of your data. Now you need to maintain two sets of accounts and ACLs for each warehouse.). (And for what it's worth -- this is a bit off topic of the current thread -- we do recognize there is often a use case for warehousing when the tradeoffs of federation aren't worth it. We can actually act as a warehouse too, which makes it easy to start with a data mesh but have the option to bail out to warehousing for the specific data assets that need it. Ideally, you can just click a button that says something like "snapshot data" or "schedule query" or "replicate data source." To ingest and store the data, we can leverage the concept we introduced of "data images" for storing versioned snapshots of data in delta-compressed, content-addressable chunks of hot-swappable cstore_fdw files. You can create data images with a declarative, Dockerfile-like syntax called Splitfiles. They look something like FROM upstream:tag IMPORT { SELECT x, y FROM ... }). You get full provenance and can `sgr rebuild` an updated image only when its upstream changes, just like Docker.)
- redandblack 6y ago
  Same thesis for us. Pushing to use replicated sources for a data mesh, and build around query and cache services. The thesis is analysts know what they want, and operating only on production ready reports takes too much away from business. So, more power to analysts
- georgewfraser 6y ago
  Replicating a Postgres instance to a data warehouse takes ~1 hour to set up and $500/month, depending on the database size. The cost of the data warehouse is similar. If you can’t afford that cost, you’re not going to be able to afford an analytics team to make meaningful use of that data. In that scenario, you’re better off using Google analytics + the occasional ad hoc query of your production DB (which you should only have one of, you are at way too small scale to have multiple DBs). Federated data systems, like what is described in this post, are a kind of “nerd trap.” They’re fascinating and people keep building them, but they have little real world utility. Everyone is either big enough where it’s worth it to build a real data warehouse, or small enough that they shouldn’t be messing around with complex data infrastructure.
nunie123 6y ago
I agree the BigQuery and Snowflake are very nice options for an analytical database. I've used Postgres's FDWs for an analytical DB at a past job. The reason we used them was because we needed minimal analytics for the projects, and didn't want to deal with a more complex data pipeline solution. It sounds like the author was also trying to avoid a more sophisticated data pipeline solution for as long as possible.
rockostrich 6y ago
> BugQuery Is this a typo or some kind of joke about BigQuery being buggy? Anyway, agreed on this being needlessly complex and I stopped reading pretty much immediately when I saw the article continue to talk about postgres when it came to the "data lake". If you already have a postgres database backing every service and already have views defined for the data then just set up exports of those views to BigQuery/Snowflake/Redshift/etc. All of them have out of the box templates for doing exactly that.
- georgewfraser 6y ago
  Typo :)
rualca 6y ago
> You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical database If you intentionally get rid of your original data then you're saying goodbye to provenance tracking and also open the door to lose information with each conversion you believe makes things "simpler to maintain".
glogla 6y ago
This is fundamentally a data virtualisation approach - even though it is achieved by streaming replicas and fdws instead of tradictional data replication tools. It is probably more complex than replicating a single database to a Snowflake, but one huge benefit of the described solution is that it's real-time - and getting realtime replication to data warehouse is a very different challenge. It allows you to create both batch and realtime data products just by creating views or materialized views, which is pretty simple and neat. You also get real-time ad-hoc querying, which is very valuable. The issue with this approach is it keeps the data in row-based format so from certain size it isn't going to be practical. For the "daily report" use case, it's probably fine for a while, but if the source system grow to terabyte size, you will be spending a lot of time waiting on Postgres to do it's thing. So as the data grows, you might lose first the real-time ad-hoc querying (if you ever had it), then the real-time reporting and then the daily reporting. But maybe the data will never grow that much and everything is going to be fine for a long time? Maybe this gets you where you need to be for a few years and who knows how the data platforms will look like in a few years.
zshrdlu 6y ago
Did exactly that, with a simple in-house ETL in python that has not failed us once and is virtually real-time, something the SAAS solutions could not do. Also blogged about not doing this: https://unwindprotect.com/postgres-fdw https://unwindprotect.com/postgres-fdw

chatmasta 6y ago

It seems difficult to make the argument that replicating data to a warehouse is "simpler" than leaving the data in its original place (which could actually be a warehouse) and querying it through an FDW. In your scenario you need to maintain and monitor an ETL pipeline, pay storage costs for the warehouse, and likely pay bandwidth costs for moving your data into it. In the blog post scenario, the only cost is a Postgres instance and bandwidth for the data your queries return. There are many cases where replication to a warehouse makes sense, but there are also plenty where it doesn't. Our philosophy at Splitgraph, where we're building something just like this blog post, is that querying should be cheap and easy in the experimentation phase. Analysts shouldn't need to ask engineers to setup a data pipeline just so they can query some data they might not even want. So why not start with a "data lake" (or "data mesh")? You can query any data connected to the mesh without any setup or waiting time. If you eventually decide that you don't want the tradeoffs of federation, then you can selectively warehouse only the data you need to optimize certain queries. (I might go even further and suggest that the data industry has the idea of the "modern data stack" completely wrong. Replicating data to a warehouse is the ultimate act of centralization. Every other aspect of software is trending toward decentralization; it's the only real way to deal with increasing complexity and sprawl. It's only a matter of time before this paradigm shift hits the data stack too.)

nunie123 6y ago
I agree with much of what you said. However, I'm disinclined to believe there will be a trend away from a centralized data warehouse (or data lake). There is inherent value in having a single source of truth for analytics. With modern cloud tools abstracting away the complexity distributed analytical databases, data warehousing is getting easier and more powerful. It's true that there is added complexity in centralizing the data. But as the author of this article suggested, you're in a bad spot if your marketing team and your sales team can't agree on last month's revenue. I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized.
- michael_j_ward 6y ago
  > There is inherent value in having a single source of truth for analytics. > I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized. Isn't the entire point of this article that the author achieved a single source of truth without moving the data by using postgres's Foreign Data Wrappers?
  - nunie123 6y ago
    The data is being moved, it's just that the mechanism to move it was the FDWs, instead of e.g. an Airflow DAG. The FDWs are interesting because they're update-on-query as opposed to updating on a fixed schedule or update on a source system event, which is more common.
    - michael_j_ward 6y ago
      > The data is being moved My understanding is that FDW's act as a proxy. I don't believe the tables are materialized. Can you link me where it says otherwise? I don't see anything in the docs [1]. [1] https://www.postgresql.org/docs/current/postgres-fdw.html https://www.postgresql.org/docs/current/postgres-fdw.html
sa46 6y ago
> decentralization; it's the only real way to deal with increasing complexity and sprawl Decentralization can help with sprawl (aka scale) but it makes software more complex so it’s not a way to tackle complexity. Decentralization is only tolerated because, at some point, it’s the only method left to scale. As an example, distributed databases are significantly more complex to build than a single node database. You’ve also ignored all of the downsides of using your OLTP database for OLAP queries. The most important is that adhoc queries can impact production. In my experience, that cost dwarfs the costs of maintaining an ETL pipeline as soon as you have your first outage. Another downside is that with a federated, pull based model you need to keep the mapping (FDW definition) up to date. In essence, the maintenance cost moves from an ETL pipeline to an FDW definition.
- chatmasta 6y ago
  Re: ad-hoc queries impacting production, you can mitigate this by pointing your data mesh to a read replica of the prod DB (which you can colocate with it to avoid bandwidth costs). Admittedly at that point you might be approaching a warehousing use case. Re: maintaining the mapping. Sure that's a tradeoff. But I'd rather manage declarative FDW definitions than a programmatic ETL pipeline. Yes, handling schema changes is non-trivial (a naive starting point is periodic introspection of the remote table), but that's true in an ETL pipeline too. As you say, you're moving the cost from the ETL pipeline to the FDW definition. But what if we can lower the cost of the "FDW definition" (a.k.a. setting up and maintaining the data mesh)? From our perspective, at least with the product we're building, we think we can make a data mesh that's low maintenance enough that it makes sense as a first default before ETL. But the two aren't mutually exclusive. A data mesh can connect multiple warehouses just as easily as it can your production databases. For example, you could move data (ETL) from your data sources to your Snowflake and BigQuery warehouses, and then connect them both to your data mesh. Also, a mesh can add value as a unified access layer that proxies queries and applies data governance rules (e.g. sharing data, access control, column masking, query whitelisting, rewriting, rate limiting, auditing, firewalling, etc.). It would be hard to do this in your warehouse, because as soon as you have another one, or there's some data that you can't ETL into it, you lose any benefits of its access layer. (e.g. Maybe you have two warehouses because you haven't fully migrated to one, or for cost reasons like storing FireBase clickstream data in BigTable that you can't move to Snowflake with the rest of your data. Now you need to maintain two sets of accounts and ACLs for each warehouse.). (And for what it's worth -- this is a bit off topic of the current thread -- we do recognize there is often a use case for warehousing when the tradeoffs of federation aren't worth it. We can actually act as a warehouse too, which makes it easy to start with a data mesh but have the option to bail out to warehousing for the specific data assets that need it. Ideally, you can just click a button that says something like "snapshot data" or "schedule query" or "replicate data source." To ingest and store the data, we can leverage the concept we introduced of "data images" for storing versioned snapshots of data in delta-compressed, content-addressable chunks of hot-swappable cstore_fdw files. You can create data images with a declarative, Dockerfile-like syntax called Splitfiles. They look something like FROM upstream:tag IMPORT { SELECT x, y FROM ... }). You get full provenance and can `sgr rebuild` an updated image only when its upstream changes, just like Docker.)
redandblack 6y ago
Same thesis for us. Pushing to use replicated sources for a data mesh, and build around query and cache services. The thesis is analysts know what they want, and operating only on production ready reports takes too much away from business. So, more power to analysts
georgewfraser 6y ago
Replicating a Postgres instance to a data warehouse takes ~1 hour to set up and $500/month, depending on the database size. The cost of the data warehouse is similar. If you can’t afford that cost, you’re not going to be able to afford an analytics team to make meaningful use of that data. In that scenario, you’re better off using Google analytics + the occasional ad hoc query of your production DB (which you should only have one of, you are at way too small scale to have multiple DBs). Federated data systems, like what is described in this post, are a kind of “nerd trap.” They’re fascinating and people keep building them, but they have little real world utility. Everyone is either big enough where it’s worth it to build a real data warehouse, or small enough that they shouldn’t be messing around with complex data infrastructure.

nunie123 6y ago

I agree the BigQuery and Snowflake are very nice options for an analytical database. I've used Postgres's FDWs for an analytical DB at a past job. The reason we used them was because we needed minimal analytics for the projects, and didn't want to deal with a more complex data pipeline solution. It sounds like the author was also trying to avoid a more sophisticated data pipeline solution for as long as possible.

rockostrich 6y ago

> BugQuery Is this a typo or some kind of joke about BigQuery being buggy? Anyway, agreed on this being needlessly complex and I stopped reading pretty much immediately when I saw the article continue to talk about postgres when it came to the "data lake". If you already have a postgres database backing every service and already have views defined for the data then just set up exports of those views to BigQuery/Snowflake/Redshift/etc. All of them have out of the box templates for doing exactly that.

georgewfraser 6y ago
Typo :)

rualca 6y ago

> You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical database If you intentionally get rid of your original data then you're saying goodbye to provenance tracking and also open the door to lose information with each conversion you believe makes things "simpler to maintain".

glogla 6y ago

This is fundamentally a data virtualisation approach - even though it is achieved by streaming replicas and fdws instead of tradictional data replication tools. It is probably more complex than replicating a single database to a Snowflake, but one huge benefit of the described solution is that it's real-time - and getting realtime replication to data warehouse is a very different challenge. It allows you to create both batch and realtime data products just by creating views or materialized views, which is pretty simple and neat. You also get real-time ad-hoc querying, which is very valuable. The issue with this approach is it keeps the data in row-based format so from certain size it isn't going to be practical. For the "daily report" use case, it's probably fine for a while, but if the source system grow to terabyte size, you will be spending a lot of time waiting on Postgres to do it's thing. So as the data grows, you might lose first the real-time ad-hoc querying (if you ever had it), then the real-time reporting and then the daily reporting. But maybe the data will never grow that much and everything is going to be fine for a long time? Maybe this gets you where you need to be for a few years and who knows how the data platforms will look like in a few years.

zshrdlu 6y ago

Did exactly that, with a simple in-house ETL in python that has not failed us once and is virtually real-time, something the SAAS solutions could not do. Also blogged about not doing this: https://unwindprotect.com/postgres-fdw https://unwindprotect.com/postgres-fdw