Skip site navigation (1) Skip section navigation (2)

Re: Performance Concern

From: Allen Landsidel <all(at)biosys(dot)net>
To: thebfh(at)toolsmythe(dot)com, john(at)pagakis(dot)com,pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance Concern
Date: 2003-10-25 11:20:03
Message-ID: 6.0.0.22.0.20031025070905.02458060@pop.hotpop.com (view raw or flat)
Thread:
Lists: pgsql-performance
At 05:56 10/25/2003, John Pagakis wrote:

Snipping most of this, I only have one suggestion/comment to make.

[snip]

>CREATE TABLE baz (
>     baz_key int4 NOT NULL,
>     baz_number CHAR(15) NOT NULL,
>     customer_id CHAR(39),
>     foobar_id INTEGER,
>     is_cancelled BOOL DEFAULT false NOT NULL,
>     create_user VARCHAR(60) NOT NULL,
>     create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
>     last_update_user VARCHAR(60) NOT NULL,
>     last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
>     CONSTRAINT PK_baz PRIMARY KEY (baz_key)
>);
>
>ALTER TABLE baz
>     ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id);
>
>ALTER TABLE baz
>     ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id);

[snip]

>I needed to do this because I absolutely positively cannot over-allocate
>baz.  I cannot allocate more than 100,000 period, and any number of users
>can attempt to purchase one or more baz simultaneously.  I am attempting to
>avoid a race condition and avoid using database locks as I feared this table
>would turn into a bottleneck.

[snip]

I have a similar situation in the database here, using the following 
example schema:

CREATE TABLE foo
(
   nID serial UNIQUE NOT NULL,
   bAvailable boolean NOT NULL DEFAULT true,
   nSomeField int4 NOT NULL,
   sSomeField text NOT NULL
);

CREATE TABLE bar
(
   nfoo_id int4 UNIQUE NOT NULL
);

Assume foo is the table with the 100k pre-populated records that you want 
to assign to visitors on your site.  bar is a table whos only purpose is to 
eliminate race conditions, working off the following business rules:

1. -- someone attempts to get a 'foo'
   SELECT nID from foo WHERE bAvailable;

2. -- we first try to assign this 'foo' to ourselves
    -- the ? is bound to the foo.nID we selected in step 1.
   INSERT INTO bar (nfoo_ID) VALUES (?)

3. -- Only if step 2 is successful, do we continue, otherwise someone beat 
us to it.
   UPDATE foo SET ... WHERE nID=?

The key here is step 2.

Since there is a UNIQUE constraint defined on the bar.nfoo_id (could even 
be an FK), only one INSERT will ever succeed.  All others will fail.  In 
step 3, you can set the bAvailable flag to false, along with whatever other 
values you need to set for your 'baz'.

This will get much easier once 7.4 is production-ready, as the WHERE IN .. 
or WHERE NOT IN.. subselects are (according to the HISTORY file) going to 
be as efficient as joins, instead of the O(n) operation they apparently are 
right now.

Until then however, I've found this simple trick works remarkably well.

-Allen 


In response to

pgsql-performance by date

Next:From: Greg StarkDate: 2003-10-25 15:08:03
Subject: Re: Performance Concern
Previous:From: John PagakisDate: 2003-10-25 09:56:10
Subject: Re: Performance Concern

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group