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

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:

-- Blockchain events have different structures per event type
payload JSONB  -- {'type': 'Transfer', 'from': '0x...', 'to': '0x...', 'value': 100}
               -- {'type': 'Approval', 'owner': '0x...', 'spender': '0x...'}

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

raw_webhook JSONB  -- store the full webhook payload for replay/debugging

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

events JSONB  -- array of sub-events within a transaction

When JSONB Becomes a Trap

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

-- Wrong: using JSONB to avoid defining proper columns
user_data JSONB  -- {'name': 'John', 'email': 'john@...', 'age': 30}
 
-- Correct: these are stable, always-present attributes
name TEXT NOT NULL,
email TEXT NOT NULL,
age INT

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:

-- Querying this requires a GIN index or sequential scan
WHERE payload->>'status' = 'confirmed'
 
-- This is always better:
WHERE status = 'confirmed'  -- direct column, B-tree index, planner statistics

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.

Getting the partition key type right matters beyond just this section's mechanics — Module 6's query-planning module walks through a production incident where an implicit type coercion on a partitioned block_height column silently disabled partition pruning, turning a 1ms query into a 14-second full scan across all 36 partitions.

Range Partitioning

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

CREATE TABLE transactions (
  id          BIGSERIAL,
  hash        BYTEA NOT NULL,
  block_height BIGINT NOT NULL,
  timestamp   TIMESTAMPTZ NOT NULL,
  amount      NUMERIC(38, 8)
) PARTITION BY RANGE (block_height);
 
CREATE TABLE transactions_0_to_5m
  PARTITION OF transactions FOR VALUES FROM (0) TO (5000000);
 
CREATE TABLE transactions_5m_to_10m
  PARTITION OF transactions FOR VALUES FROM (5000000) TO (10000000);
 
-- Create indexes on each partition (or use inherited indexes)
CREATE INDEX ON transactions_0_to_5m (block_height);
CREATE INDEX ON transactions_5m_to_10m (block_height);

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:

-- Create next partition proactively (run as a scheduled job)
CREATE TABLE transactions_10m_to_15m
  PARTITION OF transactions FOR VALUES FROM (10000000) TO (15000000);

Hash Partitioning

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

CREATE TABLE sessions (
  id      UUID DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  data    JSONB
) PARTITION BY HASH (user_id);
 
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

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.

CREATE TABLE events (
  id        BIGSERIAL,
  region    TEXT NOT NULL,
  event_type TEXT NOT NULL,
  payload   JSONB
) PARTITION BY LIST (region);
 
CREATE TABLE events_us PARTITION OF events FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE events_eu PARTITION OF events FOR VALUES IN ('eu-west', 'eu-central');
CREATE TABLE events_asia PARTITION OF events FOR VALUES IN ('ap-southeast', 'ap-northeast');

When Partitioning Hurts

1. Cross-partition queries become more expensive:

-- Without predicate on partition key = scans ALL partitions
SELECT count(*) FROM transactions WHERE status = 'pending';
-- Append node: scans every partition sequentially

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:

SELECT t.*, b.validator
FROM transactions t  -- partitioned by block_height
JOIN blocks b ON b.height = t.block_height
WHERE t.sender = '0xabc...';  -- not the partition key
-- Must scan all transaction partitions for this sender

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

-- This works: hash is unique within each partition
ALTER TABLE transactions ADD CONSTRAINT transactions_hash_unique UNIQUE (block_height, hash);
-- block_height is the partition key, so this is enforced per-partition
 
-- This does NOT work: can't guarantee global uniqueness across partitions
-- ALTER TABLE transactions ADD CONSTRAINT transactions_hash_unique UNIQUE (hash);

---

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.

-- Wrong: allows NULL sender, creating ambiguity
sender VARCHAR(66)
 
-- Correct: sender is always required
sender VARCHAR(66) NOT 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:

ALTER TABLE transactions ADD CONSTRAINT amount_positive
CHECK (amount >= 0);
 
ALTER TABLE transactions ADD CONSTRAINT status_valid
CHECK (status IN ('pending', 'confirmed', 'failed'));
 
-- Complex constraint: amount must be 0 for 'failed' transactions
ALTER TABLE transactions ADD CONSTRAINT failed_zero_amount
CHECK (status != 'failed' OR amount = 0);

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:

-- Step 1: add constraint as NOT VALID (skips existing rows, enforces future inserts)
ALTER TABLE transactions
ADD CONSTRAINT amount_positive CHECK (amount >= 0) NOT VALID;
 
-- Step 2: validate existing rows (takes ShareUpdateExclusiveLock — reads allowed)
ALTER TABLE transactions VALIDATE CONSTRAINT amount_positive;

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 row-level FOR KEY SHARE lock on the referenced row (not a table-level lock mode like ShareLock) — just enough to prevent that specific row from being deleted or having its key changed out from under the new reference while the insert commits.

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

-- High-throughput inserts into transactions referencing blocks
-- Each insert acquires ShareLock on the referenced block row
-- Concurrent block updates cause 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:

-- Step 1: create the unique index concurrently (no blocking)
CREATE UNIQUE INDEX CONCURRENTLY idx_transactions_hash_unique ON transactions (hash);
 
-- Step 2: add the constraint using the existing index (instant)
ALTER TABLE transactions
ADD CONSTRAINT transactions_hash_unique UNIQUE USING INDEX idx_transactions_hash_unique;

---

Online Schema Migrations

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

Adding a Column

-- Adding a nullable column: instant (no table rewrite)
ALTER TABLE transactions ADD COLUMN processed_at TIMESTAMPTZ;
 
-- Adding a NOT NULL column with a default: instant in PG 11+ (stores default in catalog)
ALTER TABLE transactions ADD COLUMN version INT NOT NULL DEFAULT 1;
-- PG 11+: the default is stored in pg_attribute, not written to every row
-- PG 10 and earlier: full table rewrite required (hours on large tables)
 
-- Adding a NOT NULL column without default: requires backfill first
ALTER TABLE transactions ADD COLUMN region TEXT;              -- step 1: add nullable
UPDATE transactions SET region = 'us-east' WHERE region IS NULL;  -- step 2: backfill
ALTER TABLE transactions ALTER COLUMN region SET NOT NULL;     -- step 3: add constraint

Renaming a Column (Zero-Downtime)

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

-- Wrong: instant outage if any connection uses old name
ALTER TABLE transactions RENAME COLUMN sender TO from_address;

Zero-downtime rename:

-- Step 1: add new column
ALTER TABLE transactions ADD COLUMN from_address VARCHAR(66);
 
-- Step 2: keep new column in sync (trigger or application dual-write)
CREATE TRIGGER sync_from_address
BEFORE INSERT OR UPDATE ON transactions
FOR EACH ROW EXECUTE FUNCTION
  sync_from_address_func();  -- copies sender → from_address
 
-- Step 3: backfill old rows
UPDATE transactions SET from_address = sender WHERE from_address IS NULL;
 
-- Step 4: deploy application reading from new column
-- Step 5: drop trigger and old column
DROP TRIGGER sync_from_address ON transactions;
ALTER TABLE transactions DROP COLUMN sender;

Changing a Column Type

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

-- Requires full table rewrite (locks table):
ALTER TABLE transactions ALTER COLUMN amount TYPE NUMERIC(40, 8);
 
-- Zero-downtime approach:
-- Step 1: add new column
ALTER TABLE transactions ADD COLUMN amount_v2 NUMERIC(40, 8);
 
-- Step 2: backfill with conversion
UPDATE transactions SET amount_v2 = amount::NUMERIC(40, 8) WHERE amount_v2 IS NULL;
 
-- Step 3: application writes to both columns
-- Step 4: application reads from new column
-- Step 5: drop old column
ALTER TABLE transactions DROP COLUMN amount;
ALTER TABLE transactions RENAME COLUMN amount_v2 TO amount;

The pg_repack Alternative

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

# Rewrite table with new schema (specified via SQL file)
pg_repack -h localhost -d mydb -t transactions --schema-only

---

The Blockchain Indexer Schema: A Case Study

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

CREATE TABLE transactions (
  -- BIGSERIAL not SERIAL — learned from a near-exhaustion incident
  id            BIGSERIAL PRIMARY KEY,
 
  -- BYTEA not TEXT — 2x storage savings, exact comparison
  hash          BYTEA NOT NULL UNIQUE,
 
  -- Indexed for range queries, partition key candidate
  block_height  BIGINT NOT NULL,
 
  -- VARCHAR for blockchain addresses (fixed max length = 66 chars for EVM)
  sender        VARCHAR(66) NOT NULL,
  recipient     VARCHAR(66),  -- nullable: coinbase transactions have no recipient
 
  -- NUMERIC for financial precision, not FLOAT
  amount        NUMERIC(38, 8),
 
  -- CHECK constraint enforces domain rule
  status        VARCHAR(20) NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'confirmed', 'failed')),
 
  -- TIMESTAMPTZ not TIMESTAMP — always store timezone-aware timestamps
  timestamp     TIMESTAMPTZ NOT NULL,
 
  -- JSONB for genuinely variable event data
  payload       JSONB,
 
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
-- Partition by block_height for range query performance
-- Each partition = 500,000 blocks ≈ ~7 days of Ethereum blocks
ALTER TABLE transactions PARTITION BY RANGE (block_height);

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 →

---