Indexing Only What You Actually Query
Traditional indexes cover every row in a table regardless of value. But most queries filter on a small subset of data. An active users query hits maybe 5% of a user table. A pending orders query hits a tiny fraction of an orders table that spans years. Indexing all rows to serve queries that touch 5% of them wastes space and slows writes without helping reads. Partial indexes solve this by indexing only the rows matching a WHERE condition.
PostgreSQL introduced partial indexes as a first-class feature: CREATE INDEX idx_active_users ON users(email) WHERE status = 'ACTIVE'. The index is smaller, faster to scan, and cheaper to maintain than a full index on the same column. A user management system with 10 million rows but only 200,000 active accounts benefits enormously from a partial index on active accounts rather than a full index on all accounts.
The best index is the smallest index that serves your query. Partial indexes let you build indexes that are small because they only cover the rows that queries actually touch.
Expression Indexes: Indexing Derived Values
Standard indexes operate on column values as stored. But many queries filter on transformed values: case-insensitive email lookups require LOWER(email), date range queries might filter on EXTRACT(year FROM created_at), geographic queries filter on computed distance expressions. Without expression indexes, these queries cause full table scans because the regular column index does not match the expression in the WHERE clause.
PostgreSQL expression indexes create a B-tree on the result of a function: CREATE INDEX idx_lower_email ON users(LOWER(email)). Now WHERE LOWER(email) = 'user@example.com' uses the index efficiently. Functional indexes in MySQL 8.0 and filtered indexes in SQL Server provide similar capabilities. The key is that the expression in the index must exactly match the expression in your WHERE clause.
The Denormalization Decision
Normalization prevents data anomalies. Indexes accelerate queries. But sometimes neither is enough. When a social media feed query requires six table joins to display a single post, the join overhead dominates response time even with perfect indexes. Denormalization intentionally introduces redundancy for performance.
Facebook's news feed is the canonical case. Computing a personalized feed on demand from a normalized schema, joining posts, users, likes, comments, shares, and media tables for each of a user's thousands of friends, is computationally impossible at their scale. Their solution: precompute feeds and store the denormalized results per user. When a post is created, fan out the write to every follower's feed immediately. This shifts work from read time to write time.
A simpler common case: storing likes_count directly on the posts table instead of computing COUNT(*) from a likes table on every read. The posts table is read a thousand times for every like event. The denormalized count column makes reads nearly free at the cost of a slightly more complex write (update both the likes table and the count on the post).
Keeping Denormalized Data in Sync
Denormalization without a synchronization strategy is not optimization. It is data corruption with delayed detection. Every denormalized value needs a clear owner and a clear update mechanism. Common approaches: application-level writes (the service layer updates both the source table and the denormalized column in the same transaction), triggers (a database trigger updates the denormalized column on every source change), and background jobs (a periodic process reconciles the denormalized value with the source of truth, accepting temporary inconsistency).
Denormalize reads that are 1000:1 over writes. The write overhead of maintaining a denormalized value is worthwhile when reads dominate heavily.
Always have a source of truth and derived values, never two equal authorities. The source is what gets updated. The denormalized copy is derived.
Test your sync mechanism under concurrent load. Race conditions between the source update and the denormalized update create intermittent inconsistency bugs that are hard to reproduce.
Partial and expression indexes are often better than denormalization. Try indexes first. Denormalize only when indexes are not enough.
Measuring the Impact of Partial Indexes
The benefit of a partial index depends entirely on the selectivity of the condition. A partial index WHERE status = 'active' on a users table where 95% of users are active saves almost nothing: the index covers 95% of rows, nearly as large as a full index. The same partial index on a users table where 2% of users are active is dramatically smaller, faster to scan, and cheaper to maintain. The selectivity of the partial condition determines the benefit. Always check the data distribution before creating a partial index.
Expression indexes require the expression in the WHERE clause to exactly match the expression in the index definition. LOWER(email) in the index definition must be matched by LOWER(email) in every query that should use the index. The query planner will not recognize LOWER(email) and lower(email) as different expressions (they are not, case-insensitive), but it will distinguish LOWER(email) from email::text::lower (a different expression even if logically equivalent). The safest approach: standardize on one canonical expression form in both the index definition and query code.
The Fan-Out on Write Pattern
The most significant denormalization pattern at scale is fan-out on write: when content is created, precompute and store derived data for every relevant consumer. Facebook's news feed uses this: when a user posts, their post is copied to every follower's feed immediately. At write time, this is expensive (potentially millions of writes for a celebrity post). At read time, fetching a personalized feed is a single indexed lookup on a precomputed table.
The alternative is fan-out on read: compute the feed at query time by fetching all posts from all followed users sorted by date. For users following 10 people, this is trivial. For users following 50,000 people (or for systems where reads are millions per second), this becomes prohibitively expensive. The choice between fan-out on write and fan-out on read is one of the most common system design questions in backend interviews for social and content platforms.
Denormalization Patterns and Their Sync Strategies
Counter caches (likes_count on posts): increment/decrement in the same transaction as the likes table INSERT/DELETE. Use SELECT FOR UPDATE on the post row to prevent lost updates under concurrent writes.
Precomputed aggregates (monthly revenue column): compute in a background job, accept that the value is slightly stale (eventual consistency is fine for reporting).
Denormalized display names (author_name on posts): update via a trigger or background job when users update their display name. The business decision is whether historical posts show the name at post time or the current name.
Materialized views: database-native denormalization. PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds without locking. Useful for complex aggregate queries that are expensive to compute but tolerate seconds of staleness.
Redis for fast counters: increment in Redis synchronously, write to PostgreSQL asynchronously. Best for very high-frequency counters that do not need immediate SQL-queryable values.
Measuring the Impact of Partial Indexes
The benefit of a partial index depends entirely on the selectivity of the condition. A partial index WHERE status = 'active' on a users table where 95% of users are active saves almost nothing: the index covers 95% of rows, nearly as large as a full index. The same partial index on a users table where 2% of users are active is dramatically smaller, faster to scan, and cheaper to maintain. The selectivity of the partial condition determines the benefit. Always check the data distribution before creating a partial index.
Expression indexes require the expression in the WHERE clause to exactly match the expression in the index definition. LOWER(email) in the index definition must be matched by LOWER(email) in every query that should use the index. The query planner will not recognize LOWER(email) and lower(email) as different expressions (they are not, case-insensitive), but it will distinguish LOWER(email) from email::text::lower (a different expression even if logically equivalent). The safest approach: standardize on one canonical expression form in both the index definition and query code.
The Fan-Out on Write Pattern
The most significant denormalization pattern at scale is fan-out on write: when content is created, precompute and store derived data for every relevant consumer. Facebook's news feed uses this: when a user posts, their post is copied to every follower's feed immediately. At write time, this is expensive (potentially millions of writes for a celebrity post). At read time, fetching a personalized feed is a single indexed lookup on a precomputed table.
The alternative is fan-out on read: compute the feed at query time by fetching all posts from all followed users sorted by date. For users following 10 people, this is trivial. For users following 50,000 people (or for systems where reads are millions per second), this becomes prohibitively expensive. The choice between fan-out on write and fan-out on read is one of the most common system design questions in backend interviews for social and content platforms.
Denormalization Patterns and Their Sync Strategies
Counter caches (likes_count on posts): increment/decrement in the same transaction as the likes table INSERT/DELETE. Use SELECT FOR UPDATE on the post row to prevent lost updates under concurrent writes.
Precomputed aggregates (monthly revenue column): compute in a background job, accept that the value is slightly stale (eventual consistency is fine for reporting).
Denormalized display names (author_name on posts): update via a trigger or background job when users update their display name. The business decision is whether historical posts show the name at post time or the current name.
Materialized views: database-native denormalization. PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds without locking. Useful for complex aggregate queries that are expensive to compute but tolerate seconds of staleness.
Redis for fast counters: increment in Redis synchronously, write to PostgreSQL asynchronously. Best for very high-frequency counters that do not need immediate SQL-queryable values.
Related reading
Measuring the Impact of Partial Indexes
The benefit of a partial index depends entirely on the selectivity of the condition. A partial index WHERE status = 'active' on a users table where 95% of users are active saves almost nothing: the index covers 95% of rows, nearly as large as a full index. The same partial index on a users table where 2% of users are active is dramatically smaller, faster to scan, and cheaper to maintain. The selectivity of the partial condition determines the benefit. Always check the data distribution before creating a partial index.
Expression indexes require the expression in the WHERE clause to exactly match the expression in the index definition. LOWER(email) in the index definition must be matched by LOWER(email) in every query that should use the index. The query planner will not recognize LOWER(email) and lower(email) as different expressions (they are not, case-insensitive), but it will distinguish LOWER(email) from email::text::lower (a different expression even if logically equivalent). The safest approach: standardize on one canonical expression form in both the index definition and query code.
The Fan-Out on Write Pattern
The most significant denormalization pattern at scale is fan-out on write: when content is created, precompute and store derived data for every relevant consumer. Facebook's news feed uses this: when a user posts, their post is copied to every follower's feed immediately. At write time, this is expensive (potentially millions of writes for a celebrity post). At read time, fetching a personalized feed is a single indexed lookup on a precomputed table.
The alternative is fan-out on read: compute the feed at query time by fetching all posts from all followed users sorted by date. For users following 10 people, this is trivial. For users following 50,000 people (or for systems where reads are millions per second), this becomes prohibitively expensive. The choice between fan-out on write and fan-out on read is one of the most common system design questions in backend interviews for social and content platforms.
Denormalization Patterns and Their Sync Strategies
- Counter caches (likes_count on posts): increment/decrement in the same transaction as the likes table INSERT/DELETE. Use SELECT FOR UPDATE on the post row to prevent lost updates under concurrent writes.
- Precomputed aggregates (monthly revenue column): compute in a background job, accept that the value is slightly stale (eventual consistency is fine for reporting).
- Denormalized display names (author_name on posts): update via a trigger or background job when users update their display name. The business decision is whether historical posts show the name at post time or the current name.
- Materialized views: database-native denormalization. PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds without locking. Useful for complex aggregate queries that are expensive to compute but tolerate seconds of staleness.
- Redis for fast counters: increment in Redis synchronously, write to PostgreSQL asynchronously. Best for very high-frequency counters that do not need immediate SQL-queryable values.
