Dear Fineract community, I’d like to open a discussion on our current use of auto-increment database primary keys (e.g. BIGINT IDENTITY) and whether it’s time to evolve this design.
Current Issues with Auto-Increment PKs Always requires flush/commit: The database generates the ID only once the transaction is flushed or committed, which makes it difficult to work with new entities before persistence. This complicates batching, caching, and testability. Performance implications: Because IDs are only known after flush, code often performs additional flushes just to obtain generated IDs. This introduces unnecessary round-trips and slows down persistence at scale. Also this cause various issues around having multiple flushes per transactions. Predictability: Sequential IDs are trivially guessable. When exposed via APIs, this can pose a security and privacy concern. Operational flexibility: While a single database removes the need for distributed coordination, relying solely on DB-generated sequences ties identity generation to persistence, which reduces flexibility when considering service decomposition, replication, or multi-tenant setups. Recommendation: Snowflake-style BIGINT IDs Application-level ID generation using a Snowflake-style algorithm (64-bit IDs with timestamp + machine ID + sequence). Pros: IDs are available immediately in memory, no flush required. Globally unique across instances. Roughly time-ordered, which helps with debugging and querying. Removes guessability of plain sequential IDs. Still compact BIGINT for indexing and joins. No need for migration! Cons: Requires a generator implementation and clock synchronization safeguards. IDs are less human-friendly than sequential integers. Looking forward to hear your thoughts on this proposal! Regards, Adam
