Thanks folks - extremely insightful. Much appreciated.
Brian On Wed 10 Jan 2018 at 01:33 Vik Fearing < Vik Fearing ( Vik Fearing <vik.fear...@2ndquadrant.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 > > >
