One Sequence to Rule Them All!

[fsalvelt]

Hi folks!

I have a question about a best practice when using sequences.

Do you guys think it is better to create one master sequence that can be used to populate all candidate keys for all tables, or is it recommended to create a sequence for each candidate key?

Here is an example of a global sequence:

Code: Select all

dbadmin=> create table tab1 (pk1 int);
CREATE TABLE
dbadmin=> create table tab2 (pk2 int);
CREATE TABLE
dbadmin=> create table tab3 (pk3 int);
CREATE TABLE
dbadmin=> create sequence master_seq;
CREATE SEQUENCE
dbadmin=> insert into tab1 select master_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into tab2 select master_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into tab3 select master_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from tab1;
 pk1
-----
   1
(1 row)

dbadmin=> select * from tab2;
 pk2
-----
   2
(1 row)

dbadmin=> select * from tab3;
 pk3
-----
   3
(1 row)

Here is an example of a sequence for each table:

Code: Select all

dbadmin=> create table tab1 (pk1 int);
CREATE TABLE
dbadmin=> create table tab2 (pk2 int);
CREATE TABLE
dbadmin=> create table tab3 (pk3 int);
CREATE TABLE
dbadmin=> create sequence tab1_seq;
CREATE SEQUENCE
dbadmin=> create sequence tab2_seq;
CREATE SEQUENCE
dbadmin=> create sequence tab3_seq;
CREATE SEQUENCE
dbadmin=> insert into tab1 select tab1_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into tab2 select tab2_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into tab3 select tab3_seq.nextval;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from tab1;
 pk1
-----
   1
(1 row)

dbadmin=> select * from tab2;
 pk2
-----
   1
(1 row)

dbadmin=> select * from tab3;
 pk3
-----
   1
(1 row)

So, what are the opinions out there on which method is best?

Thanks!