Re: Advice on Contiguous IDs

> On Jan 9, 2018, at 1:06 AM, Brian McKiernan <brian(dot)mckiernan(at)firstcircle(dot)com> 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_column.3F_Why_aren.27t_my_sequence_nu
> 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