public inbox for [email protected]
help / color / mirror / Atom feedAdvice on Contiguous IDs
6+ messages / 5 participants
[nested] [flat]
* Advice on Contiguous IDs
@ 2018-01-09 09:06 Brian McKiernan <[email protected]>
2018-01-09 16:47 ` Re: Advice on Contiguous IDs Alvaro Herrera <[email protected]>
2018-01-09 16:57 ` Re: Advice on Contiguous IDs David G. Johnston <[email protected]>
2018-01-09 17:14 ` Re: Advice on Contiguous IDs Steve Atkins <[email protected]>
2018-01-09 17:33 ` Re: Advice on Contiguous IDs Vik Fearing <[email protected]>
0 siblings, 4 replies; 6+ messages in thread
From: Brian McKiernan @ 2018-01-09 09:06 UTC (permalink / raw)
To: pgsql-docs
Hi Folks,
Looking for some help/advice - not sure if this is the appropriate channel.
My Issue:
My primary keys in a certain table are not contiguous.
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... ( https://wiki.postgresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_col... )
mbers_reused_on_transaction_abort.3F ( https://wiki.postgresql.org/wiki/FAQ#Why_are_there_gaps_in_the_numbering_of_my_sequence.2FSERIAL_col... )
My Question:
1) What event would cause the CACHE clause in CREATE SEQUENCE to make an out of sequence next number?
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?
Many thanks in advance,
Brian
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Advice on Contiguous IDs
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
@ 2018-01-09 16:47 ` Alvaro Herrera <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2018-01-09 16:47 UTC (permalink / raw)
To: Brian McKiernan <[email protected]>; +Cc: pgsql-docs
Brian McKiernan wrote:
> My Issue:
> My primary keys in a certain table are not contiguous.
If you have a need to have values that are contiguous, you need to ask
yourself why and then see what mechanism provides the semantics you
need. An easy way is to lock the table containing the column, for
example, which of course means only one transaction can do it at a time.
For many use cases this is good enough.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Advice on Contiguous IDs
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
@ 2018-01-09 16:57 ` David G. Johnston <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2018-01-09 16:57 UTC (permalink / raw)
To: Brian McKiernan <[email protected]>; +Cc: pgsql-docs
On Tue, Jan 9, 2018 at 2:06 AM, Brian McKiernan <
[email protected]> wrote:
> 1) What event would cause the CACHE clause in CREATE SEQUENCE to make an
> out of sequence next number?
>
None - it will always issue the next sequential value when asked. But the
transaction asking doesn't have to use the provided value as the PK for the
table or, and even if it does, if the transaction fails and rolls-back the
sequence value it received is discarded/lost.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Advice on Contiguous IDs
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
@ 2018-01-09 17:14 ` Steve Atkins <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: Steve Atkins @ 2018-01-09 17:14 UTC (permalink / raw)
To: pgsql-docs; +Cc: Brian McKiernan <[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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Advice on Contiguous IDs
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
@ 2018-01-09 17:33 ` Vik Fearing <[email protected]>
2018-01-10 10:25 ` Re: Advice on Contiguous IDs Brian McKiernan <[email protected]>
3 siblings, 1 reply; 6+ messages in thread
From: Vik Fearing @ 2018-01-09 17:33 UTC (permalink / raw)
To: Brian McKiernan <[email protected]>; pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Advice on Contiguous IDs
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
2018-01-09 17:33 ` Re: Advice on Contiguous IDs Vik Fearing <[email protected]>
@ 2018-01-10 10:25 ` Brian McKiernan <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Brian McKiernan @ 2018-01-10 10:25 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; pgsql-docs
Thanks folks - extremely insightful.
Much appreciated.
Brian
On Wed 10 Jan 2018 at 01:33 Vik Fearing < Vik Fearing ( Vik Fearing <[email protected]> ) > wrote:
>
>
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-01-10 10:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 09:06 Advice on Contiguous IDs Brian McKiernan <[email protected]>
2018-01-09 16:47 ` Alvaro Herrera <[email protected]>
2018-01-09 16:57 ` David G. Johnston <[email protected]>
2018-01-09 17:14 ` Steve Atkins <[email protected]>
2018-01-09 17:33 ` Vik Fearing <[email protected]>
2018-01-10 10:25 ` Brian McKiernan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox