Queue-Style Batch Deletion Woes in PostgreSQL

Shayon Mukherjee runs into an interesting issue:

I recently discovered an unexpected behavior in PostgreSQL involving a pattern of using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items from a queue-like table. What seemed straightforward turned out to have a surprising interaction with the query planner.

Click through to see what happened, as well as Shayon’s recommendation on how to fix it.