RE: LWLocks by LockManager slowing large DB

> For toast tables we do not keep locks held for the duration of the
transaction, > but release the lock as soon as one access is done.
...
> The ability to lock a toast table? Yea, it might be worth doing that. I
seem to > recall this being discussed not too long ago...

Actually, the requested improvement I was thinking of was to have the
locks on the toast table somehow have the same lifespan as the locks on
the main table to avoid this problem to begin with.

---Paul

Paul Friedman
CTO

677 Harrison St  |  San Francisco, CA 94107
M: (650) 270-7676
E-mail: paul(dot)friedman(at)streetlightdata(dot)com

-----Original Message-----
From: Andres Freund <andres(at)anarazel(dot)de>
Sent: Tuesday, April 13, 2021 1:48 PM
To: Paul Friedman <paul(dot)friedman(at)streetlightdata(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: LWLocks by LockManager slowing large DB

Hi,

On 2021-04-13 11:47:06 -0700, Paul Friedman wrote:
> YES!!! This completely alleviates the bottleneck and I'm able to run
> the queries full-throttle. Thank you SO much for your help+insight.

Cool. And damn: I can't immediately think of a way to optimize this to not
require this kind of hack in the future.

> Interestingly, "lock pg_toast.pg_toast_2233612264 in ACCESS SHARE MODE;"
> which should do the same thing returns an error " ERROR:
> "pg_toast_2233612264" is not a table or view"
>
> Sounds like I should file this as a requested improvement?

The ability to lock a toast table? Yea, it might be worth doing that. I
seem to recall this being discussed not too long ago...

Greetings,

Andres Freund