Universally Unique Identifiers (UUIDs) are widely used as primary keys in distributed systems — they eliminate the need for a central ID generator and allow records to be created independently across multiple nodes. However, implementation choices have a major impact on performance. This article provides a comprehensive guide to working with UUIDs in Firebird, covering storage strategies, key generation options, and their measurable effect on index efficiency, insert throughput, and query performance.
Storage: Binary vs. String
The first and most impactful decision is how to store a UUID. The recommended type is BINARY(16) — compact and efficient. Storing UUIDs as CHAR(36) strings uses more than twice the space per value, and the penalty compounds at the index level: in benchmarks on 1 million rows, a string UUID index required writing 2,356 pages during creation versus only 1,257 pages for the binary equivalent. String indexes also have worse tree depth and clustering factor, directly affecting query performance.
UUID Version 4 vs. UUID Version 7
Firebird's built-in GEN_UUID() produces UUID v4 — fully random values. Random UUIDs cause significant B-tree fragmentation because new keys are inserted at arbitrary positions. UUID v7 is time-ordered, making it monotonically increasing within a server instance. This has major practical consequences, backed by benchmark data:
BIGINT key.The root cause is the clustering factor: UUID v4 indexes have near-worst-case clustering (ratio ≈ 1.0), meaning every sorted access causes random I/O. UUID v7 achieves a clustering ratio of ~0.60, significantly reducing page reads for range scans and ordered queries.
Additional Benefits of UUID v7
Because UUID v7 encodes a millisecond timestamp in its high bits, a descending index on a UUID v7 column can meaningfully return the most recently inserted records — something semantically impossible with UUID v4. Firebird 6.0 supports GEN_UUID(7) natively; in Firebird 5.0, UUID v7 can be generated on the application side or via an external UDR.
Key Recommendations
BINARY(16), never as CHAR(36).BIGINT keys, with only ~20% insert overhead and no need for a centralized sequence.Recommended reading for developers designing Firebird schemas for distributed or replicated environments.
Read article in PDF ou HTML in