Choosing a Postgres Primary Key

Summary

Choosing a Postgres primary key involves balancing simplicity, storage, sortability, standards compliance, information leakage, and collision avoidance. The discussion starts with natural and surrogate keys, then compares integer and biginteger identity columns with serial sequences, noting that sequences can have gaps and exposed numeric IDs may reveal information. It then examines pseudorandom and secure random numeric IDs before turning to UUIDs, including UUIDv4 and time-based alternatives, whose generation methods and index behavior differ. Postgres integration, size queries, and the pg_idkit library provide practical ways to generate and evaluate identifiers; the suggested rules of thumb favor integers for simplicity, UUIDv4 for collision avoidance, xid for time-based sort friendliness, and UUIDv6 or UUIDv7 for a more standards-oriented choice.

Takeaways

1. 1

   Postgres serial and bigserial use database-managed sequences, so application instances can insert rows without coordinating the next numeric ID; sequence values can still contain gaps after rolled-back inserts.

2. 2

   Pseudorandom numeric IDs can repeat predictable sequences when seed initialization is weak. Secure random generation improves this, but a bounded numeric keyspace remains practically enumerable.

3. 3

   The source recommends UUIDv4 when collision avoidance is the priority, xid for time-based and sort-friendly IDs, and UUIDv6 or UUIDv7 for a more standards-oriented option; integers remain advantageous for simplicity, storage, and sortability.