---
title: "Choosing a Postgres Primary Key"
description: "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."
---

# Choosing a Postgres Primary Key

[Supabase](https://yomu.fyi/company/supabase) · victor · Sep 8, 2022

**Type:** Explainer

## 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.

## Context

The choice of primary key affects how rows are identified and retrieved, while also involving trade-offs around storage, sorting, information leakage, standards compliance, and collision avoidance. The source presents the decision as dependent on the application's use case and workload.

## Approach / What changed

The discussion compares natural keys, surrogate integer keys, serial and identity columns, pseudorandom and secure random numeric IDs, and multiple UUID approaches. It also considers generating identifiers in Postgres, using pg\_idkit, inspecting table and index sizes, and measuring index fragmentation and sort friendliness.

## Takeaways

- 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.
- 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.
- 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.

**Tags:** [Migrations](https://yomu.fyi/topic/migration), [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres)

- Source: [Supabase](https://supabase.com/blog/choosing-a-postgres-primary-key)
- Source URL: https://supabase.com/blog/choosing-a-postgres-primary-key
- Ingested by Yomu: 2026-08-29T00:01:00.849Z

[Read original post](https://supabase.com/blog/choosing-a-postgres-primary-key)