Thread: 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 ofour tables. As I understand it, this is due to PostgreSQL effectively caching 32 additional sequence values (hardcodedin 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 Postgresqlservice. I do not allow AWS AS to force the scaling in our environment. When scaling events happen 10's of timesper 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