ate indexes force the planner to choose one
// or merge them, leading to suboptimal execution plans.
CREATE INDEX idx_ledger_tenant ON financial_ledger (tenant_id);
CREATE INDEX idx_ledger_status ON financial_ledger (status);
CREATE INDEX idx_ledger_created ON financial_ledger (created_at);
// ✅ Best Practice: Composite index ordered by selectivity and sort requirement.
// tenant_id is highly selective (partitions data).
// status filters the subset.
// created_at DESC supports sorting without an explicit sort step.
CREATE INDEX idx_ledger_tenant_status_time
ON financial_ledger (tenant_id, status, created_at DESC);
**Rationale:**
This single index satisfies the filter and sort requirements in one pass. The database can perform an index range scan that returns rows already sorted, eliminating the need for a separate sort operation. This reduces CPU usage and memory pressure during query execution.
### 2. Covering Indexes for Hot Paths
For read-heavy endpoints, the cost of fetching row data from the table heap can dominate latency. A covering index includes all columns required by the query, allowing the database to satisfy the request using only the index structure.
**Implementation:**
Use the `INCLUDE` clause to add non-key columns to the index leaf nodes. These columns are stored in the index but are not part of the search key, meaning they do not affect index ordering or uniqueness constraints.
```typescript
// Query: Dashboard fetching summary data for published transactions.
// SELECT tenant_id, transaction_id, amount, currency
// FROM financial_ledger
// WHERE tenant_id = 't_123' AND status = 'completed';
// ✅ Covering Index:
// Key columns drive the search. INCLUDE columns satisfy the SELECT list.
CREATE INDEX idx_ledger_dashboard_covering
ON financial_ledger (tenant_id, status)
INCLUDE (transaction_id, amount, currency);
Rationale:
With this index, the execution plan shows an "Index Only Scan." The database never touches the main table, avoiding random I/O. In production audits, this technique has reduced query times from 50ms to sub-millisecond levels for dashboard endpoints. However, include columns increase index size, so they should be reserved for high-frequency queries where the read benefit outweighs the storage and write cost.
3. Application-Layer Query Consolidation
Database optimization cannot compensate for inefficient application logic. The N+1 query pattern, where related data is fetched row-by-row in a loop, is a common source of perceived database slowness.
Implementation:
Replace iterative fetching with batched queries using JOIN operations or ORM eager loading.
// ❌ Anti-pattern: N+1 Query Loop
// Fetches 100 transactions, then executes 100 separate queries for user details.
const transactions = await db.query(
'SELECT * FROM financial_ledger WHERE status = $1', ['pending']
);
for (const tx of transactions) {
const user = await db.query(
'SELECT name FROM users WHERE id = $1', [tx.user_id]
);
tx.userName = user.name;
}
// ✅ Best Practice: Single Query with JOIN
// Retrieves all data in one round-trip.
const results = await db.query(`
SELECT
fl.transaction_id,
fl.amount,
u.name AS user_name
FROM financial_ledger fl
JOIN users u ON fl.user_id = u.id
WHERE fl.status = $1
`, ['pending']);
Rationale:
Network round-trip latency often exceeds query execution time. Consolidating queries reduces the number of round trips from N+1 to 1. This is an application-level fix that yields immediate performance gains regardless of index strategy.
4. Write Tax Management
Indexes must be audited regularly. Unused indexes consume storage and degrade write performance without providing any read benefit.
Implementation:
Query system statistics to identify indexes with zero scans in production.
-- Identify indexes that have never been used by the query planner
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan AS total_scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Rationale:
An index with zero scans is pure overhead. Removing unused indexes reduces write amplification and frees storage. In one production case, removing 7 unused indexes from a table with 14 total indexes reduced write latency by 4x.
Pitfall Guide
1. The Selectivity Swap
Explanation: Placing low-selectivity columns (e.g., boolean flags or status enums) before high-selectivity columns in a composite index. This reduces the index's ability to prune the search space early.
Fix: Analyze column cardinality using n_distinct or histogram data. Always place the most selective columns at the beginning of the composite key.
2. Write Amplification Blindness
Explanation: Adding indexes to optimize a single slow query without measuring the impact on write throughput. In write-heavy systems, this can cause transaction queues to back up.
Fix: Before adding an index, benchmark write latency with EXPLAIN ANALYZE on INSERT/UPDATE statements. Monitor write p99 latency after deployment. If writes degrade significantly, reconsider the index necessity or explore partitioning.
3. The N+1 Mirage
Explanation: Attributing high latency to database performance when the root cause is the N+1 query pattern in the application code.
Fix: Enable query logging in development environments. Look for repetitive query patterns with varying parameters. Use ORM tools that detect N+1 patterns or implement batch loading utilities.
4. Covering Index Bloat
Explanation: Including too many columns in a covering index, causing the index size to approach or exceed the table size. This increases memory pressure and write costs.
Fix: Limit INCLUDE columns to only those required by the specific hot path. If multiple queries need different column sets, create separate covering indexes rather than one massive index. Monitor index-to-table size ratio; if it exceeds 1.0, review the strategy.
5. Static Indexes in Dynamic Workloads
Explanation: Indexes that were optimal at launch may become inefficient as data volume and distribution change. Selectivity can shift, rendering composite orders suboptimal.
Fix: Schedule periodic index reviews. Use ANALYZE to update statistics. Monitor query plans over time; if a query switches from an index scan to a sequential scan, investigate data distribution changes.
6. Ignoring Index Order for Sorting
Explanation: Creating a composite index that filters correctly but fails to support ORDER BY, forcing the database to perform an explicit sort operation.
Fix: Append ORDER BY columns to the end of the composite index in the same direction (ASC/DESC). This enables index-only sorting, which is significantly faster than in-memory or disk-based sorts.
7. The Leftmost Prefix Misconception
Explanation: Assuming a composite index (A, B, C) will efficiently support a query filtering only on B or C. The database can only use the index if the query filters on the leftmost prefix columns.
Fix: Ensure query predicates align with the index column order. If queries frequently filter on B alone, create a separate index starting with B. Do not rely on partial index usage for non-prefix columns.
Production Bundle
Action Checklist
Decision Matrix
| Scenario | Recommended Approach | Why | Cost Impact |
|---|
| Read-Heavy Dashboard | Covering Index with INCLUDE | Enables index-only scan; eliminates heap fetch; sub-ms latency. | Moderate write increase; higher storage usage. |
| High-Frequency Logging | Minimal Indexes + Partitioning | Prioritizes write throughput; avoids index maintenance overhead. | Slower ad-hoc reads; requires partition pruning logic. |
| Multi-Tenant SaaS App | Composite Index (tenant_id, ...) | Ensures data isolation and leverages high selectivity of tenant ID. | Low write overhead; optimal read performance per tenant. |
| Ad-Hoc Reporting | Partial Indexes or Materialized Views | Avoids indexing all data; focuses on relevant subsets or pre-aggregates. | Storage cost for views; index maintenance only on subset. |
| Write-Intensive Ingestion | Deferred Indexing or Bulk Loads | Bypasses index updates during bulk operations; rebuilds indexes post-load. | Complex pipeline; temporary read unavailability during load. |
Configuration Template
Use this template to standardize index creation with documentation and safety checks.
-- Index Creation Template
-- Purpose: Optimize query pattern for [Description]
-- Query: SELECT ... WHERE [Columns] ORDER BY [Columns]
-- Expected Benefit: [e.g., Index-Only Scan, Sort Elimination]
BEGIN;
-- Verify index does not already exist
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE tablename = 'financial_ledger'
AND indexname = 'idx_ledger_tenant_status_time'
) THEN
EXECUTE '
CREATE INDEX idx_ledger_tenant_status_time
ON financial_ledger (tenant_id, status, created_at DESC)
';
RAISE NOTICE 'Index created successfully.';
ELSE
RAISE NOTICE 'Index already exists.';
END IF;
END $$;
COMMIT;
-- Post-creation verification
-- Run EXPLAIN ANALYZE on the target query to confirm usage.
-- Monitor pg_stat_user_indexes for idx_scan growth over 24 hours.
Quick Start Guide
- Identify the Bottleneck: Run
EXPLAIN ANALYZE on the slowest query. Look for Seq Scan, Sort, or Bitmap Heap Scan with high filter costs.
- Create the Composite Index: Based on the query's
WHERE and ORDER BY clauses, create a composite index with columns ordered by selectivity.
CREATE INDEX idx_new_strategy ON target_table (col_a, col_b, col_c DESC);
- Verify the Plan: Re-run
EXPLAIN ANALYZE. Confirm the plan now uses Index Scan or Index Only Scan and that execution time has decreased.
- Check Write Latency: Execute a sample
INSERT or UPDATE and measure latency. Ensure write performance has not degraded beyond acceptable thresholds.
- Monitor Production: Track
idx_scan counts in pg_stat_user_indexes over the next 24 hours. If scans remain zero, drop the index immediately.
