Postgres sequences can skip 32 unexpectedly

Re:Postgres sequences can skip 32 unexpectedly by peterejhamilton on Friday July 16, 2021 @11:48AM (#27857538)

Super interesting, and that makes a lot of sense!

Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references!

This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!

[ Parent | on HN ]

Re:Postgres sequences can skip 32 unexpectedly by CodesInChaos on Friday July 16, 2021 @02:38PM (#27859636)

Timestamps have monotonicity issues as well:

1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue.

2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem.

The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.

[ Parent | on HN ]

Re:Postgres sequences can skip 32 unexpectedly by wongarsu on Friday July 16, 2021 @02:31PM (#27859549)

Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well

[ Parent | on HN ]