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

> On Jul 28, 2025, at 10:28 AM, Ron Johnson <ronljohnsonjr(at)gmail(dot)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 not fully 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 was not full thought through or two different routines process records in a different way (which is common, when you have team 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 same invoice.
Where as; if you update the invoice table then the invoice_detail; then there should not be any deadlocks regardless if two people try and update the same invoice.