Mime-Version: 1.0
To: "Vik Fearing" <vik.fearing@2ndquadrant.com>, pgsql-docs@postgresql.org
Message-ID: <5a55e9c44a197700005f0ec1@polymail.io>
Subject: Re: Advice on Contiguous IDs
Date: Wed, 10 Jan 2018 02:25:24 -0800
In-Reply-To: <a1df83a6-dbdf-4f29-83eb-af5eae74aa76@2ndquadrant.com>
References: <a1df83a6-dbdf-4f29-83eb-af5eae74aa76@2ndquadrant.com>
From: "Brian McKiernan" <brian.mckiernan@firstcircle.com>
Content-Type: multipart/alternative;
 boundary=52df1f4f88b1b8167ac8e657f06cd1223f3f09aa3272f8c36b449ad931eb
Precedence: bulk

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

Thanks folks - extremely insightful.

Much appreciated.

Brian

On Wed 10 Jan 2018 at 01:33 Vik Fearing < Vik Fearing ( Vik Fearing <vik.fe=
aring@2ndquadrant.com> ) > wrote:

>=20
>=20
>=20
> On 01/09/2018 10:06 AM, Brian McKiernan wrote:
> > Hi Folks,
> >
> > Looking for some help/advice - not sure if this is the appropriate
> channel.
>=20
> It is not. You want the pgsql-general list, or perhaps pgsql-novice.
>=20
> > My Issue:
> > My primary keys in a certain table are not contiguous.
>=20
> Is that really an issue? The only valid case of gapless sequences I've
> ever seen is invoice numbers. If you're not doing that, why do you care?
>=20
> > My Question:
> > 1) What event would cause the=C2=A0CACHE clause in CREATE SEQUENCE to m=
ake an
>=20
> > out of sequence next number?
>=20
> If the server crashes, it can jump ahead by up to 32 values. This is so
> sequences don't have to be WAL logged every single time which could be
> quite slow.
>=20
> > 2) In all cases am I correct in my thinking that in order to create
> > contiguous primary key IDs then performance will greatly suffer? Do we
> > have an idea of how bad this will generally be or what does that depend
> > upon?
>=20
> Performance itself doesn't really suffer, concurrency does. If you have
> a lot of concurrent inserts on this table, then global performance will
> indeed be worse than if you didn't care about gaps. If it's just one
> process doing the insert, you won't notice any performance drop at all.
> --
> Vik Fearing +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>=20
>=20
>
--52df1f4f88b1b8167ac8e657f06cd1223f3f09aa3272f8c36b449ad931eb
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/NWE1NWU5YzQ0YTE5/2hQKsrrIJ2KE1qlogkaR6JbkZ4=
-5ftc26NrwX3jSQsptHisvrgGhS_9F3GIwpLV4WlKxthmqMQfIcYog7EpetOGPJyoPbv30wyLTg=
VpKcoQONEe8fGw2DN80K9iC4fjthqkzLXoCJ7jqs9gp8-c4N35ff7o=3D.png" alt=3D"" wid=
th=3D"0px" height=3D"0px" border=3D"0" /><div></div>
<div></div>
<div>
Thanks folks - extremely insightful.<br>
</div><div><br></div><div>Much appreciated.</div><div><br></div><div>Brian<=
/div>
<div id=3D"psignature">
<br>
</div>
<div class=3D"gmail_extra"><br><div class=3D"gmail_quote"><div dir=3D"ltr">=
On Wed 10 Jan 2018 at 01:33 Vik Fearing <vik.fearing@2ndquadrant.com> &lt;<=
a href=3D"mailto:Vik Fearing &lt;vik.fearing@2ndquadrant.com&gt;">Vik Feari=
ng <vik.fearing@2ndquadrant.com></vik.fearing@2ndquadrant.com></a>&gt; wrot=
e:<br></vik.fearing@2ndquadrant.com></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><p=
>On 01/09/2018 10:06 AM, Brian McKiernan wrote:
<br>&gt; Hi Folks,
<br>&gt;=20
<br>&gt; Looking for some help/advice - not sure if this is the appropriate=
 channel.
<br>
<br>It is not.  You want the pgsql-general list, or perhaps pgsql-novice.
<br>
<br>&gt; My Issue:
<br>&gt; My primary keys in a certain table are not contiguous.
<br>
<br>Is that really an issue?  The only valid case of gapless sequences I've
<br>ever seen is invoice numbers.  If you're not doing that, why do you car=
e?
<br>
<br>&gt; My Question:
<br>&gt; 1) What event would cause the&nbsp;CACHE clause in CREATE SEQUENCE=
 to make an
<br>&gt; out of sequence next number?
<br>
<br>If the server crashes, it can jump ahead by up to 32 values.  This is s=
o
<br>sequences don't have to be WAL logged every single time which could be
<br>quite slow.
<br>
<br>&gt; 2) In all cases am I correct in my thinking that in order to creat=
e
<br>&gt; contiguous primary key IDs then performance will greatly suffer? D=
o we
<br>&gt; have an idea of how bad this will generally be or what does that d=
epend
<br>&gt; upon?
<br>
<br>Performance itself doesn't really suffer, concurrency does.  If you hav=
e
<br>a lot of concurrent inserts on this table, then global performance will
<br>indeed be worse than if you didn't care about gaps.  If it's just one
<br>process doing the insert, you won't notice any performance drop at all.
<br>--=20
<br>Vik Fearing                                          +33 6 46 75 15 36
<br><a href=3D"http://2ndQuadrant.fr">http://2ndQuadrant.fr</a>     Postgre=
SQL : Expertise, Formation et Support
<br></p></blockquote></div></div><br>
--52df1f4f88b1b8167ac8e657f06cd1223f3f09aa3272f8c36b449ad931eb--