public inbox for [email protected]  

help / color / mirror / Atom feed

From: Steve Atkins <[email protected]>
To: [email protected]
Cc: Brian McKiernan <[email protected]>
Subject: Re: Advice on Contiguous IDs
Date: Tue, 9 Jan 2018 09:14:38 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>


> On Jan 9, 2018, at 1:06 AM, Brian McKiernan <[email protected]> wrote:
> 
> 
> Hi Folks,
> 
> Looking for some help/advice - not sure if this is the appropriate channel.

pgsql-general would be a better bet.

> 
> My Issue:
> My primary keys in a certain table are not contiguous.

That itself isn't a problem at all. If there's a business requirement for them to be contiguous that's the issue to consider first.

> 
> What I have done so far:
> I have checked the documentation and found: https://wiki.postgresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_col...
> mbers_reused_on_transaction_abort.3F
> 
> My Question:
> 1) What event would cause the CACHE clause in CREATE SEQUENCE to make an out of sequence next number?

It causes PostgreSQL to assign batches of numbers to each connection that needs one, making it more likely that they'll be used out of order or that some won't be used at all.

Using cache just makes it more obvious, though. There's no guarantee that a sequence will give you consecutive numbers, nor that they'll be ordered, in general. About the only thing that is guaranteed is that they'll be unique.

> 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?

Yes. You will have to effectively serialize all inserts into those tables, eliminating any concurrency.

You'd need to have a pretty compelling hard business requirement for consecutive numbers before it'd be worth considering.

Cheers,
  Steve

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