Updates added, mixture of good and bad news:
How long does each process take in total? How strict does that FIFO really need to be when you are already doing SKIP LOCKED anyway?
The processes all bottleneck[ed] for several minutes, approximately exponential to the number above the threshold where the problem happened. Up to around 60 concurrent worked with minimal delay but beyond that a few more added about a minute, 70 about 5 minutes, 80 about 30 minutes and beyond that was hours (I left up to 12 hours one time).
However, I removed the order by clause which eliminated [only] the high CPU. The processes all stopped in the same pattern, just without the high CPU use. So the ordering was the factor in the CPU use, but was not responsible for the - forgive the pun - lock up.
I then added a random few seconds of delay to each process before
it executes the select in order to prevent too many of them
colliding on simultaneous selects. That was enough to make the
lock-up disappear and individual selects complete in a few ms,
regardless of how many other concurrent transactions are in
progress (tested up to 192 concurrent). But still not quite out
the woods - read below.
Can you expound on the partitioning? Are all consumers of the queue always hitting one active partition and anytime a row is processed, it always moves to one of many? archived type partitions?
Partitions are list partitioned as 'incoming', 'processing', 'retry', 'ok', 'failed':
Incoming: This is itself hash partitioned (64 partitions) approx 10M rows added/day so partitioned to allow incoming throughput; this works well.
Processing: Simple partition, data is moved into this in blocks as the rows count drops below a threshold, another block is added, coming from the incoming.
Retry: simple partition, non fatal errors go in here and go back into the processing queue for retries later.
Failed: simple partition, fatal errors go here. Thankfully very few.
OK: hash partition, as everything that was in incoming should eventually end up here. 64 partitions currently.
There is one interesting thing about this. When the SELECT FOR UPDATE SKIP LOCKED is executed, reasonably frequently, the select aborts with the error:
Tuple to be locked was already moved to another partition due to concurrent update.
This error still persists even though the lock-up has been
removed by the time delay, so there is a regular stream of
transactions aborting due to this (I just re-run the transaction
to recover).
Now, if locking worked as I understand it, if another process locked and migrated, this should still have left the lock in place on the original partition and created a new one on the newly inserted partition until a commit was done. The second process should not have visibility on the newly inserted row and the skip locked should simply have skipped over the locked but deleted row on the original partition.
What am I missing? All of this feels like some
locking/partitioning issue but I'm unsure exactly what.
Alas not FDW, an actual external system elsewhere in the world which sends an ACK when it has processed the message. I have no control or influence on this.Is that done via FDW or otherwise within the same database transaction? Are you connecting some queue consumer application code to Postgres, select for update, doing work on some remote system that is slow, and then coming back and committing the DB work?
Noted!By the way, top-posting is discouraged here and partial quotes with interspersed comments are common practice.
