21–30 of 42 posts
Noob question: What is the advantage of replicating data into a warehouse vs. just querying it in place on a postgres database?
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.
Noob question: What is the advantage of replicating data into a warehouse vs. just querying it in place on a postgres database?
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.
Plus how is the deduplication process handled? Fivetran for example creates staging tables and scans the target table. Since it does support BigQuery's integer based partitioning. A table partitioned by Primary key helps in cost optimizations.
Are BigQuery's clustered and partitioned supported (both as a source and sink). Plus how is the deduplication process handled? Fivetran for example creates staging tables and scans the target table. Since it does support BigQuery's integer based partitioning. A table partitioned by Primary key helps in cost optimizations.
Regarding the deduplication process, we stage raw CDC data into a staging table and run periodic MERGE operations. The raw table is also auto-clustered and partitioned similar to final table. This helps save costs.
Are BigQuery's clustered and partitioned supported (both as a source and sink). Plus how is the deduplication process handled? Fivetran for example creates staging tables and scans the target table. Since it does support BigQuery's integer based partitioning. A table partitioned by Primary key helps in cost optimizations.
Noob question: What is the advantage of replicating data into a warehouse vs. just querying it in place on a postgres database?
Earlier quoted context omitted.
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.
What about using a read-only replica for reporting. Are there any downsides to that? Seems to be easier to manage
If not, then is there any other solution?
How do you handle Postgres cluster failover? Does PeerDB automatically restore logical replication slot on a new primary?
Great question! We have retry logic in place to handle Postgres restarts. If the failure occurs in-place, you should be fine as the slot will persist. If Postgres fails over to the standby, PeerDB will wait until the slot is created. We did consider automatically creating the slot if it doesn't exist on retries, but ensuring data reliability (creating the slot right after failover without data being ingested) is tric…