Mastering Incremental Numbering for Databases and Spreadsheets

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