Обсуждение: Sequence vs Serial
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-SERIAL). 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
Daniel CAUNE <d.caune@free.fr> Said: > 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-SERIAL). > Actually, I never use serial – I prefersequence for some > reasons that I explain later in this electronic mail – but I may > be wrong. It’s never late to learn! >From the psql command line: \h alter sequence Command: ALTER SEQUENCE Description: change the definition of a sequence generator Syntax: ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] \h create sequence Command: CREATE SEQUENCE Description: define a new sequence generator Syntax: CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUEmaxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] Basically, the only thing that I see you can't define with alter is to make it a temp sequence, which wouldn't make a whole lot of sequence. One reason for using serial versus sequence is that a serial gives you automatic dependency tracking. If you create a table with a serial and drop the table, the sequence underlying the serial type is automagically deleted for you. OTOH, if you are gonna have two or more tables share a sequence, then it might be better to not use a serial to start it, as you might decide to delete the table that created the sequence and things would break in strange ways. Either way, I'm just glad PostgreSQL's sequences aren't as difficult to alter as Oracles. Read the post at the bottom of this page to gape in wonder at one of the many hoops I have to jump through when I use that bothersome database: http://www.techonthenet.com/oracle/sequences.php urg.
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Daniel CAUNE <d.caune@free.fr> Said:
>> I was wondering when it is better to choose sequence, and when
>> it is better to use serial.
> One reason for using serial versus sequence is that a serial gives you
> automatic dependency tracking.
Note that as of 8.2, with the introduction of ALTER SEQUENCE OWNED BY,
you can manipulate the column-to-sequence dependency by hand. This
means there really is no difference between starting from a separate
sequence and starting with a "serial" column declaration --- you can get
to all the same database states either way.
I tend to think that it's best to use a serial if you intend only the
one column to use the sequence generator, whereas if you intend multiple
columns to be fed from the same sequence generator you ought to declare
the sequence as a freestanding object to start with. But it's just a
question of style.
regards, tom lane