Schema Design Decisions That Haunt You at Million User Scale
Schema design decisions at scale refers to the database structure choices made early in a product's development that become expensive to change once data volumes are large and production traffic is continuous. These decisions include primary key type selection, timestamp precision, soft delete implementation, JSON column usage, and index strategy. Changes to these structures on large tables require careful migration strategies to avoid downtime, and some choices are practically irreversible once millions of rows exist.
What you need to know
- UUID vs sequential integer primary keys is a consequential choice for tables that see heavy writes. Sequential IDs create index hot spots at scale; UUIDs distribute them.
- Soft delete without row archival causes tables to grow without bound and slows every query that needs to filter deleted rows.
- EAV (entity attribute value) patterns feel flexible in early design and become performance bottlenecks at any meaningful scale. Use JSON columns instead.
- Timestamp columns must be timestamptz, not timestamp. Timezone bugs at scale are subtle and expensive to diagnose.
- JSON columns are appropriate for genuinely flexible data and inappropriate for data that is queried or filtered frequently.
The core argument
Schema decisions are uniquely painful to change at scale because the migration tooling for large tables is limited. Renaming a column on a table with 50 rows takes a second. Renaming a column on a table with 200 million rows requires a new column, a backfill job running over days, dual writes from application code, and a cutover coordination window. The migration cost at scale means that schema decisions made early in a product's life are effectively permanent unless the team has significant operational bandwidth to manage the migration.
The schema mistakes that cause the most pain over time are not the obvious ones. Nobody ships a production product with a column named x1. The mistakes are the ones that look reasonable at design time: storing timestamps as Unix integers because it seemed simpler, using a JSON blob for a configuration object that later needs to be queried, soft deleting rows without any archival plan. Each of these decisions is defensible at 1,000 users and painful at 1,000,000.
The decision that I see cause the most recurring pain in products I have worked with is the soft delete pattern implemented without row archival. The deleted rows accumulate forever. A users table with 10 percent annual churn accumulates a significant fraction of deleted rows within a few years. Every query hits the dead weight, every index includes dead entries, and vacuuming the table takes longer as the dead row count grows. The fix, migrating deleted rows to an archive table, is straightforward but requires coordination across the application to ensure deleted row lookups (for audit trail, support, or compliance purposes) query the archive rather than the main table.
Common mistakes
Using integer primary keys on multi tenant tables with parallel write patterns. Multi tenant tables where multiple tenants are inserting rows concurrently generate sequential ID contention at the B tree leaf level. This is measurable as lock wait time on the primary key index under load. Switching to UUID primary keys on multi tenant tables eliminates this contention class before it becomes an incident.
Not creating composite indexes that match the most common WHERE patterns. A WHERE clause filtering on (tenant_id, status, created_at) requires a composite index on those three columns in that order to be used efficiently. An index on tenant_id alone forces the query planner to filter on status and created_at after the tenant_id scan, which may be acceptable at 100,000 rows and unacceptable at 100,000,000 rows. Analyze the most common WHERE patterns and build composite indexes to match them.
Storing derived or computed values as columns without a consistency mechanism. A column that caches the result of an aggregation (total_order_count on the users table) must be kept in sync with the source data. Without a disciplined consistency mechanism (database triggers, event driven updates), it drifts from the actual value. At scale, inconsistent cached values produce data quality problems visible to users and require periodic reconciliation jobs.
Not planning for large table migrations before the table is large. Adding a NOT NULL column with a default value to a table requires a table rewrite in older PostgreSQL versions. Dropping a column does not reclaim space without a VACUUM FULL. Changing a column type may require a full table scan and temporarily storing the data twice. Understanding the cost of schema changes before the table is large allows the engineering team to plan ahead rather than discover constraints in the middle of an incident.
Using TEXT for columns with a bounded set of values instead of an enum or check constraint. A status column that accepts any text value will eventually contain inconsistent values (active, Active, ACTIVE, enabled, Enabled) that break filtering and reporting. Use an enum or a check constraint to enforce the allowed values at the database level. Correcting inconsistent text data in a large table costs more than adding the constraint from the start.
Where to start
Audit the five tables with the highest row counts in the production database. For each, check: primary key type (int vs UUID), whether soft delete is implemented without archival, whether there are JSON columns being queried as filter conditions, and whether all common WHERE patterns have corresponding indexes. This audit takes two hours and reveals the most important schema debt items before they become incidents.
Implement row archival for soft deleted records. Create an archive table with the same schema as the main table. Build a scheduled job that moves rows where deleted_at is older than a threshold (30 days, 90 days depending on business requirements) from the main table to the archive. Ensure the application code can query the archive table for lookup use cases (support, audit trail). This prevents the indefinite accumulation of dead rows in tables with high churn.
Add timestamptz to all new timestamp columns and plan migration of existing timestamp columns. Review all timestamp columns for timestamptz vs timestamp usage. Schedule migration of timestamp columns to timestamptz during a quiet traffic window, renaming and adding columns rather than altering in place, to avoid table locks. The migration is straightforward and the correctness benefit for timezone handling is permanent.
FAQ
Frequently asked
- Why does primary key type matter at scale?
- What is the soft delete problem at scale?
- What is the EAV (entity attribute value) antipattern and when does it appear?
- How should timestamps be stored and why does precision matter?
- When should you use JSON columns in PostgreSQL versus normalized tables?
Author
The reason I write these
I write these because the writing is the proof. Yashveer Singh, founder of Yashveer Labs. The systems I build are not theoretical. They are running right now, serving real users, generating real revenue. That is the bar I hold this writing to. If you want to hire someone who can match that bar, I am the call.