Re: One Sequence for all tables or one Sequence for each

Hi,

I suppose the paralel work will be a problem if you are using one
sequence for all tables. If you insert a large amount of rows in
different tables there will be great slowdown because your sequence is
the bottle neck of your database. All the inserts must read from it one
by one. If you have many sequences (one for each table PK) every insert
in a different table will use different sequence and this will improve
performance.

Kaloyan Iliev

Janning Vygen wrote:

>Hi,
>
>if you define a SERIAL column postgresql's default is to generate a sequence
>for each SERIAL column (table_column_seq). But you can use one sequence for
>the whole database like this:
>
>CREATE dbsequence;
>CREATE TABLE one (
> id int4 NOT NULL DEFAULT nextval('dbseq')
>);
>CREATE TABLE two (
> id int4 NOT NULL DEFAULT nextval('dbseq')
>);
>
>One drawback: You reach the internal end of a sequence faster if you use your
>sequence for all tables. But this can be avoided if you use int8 datatype.
>
>Are there other drawbacks/benfits using one Sequence for each table or one
>sequence for all tables?
>
>kind regards,
>janning
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>