I always wondered about the claim that SKIP LOCKED is all that efficient. Surely there are lots of cases where this is a really suboptimal pattern.
Simple example: if you have a mixture of very short jobs and longer duration jobs, then there might be hundreds or thousands of short jobs executed for each longer job. In such a case the rows in the jobs table for the longer jobs will be skipped over hundreds of times. The more long-running jobs running concurrently, the more wasted work as locked rows get skipped again and again. It wouldn't be a huge issue if load is low, but surely a case where rows get moved to a separate "running" table would be more efficient. I can think of several other scenarios where SKIP LOCKED would lead to lots of wasted work.
Simple example: if you have a mixture of very short jobs and longer duration jobs, then there might be hundreds or thousands of short jobs executed for each longer job. In such a case the rows in the jobs table for the longer jobs will be skipped over hundreds of times. The more long-running jobs running concurrently, the more wasted work as locked rows get skipped again and again. It wouldn't be a huge issue if load is low, but surely a case where rows get moved to a separate "running" table would be more efficient. I can think of several other scenarios where SKIP LOCKED would lead to lots of wasted work.