January 2026 Summaries

A new API endpoint at Mergify encountered performance issues when deployed to production, timing out due to inefficient SQL queries. Initially, a naive approach involved a subquery that scanned 660,000 rows to map SHAs to pull requests, which significantly slowed down the system as it constructed a comprehensive SHA-to-pull-request mapping before applying selective filters. This inefficiency was rectified by using a LATERAL JOIN, which allowed for selective filters to be applied first, drastically reducing the number of rows processed. By performing an indexed lookup for each row in the test metrics rather than a full table scan, the response time improved by a factor of 1000, demonstrating the impact of join order on query performance in PostgreSQL.

Asyncio's concurrency relies on cooperative multitasking, where blocking calls can freeze an entire application by preventing other tasks from progressing. This issue is often difficult to detect, as it can cause subtle delays in HTTP handlers, background jobs, and other time-sensitive operations. A solution is to implement a simple watchdog coroutine that measures event loop latency by checking how late it wakes up after a sleep command, which can signal when the loop is blocked. By exporting this latency as metrics, developers can create graphs and alerts to identify and address blocking tasks immediately. Common sources of blocking include time.sleep(), synchronous HTTP or database clients, CPU-heavy tasks, and synchronous filesystem operations. The watchdog helps correlate latency spikes with other system metrics, allowing developers to address issues by moving CPU-heavy operations to separate executors, switching to asynchronous clients, or isolating tasks into worker services.