A slow query hits production on a Friday afternoon. The dashboard that loaded in 200 milliseconds last month now takes 8 seconds. Someone checks the query plan, finds a sequential scan on a 12 million row table, adds an index, and the query drops to 15 milliseconds. Crisis resolved in five minutes.
This scenario plays out constantly, and it is almost always preventable. Indexing is one of the most impactful performance tools available to any engineering team, yet most developers treat it as an afterthought. They create tables, write queries, and only think about indexes when something breaks.
We have optimized PostgreSQL databases powering SaaS platforms, marketplace applications, and financial systems. The pattern is consistent: teams that approach indexing as a deliberate architecture decision rather than a reactive fix ship faster products and spend far less time firefighting. Indexing strategy belongs in your system architecture planning alongside schema design and query patterns.
Index Types You Need to Know
An index is a separate data structure that PostgreSQL maintains alongside your table. Without one, every query performs a sequential scan, reading every row. With the right index, the database jumps directly to matching rows.
B-tree indexes are the default and handle the vast majority of use cases: equality checks, range queries, sorting, and prefix text matching. On a 10 million row table, a B-tree finds a specific row in roughly 3 to 4 page reads instead of scanning every row.
GIN (Generalized Inverted Index) indexes handle values containing multiple elements. They are essential for full text search on tsvector columns, JSONB queries, and array operations. If your application stores flexible data in JSONB columns, GIN indexes are often the difference between a usable query and a timeout.
GiST (Generalized Search Tree) indexes serve geometric data, range types, and spatial queries. If your product involves location based lookups or overlapping time ranges (like booking conflict detection), GiST is the right tool.
Partial indexes are one of PostgreSQL's most underused features. They only include rows matching a condition: CREATE INDEX idx_active_users ON users (email) WHERE status = 'active'. If 90% of your table is inactive rows your queries never touch, this index is a fraction of the size of a full index while being just as fast for the queries that matter.
Composite indexes cover multiple columns. Column order matters significantly. An index on (tenant_id, created_at) helps queries filtering on tenant_id alone or on both columns, but does not help queries filtering only on created_at. We cover related schema patterns in our database schema design guide.
When to Add Indexes
The decision should be driven by actual query patterns, not speculation.
Foreign key columns. Primary keys and unique constraints get automatic indexes. Foreign keys do not. This is one of the most common performance oversights we see. If you have an orders table with a customer_id foreign key and you query orders by customer, that column needs an explicit index.
Columns in WHERE clauses on large tables. If a query filters on a column and the table has more than a few thousand rows, that column is a candidate. The higher the selectivity, the more beneficial the index.
JOIN and ORDER BY columns. Unindexed join columns cause nested loop scans that grow quadratically. Unindexed sort columns force PostgreSQL to sort entire result sets in memory.
When NOT to Index
Indexes are not free. Every index consumes disk space, slows down writes, and adds VACUUM overhead.
Write heavy tables with low read frequency. If a table receives thousands of inserts per second and is rarely queried (like a raw event log), indexes hurt throughput. We have seen ingestion pipelines improve by 40% after removing unnecessary indexes from staging tables.
Low cardinality columns. An index on a boolean or a status column with 3 possible values is rarely useful. The exception is combining low cardinality with a partial index on the rare value.
Small tables. A table with 500 rows scans faster than the overhead of an index lookup. PostgreSQL is smart enough to choose sequential scans on small tables regardless.
Identifying Missing Indexes in Production
PostgreSQL gives you the data. You do not have to guess.
EXPLAIN ANALYZE shows the exact execution plan for any query. Look for sequential scans on large tables with filters that discard most rows. That pattern is a clear signal that an index is missing.
pg_stat_user_tables tracks scan counts per table. Query it with SELECT relname, seq_scan, idx_scan, seq_tup_read FROM pg_stat_user_tables ORDER BY seq_tup_read DESC. A table with millions of rows and a high seq_scan count almost certainly needs better indexing.
pg_stat_user_indexes reveals which indexes are actually used. An index with zero scans is dead weight. We audit index usage quarterly and drop indexes unused for 90 days.
pg_stat_statements tracks query performance statistics. Sorting by total_time reveals your biggest optimization targets. We have written about broader scaling patterns in our scaling guide for growing applications.
Index Bloat and Maintenance
Indexes degrade over time. As rows change, indexes accumulate dead entries. When VACUUM cannot keep pace, the result is index bloat, where indexes grow larger than necessary, consuming extra memory and disk IO.
Monitor bloat using the pgstattuple extension. Bloat above 30% is worth addressing. REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds indexes without locking writes, making it safe for production. We schedule this during low traffic windows for fast changing indexes.
Tune autovacuum for high traffic tables. The default settings are conservative. Adjusting autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold per table keeps bloat under control.
Real Patterns From Production
On a multi tenant SaaS platform we optimized, the activity_logs table had 85 million rows with no index on tenant_id. The team assumed PostgreSQL indexed foreign keys automatically. Adding a single B-tree index dropped join queries from 12 seconds to 40 milliseconds.
On a financial reporting system, we replaced a generic GIN index on a JSONB column with three targeted partial B-tree indexes on fields the application actually queried. Index size dropped by 70% and query performance improved by 3x.
Building an Indexing Discipline
Indexing should not be something you think about only when things break. Review EXPLAIN ANALYZE output for new queries before they reach production. Monitor pg_stat_user_tables weekly. Audit unused indexes quarterly. Treat index decisions with the same rigor you give to schema design.
If your database is slowing down and you are not sure where to start, or if you want to build a performance foundation that scales with your product, reach out to us. We will profile your queries, identify the highest impact optimizations, and build an indexing strategy that keeps your application fast as it grows.