Love, Death and PostgreSQL Triggers (2022)

The article discusses how the GitGuardian engineering team initially leveraged PostgreSQL triggers to enhance performance but later decided to remove them due to various issues. Triggers were used to precompute aggregates for complex queries, but they led to unexpected behavior and performance problems over time. The cascading nature of triggers caused hidden complexities and performance issues, especially with bulk inserts. To address these issues, the team decided to remove triggers and opt for a simpler approach by computing fields in asynchronous tasks using Celery. This decision aimed to maintain read performance while accepting lower write performance and a slight delay in updating computed fields. The team concluded that while triggers can be useful for enforcing consistency, they must be kept simple and integrated into standard development processes. Despite the challenges faced, some triggers were retained at GitGuardian.