Unique Invoice Number Generation without Gaps in PostgreSQL

When working with systems requiring unique identifiers, such as invoice numbers, it is essential to ensure they are generated consistently without any gaps. However, using traditional methods like queries with isolation levels like serialization may not suffice.

Sequences in PostgreSQL do not guarantee gap-free numbers, as a rollback or error can consume the sequence value. So, how can we address this challenge?

Understanding Gap-Free Number Generation

Achieving gap-free number generation depends on three key factors:

• Enforcing no gaps in the series
• Multiple processes accessing the number generation
• Numbers being generated at the time of entity creation

Potential Solutions

• Gap Acceptable Series: If gaps are permissible, Oracle's Sequence object is a high-performing solution that minimizes gaps resulting from process failures.
• Batch Generation with Sequential Insert: For single-process invoice generation, it's possible to read the current maximum invoice number and incrementally assign numbers to a batch of invoices being inserted into a temporary table.
• Post-Generation of Numbers: If instant generation is not required, the numbers can be generated after the entity creation and transaction commitment by a batch job update or a separate table insert.

Gap-Free Generation with Multiple Processes

Achieving gap-free number generation with multiple processes requires careful serialisation to prevent gaps:

• Use a dedicated table to store current values instead of a sequence.
• Encapsulate number generation in a function or procedure accessible by all processes.
• Serialise access to the number generator with DBMS_Lock for each series.
• Hold the lock until transaction completion, releasing it on commit.
• Delay number generation to the latest possible moment.
• Consider impact of unexpected errors and countermeasures for returning unused numbers to the pool.
• Explore encapsulation in triggers, or API calls that automatically insert and commit rows.

Conclusion

Generating gap-free unique ID series is not always straightforward, but it is possible by understanding the principles of number generation and applying techniques that minimize gaps and serialise access to the number generator effectively.