PostgreSQL Stories: A simple query with a big problem

Eric Fritz at Render shares a debugging journey involving a problematic PostgreSQL query that blocked their staging pipeline, despite appearing simple with relevant indexes and a LIMIT 1 clause. The query caused significant memory and CPU usage spikes, leading to a four-hour-old transaction that hindered database migrations and deploys to production. The culprit was identified using the pg_stat_activity system view, which revealed a long-running query issued accidentally by an engineer through a psql shell while validating event data. The engineer's attempt to cancel the query was ineffective, as it continued running in the background. Once identified, the query was terminated, resolving the immediate issue, but questions about why it was so slow remain. This incident highlights the importance of understanding query performance and the potential pitfalls of open transactions in PostgreSQL. The story also emphasizes Render's commitment to delivering a reliable and efficient cloud infrastructure experience for developers.