Yep, this is particularly relevant when building pagination. If you use the primary key, which itself is sortable and based on a sequence, you might be surprised when you skip over rows that were yet to be committed because the IDs won't respect transaction commit order.

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!

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.

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