Emmas is right that the biggest win is keeping transactions off the wire, so I’ll build on that with the parts specific to your example and to n8n in queue mode.
On your snippet: the SELECT … FOR UPDATE followed immediately by a single-row UPDATE is locking twice. A single UPDATE already takes the row lock it needs, so the explicit FOR UPDATE just widens the window you hold it. For a status flip like this, collapse the whole block into one conditional update: UPDATE workflow_jobs SET status='completed' WHERE job_id=123 AND status='processing';. That is atomic, holds the lock for the shortest possible instant, and gives you optimistic concurrency for free: if another worker already advanced the row, zero rows are affected and you know it without a separate read. That is your answer to row-level vs optimistic, make the conditional update the default and keep FOR UPDATE only for the cases where you genuinely do more work between the lock and the commit.
On where the contention actually comes from in queue mode: it is often not your tables at all, it is n8n’s. Every worker writes execution_entity and execution_data, and if EXECUTIONS_DATA_SAVE_ON_SUCCESS is left at ‘all’, every successful run during a webhook burst writes a large row at exactly the moment you have the most concurrency. Turning off success-data saving for the high-volume workflows and pruning execution data on a schedule removes a whole band of write contention that has nothing to do with workflow_jobs. Check pg_stat_user_tables to see whether the hot table is even yours before you tune your own locking.
On deadlocks and on catching this early, which are the same problem: a deadlock is almost always two transactions taking the same rows in a different order, so when you do need multi-row locks, take them in a consistent order (ORDER BY job_id in the locking read). More importantly, set two timeouts so a stuck lock fails fast and visibly instead of silently blocking every worker behind it. lock_timeout, so a statement that cannot get its lock gives up quickly, and idle_in_transaction_session_timeout, so a transaction left open by a wedged node (the exact thing Emmas warned about) gets killed instead of holding rows forever. Without those, the failure is silent: workers pile up waiting, throughput collapses, and nothing errors.
For the leading indicator you asked about, lock wait time only tells you once it is already hurting. The earlier signal, run on a schedule, is SELECT pid, state, wait_event_type, now()-xact_start AS tx_age, query FROM pg_stat_activity WHERE state='idle in transaction' OR wait_event_type='Lock' ORDER BY tx_age DESC;. Anything sitting in idle in transaction with a growing tx_age is a transaction holding locks while doing nothing, which is the state that becomes contention a few seconds later. Alert on that age crossing a threshold and you catch it before it blocks anyone.
This is basically what I do for people running n8n in production, turning a silent slowdown into something you can see and alert on before it takes the instance down, so if a second set of eyes on the live instance would help, happy to talk. But the conditional update and the two timeouts you can apply today.