I managed to bypass the need for the sort by relying on the active feed only sending the oldest items in for processing (it was always doing that) but based on some of the earlier e-mails in this thread, it prompted the revelation that my order by when processing was really pretty pointless because I need more-or-less ordered rather than strictly ordered and that was already happening due to how the process list was being fed.Great to hear that some of the issues are now mitigated. Though, perhaps you actually require that ORDER BY if items are expected to be sitting in the queue quite some time because you have incoming queue items in a burst pattern and have to play catch up sometimes. If so, I highly suspect the index on q_id is becoming very bloated and reindex concurrently would help.
I still need to look at that, but since I had made some progress, I got pretty exited and have not got round to this yet.
I don't know if you might have missed my last message, and the suggestion from Laurenz to check pgstattuple.
* https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/
This does warn about the overhead, but I've also upgraded pg_top on my system today and saw a useful additional data point that it displays - the number of locks held by a process.
What I see happening is that when the select statements collide, they are holding about 10-12 locks each and then begin to very slowly acquire more locks every few seconds. One process will grow quicker than others then reach the target (250) and start processing. Then another takes the lead and so on until a critical mass is reached and then the remaining all acquire their locks in a few seconds.
I still keep thinking there is some scaling type issue here in
the locking and possibly due to it being a partitioned table (due
to that tuple moved error).
