Performance Bottleneck with Deep Child Updates – Major Optimization Ready #601
Description
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