Mime-Version: 1.0
Subject: Advice on Contiguous IDs
Date: Tue, 09 Jan 2018 01:06:26 -0800
Message-ID: <5a546fcab6ae2e0000bdb8e4@polymail.io>
From: "Brian McKiernan" <brian.mckiernan@firstcircle.com>
To: pgsql-docs@postgresql.org
Content-Type: multipart/alternative;
 boundary=fe2b427c7ca1e9ba9268224cad304e2e1512225e510203022714394badb2
Precedence: bulk

--fe2b427c7ca1e9ba9268224cad304e2e1512225e510203022714394badb2
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8

Hi Folks,

Looking for some help/advice - not sure if this is the appropriate channel.

My Issue:
My primary keys in a certain table are not contiguous.

What I have done so far:
I have checked the documentation and found:=C2=A0https://wiki.postgresql.or=
g/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_colu=
mn.3F_Why_aren.27t_my_sequence_nu ( https://wiki.postgresql.org/wiki/FAQ#Wh=
y_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_column.3F_Why_are=
n.27t_my_sequence_numbers_reused_on_transaction_abort.3F )
mbers_reused_on_transaction_abort.3F ( https://wiki.postgresql.org/wiki/FAQ=
#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_column.3F_Why_=
aren.27t_my_sequence_numbers_reused_on_transaction_abort.3F )

My Question:
1) What event would cause the=C2=A0CACHE clause in CREATE SEQUENCE to make =
an out of sequence next number?
2) In all cases am I correct in my thinking that in order to create contigu=
ous primary key IDs then performance will greatly suffer? Do we have an ide=
a of how bad this will generally be or what does that depend upon?

Many thanks in advance,
Brian
--fe2b427c7ca1e9ba9268224cad304e2e1512225e510203022714394badb2
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=UTF-8

<img style=3D"border: none; background:none; width: 0; height: 0;" src=3D"h=
ttps://share.polymail.io/v2/z/a/NWE1NDZmY2FiNmFl/YFEHcjV7mmc0Vgmvta6hEb0bHE=
fQb4Pwwk-2KHD8U-X_7AmwmPTtqePmwzsNIk7HHtpVGy87mIrBCgOxY88FQsat1qF5zu9xfXdET=
e7OPgM0le41J6n2suqFA5ohbsjbH1oAb3uizZU7cxyZVZZO9XVWBfA=3D.png" alt=3D"" wid=
th=3D"0px" height=3D"0px" border=3D"0" /><div></div>
<div>Hi Folks,</div><div><br></div><div>Looking for some help/advice - not =
sure if this is the appropriate channel.</div><div><br></div><div>My Issue:=
</div><div>My primary keys in a certain table are not contiguous.</div><div=
><br></div><div>What I have done so far:</div><div>I have checked the docum=
entation and found:&nbsp;<a href=3D"https://wiki.postgresql.org/wiki/FAQ#Wh=
y_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_column.3F_Why_are=
n.27t_my_sequence_numbers_reused_on_transaction_abort.3F">https://wiki.post=
gresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSE=
RIAL_column.3F_Why_aren.27t_my_sequence_nu</a></div><div><a href=3D"https:/=
/wiki.postgresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_seq=
uence.2FSERIAL_column.3F_Why_aren.27t_my_sequence_numbers_reused_on_transac=
tion_abort.3F">mbers_reused_on_transaction_abort.3F</a><br></div><div><br><=
/div><div>My Question:</div><div>1) What event would cause the&nbsp;<span s=
tyle=3D"font-family: sans-serif; font-size: 12.7px; font-variant-ligatures:=
 normal; orphans: 2; widows: 2; background-color: rgb(255, 255, 255);">CACH=
E clause in CREATE SEQUENCE to make an out of sequence next number?</span><=
/div><div><span style=3D"font-family: sans-serif; font-size: 12.7px; font-v=
ariant-ligatures: normal; orphans: 2; widows: 2; background-color: rgb(255,=
 255, 255);">2) In all cases am I correct in my thinking that in order to c=
reate contiguous primary key IDs then performance will greatly suffer? Do w=
e have an idea of how bad this will generally be or what does that depend u=
pon?</span></div><div><span style=3D"font-family: sans-serif; font-size: 12=
.7px; font-variant-ligatures: normal; orphans: 2; widows: 2; background-col=
or: rgb(255, 255, 255);"><br></span></div><div><span style=3D"font-family: =
sans-serif; font-size: 12.7px; font-variant-ligatures: normal; orphans: 2; =
widows: 2; background-color: rgb(255, 255, 255);">Many thanks in advance,</=
span></div><div><span style=3D"font-family: sans-serif; font-size: 12.7px; =
font-variant-ligatures: normal; orphans: 2; widows: 2; background-color: rg=
b(255, 255, 255);">Brian</span></div><div><span style=3D"font-family: sans-=
serif; font-size: 12.7px; font-variant-ligatures: normal; orphans: 2; widow=
s: 2; background-color: rgb(255, 255, 255);"><br></span></div><div></div>
--fe2b427c7ca1e9ba9268224cad304e2e1512225e510203022714394badb2--