Re: Advice on Contiguous IDs

Thanks folks - extremely insightful.

Much appreciated.

Brian

On Wed 10 Jan 2018 at 01:33 Vik Fearing < Vik Fearing ( Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com> ) > wrote:

>
>
>
> 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.
>
> It is not. You want the pgsql-general list, or perhaps pgsql-novice.
>
> > My Issue:
> > My primary keys in a certain table are not contiguous.
>
> 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?
>
> > My Question:
> > 1) What event would cause the CACHE clause in CREATE SEQUENCE to make an
>
> > out of sequence next number?
>
> 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.
>
> > 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?
>
> 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
>
>
>