Three Decisions That Affect Every Table You Create
Some schema design decisions are table-level choices. Others affect every table in a system. How you generate primary keys, whether you use structured columns or flexible JSON storage, and how you evolve your schema safely under production conditions are system-wide decisions. Getting them right early saves significant pain at scale.
UUID vs Auto-Increment
Auto-incrementing integers are compact (4 bytes), sequential (good for B-tree insertion performance), and simple. They fail at distributed scale: two shards cannot independently increment the same counter. They leak information: a user ID of 847,293 reveals approximately how many users you have. They require coordination: the primary server must be consulted for every new ID.
UUIDs (128-bit random identifiers) solve all of these: globally unique without coordination, reveal nothing about ordering or record counts, and work correctly across shards. The cost: 16 bytes per ID versus 4 bytes for integers, and random UUID insertion scatters across the B-tree index instead of appending to the end, causing more page splits and higher write amplification.
ULIDs and UUID v7 are the modern compromise: they embed a timestamp prefix that makes them sortable while remaining globally unique. B-tree insertions are approximately sequential because recent ULIDs cluster together. UUID v7 is now part of the official UUID specification and supported by PostgreSQL 17. For new systems, UUID v7 is the correct default: globally unique, time-ordered, non-guessable.
A practical compromise for many systems: use integer IDs for internal database foreign keys (B-tree performance, compact storage) and expose UUIDs to external clients (non-guessable, globally unique). Index both. Map between them at the API boundary.
JSONB: The Semi-Structured Compromise
Strict relational schemas give every attribute a column. But user preferences have 200 possible keys where each user sets 5. Product attributes vary wildly by category: a laptop has RAM and screen size, a shirt has size and color. Form responses vary per form. PostgreSQL's JSONB column type stores binary JSON inside a relational column while supporting indexing and querying with GIN indexes and JSON operators.
JSONB bridges relational discipline and document flexibility. Core business data belongs in typed columns with constraints and indexes: title, price, user_id, created_at. Flexible metadata belongs in a JSONB column: category-specific attributes, user preferences, third-party integration data. The anti-pattern is storing everything in JSONB to avoid schema design. That recovers all the problems that the relational model solved in 1970.
Schema Migrations: Treating Schema Changes as Code
Early databases were modified with ad-hoc ALTER TABLE commands run directly against production. This created schema drift between environments, made it impossible to reproduce the exact production schema, and caused the occasional disaster when someone ran the wrong script on the wrong database. Schema-as-code tools changed the culture: Flyway, Alembic, Prisma Migrate, and Rails Migrations all represent schema changes as versioned migration files checked into source control.
Zero-downtime migrations require additional discipline. Adding a NOT NULL column to a large table with no default value locks the table while it fills in values for every existing row. The alternative: add the column as nullable first, deploy code that writes to it, backfill existing rows in batches, then add the NOT NULL constraint once every row has a value. Two deployments instead of one, but no table lock and no downtime.
UUID v7 for new systems: globally unique, time-ordered, non-guessable. Keeps B-tree performance while eliminating coordination.
JSONB for genuinely variable attributes, not as an escape from schema design. Core business data gets columns.
Every schema change is a migration file in version control. No direct ALTER TABLE against production.
Large migrations run in multiple phases: add nullable column, deploy, backfill, add constraint. Never block a table for a long-running operation.
UUID Performance in PostgreSQL Deep Dive
The performance case against random UUIDs (v4) is real but often overstated. B-tree index fragmentation from random insertion is measured in the percentage range for most workloads, not orders of magnitude. The practical impact depends on table size and write throughput. A table receiving thousands of inserts per second with random UUID primary keys will show measurable write amplification versus sequential integer keys. A table receiving dozens of inserts per minute will not show measurable difference.
UUID v7 eliminates most of the B-tree fragmentation concern by using a timestamp prefix. The first 48 bits are a Unix millisecond timestamp, making recent UUIDs cluster together in the index just like auto-increment integers. UUID v7 is available in PostgreSQL through the pg_uuidv7 extension and natively in PostgreSQL 17. For new systems, UUID v7 is the correct default: globally unique without coordination, non-guessable to external observers, and B-tree friendly.
JSONB Indexing Strategies
JSONB columns support two index types. A GIN index on the entire column supports containment queries (@>) and key existence queries (?). A GIN index on a specific key expression supports equality and range queries on that key. For a metadata JSONB column that stores user preferences, a GIN index on the entire column supports 'find all users who have set this preference to any value'. A functional index on metadata->>'theme' supports 'find all users who have set their theme to dark'.
The anti-pattern to avoid: putting columns that are frequently queried in a WHERE clause or JOIN condition into JSONB. The database cannot use statistics on JSONB values the same way it can on typed columns, leading to poor query plan choices. Frequently queried attributes belong in typed columns with standard indexes. JSONB belongs to the attributes that vary per row and are rarely used in WHERE clauses.
Zero-Downtime Migration Patterns
Adding a nullable column: instant in PostgreSQL (table metadata update only). Safe to deploy immediately.
Adding a NOT NULL column with a DEFAULT: PostgreSQL 11+ can add a column with a constant default without rewriting the table. The default is stored in catalog, not on each row.
Adding a NOT NULL column without a DEFAULT: requires table rewrite (full scan to fill in values). For large tables, do this in phases: add nullable, deploy, backfill, add constraint.
Adding an index: use CREATE INDEX CONCURRENTLY to build the index without blocking writes. Takes longer but does not lock the table.
Renaming a column: requires two deployments. Phase 1: add the new column name and read from old, write to both. Phase 2: remove the old column after all consumers are updated.
Changing a column type: almost always requires table rewrite. Plan for a maintenance window or use a shadow table migration strategy.
UUID Performance in PostgreSQL Deep Dive
The performance case against random UUIDs (v4) is real but often overstated. B-tree index fragmentation from random insertion is measured in the percentage range for most workloads, not orders of magnitude. The practical impact depends on table size and write throughput. A table receiving thousands of inserts per second with random UUID primary keys will show measurable write amplification versus sequential integer keys. A table receiving dozens of inserts per minute will not show measurable difference.
UUID v7 eliminates most of the B-tree fragmentation concern by using a timestamp prefix. The first 48 bits are a Unix millisecond timestamp, making recent UUIDs cluster together in the index just like auto-increment integers. UUID v7 is available in PostgreSQL through the pg_uuidv7 extension and natively in PostgreSQL 17. For new systems, UUID v7 is the correct default: globally unique without coordination, non-guessable to external observers, and B-tree friendly.
JSONB Indexing Strategies
JSONB columns support two index types. A GIN index on the entire column supports containment queries (@>) and key existence queries (?). A GIN index on a specific key expression supports equality and range queries on that key. For a metadata JSONB column that stores user preferences, a GIN index on the entire column supports 'find all users who have set this preference to any value'. A functional index on metadata->>'theme' supports 'find all users who have set their theme to dark'.
The anti-pattern to avoid: putting columns that are frequently queried in a WHERE clause or JOIN condition into JSONB. The database cannot use statistics on JSONB values the same way it can on typed columns, leading to poor query plan choices. Frequently queried attributes belong in typed columns with standard indexes. JSONB belongs to the attributes that vary per row and are rarely used in WHERE clauses.
Zero-Downtime Migration Patterns
Adding a nullable column: instant in PostgreSQL (table metadata update only). Safe to deploy immediately.
Adding a NOT NULL column with a DEFAULT: PostgreSQL 11+ can add a column with a constant default without rewriting the table. The default is stored in catalog, not on each row.
Adding a NOT NULL column without a DEFAULT: requires table rewrite (full scan to fill in values). For large tables, do this in phases: add nullable, deploy, backfill, add constraint.
Adding an index: use CREATE INDEX CONCURRENTLY to build the index without blocking writes. Takes longer but does not lock the table.
Renaming a column: requires two deployments. Phase 1: add the new column name and read from old, write to both. Phase 2: remove the old column after all consumers are updated.
Changing a column type: almost always requires table rewrite. Plan for a maintenance window or use a shadow table migration strategy.
UUID Performance in PostgreSQL Deep Dive
The performance case against random UUIDs (v4) is real but often overstated. B-tree index fragmentation from random insertion is measured in the percentage range for most workloads, not orders of magnitude. The practical impact depends on table size and write throughput. A table receiving thousands of inserts per second with random UUID primary keys will show measurable write amplification versus sequential integer keys. A table receiving dozens of inserts per minute will not show measurable difference.
UUID v7 eliminates most of the B-tree fragmentation concern by using a timestamp prefix. The first 48 bits are a Unix millisecond timestamp, making recent UUIDs cluster together in the index just like auto-increment integers. UUID v7 is available in PostgreSQL through the pg_uuidv7 extension and natively in PostgreSQL 17. For new systems, UUID v7 is the correct default: globally unique without coordination, non-guessable to external observers, and B-tree friendly.
JSONB Indexing Strategies
JSONB columns support two index types. A GIN index on the entire column supports containment queries (@>) and key existence queries (?). A GIN index on a specific key expression supports equality and range queries on that key. For a metadata JSONB column that stores user preferences, a GIN index on the entire column supports 'find all users who have set this preference to any value'. A functional index on metadata->>'theme' supports 'find all users who have set their theme to dark'.
The anti-pattern to avoid: putting columns that are frequently queried in a WHERE clause or JOIN condition into JSONB. The database cannot use statistics on JSONB values the same way it can on typed columns, leading to poor query plan choices. Frequently queried attributes belong in typed columns with standard indexes. JSONB belongs to the attributes that vary per row and are rarely used in WHERE clauses.
Zero-Downtime Migration Patterns
- Adding a nullable column: instant in PostgreSQL (table metadata update only). Safe to deploy immediately.
- Adding a NOT NULL column with a DEFAULT: PostgreSQL 11+ can add a column with a constant default without rewriting the table. The default is stored in catalog, not on each row.
- Adding a NOT NULL column without a DEFAULT: requires table rewrite (full scan to fill in values). For large tables, do this in phases: add nullable, deploy, backfill, add constraint.
- Adding an index: use CREATE INDEX CONCURRENTLY to build the index without blocking writes. Takes longer but does not lock the table.
- Renaming a column: requires two deployments. Phase 1: add the new column name and read from old, write to both. Phase 2: remove the old column after all consumers are updated.
- Changing a column type: almost always requires table rewrite. Plan for a maintenance window or use a shadow table migration strategy.