Re: the jokes for pg concurrency write performance

On Tue, 2 Feb 2010, wyx6fox(at)sina(dot)com wrote:

> hi, first, thanks u for make so good opensource db .

first you thank the developers, then you insult them. are you asking for
help or just trying to cause problems.

> recently maybe half an years ago ,i begin to use pg in a big project for insurance project, belong as the project go on ,and
> i found some performance problem on concurrency write situation , then i do a research on concurrency write strategy on postgresql ,
>
> i found a joke ,maybe this joke concurrency strategy is the designer's pround idea, but i think it is a joke , next let me describe the problems:
>
> * joke 1: insert operation would use a excluse lock on reference row by the foreign key . a big big big performance killer , i think this is a stupid design .

this I don't know enough to answer

> * joke 2: concurrency update on same row would lead to that other transaction must wait the earlier transaction complete , this would kill the concurrency performance in some long time transaction situation . a stupid design to ,
>
> this joke design's reason is avoid confliction on read committed isolation , such as this situation:
> UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
> when concurrency write transaction on read committed isolation , the hits may result wrong .
>
> this joke design would do seriable write , but i think any stupid developer would not write this code like this stupid sample code , a good code is
> use a exclusive lock to do a seriable write on this same row , but the joker think he should help us to do this , i say ,u should no kill concurrency performance and help i do this fucking stupid sample code , i would use a select .. for update to do this :
>
> select 1 from lock_table where lockId='lock1' for update ;
> UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
>

If you have one transaction start modifying a row, and then have another
one start, how do you not have one wait for the other? Remember that any
transaction can end up running for a long time and may revert at any time.

Why would you want to lock the entire table for an update as simple as you
describe?

> * joke 3: update 100000 rows on a table no any index , it cost 5-8 seconds , this is not acceptable in some bulk update situation .

This one is easy, you did 100000 inserts as seperate transactions, if you
do them all as one transaction (or better still as a copy) they would
complete much faster.

You seem to be assuming incopatence on the part of the developers whenever
you run into a problem. If you want them to help you, I would suggest that
you assume that they know what they are doing (after all, if they didn't
you wouldn't want to use their code for anything important anyway), and
instead ask what the right way is to do what you are trying to do.

David Lang