Why We Shifted Away from Database-Generated IDs to 64‑Bit Integer IDs

The team building a SQL Server‑based data catalog needed to decouple the system, adhering to two core principles: preventing linear growth of complexity as features increase, and prioritizing customer needs, performance, query patterns, and business changes so components can be swapped as requirements evolve.

Inspired by the protoactor‑go motto "software should be composed, not built," they examined the impact of persistence coupling.

To avoid tying state persistence to a specific database engine, they decided not to pass persistence concerns to the domain layer; instead, persistence should appear in domain events, leading to varied storage requirements that can be addressed with patterns such as CQRS and DDD aggregates.

Relying on database‑generated auto‑increment keys couples the business layer to the storage system, making it hard to switch databases or scale horizontally, especially in distributed environments where different databases use different ID strategies.

The solution is to let the domain layer generate identifiers, representing them as unsigned 64‑bit integers. Domain consumers can choose how to serialize these IDs (e.g., as strings for client consumption), eliminating the need for database‑generated IDs.

While UUIDs are convenient, they incur higher storage and indexing costs. A 64‑bit integer generated via the Twitter Snowflake algorithm offers a more efficient primary‑key type for distributed systems. The team uses the open‑source IdGen library (Rob Janssen) for .NET to implement this approach.

References: Twitter ID documentation and the IdGen GitHub repository.