3

We are using logical replication to sync data into a table. Perfect solution so far!

The problem now is we need triggers on this subscriber table. Those triggers make heavy calculations (think of calculating balances and aggregated data from the "raw" data in the subscriber table). That's why FOR EACH STATEMENT trigger would be preferable. We definitely wanna avoid that those calculations happen on every single row insert.

Documentation (https://www.postgresql.org/docs/current/logical-replication-architecture.html) says:

The logical replication apply process currently only fires row triggers, not statement triggers.

Any ideas how we can achieve something similar without FOR EACH STATEMENT trigger? Keeping those two tables in sync - the subscriber table with the raw data & the table with calculated/aggregated data?

Tom
  • 131
  • 3

2 Answers2

1

In my opinion, the simplest solution is not to have a trigger run on the logical standby, but to replicate the calculated/aggregated data along with the rest. That saves you from having to perform the same expensive calculations on the standby.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
0

Probably academic at this late moment, but if you feed enough information to your logical replica to make a decision as to when kick off what calculation process, perhaps through a second table that acts as a 'job controller', that should place all the control back into your hands.

Ellert van Koperen
  • 190
  • 1
  • 6