Postgres SELECT DISTINCT Does Not Scale

DBOS describes a performance limitation in PostgreSQL’s `SELECT DISTINCT` when identifying active partitions in Postgres-backed queues. Although an index on queue name, workflow status, and partition key was expected to let the query retrieve one row per unique partition, PostgreSQL instead performed a full index scan across every matching enqueued workflow, causing latency to scale linearly with rows per partition rather than with the number of partitions. This was especially problematic for narrow, deep queues containing few partitions with many workflows, where a query expected to take under a millisecond could take seconds. The post attributes this behavior to PostgreSQL lacking a loose index scan operator comparable to MySQL’s, while noting that PostgreSQL 18’s skip-scan optimization does not address this particular `SELECT DISTINCT` case. As a workaround, DBOS used a recursive CTE that repeatedly finds the next smallest partition key through indexed `MIN()` queries, retrieving one value per iteration and achieving performance proportional to the number of unique partitions rather than the total number of rows.

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.