Re: Simulating sequences - Mailing list pgsql-general
From | Stephan Szabo |
---|---|
Subject | Re: Simulating sequences |
Date | |
Msg-id | 20030818083807.J27257-100000@megazone.bigpanda.com Whole thread Raw |
In response to | Simulating sequences ("Vilson farias" <vilson.farias@digitro.com.br>) |
List | pgsql-general |
On Mon, 18 Aug 2003, Vilson farias wrote: > 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. From that state, you might want to try a REINDEX (in case you're running into index growth problems), and look at the output of VACUUM VERBOSE. Also, you might want to see what kind of plans are being generated for the queries inside the function (rather than what would be generated on the command line with values inserted). I believe Tom Lane sent an example of doing this within the last couple of months to one of the mailing lists, you may be able to find it in the archives. One other thing is that you should probably be vacuuming this table alot more frequently than you appear to be. Depending on the frequency of calls to this, you may also want to raise your free space map settings (you can get some guidelines later based on the results of vacuum verbose). > 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. Generally, yes. They also don't force you down to only having one transaction that attempts to get the value at a time, but don't actually guarantee sequential values, merely unique ones (within their range).
pgsql-general by date: