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
signature.asc
Description: PGP signature