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?

Improve this question
7
  • Did you test that the row level trigger really is that much slower? In my experience the assumed performance improvement for a statement level trigger isn't as big as one would expect. Maybe you could create a materialized view for the aggregated data on the replica?
    – user1822
    Commented Nov 30, 2022 at 9:53
  • @a_horse_with_no_name good catch! To be honest i didn't measure the performance difference between ROW and STATEMENT triggers. But it just "feels wrong" to calculate that much data on a row level when a batch INSERT with 10.000 rows arrives... But maybe i'm totally wrong. Will test this, thanks. Materialized table is a good idea, but the subscriber table already has +100 million rows. The refresh process is just not efficient anymore.
    – Tom
    Commented Nov 30, 2022 at 10:00
  • @a_horse_with_no_name that's unfortunate now. I just did a test run with FOR EACH ROW instead of FOR EACH STATEMENT and the INSERT time increased from 1 minute to over an hour (i'm canceling the query now). Do you see any other workaround? Thanks!
    – Tom
    Commented Nov 30, 2022 at 12:10
  • How to re-write the logic of a FOR EACH STATEMENT trigger into a FOR EACH ROW to do the same thing would depend on the logic of the trigger, which you haven't shown or described in any meaningful way.
    – jjanes
    Commented Dec 1, 2022 at 15:20
  • What is your test case like? If the master doesn't have the info needed to do the work, and the replica can't use FOR EACH STATEMENT, then where are you getting the timing of the FOR EACH STATEMENT?
    – jjanes
    Commented Dec 1, 2022 at 15:24

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Thank you so much for your input! One problem that prevents me from doing it like this is, that only the standby has all the needed data for calculations and aggregations. That's the beauty of the solution with triggers. Once the data arrives from the publishers i can collect everything (spread across multiple tables) and write the result to the final dimension table. Or am i missing something here?
    – Tom
    Commented Nov 30, 2022 at 12:48
  • 1
    Ah, yes, then that won't work of course.
    – Laurenz Albe
    Commented Nov 30, 2022 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.