How to Generate Gap-Free ID Sequences in PostgreSQL Without Retries?

Generating Gap-Free ID Sequences in PostgreSQL without Retry Logic

In scenarios where gap-free unique identifiers are essential, such as invoice numbering, generating such sequences without gaps is a common challenge. The traditional approach of querying for the last used number and incrementing it may encounter issues under serialization isolation levels, complicating the task.

Understanding the Gap Generation Problem

Gaps in number sequences arise because of database mechanisms. When a transaction that has generated a number rolls back or encounters an error, the database marks the used number as "used" while not actually assigning it. This results in potential gaps in future generated sequences.

Alternative Gap-Eliminating Techniques

Dedicated Number Generator Table:

Instead of using sequences, maintain a dedicated table to store and manage the generated numbers. This table can be designed to handle concurrent access using appropriate locking mechanisms.

Encapsulate Number Generation Logic:

Create a function or procedure that encapsulates the number generation logic and serializes access using locking mechanisms. This approach ensures that all processes use the same method and prevents conflicts.

Delayed Number Generation:

Postpone the generation of numbers until the last possible moment in the transaction. This minimizes the window of opportunity for potential gaps due to rollbacks or errors.

Handling Errors:

Implement a mechanism to "return" generated numbers to the pool if the transaction fails. This ensures that the numbers are not lost and can be used in future attempts.

Original Article Reference:

For further insights, refer to the comprehensive article titled "The Difficulty of Keeping Invoice Numbers Continuous Using Databases" by Simon Fell: https://blog.codinghorror.com/the-difficulty-of-keeping-invoice-numbers-continuous-using-databases/

The above is the detailed content of How to Generate Gap-Free ID Sequences in PostgreSQL Without Retries?. For more information, please follow other related articles on the PHP Chinese website!