G'Day Adam, Hey please excuse the questions from a rusty db guy but ... Isn't this fairly risky , perhaps not for a single instance but what about for a cluster where you would need to ensure uniqueness across nodes this would become hard really quickly couldn't it ? My understanding is that fineract can and does often run with multiple instances , but please correct me if I am wrong as I am certainly no fineract expert.
I do take the point about security and sequential keys but it seems that the DB engines have UUID generators or non-sequential key gem and or ways that work with JPA such that you could retain BIGINT internally yep expose only UUIDs or randomised IDs (which would answer the security issue I believe) I may well be out of date and perhaps what you suggest is best practice , just saw your note and thought "humm that takes a lot of getting right" and so figured I would ask the question. I suppose the big question would be how to test and ensure this works "under load" , single instance and clustered and my understanding is that load gen tools arer a little "scarce" ..so how would you plan to prove this new code/ new approach ? cheers Tom > On 4 Sep 2025, at 9:54 am, Ádám Sághy <adamsa...@gmail.com> wrote: > > 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 > > >
