How do I tune PostgreSQL indexes for a latency-sensitive API?

For a latency-sensitive API, PostgreSQL index tuning is mostly about minimizing the number of rows PostgreSQL must touch per request and making sure the planner reliably picks the right index. The best approach is usually: profile the slow queries, create the smallest useful indexes for those exact access patterns, and keep them maintained.

1) Start with the actual slow queries

Use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Look for:

• Seq Scan on large tables
• Index Scan that still reads many rows
• High buffer reads
• Sorts that could be avoided with an index
• Nested loops over large result sets

For APIs, optimize the top 5–20 queries by p95/p99 latency, not “all queries.”

---

2) Index for the query shape, not the table

Think in terms of:

• WHERE
• ORDER BY
• JOIN
• LIMIT
• DISTINCT

Common patterns

Equality lookups

WHERE user_id = ?

Use:

CREATE INDEX ON table_name (user_id);

Multiple filters

WHERE tenant_id = ? AND status = ?

Use a composite index:

CREATE INDEX ON table_name (tenant_id, status);

Order matters: put columns with the most selective and commonly filtered leading position first, but also consider query patterns.

Filtering + sorting + limit

WHERE tenant_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 20

Use:

CREATE INDEX ON table_name (tenant_id, status, created_at DESC);

This can avoid a sort and let PostgreSQL stop early.

---

3) Prefer covering indexes when they reduce heap access

If a query only needs a few columns, use INCLUDE:

CREATE INDEX ON orders (tenant_id, created_at DESC) INCLUDE (amount, status);

This can enable index-only scans when visibility map conditions are favorable. It’s especially useful for read-heavy APIs.

Avoid bloating indexes with too many included columns—only include what materially reduces heap reads.

---

4) Use partial indexes for hot subsets

If the API mostly queries a small subset of rows, index only that subset.

Example:

CREATE INDEX ON jobs (tenant_id, created_at DESC)
WHERE status = 'pending';

This is excellent for:

• active records
• soft-deleted rows (deleted_at IS NULL)
• “open” states
• feature-flagged subsets

Partial indexes are often one of the highest ROI optimizations for latency-sensitive workloads.

---

5) Don’t over-index write-heavy tables

Every index slows:

• INSERT
• UPDATE
• DELETE
• vacuuming
• replication

For API latency, too many indexes can hurt overall throughput and increase tail latency.

General rule:

• Keep only indexes that support a known query pattern
• Remove unused indexes
• Watch index bloat and write amplification

---

6) Make sure the index order matches how PostgreSQL can use it

For a composite index like:

(a, b, c)

It’s most useful for:

• a = ...
• a = ... AND b = ...
• a = ... AND b = ... AND c = ...

It’s less useful for:

• b = ... alone
• c = ... alone

If queries vary, you may need separate indexes or rethink query shapes.

---

7) Use the right index type

Most API workloads use B-tree indexes.

But consider:

• GIN for arrays, jsonb, full-text search
• GiST for ranges / geospatial
• BRIN for huge append-only tables with natural ordering

Examples:

CREATE INDEX ON events USING gin (payload jsonb_path_ops);
CREATE INDEX ON logs USING brin (created_at);

For low-latency APIs, B-tree is still the default winner unless your data model demands otherwise.

---

8) Watch out for queries that defeat indexes

Indexes may not help much if your query uses:

• functions on indexed columns
• leading wildcards
• implicit casts
• OR across unrelated fields
• NOT IN on large sets

Examples:

WHERE lower(email) = lower(?)

Use a functional index:

CREATE INDEX ON users (lower(email));

For text search:

• LIKE 'prefix%' can use B-tree
• LIKE '%substring%' usually cannot; consider trigram indexes:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON documents USING gin (title gin_trgm_ops);

---

9) Keep statistics fresh

Bad estimates lead to bad plans.

Make sure autovacuum is working and stats are current:

ANALYZE table_name;

For skewed columns, increase stats targets if needed:

ALTER TABLE table_name ALTER COLUMN status SET STATISTICS 1000;
ANALYZE table_name;

This helps PostgreSQL choose the right index more consistently.

---

10) Verify with realistic data and workloads

A query that looks fast in isolation may be slow under concurrency or with production distributions.

Test:

• production-like row counts
• realistic parameter values
• concurrency
• cold vs warm cache
• p95/p99 latency, not just average

Use:

• EXPLAIN (ANALYZE, BUFFERS)
• pg_stat_statements
• auto_explain
• load testing with production-like traffic

---

11) Check for index bloat and fragmentation

Indexes can degrade over time.

Monitor:

• index size
• bloat
• dead tuples
• vacuum effectiveness

Useful tools:

• pgstattuple
• pg_stat_user_indexes
• pg_stat_all_tables

If needed, rebuild carefully:

REINDEX INDEX CONCURRENTLY index_name;

---

12) Practical workflow for API tuning

1. Identify top slow queries from pg_stat_statements
2. Run EXPLAIN (ANALYZE, BUFFERS)
3. Add the smallest useful index
4. Re-test latency and plan stability
5. Remove redundant indexes
6. Repeat for the next bottleneck

---

13) Common anti-patterns

• Creating one index per column “just in case”
• Adding giant multi-column indexes without query evidence
• Forgetting ORDER BY/LIMIT patterns
• Ignoring partial indexes
• Using LIKE '%term%' and expecting B-tree magic
• Not analyzing after large data changes
• Keeping stale or duplicate indexes

---

14) A simple rule of thumb

For latency-sensitive APIs:

• Index the filters
• Index the sort order
• Use partial indexes for hot subsets
• Use INCLUDE for covering reads
• Keep the index count lean
• Measure everything with real query plans

If you want, I can help you design the exact indexes for a specific query or table schema—paste the query and EXPLAIN (ANALYZE, BUFFERS) output.