Incident Library

IL-0582 Kanmu · 2024-12-08 (published)databaseaws

Jobs piled up in Kanmu's PostgreSQL-based qg job queue on Aurora as dequeue slowed and dead tuples kept growing

Kanmu runs its product database on Amazon Aurora PostgreSQL, a cluster with a writer instance and a reader instance. The same database is a transactional job queue through qg, a Go implementation of Que: jobs are rows in the que_jobs table, enqueued by the api and worker workloads and deleted by workers when finished. Workers dequeue with a recursive CTE that tries pg_try_advisory_lock on candidate jobs.

pg_stat_activity showed an unintended query from the PostgreSQL user created for Redash. Redash was meant to query the reader instance, but when the author had registered the Aurora database as a Redash data source in the past, the author had set the cluster endpoint, which points at the writer, instead of the reader endpoint. The query combined large table scans with CTEs, showed wait_event io:BufFileWrite, and kept running after the Redash client had disconnected, because the user had no query timeout such as statement_timeout. Under READ COMMITTED the query took its snapshot when it started, so que_jobs rows deleted after that were still visible to it and VACUUM would not remove them; the query's backend_xmin, 1226078887, matched the oldest xmin in the VACUUM output. The dequeue query filters only on queue and run_at, so the index scan also reached rows that were already dead and had to fetch each one to check its visibility, which slowed dequeue and raised the database load.

The team changed the Redash data source to the reader endpoint, ended the query's process with pg_terminate_backend and ran VACUUM again by hand, which cleaned up the dead tuples in one go, and dequeue performance returned to normal. Performance Insights had shown the query prominently, but it was missed at the time.

Severity
–
Scope
Asynchronous jobs in the qg job queue on the product database piled up and were processed slowly
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

A Datadog alert fired because a large number of unprocessed jobs had piled up in Kanmu's job queue and were not being worked off. The jobs were the batch enqueued at about the same time every day, in the usual volume, but the workers took far longer than normal to dequeue and process them.

Worker logs showed the job fetch time, lock_time, longer than normal. The Datadog dead tuple metric postgresql.dead_rows showed the dead tuples of the que_jobs table growing steadily from a certain time. The team already knew that growing dead tuples degrade qg performance. autovacuum, which should remove them, did not seem to be working.

A manual VACUUM VERBOSE que_jobs reported "found 0 removable, 768195 nonremovable row versions in 15623 out of 15623 pages" and "679473 dead row versions cannot be removed yet, oldest xmin: 1226078887", so running VACUUM did not reclaim anything.

Symptom Throughput degradationHigh latency

Root cause

Root cause Operator error

Component DatabasePostgreSQL vacuum
Contributing factor Change with a delayed effectUnreviewed defaults
Trigger
A heavy ad hoc analysis query from the BI tool Redash ran for a long time on the writer instance, because the Redash data source pointed at the cluster endpoint.

Detection

A Datadog alert on unprocessed jobs piling up in the job queue.

Remediation

Action items

Lessons

A long-running query on the same database instance, even one unrelated to que_jobs, can keep VACUUM from removing que_jobs dead tuples, so the table keeps growing and the job queue slows down. qg's design with advisory locks and a recursive CTE is sensitive to dead tuples.

Sources

References

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, PostgreSQL vacuum among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.