An index should address a measured query pattern, not decorate every column that looks important. Start with the request users experience as slow, then inspect the actual database work behind it.
For a SaaS, tenant filters, status filters, joins, and ordering often appear together. Evaluating them separately can lead to an index that exists but does little for the real query.
Capture the query and operating context
Record the query shape, parameters, tenant size, returned rows, frequency, and observed latency distribution. Include concurrent load and whether the delay is actually in the database rather than an external API or repeated application requests.
Use the official PostgreSQL EXPLAIN guide to understand plan inspection. Be careful with tools that execute queries while measuring them, especially writes or expensive operations. Use an authorised environment and representative fixtures for experiments.
Evaluate a fictional open-request list
Suppose staff list open service requests for one organisation, ordered by newest update, with pagination. The production-like dataset has one large tenant and many small ones. A plan observed on a tiny development database may not represent either group's actual work.
Compare the unmodified query with a candidate index designed for the combined filter and ordering. Inspect the plan, rows processed, sort work, and observed request behaviour under the same conditions. Record the dataset and environment so the comparison is reproducible.
Then test inserts and status updates, not only reads. The index adds maintenance work and storage. Consult the PostgreSQL index reference for available designs and their tradeoffs rather than assuming one index type fits every access pattern.
Verify that the application uses the intended pattern
An index cannot repair a query that fetches every row and filters in application memory. It may also fail to help a differently shaped query introduced by an ORM or a new sorting option. Inspect the generated operation and realistic parameters.
Review deployment impact for the installed database version and table size. Creating or changing an index is an operational change with possible resource and lock effects. Plan the method and observation window rather than treating the migration file as the entire task.
The tradeoff is read benefit versus write and maintenance cost. Keep indexes that serve meaningful workloads and review overlapping or unused candidates with evidence. Do not claim an improvement until it is measured in the relevant environment.
The StackFlight case study provides PostgreSQL-backed SaaS context. The open-request query is fictional and includes no benchmark claim.
Questions about index decisions
Should every tenant-owned table have the same index?
Not automatically. Use the actual filters, joins, ordering, and workload. Similar ownership does not imply identical query patterns.
Is a sequential scan always bad?
No. It can be appropriate for some table sizes and result sets. Interpret the whole plan and observed workload instead of judging one node name.
What should a review packet contain?
Include the query, representative data distribution, plans, read observations, write impact, and rollout method. Bring it to a database performance review.