[SQL] Sequence vs Serial

[Daniel CAUNE]

Hi,

 

I was wondering when it is better to choose sequence, and when it is better
to use serial.  The serial type is a sequence with default parameters
(http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SE
RIAL).  Actually, I never use serial - I prefer sequence for some reasons
that I explain later in this electronic mail - but I may be wrong.  It's
never late to learn!

 

There is not that much difference between using sequence or serial when the
current value needs to be returned (which is most of my cases).

 

Using a sequence [1]:

 

SELECT INTO v_mytable_id nextval('seq_mytable_id');

 

    INSERT INTO mytable(id, mycolumn1, mycolumn2)

      VALUES (v_mytable_id, v_mycolumn1, v_mycolumn2);

 

    RETURN v_mytable_id;

 

 

Using a serial:

 

    INSERT INTO mytable(mycolumn1, mycolumn2)

      VALUES (v_mycolumn1, v_mycolumn2);

 

SELECT INTO v_mytable_id currval('mytable_id_seq');

 

RETURN v_mytable_id;

 

 

I would say that these two code snippets have equivalent performance, if
sequence seq_mytable_id has been created with default parameters.

 

The reason why I generally prefer sequence over serial is that it is
possible to "tune" sequence, which it seems to not be possible while using
serial.  For instance, the sequence allows defining a cache: the optional
clause CACHE specifies how many sequence numbers are to be preallocated and
stored in memory for faster access.  When inserting a lot of entities in a
few times, that should make a big difference!

 

Now, when is it better to use serial?  Serial is easier to define and to
use.  I would say that serial could be used to insert entities in dimension
tables (such as a table that references countries, and where a unique
identifier must be defined), a table which data are inserted when the
operational system is setup.  However, using serial for fact tables (such as
a table that stores player actions in an MMO game) seems to not be that much
relevant. 

 

What are your experiences?

 

Regards,

 

 

 

[1] It is possible to use another form, similar to serial:

 

CREATE SEQUENCE seq_mytable_id;

 

CREATE TABLE v_mytable(id int nextval('seq_mytable_id'), mycolumn1 int ,
mycolumn2 int);

 

Then it is possible to use the sequence as follows:

 

    INSERT INTO mytable(mycolumn1, mycolumn2)

      VALUES (v_mycolumn1, v_mycolumn2);

 

SELECT INTO v_mytable_id currval('seq_mytable_id');

 

    RETURN v_mytable_id;

 

--

Daniel