Re: Simulating sequences - Mailing list pgsql-general

I would collect the way that 5-10 of the top databases handle unique id's (sequences) for columns and compare them in a
smallpaper. Show your boss that sequences are fairly standard and he should come around. 

Vilson farias wrote:
> Greetings,
>
>   I'm getting a big performance problem and I would like to ask you what
> would be the reason, but first I need to explain how it happens.
>
>   Let's suppose I can't use sequences (it seams impossible but my boss
> doesn't like specific database features like this one).
>
>   For sequence simulation I had created a table called cnfg_key_generation
> and each tuple holds information for one of my tables (tablename,
> current_sequencial_number). Lets check :
>
> CREATE TABLE cnfg_key_generation (
>        department  integer     NOT NULL,
>        table_name  varchar(20) NOT NULL,
>        current_key integer NOT NULL,
>        CONSTRAINT XPKcnfg_key_generation PRIMARY KEY (department,
> table_name)
> );
>
>
> Per example, for a table called 'my_test' I would have the following values
> :
>   department  = 1
>   table_name  = 'my_test'
>   current_key = 1432
>
> Everytime I want a new key to use in my_test primary-key I just increment
> current_key value. For this job, I've created a simple stored procedure
> called key_generation
>
>
> CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer AS'
> DECLARE
>   the_department ALIAS FOR $1;
>   the_table_name ALIAS FOR $2;
>   new_key_value  integer;
>   err_num        integer;
> BEGIN
>   new_value := 0;
>
>   LOCK TABLE cnfg_key_generation IN ROW EXCLUSIVE MODE;
>
>   SELECT current_value INTO new_value
>     FROM cnfg_key_generation
>     WHERE the_department = department AND the_table_name = table_name;
>
>   IF NOT FOUND THEN
>     new_key_value := 1;
>     INSERT INTO cnfg_key_generation VALUES (the_department,the_table_name,
> new_key_value);
>   ELSE
>     new_key_value := new_key_value + 1;
>
>     UPDATE cnfg_key_generation
>       SET current_key_value = new_key_value
>       WHERE department = the_department AND
>             table_name = the_table_name;
>   END IF;
>
>   RETURN new_key_value;
>
> END;
> '
> LANGUAGE 'plpgsql';
>
>
> Data insertion is done by the following way :
>
> INSERT INTO my_test VALUES (key_generation(1, 'my_test'), ...other
> fields...);
>
> Ok, this works, but has a huge performance problem. I read in pg docs that
> everytime I make an UPDATE, a new tuple is created and the old is marked as
> invalid. For a tuple that holds sequencial key generation, everytime a new
> key is generated, a new tuple is created inside cfg_key_generation. It means
> after 2million key generations for same table, performance will be completly
> degradated because there will be 2million of old versions of same tuple. For
> instance, I have a table called 'cham_chamada' that actually holds
> 1.5Million of tuples. The response time for key_generation execution for
> this table is more than 5seconds. In this same case if I execute
> key_generation for a table that has just few values (current key = 5 per
> example), response time is just some miliseconds (30 to 50ms).
>
> I tryied to fix this problem with a VACUUM and it was completly ineffective.
> After execution the problem was still there. Later, after execution of every
> kind of vacuum I knew (with and without ANALYZE, especific for that table,
> vacuumdb from shell ...) I gave up and tryied a COPY TO/DROP/CREATE/COPY
> FROM. At this time, the performance problem was fixed.
>
> What can I do to solve this problem without table reconstruction? Is there a
> performance degradation in PostgreSQL that can't be solved? If a have a huge
> table with millions of data being update constantly, what can I do to keep a
> good performance if vacuum isn't 100%?
>
> Does PostgreSQL sequences deal with these kinds performance questions? Is it
> clean, fast and secury to use sequences? Maybe I still can make up my boss
> mind about this subject if I get good arguments here to justify the use of
> sequences.
>
> Am I doing some stupid thing?
>
> Best regards,
>
> -----------------------------------------------------------------
> Jos? Vilson de Mello de Farias
> Software Engineer
>
> D?gitro Tecnologia Ltda - www.digitro.com.br
> APC - Customer Oriented Applications
> E-mail: vilson.farias@digitro.com.br
> Tel.: +55 48 281 7158
> ICQ 11866179
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>