Thread: Sequence vs Serial

Sequence vs Serial

From

Daniel CAUNE

Date:

01 April 2007, 05:09:16

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
10.0pt;font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">I was wondering when it is better to choose sequence, and when it is better to use serial.
 Theserial type is a sequence with default parameters (<a
href="http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL">http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL</a>).
 Actually,I never use serial – I prefer sequence for some reasons that I explain later in this electronic mail – but I
maybe wrong.  It’s never late to learn!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">There is not that much difference between using sequence or serial when the current value
needsto be returned (which is most of my cases).</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Using a sequence [1]:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">nextval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id');</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
color="black"face="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(id, mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mytable_id, v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Using a serial:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt; 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">currval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('mytable_id_seq');</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:black"> </span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">I would say that these two code snippets have equivalent performance, if sequence
</span></font><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black">seq_mytable_id</span></font><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial">has been created with default parameters.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">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:
<i><spanstyle="font-style:italic">the optional clause CACHE specifies how many sequence numbers are to be preallocated
andstored in memory for faster access.</span></i>  When inserting a lot of entities in a few times, that should make a
bigdifference!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Now, when is it better to use serial?  Serial is easier to define and to use.  I would say
thatserial could be used to insert entities in <i><span style="font-style:italic">dimension</span></i> tables (such as
atable that references countries, and where a unique identifier must be defined), a table which data are inserted when
theoperational system is setup.  However, using serial for <i><span style="font-style:italic">fact</span></i> tables
(suchas a table that stores player actions in an MMO game) seems to not be that much relevant. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">What are your experiences?</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Regards,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">[1] It is possible to use another form, similar to serial:</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">CREATE SEQUENCE </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:black">seq_mytable_id;</span></font><pclass="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold"> </span></font></b><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">CREATE TABLE </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable(id int </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">nextval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id'), mycolumn1 int , mycolumn2 int);</span></font><p
class="MsoNormal"style="text-indent:24.0pt;text-autospace:none"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial">Then it is possible to use the sequence as follows:</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
color="black"face="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">currval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id');</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">--</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Daniel</span></font></div>

Re: Sequence vs Serial

From

Scott Marlowe

Date:

01 April 2007, 07:58:45

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.

Re: Sequence vs Serial

From

Tom Lane

Date:

01 April 2007, 18:51:59

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