Sequence gaps after restart
Hello.
I'm using PostgreSQL v. 10.14 via AWS Aurora Serverless. Our users
recently noticed gaps in the primary ids for many of our tables. As I
understand it, this is due to PostgreSQL effectively caching 32 additional
sequence values (hardcoded in sequence.c ) and a 'restart' occurs. Losing
a few sequence values is normal and generally acceptable. However, this
problem seems to be exacerbated by the scaling events of Aurora Serverless
as they require restarts of the Postgresql service. I do not allow AWS AS
to force the scaling in our environment. When scaling events happen 10's of
times per day, the loss is significant.
Are there any plans to fix this issue or otherwise play nicely with Aurora
Serverless?
Thanks.
-Chris
--
This message may be confidential and privileged. Use or disclosure by
anyone other than an intended addressee is prohibited. If you received this
message in error, please delete it and advise the sender by reply email.
On Tue, May 11, 2021 at 5:55 PM Christopher Sumner
<christopher.sumner@adimab.com> wrote:
Hello.
I'm using PostgreSQL v. 10.14 via AWS Aurora Serverless. Our users recently noticed gaps in the primary ids for many of our tables. As I understand it, this is due to PostgreSQL effectively caching 32 additional sequence values (hardcoded in sequence.c ) and a 'restart' occurs. Losing a few sequence values is normal and generally acceptable. However, this problem seems to be exacerbated by the scaling events of Aurora Serverless as they require restarts of the Postgresql service. I do not allow AWS AS to force the scaling in our environment. When scaling events happen 10's of times per day, the loss is significant.
Are there any plans to fix this issue or otherwise play nicely with Aurora Serverless?
I don't believe there are any plans to change this in PostgreSQL, as
it's generally not a problem.
AWS Aurora however, is not PostgreSQL, it's a different database
(which shares some parts, but it's fundamentally quite different) It's
also not open source so there is no way for us to know. You'll have to
ask the AWS support about that one.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
On 5/11/21 9:54 AM, Christopher Sumner wrote:
Hello.
I'm using PostgreSQL v. 10.14 via AWS Aurora Serverless. Our users
recently noticed gaps in the primary ids for many of our tables. As I
understand it, this is due to PostgreSQL effectively caching 32
additional sequence values (hardcoded in sequence.c ) and a 'restart'
occurs. Losing a few sequence values is normal and generally
acceptable. However, this problem seems to be exacerbated by the
scaling events of Aurora Serverless as they require restarts of the
Postgresql service. I do not allow AWS AS to force the scaling in our
environment. When scaling events happen 10's of times per day, the
loss is significant.Are there any plans to fix this issue or otherwise play nicely with
Aurora Serverless?Thanks.
-Chris
I'm curious about the use case which requires the primary key
(arbitrarily assigned by the database engine) to be gaplessly
consecutive through all time.
Christopher Sumner <christopher.sumner@adimab.com> writes:
I'm using PostgreSQL v. 10.14 via AWS Aurora Serverless. Our users
recently noticed gaps in the primary ids for many of our tables. As I
understand it, this is due to PostgreSQL effectively caching 32 additional
sequence values (hardcoded in sequence.c ) and a 'restart' occurs.
Well, 32 is the worst case, but yes this is behaving as intended.
Are there any plans to fix this issue
No. If you must have gapless values, don't use sequences.
regards, tom lane