Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: Advice on Contiguous IDs
From: Steve Atkins <steve@blighty.com>
In-Reply-To: <5a546fcab6ae2e0000bdb8e4@polymail.io>
Date: Tue, 9 Jan 2018 09:14:38 -0800
Cc: Brian McKiernan <brian.mckiernan@firstcircle.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F1B6FDCC-FE62-405D-828C-D7C20DEE9033@blighty.com>
References: <5a546fcab6ae2e0000bdb8e4@polymail.io>
To: pgsql-docs@postgresql.org
Precedence: bulk


> On Jan 9, 2018, at 1:06 AM, Brian McKiernan =
<brian.mckiernan@firstcircle.com> wrote:
>=20
>=20
> Hi Folks,
>=20
> Looking for some help/advice - not sure if this is the appropriate =
channel.

pgsql-general would be a better bet.

>=20
> 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.

>=20
> 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_o=
f_my_sequence.2FSERIAL_column.3F_Why_aren.27t_my_sequence_nu
> mbers_reused_on_transaction_abort.3F
>=20
> 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