Thread: One Sequence for all tables or one Sequence for each table?

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

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
>
>
>
>

On Thu, Jun 02, 2005 at 12:58:33PM +0300, Kaloyan Iliev Iliev wrote:
> 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.

I don't know about this. Sequences are designed to be very efficient,
they don't rollback and can be cached by backends.

In several of the databases I setup, I sometimes arranged for sequences
to start at different points so when you setup a foreign key there was
no chance you linked it to the wrong table. This especially in cases
where there might be confusion about which table links where.

Using one serial for everything does this even better. As for
performance, I think disk I/O is going to be an issue before getting
sequence numbers will be...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Am Donnerstag, 2. Juni 2005 12:03 schrieb Martijn van Oosterhout:
> On Thu, Jun 02, 2005 at 12:58:33PM +0300, Kaloyan Iliev Iliev wrote:
> > Hi,
> >
> > I suppose the paralel work will be a problem if you are using one
> > sequence for all tables.
>
> I don't know about this. Sequences are designed to be very efficient,
> they don't rollback and can be cached by backends.
>
> In several of the databases I setup, I sometimes arranged for sequences
> to start at different points so when you setup a foreign key there was
> no chance you linked it to the wrong table. This especially in cases
> where there might be confusion about which table links where.
>
> Using one serial for everything does this even better. As for
> performance, I think disk I/O is going to be an issue before getting
> sequence numbers will be...

I guess i will use one sequence for all tables if there are now drawbacks.
BTW: OIDs are using the same conecpt, don't they? And for me it makes sense
to use a sequence only for getting a unique identifier and nothing else. even
better if this identifier is unique among all tables.

Thanks a lot for your opinions!

regards
janning