Subject: Re: Advice on Contiguous IDs
To: Brian McKiernan <brian.mckiernan@firstcircle.com>,
 pgsql-docs@postgresql.org
References: <5a546fcab6ae2e0000bdb8e4@polymail.io>
From: Vik Fearing <vik.fearing@2ndquadrant.com>
Organization: 2ndQuadrant France
Message-ID: <a1df83a6-dbdf-4f29-83eb-af5eae74aa76@2ndquadrant.com>
Date: Tue, 9 Jan 2018 18:33:49 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.0
MIME-Version: 1.0
In-Reply-To: <5a546fcab6ae2e0000bdb8e4@polymail.io>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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