Re:Build your own “data lake” for reporting purposes
by 0xbadcafebee on Sunday March 14, 2021 @09:50PM
(#26461740)
Do you have a link to an article on the tuning (or could you draft one)?
Re:Build your own “data lake” for reporting purposes
by xyzzy_plugh on Sunday March 14, 2021 @10:21PM
(#26461925)
I don't, but the most important tunables are in the replication docs: https://www.postgresql.org/docs/current/runtime-config-repli...
I'd simply suggest reading this top-to-bottom very carefully, until you understand what each option does. That should in turn help you understand how and where replication impacts the source and the sinks. The docs can be terse, but it's rare for anything to be omitted -- read with care.
There are two potential flaws, though.
One is that while using a read replica for reporting works, there be dragons. There is a reasonable amount of tuning possible here. It's important not to conflate the reporting replica with a standby replica for prod. You also might consider asynchronous replication over synchronous replication for the reporting DB. Lastly, there are (tunables) limits to how much you can get away with, but long running queries on either end can potentially cause queries to get cancelled should they hold up replication enough. In other words, it is still very valuable to have optimized, small queries on the reporting DB where possible.
Second is that this works fine until your data doesn't fit on an instance. Large RDS instances are not cheap, and at some large table size it begins to make sense to look at warehouse solutions, like Spark on S3/whatever or Redshift or Snowflake, which can scale out to match your data size. I'd be concerned to find a rats-nest of reporting database instances when a proper cluster should be used.