Module 7 — Schema Design at Scale: Decisions That Cannot Be Undone

What this module covers: Schema decisions made on day one compound at 100x data volume. Choosing the wrong primary key type, the wrong partition strategy, or the wrong normalization level creates constraints that are expensive or impossible to reverse without downtime. This module covers the decisions that matter most — not as rules to follow, but as trade-offs to understand so you can derive the right answer for your specific workload.

Why Schema Decisions Are Different From Other Decisions

Most engineering decisions are reversible. You can refactor code, swap a library, change an API response shape, rewrite a service. The cost is development time.

Schema decisions in a production database are different. Once a table has 500 million rows:

• Changing a column type requires a full table rewrite (hours of downtime, or a complex online migration)
• Adding a NOT NULL column requires a default or a multi-step migration to avoid locking
• Changing a primary key type (INT → BIGINT) requires rewriting the table and every foreign key table
• Repartitioning requires rebuilding the entire table and its indexes
• Removing normalization (collapsing two tables) requires a data migration and application changes across every service that touches both tables

The cost of reversing a bad schema decision grows linearly with data volume and quadratically with the number of services that depend on the schema. At 10 billion rows, some decisions cannot be reversed at all without extended downtime.

This is why schema design deserves disproportionate thought at the start.

Data Type Choices: The Foundation

Integer Primary Keys: INT vs BIGINT

INT (32-bit signed) holds values up to 2,147,483,647 (~2.1 billion). BIGINT (64-bit signed) holds up to 9,223,372,036,854,775,807 (~9.2 quintillion).

The INT vs BIGINT decision seems trivial. It has caused production outages at multiple large companies.

At 1,000 inserts/second, an INT primary key exhausts in:

At 10,000 inserts/second:

At 100,000 inserts/second (realistic for a blockchain indexer):

When an INT sequence exhausts, inserts fail with:

Migrating from INT to BIGINT on a 500M-row table with 8 foreign key tables is a multi-day operation requiring careful sequencing of table rewrites and application deployments.

Rule: always use BIGINT for primary keys. The 4-byte difference per row is irrelevant at any scale. The migration cost if you're wrong is catastrophic.

UUID vs BIGSERIAL: The Real Trade-off

UUIDs are attractive for distributed systems: generated client-side, globally unique without coordination, naturally partition-friendly. The cost is real and often underestimated.

UUIDs as primary keys on a B-tree index:

A UUID v4 is random. Inserting random UUIDs into a B-tree causes random page splits (Module 5) — every insert goes to a random leaf page instead of the rightmost page. This:

• Defeats the sequential-insert optimization in B-tree
• Causes all B-tree leaf pages to sit at ~50% fill factor
• Makes the primary key index 2x larger than it needs to be
• Increases write I/O: every insert touches a random cached page rather than the current append page

For a 500M-row table, the UUID primary key index might be 30GB instead of the 15GB it would be with BIGSERIAL. That's 15GB of extra cache pressure and 15GB of extra WAL on every full page write cycle.

UUID v7 (ordered): UUID v7 uses a time-based prefix, making them monotonically increasing. This restores the sequential-insert optimization. If you need UUIDs for distribution, use v7.

The practical guide:

Numeric Types: Precision vs Storage

For a blockchain indexer storing transaction amounts in the native token's smallest unit (satoshis, wei, etc.), BIGINT is almost always better than NUMERIC — it's smaller, faster, and the conversion to display units happens in the application layer.

Text Storage: VARCHAR vs TEXT

In Postgres, VARCHAR(n) and TEXT are stored identically. VARCHAR(n) adds a constraint check on insert/update. TEXT has no length limit.

CHAR(n) is different — it pads values to length n with spaces. Almost never what you want. Avoid it.

BYTEA vs TEXT for Binary Data

Hashes, keys, and binary blobs should be stored as BYTEA, not hex strings in TEXT.

For a blockchain indexer with 100M transaction hashes, switching from TEXT to BYTEA saves 3.4GB in the column alone — plus proportional reduction in index size.

Normalization vs Denormalization: The Production Trade-off

When Normalization Wins

Third Normal Form (3NF) — every non-key column depends only on the primary key — produces:

• No data duplication (each fact stored once)
• Referential integrity enforced by the database
• Updates propagate automatically (change one row, reflected everywhere)
• Smaller tables (more cache-efficient for targeted queries)

Normalization is correct for:

• Frequently updated reference data (prices, statuses, user profiles)
• Data where consistency matters more than read speed
• OLTP workloads with many small, targeted queries

When Denormalization Wins

Denormalization — intentionally duplicating data to avoid joins — wins when:

• Join cost exceeds duplication cost
• The joined data is rarely or never updated
• The query is on a hot path where every millisecond matters

The cost of denormalization: if block_validator changes (rare in blockchain, common in other domains), you must update every transaction row for that block. If the data never changes after insert (append-only blockchain data), the cost is zero — denormalization is a pure win.

The practical rule: denormalize data that is:

1. Written once, read many times
2. Used together so frequently that joins are a consistent bottleneck
3. Not subject to updates that would require cascading changes

Partial Denormalization: Materialized Views

When you need join performance without full denormalization, materialized views store the join result:

CONCURRENTLY allows reads during refresh (requires a unique index). Non-concurrent refresh takes an exclusive lock.

JSONB: When It's Right and When It's a Trap

The Legitimate Use Cases for JSONB

JSONB is genuinely useful for:

1. Schema-flexible attributes — when different rows have different sets of optional attributes and you cannot enumerate them all at schema design time:

2. External data ingestion — when you receive JSON from an external source and want to store it as-is without parsing every field:

3. Nested arrays/objects that don't fit the relational model cleanly:

When JSONB Becomes a Trap

1. JSONB as a schema escape hatch for relational data:

When you put relational data in JSONB, you lose:

• Column-level statistics (planner can't estimate selectivity on JSONB fields)
• CHECK constraints on individual fields
• NOT NULL guarantees per field
• Type safety (JSONB stores everything as JSON types, not Postgres types)
• Efficient index access without expression indexes

2. JSONB for frequently queried fields:

3. JSONB for large blobs that inflate page size:

JSONB columns over ~2KB use TOAST (Module 1). TOAST reads require additional I/O and prevent JSONB fields from being covered in regular indexes. Large JSONB documents on a hot-query table inflate the table's page count and reduce cache effectiveness.

The rule: JSONB for truly schema-flexible, append-heavy data. Columns for everything you query, filter, or join on.

Table Partitioning

Partitioning divides a logical table into physical sub-tables (partitions) based on a column value. The correct partitioning strategy depends entirely on your access pattern.

Range Partitioning

Best for time-series data or sequential numeric keys where queries target ranges.

Partition pruning: queries with a predicate on block_height only scan the relevant partition(s). A query for block_height = 7500000 only touches transactions_5m_to_10m.

The maintenance operation you must plan for: adding new partitions. If you partition by block height, you must create a new partition before data arrives for that range. Automate partition creation:

Hash Partitioning

Best for distributing write load evenly across partitions without a natural range key.

Hash partitioning distributes rows evenly — each partition has roughly 25% of the data. It does not help with query pruning (a query for a specific user_id still goes to exactly one partition, but a range query for all users goes to all partitions).

List Partitioning

Best for categorical partitioning where you want to colocate data by a discrete value.

When Partitioning Hurts

1. Cross-partition queries become more expensive:

If your hot queries don't filter on the partition key, partitioning adds overhead (multiple partition plans) without pruning benefit.

2. Cross-partition joins are expensive:

3. Partition count affects planning time:

Each partition is a separate table to the planner. A table with 500 partitions means the planner considers 500 relations when generating the plan. Planning time for complex queries on heavily-partitioned tables can become a significant overhead.

Keep partition count under 1,000. For time-series data, monthly or weekly partitions are usually right — daily partitions for a 5-year dataset creates 1,825 partitions.

4. Global indexes don't exist:

Postgres does not support global indexes (an index spanning all partitions). If you need a UNIQUE constraint on a non-partition-key column, you must either:

• Include the partition key in the unique constraint
• Enforce uniqueness at the application layer
• Use a separate lookup table

Constraint Design

Constraints are the database's way of guaranteeing data integrity. They are not optional — they are the difference between a database you can trust and one you have to validate in application code.

NOT NULL

The most basic constraint. Use it on every column that should never be NULL.

NOT NULL constraints have zero runtime cost (NULL check is done on insert/update anyway). There is no reason not to use them.

CHECK Constraints

Enforce domain rules at the database level:

Adding a CHECK constraint to an existing table does a full table scan to validate existing rows. On a 500M-row table this takes minutes. To add without locking:

Foreign Keys: The Locking Implications

Foreign keys enforce referential integrity but have a subtle locking implication:

When you insert a row into a table with a foreign key, Postgres acquires a ShareLock on the referenced row. When you delete from the referenced table, Postgres acquires a lock that blocks concurrent FK-checking inserts.

On a high-throughput table with a foreign key to a frequently-modified parent, this can create lock contention.

Options for high-throughput FK tables:

1. Enforce FK at the application layer, remove the database constraint
2. Keep the FK but ensure the parent table is updated infrequently
3. Use DEFERRABLE INITIALLY DEFERRED to defer FK checks to commit time (reduces lock duration)

UNIQUE Constraints and Indexes

UNIQUE constraints automatically create a unique B-tree index. Adding a unique constraint to a large table creates the index (which takes time and I/O) and validates all existing rows.

Online approach:

Online Schema Migrations

Schema changes on live production tables require careful sequencing to avoid blocking reads and writes.

Adding a Column

Renaming a Column (Zero-Downtime)

Renaming a column in a single migration breaks any code using the old name:

Zero-downtime rename:

Changing a Column Type

Type changes require a full table rewrite unless the new type is binary compatible.

The pg_repack Alternative

For complex migrations, pg_repack can rewrite a table online with minimal lock time:

The Blockchain Indexer Schema: A Case Study

The transactions table used throughout this course reflects real decisions made on a blockchain indexer:

Decisions made and why:

• BIGSERIAL over SERIAL: learned from a near-INT-exhaustion incident at 2B rows
• BYTEA for hash: 32 bytes vs 66 bytes TEXT, exact comparison semantics
• VARCHAR(66) for addresses: enforces max length (EVM addresses are always ≤66 chars with 0x prefix)
• NUMERIC(38, 8) for amount: exact decimal arithmetic for financial values
• TIMESTAMPTZ not TIMESTAMP: timezone-aware, consistent across deployments in different timezones
• JSONB for payload: event data is genuinely variable across transaction types
• Partition by block_height: API queries are always range queries on block height

Summary

Schema design sets the constraints that everything else operates within. Module 8 covers the biggest architectural change to Postgres in a decade — asynchronous I/O in PostgreSQL 18 — and what it means for everything you've learned so far.

Next: Module 8 — PostgreSQL 18: Asynchronous I/O and What It Changes →