PostgreSQL UUID Data Type

Tip: UUID Primary Key

CREATE TABLE posts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY
);

PostgreSQL 13+ has gen_random_uuid() built in.

Gotcha: UUID vs SERIAL Performance

UUIDs are larger (16 bytes vs 8 bytes) and cause more index fragmentation. But they're globally unique.

Tip: UUIDv4 vs UUIDv7

UUIDv4 is random. UUIDv7 is time-ordered and better for indexing.

Gotcha: UUID Indexing

UUIDs don't sort chronologically (v4). Use gen_random_uuid() with a separate created_at for ordering.

Tip: UUID in Laravel

$table->uuid('id')->primary();

Gotcha: UUID Storage

PostgreSQL stores UUIDs efficiently (16 bytes). Don't store as text (36 bytes).

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

UUID primary keys in PostgreSQL are common but problematic for index performance. UUIDv4 random values fragment B-tree indexes, increasing index size and decreasing cache hit rates. I use UUIDv7 (time-ordered) via the pg_uuidv7 extension or store UUIDs as bytea in a custom order. For most web applications, bigint identity columns are faster and simpler. Reserve UUIDs for distributed systems where sequential IDs are impossible.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)