Schema Design Decisions That Haunt You at Million User Scale

What you need to know

• UUID vs sequential integer primary keys is a consequential choice for high-write tables. 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 50-row table is a one-second operation. Renaming a column on a 200-million-row table requires a new column, a backfill job running over days, dual-write 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 long-term pain 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

1. 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.

1. 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.

1. 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 user-visible data quality problems and require periodic reconciliation jobs.

1. 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 temporary double-storage. Understanding the cost of schema changes before the table is large allows the engineering team to plan ahead rather than discover constraints mid-incident.

1. 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 is more expensive than adding the constraint from the start.

Where to start

1. Audit the five highest-row-count tables 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.

1. 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 high-churn tables.

1. 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 low-traffic window using a rename-and-add approach to avoid table locks. The migration is straightforward and the correctness benefit for timezone handling is permanent.

Related reading

• Query Optimization in PostgreSQL: Real Examples From Real Projects
• Scaling from One Thousand to One Hundred Thousand Users: The Invisible Database Bottlenecks
• Schema Evolution: Adding Columns Without Downtime
• SaaS Multi Tenancy Patterns: Database per Tenant vs Shared Schema