Re: Why many more deadlocks after upgrade to PG 17.5?

> On Jul 28, 2025, at 10:28 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> RHEL 8.10
> Prior version: 14.18
>
> There were deadlocks when at PG 14, but a small fraction of the current number of deadlocks.
>
> All tables were vacuumed and analyzed immediately after the pg_upgrade. 😉
>
> Tables are partitioned by range (weekly).  Physical replication; no logical replication.
>   There have been no code changes since the pg_upgrade (performed 9 nights ago).
>
> Attached is a section of the PG log file.  It's the same kind of deadlock, in the same code as before; just now there
are_more_ of them. 
>
> I don't control the schema or the application, or the code in the application; we just need to know why there would
be_more_ in 17.5 than in 14.18. 
>

Deadlock are an application issue.  The application is accesses rows in order that causes the conflict.

i.e.

process 1 attempts to update row: r1,r2,r3
process 2 attempts to update row: r1,r3,r2

That above scenario will cause a deadlock.

The real question is what is the application doing? And how is it updating the records; is there a deterministic order?

All things being equal; here are some things that can cause deadlocks where it use to work fine — if the updates are
notfully deterministic: 

1. Execution plan is different thus it changing the order of row being updated.
2. Race conditions; just different performance metrics.
3. Where the row lives in the table.
4. New index; changing the order of execution
etc.

If the application does not guarantee the update order then deadlocks can/will occur for any of those reasons.

In a well defined system there shouldn't be deadlocks.  Deadlock usually occur because the of order of execution that
wasnot full thought through or two different routines process records in a different way (which is common, when you
haveteam of developers and they code things differently).   

i.e.

If you update the invoice_detail table then invoice table; that it is likely to have deadlocks if two people update the
sameinvoice. 
Where as; if you update the invoice table then the invoice_detail; then there should not be any deadlocks regardless if
twopeople try and update the same invoice.