Why We Shifted Away from Database‑Generated IDs: Decoupling Persistence with 64‑Bit Snowflake IDs
The article explains how a team building a SQL Server data catalog decided to decouple persistence by moving ID generation from the database to the domain layer, adopting 64‑bit Snowflake‑style identifiers instead of auto‑increment keys or UUIDs to improve flexibility, scalability, and performance.
Background
The team is developing a data catalog for SQL Server and wants to keep the system loosely coupled. Two core principles guide the effort: avoid linear growth of system complexity as features increase, and prioritize decisions based on customer needs, performance, query patterns, and business changes.
Impact on Persistence
To prevent tight coupling between the domain layer and a specific database engine, the team chooses not to embed persistence concerns in the domain model. Persisting data should appear in domain events rather than being forced into a particular storage system, allowing future migration away from SQL Server if needed.
Problems with Database‑Generated IDs
Relying on the database to generate unique identifiers couples the business layer to the storage engine, making it hard to switch to databases that lack auto‑increment keys and creating inconsistencies across different systems. Moreover, database‑generated IDs are not well‑suited for distributed environments, limiting horizontal scaling.
Domain‑Driven ID Generation
By delegating ID creation to the domain’s consumers (the transport layer handling commands and queries), the system removes the dependency on the database. This approach supports distributed deployments, such as partitioning a table across multiple SQL Server instances, which would be impossible with a single auto‑increment column.
Choosing 64‑Bit Integers Over UUIDs
The team decided to generate identifiers as unsigned 64‑bit integers within the domain layer. While UUIDs are convenient, they incur higher storage and indexing costs. Proven Snowflake‑style algorithms (e.g., Twitter Snowflake) provide efficient, globally unique 64‑bit IDs. The open‑source IdGen library by Rob Janssen implements this algorithm for .NET.
References
1. Twitter Snowflake documentation: https://developer.twitter.com/en/docs/basics/twitter-ids.html
2. IdGen library: https://github.com/RobThree/IdGen
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
ITPUB
Official ITPUB account sharing technical insights, community news, and exciting events.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.