Database Indexing Mistakes That Kill SaaS Performance

Scaling SaaS applications often exposes hidden database inefficiencies. This playbook details common indexing errors and provides actionable SQL tactics to prevent performance degradation at scale.

A fast API and clean code can mask underlying database inefficiencies until a system hits critical mass. The author of a recent dev.to post claims that queries running in 12ms can slow to 4 seconds once a table reaches 500,000 records. This performance hit, often manifesting as lagging dashboards and user support tickets, typically points to database indexing issues.

The problem, the post asserts, is less about missing indexes and more about incorrect or poorly optimized ones. These can actively degrade write performance without delivering meaningful improvements to read operations. Addressing these issues proactively is crucial for SaaS founders aiming to maintain responsiveness as their user base and data volume grow.

Avoid Over-Indexing "Just in Case"

The most frequent indexing error, according to the post, is not under-indexing but rather the practice of over-indexing. New engineers often add an index to every column appearing in a WHERE clause, believing this ensures safety. This approach creates a "write tax": every INSERT, UPDATE, and DELETE operation requires the database system to update all indexes on that table. While negligible at low volumes, this overhead becomes a significant bottleneck at high throughput, such as 10,000 writes per minute.

To mitigate this, the post recommends regular index audits. For PostgreSQL, the pg_stat_user_indexes view provides insights into index usage. The query SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC; identifies indexes with idx_scan values at or near zero. These are candidates for removal after further investigation, as they have seen little to no use since the last statistics reset.

Understanding Index Selectivity

Another critical mistake involves failing to understand index selectivity. An index on a boolean column, such as is_active or is_deleted, is often ineffective. Selectivity measures the ratio of distinct values to total rows. A boolean column has only two distinct values. If, for example, 95% of rows have is_active = true, an index on this column provides minimal useful information to the query planner. The planner will frequently bypass such an index and perform a full table scan, which is often more efficient in these scenarios.

The post illustrates this with an example: CREATE INDEX idx_users_is_active ON users(is_active); is nearly useless if 95% of users are active. A more effective solution is a partial index, which only indexes rows that meet a specific condition. The example provided is CREATE INDEX idx_users_active_created ON users(created_at) WHERE is_active = true;. This index is smaller, faster, and more selective because it targets a specific subset of data. The author's rule of thumb suggests that if a column has fewer than 10-20 distinct values relative to the table size, a plain index on it will underperform, necessitating partial or composite indexes instead.

Refining Indexing for Scale

The post provides a solid foundational understanding of common indexing pitfalls, particularly for PostgreSQL and MySQL environments. To enhance this playbook for founders scaling modern SaaS, several areas warrant deeper exploration. The primary recommendation for auditing indexes, while effective, can be augmented with tools for query plan analysis. Using EXPLAIN ANALYZE in PostgreSQL provides detailed execution plans, revealing exactly how the database uses (or ignores) indexes and where bottlenecks occur. This offers a more granular understanding than pg_stat_user_indexes alone.

Furthermore, while the advice on idx_scan is sound, a zero scan count does not always mean an index is useless. It could be a rarely used but critical index for specific reports or archival queries. A more robust audit process would involve correlating idx_scan with business requirements and query logs to confirm redundancy. The post focuses on relational databases. Founders using NoSQL databases (e.g., MongoDB, Cassandra) or specialized data stores will encounter entirely different indexing paradigms, requiring distinct strategies for performance optimization.

Advanced indexing strategies, such as covering indexes (which include all columns needed by a query, avoiding table lookups) or expression indexes (indexing the result of a function or expression), could be explored. For very large tables or specific data types, PostgreSQL offers specialized index types like GIN (for full-text search and JSONB data) or BRIN (for very large, ordered data like timestamps). Proactive monitoring tools, beyond manual SQL queries, are also critical. Services like Datadog, New Relic, or even PostgreSQL's own pg_stat_statements module can provide continuous insights into query performance and index utilization, allowing for early detection of degradation.

Database indexing is not a set-and-forget task; it is an ongoing operational discipline. As data volumes and query patterns evolve, so too must the indexing strategy. Proactive auditing, a deep understanding of query execution plans, and a nuanced approach to index selectivity are essential for maintaining performance. Founders who treat database indexing as a continuous optimization challenge, rather than a one-time setup, will be better positioned to scale their applications efficiently and avoid the costly performance regressions that accompany growth.

The investor read

Operational efficiency in database management directly impacts unit economics and scalability for SaaS businesses. The signal highlights a common source of technical debt: unoptimized database indexes. While seemingly minor, these issues can lead to significant cost increases in compute resources, slower user experiences, and higher churn rates, directly affecting customer lifetime value. Investors should assess a company's database health, particularly as it scales past early adopters. Metrics like query latency, database CPU utilization, and the ratio of idx_scan to total queries can indicate underlying issues. Companies that invest in proactive database performance tuning, often through dedicated DevOps or SRE talent, demonstrate a maturity that reduces future operational risk and improves capital efficiency. This focus on backend performance is a signal of a robust engineering culture capable of supporting sustained growth.

Pull quote: “The problem, the post asserts, is less about missing indexes and more about incorrect or poorly optimized ones.”