Aurora Postgres Sequences Skipping Values

Hi Naveen - thanks for the answer but it turned out that wasn't the issue.

For anyone else encountering this scenario; we created a support ticket and ultimately AWS Support provided us with this answer:

The root cause of the gap in sequence values is a behavior caused by an implementation difference between Aurora Postgres and community Postgres. In community Postgres, these gaps in sequence values can occur during cancelled transactions or database crashes, but in Aurora, this behavior can additionally occur when sequence pages are evicted from the buffer cache. Increasing the parameter shared_buffers, may decrease the frequency at which these gaps occur, however there is no way to guarantee a true "gapless" assignment of sequence numbers.

In our case we were also running our own pg_dump operations against the primary instance a couple times a day. This is safe to do on stock Postgresql. But it appears that during a pg_dump and experiencing large transactions / activity this can occur. We implicitly solved the problem by spinning up a replica and performing the pg_dump on that host and the problem went away.

Most likely the sequence is setup with a CACHE (CREATE SEQUENCE my_seq INCREMENT BY 1 CACHE 25). Sequence cache is stored in the database cache and it can age out, just like other information (data blocks, sql plans) from memory. If you set the CACHE to 1, you won't lose values, but you will incur a small cost every time the application needs to retrieve the sequence next value.