public inbox for [email protected]  

help / color / mirror / Atom feed

From: Vik Fearing <[email protected]>
To: Brian McKiernan <[email protected]>
To: [email protected]
Subject: Re: Advice on Contiguous IDs
Date: Tue, 9 Jan 2018 18:33:49 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Advice on Contiguous IDs
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox