Hi,
On Tue, Dec 10, 2019 at 03:07:05PM -0300, Alvaro Herrera wrote:
> I'd rather have the ability to mark a table READ ONLY (or similar).
> Then any FK references can skip the row locks altogether. For the rare
> cases where you need to modify the referenced table, have it marked READ
> WRITE, and any row locks are registered normally from that point on,
> until you set it back to READ ONLY again.
However, that would require changes to applications writing to the table
and a good understanding of performance characteristics by everyone
trying to get to that scale. (OTOH, there is certainly an argument to be
made that whoever hits this kind of problem better also has an idea of
postgres performance tuning anyway.)
More troubling (to me) is that I already know of another table in the
system which should be next-in-line for the same problem, but only on
some rows: It represents accounting entities, of which a very (nearly
static) few are payment processors and all others are customers. From
the application's perspective there's not too much difference between
those, but any customer row will typically only be share locked once,
whereas share locks on payment processor rows will be held by most of
the transactions currently active.
That use-case is not very uncommon I think, so it migth be worthwhile
to implement a solution which does not require all rows of a table to
share similar lock contention characteristics, or writability.
Regards,
Drahflow
