Efficient order ID generation and collision prevention in parallel SQL trade history
date
Mar 22, 2023
slug
efficient-order-id-generation-and-collision-prevention-in-parallel-sql-trade-history
status
Published
tags
database
distributed-system
summary
The article focus on generating unique order IDs in a SQL trade history database that is being updated by multiple parallel scripts. The challenge is to prevent collisions and ensure the integrity of the order ID generation process
type
Post
Distributed ID generation
We have a SQL trade history database with unique order IDs, and multiple scripts are writing order details to the database in parallel. We need a proper solution to prevent collisions in generating a new order ID from one of the parallel scripts. On another hand, we have to find the most suitable database that can effectively handle this task while ensuring seamless and reliable order ID generation
Feasible solutions
It’s important to note that there is no perfect solution for this scenario, as each solution has its tradeoffs and considerations. There are two feasible solutions that can effectively address the challenge of preventing collisions in generating new order IDs
ㅤ | Solution 1: UUID Generation | Solution 2: Twitter Snowflake Approach |
Implementation | • Utilize a UUID library or a
built-in UUID generation
function provided by
programming language
• Choose UUID v1 because:
better performance, occupy
less storage space | •Setup multiple worker nodes to generate IDs. Ensure the system clocks synchronization between nodes, sequence, and concurrency management of each node.
• Implement load balancing to distribute request evenly across worker nodes
• When a new ID is requested, the worker follow the Snowflake ID generation process |
Database | PostgreSQL
• Natively support UUID
• Provides extension to create
UUIDv1 from persistence
layer | PostgreSQL
• Feature-rich, extendable, standards-compliance
(security), balanced between read-write speed |
Advantages | • Extremely low probability
of collision
• Each machine/node can
generate UUID
independently
• Simple to implement | • Snowflake ID is designed to be global unique
• Snowflake ID can be sorted chronologically
• Generating ID in each worker is simple, fast, and lightweight |
Drawbacks | • UUID’s length might affect
storage efficiency
• Less human-readable
• Index performance
• Do not provide natural
sorting order based on time
or sequence | • Depends on an accurate and synchronized system clock
• Difficult to config and manage workers and data centers
• Limited lifespan due to number of bits allocated to timestamp component |