Schemas at Billion-Row Scale
The patterns in the previous chapters solve schemas for millions of rows and thousands of users. The billion-scale frontier introduces problems that normalization, indexing, and simple partitioning cannot solve. Event sourcing replaces mutable state with immutable history. Row-level security moves authorization into the database. Serverless databases make schema evolution safe. Vector columns add semantic search without a separate search system. These are not theoretical capabilities. They are tools that production systems use today.
Table Partitioning
When an orders table reaches 500 million rows, even indexed queries slow down because the index itself becomes large enough to spill off memory. VACUUM operations that maintain PostgreSQL's visibility map take hours. Table partitioning splits the logical table into physical segments based on a partition key. The database routes queries to the relevant partitions automatically.
Range partitioning by date is the most common pattern: orders_2024_q1, orders_2024_q2. Queries that filter by date range touch only the relevant partition, scanning a fraction of the total data. Old partitions can be dropped instantly (DROP TABLE orders_2022_q1 takes milliseconds versus DELETE FROM orders taking hours on large tables). Partition key selection follows the same logic as shard key selection: choose the key that aligns with your most common query filters.
Event Sourcing: Immutable History as a Schema Pattern
Traditional CRUD schemas store current state: the order status IS 'shipped'. Event sourcing schemas store what happened: an OrderShipped event occurred at timestamp T. Current state is derived by replaying events. This reversal unlocks capabilities that are impossible with mutable schemas: complete audit history at no extra cost, point-in-time state reconstruction, and natural event-driven integration with other systems.
The event store schema is simple: an events table with event_type, aggregate_id (the ID of the entity the event belongs to), event_data as JSONB, and a timestamp. Current state is a materialized view derived from the event stream. CQRS (Command Query Responsibility Segregation) separates the write model (the event store, optimized for append) from the read model (materialized views, optimized for queries). Banking, insurance, supply chain, and any domain with regulatory history requirements benefit from event sourcing. Domains that only need current state do not.
Row-Level Security: Authorization in the Database
Traditional authorization lives in the application layer: if the user does not have the right role, the API returns 403. This breaks when there are multiple ways to query the database: the main API, internal tools, analytics queries, migration scripts. Each path must implement the same authorization logic independently, and each path can be wrong independently.
PostgreSQL Row-Level Security policies automatically append WHERE clauses to every query: users can only see rows where tenant_id = current_tenant(). The application sets a session variable for the current tenant. Every SELECT, UPDATE, and DELETE automatically filters to that tenant's rows. Even a SQL injection cannot access another tenant's data because the database enforces the filter unconditionally.
Serverless Databases and Schema Branching
PlanetScale (built on Vitess) introduced database branching: create a branch of your database like a git branch, run schema migrations, test in isolation, merge to production when ready. Neon (serverless PostgreSQL) added instant cloning with copy-on-write storage: a production-data copy for testing in seconds, not hours. These tools made schema evolution safer by making rollback and experimentation cheap.
Vector Columns and AI-Native Schemas
Large language models produce embeddings: high-dimensional float vectors representing semantic meaning. Finding similar content means finding nearest neighbors in vector space, which requires different algorithms from SQL's equality and range filters. PostgreSQL's pgvector extension adds vector columns and similarity search operators. HNSW indexes enable approximate nearest-neighbor search at millisecond latency.
The schema pattern: a content table with the source data in typed columns, and a content_embeddings table with a foreign key and a vector(1536) column. Keep them separate to avoid wide rows on reads that do not need the vector. Index with HNSW for fast approximate search or IVFFlat for memory-efficient exact search. Regenerate embeddings when source content changes using a background job that detects changes via a updated_at column.
Partition by the dimension that aligns with your most common query filter. For time-series data, partition by month or quarter.
Event sourcing is correct for domains where history matters as much as current state. It adds complexity; use it when the trade-off is worth it.
Row-level security is the defense-in-depth layer that application authorization cannot provide. Essential for multi-tenant systems with sensitive data.
Vector embeddings belong in a separate table, not inline with source records. Foreign key to the source, index with HNSW, regenerate on change.
Schema branching workflows are now table stakes for production database operations. Branch, test, merge. No maintenance windows.
Partition Pruning and Query Planning
Table partitioning only helps performance if the query planner can prune irrelevant partitions. Partition pruning happens when the WHERE clause contains a condition on the partition key that the planner can evaluate at plan time. WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31' on a table partitioned by month allows the planner to scan only 3 partition tables. WHERE order_date > CURRENT_DATE - INTERVAL '90 days' requires the planner to calculate the interval at plan time, which modern PostgreSQL (12+) handles correctly. WHERE EXTRACT(year FROM order_date) = 2024 does not prune effectively because the expression is not directly on the partition key.
Partitioning also affects index behavior. Each partition has its own indexes. A global index query on a partitioned table is equivalent to scanning each partition's index sequentially. For queries that cannot prune partitions (missing WHERE clause on partition key), a non-partitioned table with a good index is usually faster than a partitioned table. Partitioning is most beneficial when queries consistently filter on the partition key and old partitions can be dropped or archived.
Event Sourcing Trade-Offs in Production
Event sourcing shifts complexity from data storage to projection management. The event store is simple: append events. The read model (projections) is complex: rebuild current state from the event stream, keep projections up to date as new events arrive, handle projection failures without corrupting the event log. Event sourcing is most often adopted incrementally: start with a CRUD schema, add an event log for audit purposes, eventually migrate the source of truth to the event log when the audit value justifies the architectural cost.
Snapshots solve the event replay performance problem. Without snapshots, rebuilding an account's balance from an event store with 10 years of transaction history requires replaying every transaction. With snapshots, store a point-in-time state every N events (say, every 1000 events). Rebuilding state requires loading the most recent snapshot and replaying only the events since that snapshot. Snapshots are materialized views of aggregate state at a specific point in the event stream.
Vector Search Schema Patterns
Store embeddings in a separate table from source content. Embeddings are large (1536 floats = 6KB per vector). Joining them inline with content makes reads of non-semantic queries expensive.
Index with HNSW for fast approximate nearest-neighbor search. HNSW builds a navigable small-world graph that achieves high recall with low query latency. IVFFlat uses inverted file indexing, smaller index size but slightly lower recall.
Track embedding model version as a column. When you upgrade embedding models, you need to regenerate vectors for all content. The model_version column lets you rebuild incrementally.
Sync embeddings with source content using an updated_at column and a background worker that detects changed content and queues re-embedding jobs.
Hybrid search (keyword + semantic) combines PostgreSQL full-text search with pgvector similarity search. Rank results from both using reciprocal rank fusion and return the merged list. Best results for general-purpose search.
Partition Pruning and Query Planning
Table partitioning only helps performance if the query planner can prune irrelevant partitions. Partition pruning happens when the WHERE clause contains a condition on the partition key that the planner can evaluate at plan time. WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31' on a table partitioned by month allows the planner to scan only 3 partition tables. WHERE order_date > CURRENT_DATE - INTERVAL '90 days' requires the planner to calculate the interval at plan time, which modern PostgreSQL (12+) handles correctly. WHERE EXTRACT(year FROM order_date) = 2024 does not prune effectively because the expression is not directly on the partition key.
Partitioning also affects index behavior. Each partition has its own indexes. A global index query on a partitioned table is equivalent to scanning each partition's index sequentially. For queries that cannot prune partitions (missing WHERE clause on partition key), a non-partitioned table with a good index is usually faster than a partitioned table. Partitioning is most beneficial when queries consistently filter on the partition key and old partitions can be dropped or archived.
Event Sourcing Trade-Offs in Production
Event sourcing shifts complexity from data storage to projection management. The event store is simple: append events. The read model (projections) is complex: rebuild current state from the event stream, keep projections up to date as new events arrive, handle projection failures without corrupting the event log. Event sourcing is most often adopted incrementally: start with a CRUD schema, add an event log for audit purposes, eventually migrate the source of truth to the event log when the audit value justifies the architectural cost.
Snapshots solve the event replay performance problem. Without snapshots, rebuilding an account's balance from an event store with 10 years of transaction history requires replaying every transaction. With snapshots, store a point-in-time state every N events (say, every 1000 events). Rebuilding state requires loading the most recent snapshot and replaying only the events since that snapshot. Snapshots are materialized views of aggregate state at a specific point in the event stream.
Vector Search Schema Patterns
Store embeddings in a separate table from source content. Embeddings are large (1536 floats = 6KB per vector). Joining them inline with content makes reads of non-semantic queries expensive.
Index with HNSW for fast approximate nearest-neighbor search. HNSW builds a navigable small-world graph that achieves high recall with low query latency. IVFFlat uses inverted file indexing, smaller index size but slightly lower recall.
Track embedding model version as a column. When you upgrade embedding models, you need to regenerate vectors for all content. The model_version column lets you rebuild incrementally.
Sync embeddings with source content using an updated_at column and a background worker that detects changed content and queues re-embedding jobs.
Hybrid search (keyword + semantic) combines PostgreSQL full-text search with pgvector similarity search. Rank results from both using reciprocal rank fusion and return the merged list. Best results for general-purpose search.
Partition Pruning and Query Planning
Table partitioning only helps performance if the query planner can prune irrelevant partitions. Partition pruning happens when the WHERE clause contains a condition on the partition key that the planner can evaluate at plan time. WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31' on a table partitioned by month allows the planner to scan only 3 partition tables. WHERE order_date > CURRENT_DATE - INTERVAL '90 days' requires the planner to calculate the interval at plan time, which modern PostgreSQL (12+) handles correctly. WHERE EXTRACT(year FROM order_date) = 2024 does not prune effectively because the expression is not directly on the partition key.
Partitioning also affects index behavior. Each partition has its own indexes. A global index query on a partitioned table is equivalent to scanning each partition's index sequentially. For queries that cannot prune partitions (missing WHERE clause on partition key), a non-partitioned table with a good index is usually faster than a partitioned table. Partitioning is most beneficial when queries consistently filter on the partition key and old partitions can be dropped or archived.
Event Sourcing Trade-Offs in Production
Event sourcing shifts complexity from data storage to projection management. The event store is simple: append events. The read model (projections) is complex: rebuild current state from the event stream, keep projections up to date as new events arrive, handle projection failures without corrupting the event log. Event sourcing is most often adopted incrementally: start with a CRUD schema, add an event log for audit purposes, eventually migrate the source of truth to the event log when the audit value justifies the architectural cost.
Snapshots solve the event replay performance problem. Without snapshots, rebuilding an account's balance from an event store with 10 years of transaction history requires replaying every transaction. With snapshots, store a point-in-time state every N events (say, every 1000 events). Rebuilding state requires loading the most recent snapshot and replaying only the events since that snapshot. Snapshots are materialized views of aggregate state at a specific point in the event stream.
Vector Search Schema Patterns
- Store embeddings in a separate table from source content. Embeddings are large (1536 floats = 6KB per vector). Joining them inline with content makes reads of non-semantic queries expensive.
- Index with HNSW for fast approximate nearest-neighbor search. HNSW builds a navigable small-world graph that achieves high recall with low query latency. IVFFlat uses inverted file indexing, smaller index size but slightly lower recall.
- Track embedding model version as a column. When you upgrade embedding models, you need to regenerate vectors for all content. The model_version column lets you rebuild incrementally.
- Sync embeddings with source content using an updated_at column and a background worker that detects changed content and queues re-embedding jobs.
- Hybrid search (keyword + semantic) combines PostgreSQL full-text search with pgvector similarity search. Rank results from both using reciprocal rank fusion and return the merged list. Best results for general-purpose search.