Short alphanumeric pseudo random identifiers in Postgres
a year ago
- #Database Design
- #Performance
- #Postgres
- Introduction to generating short, alphanumeric identifiers in Postgres as an alternative to UUIDs.
- Use of public_id for external use and id for internal database references.
- Design properties include fixed size, obfuscation, reversibility, and native Postgres capabilities.
- Limitations include no support for case insensitivity or excluding similar-looking characters.
- PL/PgSQL functions for obfuscation, base62 conversion, and deobfuscation.
- Table design with generated public_id column and constraints for uniqueness and length.
- Performance impact: 3.4x slower inserts with public_id generation.
- Alternatives considered: Base32 Crockford, ULID, and NanoIDs.