Why we spent the last month eliminating PostgreSQL subtransactions

In an investigation to resolve mysterious database stalls on GitLab.com, it was discovered that SQL SAVEPOINT queries within long transactions were causing performance issues on database replicas, leading to 500 errors for users. The problem, dubbed "Nessie," was traced to PostgreSQL's handling of subtransactions, where a single SAVEPOINT during a long transaction could trigger suboverflow, leading to cache misses and high disk I/O. This was exacerbated by replication differences between primary and replica databases. To mitigate this, GitLab eliminated all SAVEPOINT queries from their code, which resolved the issue. The investigation was aided by extending observability tools and leveraging community expertise, highlighting PostgreSQL's robust code but also its limitations in handling subtransactions. The team considered but ultimately opted against using unofficial PostgreSQL patches that could allow larger subtransaction caches, focusing instead on code modifications to prevent the issue from recurring.

No tracked trend matches for this post yet.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.