Choosing the right primary key for a distributed database

UUID vs auto‑increment vs custom sequential keys in a distributed database

When you design a new system that will run across several data centres or cloud zones, the primary key you pick influences three things you care about most: performance, replication behaviour, and operational simplicity.

1. How each key type works

• UUID (Universally Unique Identifier), a 128‑bit value that can be generated anywhere without coordination. It guarantees uniqueness across nodes, but its random nature means new rows are inserted at random positions in a B‑tree index.

• Auto‑increment integer, a simple counter stored in the database. The database hands out the next number when a row is inserted. It gives strictly increasing keys, which keep the index ordered, but the counter lives on a single node, so every write must reach that node.

• Custom sequential generator, a scheme that produces increasing values while allowing distributed generation. Typical designs are Snowflake‑style IDs (timestamp + machine ID + sequence) or a dedicated ID service that hands out blocks of numbers to each node.

2. Performance impact

Because UUIDs are larger, they increase the size of primary‑key indexes and any secondary indexes that reference them. On a low‑bandwidth link, the extra bytes can add up, especially for high‑volume tables. Auto‑increment keys keep the index compact, but the single‑node counter can become a bottleneck if you have many write‑heavy services spread across regions. Custom generators strike a middle ground: they keep the index ordered and avoid a single point of contention, at the cost of an extra service or a more complex allocation algorithm.

3. Replication and multi‑region behaviour

• UUID, each replica can accept writes independently because the key is guaranteed unique, which works well with databases that support multi‑master replication.
• Auto‑increment, writes must be routed to the node that owns the counter. If you replicate across regions, you either force all writes to a single primary region (adding latency) or you accept duplicate‑key errors when two regions try to allocate the same number.
• Custom sequential, by handing out non‑overlapping blocks to each region, you preserve the monotonic order while allowing local writes. The trade‑off is the need to manage block exhaustion and to rebuild the allocation service if you add or remove nodes.

4. Operational considerations for African deployments

1. Connectivity, Many public‑sector projects operate on intermittent links. A key that can be generated locally (UUID or pre‑allocated block) avoids a round‑trip to a central coordinator.

2. Regulatory compliance, Guidelines do not dictate key format, but embedding a region code in a custom key can simplify audits that check data residency.

3. Tooling, Our stack, which includes Docker, Pulumi and GitHub Actions, can provision a Snowflake‑style ID service in a container and expose it via a stable HTTP endpoint. The same pipeline can also set up a UUID‑generating function in the database (gen_random_uuid() in PostgreSQL).

4. Future migrations, Switching primary key type after launch means rewriting every foreign key and re‑indexing tables. That effort is proportional to the amount of data and the number of dependent services. It’s far cheaper to decide early during the discovery session of our product‑design‑implementation engagement.

5. Decision framework

Use the following checklist during your discovery phase to narrow the choice:

Answering these questions gives you a clear recommendation:

• Pick UUID when you prioritize ease of deployment, want true multi‑master writes, and can tolerate larger indexes.
• Pick auto‑increment when you run a single‑region or tightly coupled cluster, need the smallest possible index, and can route all writes to the master node.
• Pick a custom sequential generator when you need ordered keys, want to avoid a single write bottleneck, and are comfortable operating an extra service for ID allocation.

6. How we help you pick and implement

During the discovery session we map the existing systems, capture the write patterns and latency constraints, and then produce a proposal that includes the recommended key strategy, the stack rationale and a proof‑of‑concept validation. Our system‑orchestration service will stand up the necessary infrastructure (Docker, Pulumi, monitoring) and hand over a fully documented, monitored system that your team can operate long after launch.

If you’re already running a system with the “wrong” key type, our project‑restoration service can audit the schema, unblock the migration and bring the database back to a maintainable state.

Related reading

• Choosing a migration workbench for legacy system modernisation in Africa
• Choosing or building a core banking platform for Kenyan sacc os
• How to backup and restore a PostgreSQL database using Docker

Ready to decide which primary key fits your distributed system? Talk to our team about it.

Photo by Gustavo Fring on Pexels.