Performance Bottleneck with Deep Child Updates – Major Optimization Ready

[DrRiyadh]

Hi @toluaina, First, thank you for the awesome tool — I’ve been using pgsync v4.1.0 in production and wanted to share some major findings and potential improvements.

My schema includes deep and wide child relationships (foreign keys), and while insert/update operations on the main/root node are extremely fast, I encountered serious performance issues with updating child tables.

Problem: Updating child nodes takes over 108 seconds per batch in real-world tests. The performance varies depending on load and data relationships, and the system becomes unstable and glitchy under high activity.

I reviewed multiple existing issues, but none resolved this situation.

Solution: After conducting a thorough investigation, I identified the root cause as the repeated resolution of keys and Elasticsearch queries per payload by_root_foreign_key_resolver (), which resulted in significant delays. I created a small optimization PR that:

• Batches and reuses filter resolutions.
• Skips redundant ES queries for each child node.
• Reduced update time from 108s ➝ <0.4s per bulk (verified in real data).
• Confirmed stable performance even when a single child change propagates to 200+ main/root document updates.

Bonus: Trigger Improvement I also improved the trigger generation logic:

• It now notifies only for the columns defined in the schema, not all table columns.
• Added a flag (TRIGGER_ALL_COLUMNS) to toggle between specific or all columns easily.

I’d love to contribute this work back to the community! Can you please give me permission to open a PR, or let me know how you'd prefer I share the code/patch for review?

Happy to share benchmarks or examples as well. Thanks again for your efforts! Riy

[toluaina]

Thanks. Please go ahead and open a PR with the proposed changes.

[DrRiyadh]

Hi Toluaina,

I tried to push my code by I got permission issues.

Best Regards Riyadh

On Sun, Jul 20, 2025 at 10:29 AM Tolu Aina @.***> wrote:

toluaina left a comment (toluaina/pgsync#601) https://github.com/toluaina/pgsync/issues/601#issuecomment-3094570020

Thanks. Please go ahead and open a PR with the proposed changes.

— Reply to this email directly, view it on GitHub https://github.com/toluaina/pgsync/issues/601#issuecomment-3094570020, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIFJYKCABGW4DVRPH5XZHIL3JORV3AVCNFSM6AAAAACBMFZGUSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTAOJUGU3TAMBSGA . You are receiving this because you authored the thread.Message ID: @.***>

[toluaina]

That is strange. nothing has changed in terms of repo permissions.

• Can you fork the repo.
• make your changes
• and then raise a PR.

Alternatively, whatever is easiest you can send me the path or diff or files directly.

[DrRiyadh]

@toluaina I've raised a PR for that https://github.com/toluaina/pgsync/pull/602 — please take a look when you have a chance.