The Problem with Over-Indexing Large Datasets
It’s a common pitfall: adding a B-tree index to every column in your WHERE clause and hoping for the best. I’ve done it too. At first, it works. But once your tables cross the 50GB or 100-million-row mark, these “standard” indexes start to fight back. They eat up disk space, bloat your backups, and slow down every INSERT or UPDATE as the engine struggles to keep the index trees balanced.
PostgreSQL offers much more granular control than most developers realize. You don’t always need to index every row or store the raw value of a column. By using Partial and Functional indexes, you can create leaner, faster databases that stay resident in memory. Let’s look at how to move beyond the default settings.
Core Concepts: Thinking Beyond Standard B-Trees
Standard indexing follows a simple rule: one entry in the index for every row in the table. If you have 100 million rows, your index has 100 million entries. This is often a waste. If 95% of your queries only target a specific subset of data, why index the other 5%?
What is a Partial Index?
Think of a partial index as a filtered map. You define exactly which rows deserve an entry using a WHERE clause. PostgreSQL then ignores any row that doesn’t meet your criteria. The result is a tiny index file that is faster to scan and far cheaper to maintain during writes.
What is a Functional Index?
Sometimes the bottleneck is how you query the data, not how much data there is. If you run WHERE LOWER(email) = '[email protected]', a standard index on email is useless. The database has to perform a slow sequential scan because the index stores the original casing. A functional index (or expression index) stores the pre-computed result of a function, making these lookups nearly instant.
Hands-on Practice: Real-World Scenarios
In my experience, these two techniques solve the majority of performance bottlenecks in read-heavy apps. Here is how they look in production.
Scenario 1: The “Soft Delete” Pattern
Most modern apps don’t delete rows; they set a deleted_at timestamp or an is_active flag. If 90% of your users are active, but you keep millions of old records for compliance, a standard index on username is bloated with data you’ll never search.
-- Standard index: Indexes all 10 million users
CREATE INDEX idx_users_username ON users(username);
-- Partial index: Only indexes the 1 million active users
CREATE INDEX idx_users_username_active ON users(username)
WHERE is_active IS TRUE;The impact is immediate. In one recent project, switching to a partial index for active records shrank the index size from 4.2GB to just 180MB. Because the index was small enough to fit entirely in the RAM Buffer Cache, query latency dropped from 150ms to under 4ms.
Scenario 2: Case-Insensitive Lookups
Users are inconsistent with capitalization. To prevent login issues, you likely use LOWER() in your queries. Without a functional index, your database is doing a lot of unnecessary work.
-- This query ignores a standard index on 'email'
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Use a functional index to fix the bottleneck
CREATE INDEX idx_users_email_lower ON users (LOWER(email));Now, PostgreSQL stores the lowercase strings directly in the B-tree. When the query planner sees the LOWER() function, it maps it straight to this index. It’s the difference between scanning 10 million rows and doing a direct pointer lookup.
Scenario 3: Efficient JSONB Queries
PostgreSQL handles document storage well, but GIN indexes on entire JSONB columns can be massive—sometimes larger than the table itself. If you only care about one specific key, use a functional index instead.
-- metadata: { "source": "mobile", "priority": "high" }
CREATE INDEX idx_metadata_source ON orders ((metadata->>'source'));
-- This query is now lightning fast
SELECT * FROM orders WHERE metadata->>'source' = 'mobile';A functional index here is often 10x to 20x smaller than a full GIN index, saving you significant IOPS.
Scenario 4: Unique Constraints with Nulls
Standard unique indexes treat NULL as a distinct value. This allows multiple rows to have a NULL phone number, which is usually fine. But what if you need a unique constraint that only applies to active records? Partial indexes solve this easily.
-- Ensures phone numbers are unique, but only for active users
CREATE UNIQUE INDEX idx_unique_phone_active
ON users(phone_number)
WHERE is_active IS TRUE;Production Best Practices
These techniques are powerful, but they require precision. Here is what I keep in mind when managing production clusters:
- Match your queries exactly: For a partial index to work, your query’s
WHEREclause must be a subset of the index’s clause. If you indexWHERE status = 'shipped', a query forWHERE status = 'pending'will revert to a slow sequential scan. - Check function volatility: You can only index
IMMUTABLEfunctions. These are functions that always return the same output for the same input, likeLOWER()orUPPER(). You cannot indexnow()because its value changes every millisecond. - Monitor usage: Use
pg_stat_user_indexesto find “dead” indexes. There is no point in paying the write penalty for a complex partial index if the query planner never picks it. - Mix and match: You can combine these features. A
LOWER(email)index that only includesWHERE is_verified IS TRUEis incredibly efficient.
Measuring the Results
Don’t guess—measure. Always run EXPLAIN ANALYZE before and after your changes. You want to see “Index Scan” in the output and a significant drop in the “execution time” metric.
EXPLAIN ANALYZE
SELECT * FROM users
WHERE is_active IS TRUE AND username = 'backend_pro';If the planner still isn’t using your new index, run ANALYZE users; to refresh the table statistics. This often nudges the optimizer in the right direction.
Final Thoughts
PostgreSQL allows you to be surgical with your optimizations. Partial indexes handle the scale of your data, while functional indexes handle the complexity of your queries. By applying these selectively, you can keep your database fast and your infrastructure costs low as your data grows. Don’t just index everything—index with intent.
