| From: | Jesper Krogh <jesper(at)krogh(dot)cc> |
|---|---|
| To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
| Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Lock pileup causes server to stall |
| Date: | 2014-11-13 06:53:41 |
| Message-ID: | B69D0038-538E-4F15-961F-F5F31867E8AC@krogh.cc |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
>>>>
>>>
>>> Current FK checking makes you wait if the referenced tuple is modified
>>> on any indexed column, not just those that are actually used in
>>> foreign keys. Maybe this case would be sped up if we optimized that.
>>
>> Even if it is an gin index that is being modified? seems like a harsh limitation to me.
>
> Well, as I recall it's only unique indexes, so it's not *that* harsh.
>
Sounds good. Indices are there for all kinds of reasons, unique ones are more related to referential integrity, so even not 100% accurate, at least 90% of the way in my world.
We do have an "star"-schema in the db with some amount of information needed in the center that needs updates, apart from that a massive update activity on the sorrounding columns, locks on the center entity has quite high impact on the sorrounding updates. (9.2 moving to 9.3 reallly soon and looking forward for this enhancement.
Jesper
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Filip Rembiałkowski | 2014-11-13 08:10:23 | 9.0 performance degradation with kernel 3.11 |
| Previous Message | Josh Berkus | 2014-11-12 17:04:06 | Re: Lock pileup causes server to stall |