3 ms·

Noob question: What is the advantage of replicating data into a warehouse vs. just querying it in place on a postgres database?

CuriouslyC 2y ago
If the postgres database is recording business transactions, you don't want to cause your business to stop being able to take credit cards because you generated a report.
- arsalanb 2y ago
  Assuming you use a connection pool, why would it stop? Either the query returns a result or it doesnt? Am I missing something?
  - CuriouslyC 2y ago
    Reporting queries can put a significant load on the db, to the point that it interrupts service.
    - necubi 2y ago
      Futhermore, Postgres is an OLTP (transactional) database, designed to efficiently perform updates and deletes on individual rows. OLAP (analytical) databases/query engines like Clickhouse, Presto, Druid, etc. are designed for efficient processing across many rows of mostly unchanging data. Analytical queries (like "find the average sales price across all orders over the past year, grouped by store and region") can be 100-1000x faster in an OLAP database compared to Postgres.
    - arsalanb 2y ago
      I see, thanks!
- throwaway82533 2y ago
  What about using a read-only replica for reporting. Are there any downsides to that? Seems to be easier to manage
  - cgio 2y ago
    That’s the use case for cdc, to make it equally easy to use a DW. As always the complexity is just air you move in the balloon. The oltp db can spit out the events and forget them, how you load them efficiently is now a data engineer’s problem to solve ( if it was easy to write event grain on an olap you would not need an oltp). Kafka usually enters the room at this stage and the simplification promise is becoming tenuous.
  - mritchie712 2y ago
    that works great if all the data you need for reporting is in the database you're replicating. You'd likely want a data warehouse if you also need to report on data that isn't in your prod database (e.g. stripe, your CRM, marketing data, etc.). If setting up a data warehouse, etl, BI, etc. sounds like a lot of work to get reports, you're right, it is. shameless plug: we're making this all much simpler at https://www.definite.app/ https://www.definite.app/
matthieucan 2y ago
Additionally, unless your data model is designed as append-only (which is unusual and requires logic downstream), you won't be able to track updates and deletions, which are valuable for reporting
ralfhn 2y ago
Data warehouses are structured to handle large volumes of data and complex queries more efficiently than a typical transactional database like PostgreSQL.
efxhoy 2y ago
Typically data warehouses are OLAP databases that have much better performance than OLTP databases for large queries. There might also be several applications in a company, each with their own database, and a need to produce reports based on combinations of data from multiple applications. I think that in many cases your question is based on an idea that is completely right. engineers are too eager to split out applications into multiple databases and tacking on separate data warehouses. The costs of maintaining separate databases is often higher than initially thought. Especially when some of the data in the warehouse needs to go back into the application database, for example for customer facing analytics. I think many companies would be better served by considering traditional data warehousing needs directly in their main application databases and abstain from splitting out databases. Having one single ACID source of truth and paying a bit more for a single beefy database server makes a lot more sense than is commonly thought. Especially now when many customer facing products, like recommendation systems, are “data driven”. At least that’s my impression after working in the space for a while.
acossta 2y ago
When you need to do large/medium-scale analytical queries. Postgres is fairly slow for aggregate/group queries needed for analytics. Think if you're building Google Analytics type functionality.
nijave 2y ago
Event sourcing Also reporting/analytics work loads tend to be ad hoc queries and hard to optimize so you generally favor fast storage over indexes. Frequently for analytics and reporting it's more efficient to use a columnar DB than a relational db

CuriouslyC 2y ago

If the postgres database is recording business transactions, you don't want to cause your business to stop being able to take credit cards because you generated a report.

arsalanb 2y ago
Assuming you use a connection pool, why would it stop? Either the query returns a result or it doesnt? Am I missing something?
- CuriouslyC 2y ago
  Reporting queries can put a significant load on the db, to the point that it interrupts service.
  - necubi 2y ago
    Futhermore, Postgres is an OLTP (transactional) database, designed to efficiently perform updates and deletes on individual rows. OLAP (analytical) databases/query engines like Clickhouse, Presto, Druid, etc. are designed for efficient processing across many rows of mostly unchanging data. Analytical queries (like "find the average sales price across all orders over the past year, grouped by store and region") can be 100-1000x faster in an OLAP database compared to Postgres.
  - arsalanb 2y ago
    I see, thanks!
throwaway82533 2y ago
What about using a read-only replica for reporting. Are there any downsides to that? Seems to be easier to manage
- cgio 2y ago
  That’s the use case for cdc, to make it equally easy to use a DW. As always the complexity is just air you move in the balloon. The oltp db can spit out the events and forget them, how you load them efficiently is now a data engineer’s problem to solve ( if it was easy to write event grain on an olap you would not need an oltp). Kafka usually enters the room at this stage and the simplification promise is becoming tenuous.
- mritchie712 2y ago
  that works great if all the data you need for reporting is in the database you're replicating. You'd likely want a data warehouse if you also need to report on data that isn't in your prod database (e.g. stripe, your CRM, marketing data, etc.). If setting up a data warehouse, etl, BI, etc. sounds like a lot of work to get reports, you're right, it is. shameless plug: we're making this all much simpler at https://www.definite.app/ https://www.definite.app/

matthieucan 2y ago

Additionally, unless your data model is designed as append-only (which is unusual and requires logic downstream), you won't be able to track updates and deletions, which are valuable for reporting

ralfhn 2y ago

Data warehouses are structured to handle large volumes of data and complex queries more efficiently than a typical transactional database like PostgreSQL.

efxhoy 2y ago

Typically data warehouses are OLAP databases that have much better performance than OLTP databases for large queries. There might also be several applications in a company, each with their own database, and a need to produce reports based on combinations of data from multiple applications. I think that in many cases your question is based on an idea that is completely right. engineers are too eager to split out applications into multiple databases and tacking on separate data warehouses. The costs of maintaining separate databases is often higher than initially thought. Especially when some of the data in the warehouse needs to go back into the application database, for example for customer facing analytics. I think many companies would be better served by considering traditional data warehousing needs directly in their main application databases and abstain from splitting out databases. Having one single ACID source of truth and paying a bit more for a single beefy database server makes a lot more sense than is commonly thought. Especially now when many customer facing products, like recommendation systems, are “data driven”. At least that’s my impression after working in the space for a while.

acossta 2y ago

When you need to do large/medium-scale analytical queries. Postgres is fairly slow for aggregate/group queries needed for analytics. Think if you're building Google Analytics type functionality.

nijave 2y ago

Event sourcing Also reporting/analytics work loads tend to be ad hoc queries and hard to optimize so you generally favor fast storage over indexes. Frequently for analytics and reporting it's more efficient to use a columnar DB than a relational db