Mastering Incremental Numbering for Databases and Spreadsheets
Incremental numbering—assigning sequential identifiers like 1, 2, 3, …—is a fundamental requirement in many systems: invoices, order numbers, tracking IDs, and rows in spreadsheets. Correctly designing and implementing incremental numbering prevents collisions, preserves order, and ensures auditability. This article explains patterns, trade-offs, and practical implementations for both databases and spreadsheets, with examples and best practices.
Why incremental numbering matters
- Uniqueness: Avoid duplicate identifiers.
- Order and traceability: Track creation order and support audits.
- Human readability: Sequential numbers are easy to reference.
- Integration: Many external systems expect predictable IDs.
Core concepts and trade-offs
- Monotonic vs. contiguous: Monotonic means always increasing (may skip values). Contiguous means no gaps. Monotonic is easier and safer under concurrency; contiguous is often desired for things like invoice numbers but harder to guarantee.
- Global vs. scoped sequences: Global (across whole system) vs. scoped (per user, per tenant). Scoped sequences require separate counters.
- Concurrency and atomicity: Multiple writers require atomic increments to avoid duplicates.
- Persistence and durability: Counters must survive crashes; use durable storage or transactional updates.
- Human-facing constraints: Legal or business rules (e.g., invoice continuity) may mandate no gaps—plan for compensation strategies for failed transactions.
Incremental numbering in relational databases
Use built-in sequence features (recommended)
Most relational databases provide sequences or auto-increment columns which are safe and performant.
- PostgreSQL: sequences and SERIAL/IDENTITY.
- CREATE SEQUENCE invoice_seq;
- Use nextval(‘invoice_seq’) in INSERT or default with IDENTITY.
- MySQL: AUTO_INCREMENT on a primary key.
- SQL Server: IDENTITY or SEQUENCE objects.
Advantages:
- Atomic, highly concurrent, durable.
- Can be configured for increment, start, caching. Drawbacks:
- Caching can cause gaps after crashes (monotonic, not contiguous).
- Changing sequence properties requires care.
Transactional counter table (for scoped or custom behavior)
When built-in sequences aren’t flexible enough (e.g., per-tenant sequences with complex rules), use a counter table and update it transactionally.
Example (PostgreSQL):
- Table: tenant_counters (tenant_id PK, last_number bigint).
- Increment atomically:
- BEGIN;
- UPDATE tenant_counters SET last_number = last_number + 1 WHERE tenant_id = $1 RETURNING last_number;
- Use returned last_number for your row insert.
- COMMIT;
Use SELECT … FOR UPDATE to lock rows when needed.
Advantages:
- Fine-grained control, per-scope counters, easy to enforce business rules. Drawbacks:
- Potential contention and reduced write throughput under heavy concurrency.
Optimistic approaches and gap handling
- Use optimistic increments with retries if conflicts occur.
- Allow gaps and provide reconciliation: mark “skipped” numbers and reuse only after manual review (for strict continuity).
Distributed systems
- Use centralized sequence service or a globally distributed ID generator (e.g., Snowflake-like IDs) when low-latency global uniqueness is needed.
- For ordered numeric sequences across regions, accept trade-offs: higher latency for strong consistency or eventual consistency with non-contiguous numbers.
Incremental numbering in spreadsheets
Simple auto-fill (Excel, Google Sheets)
- Drag-fill or use formulas like =ROW()-1 to auto-number rows. Good for simple, single-user sheets.
Stable row IDs
- For IDs that should not change when rows move, avoid formulas dependent on position. Instead:
- Manually assign IDs.
- Use a helper column with a unique formula: in Google Sheets