How do I tune PostgreSQL indexes for an API-heavy application?

For an API-heavy PostgreSQL workload, index tuning is mostly about matching your real query patterns and avoiding indexes that add write overhead without helping reads. Here’s a practical approach.

1) Start from actual queries, not guesses

Collect the top API queries by frequency and latency.

Useful tools:

• pg_stat_statements for query frequency and total time
• EXPLAIN (ANALYZE, BUFFERS) for slow queries
• App/APM traces to identify endpoints that hit the DB most

What to look for:

• WHERE filters
• JOIN keys
• ORDER BY
• LIMIT
• frequent pagination patterns
• repeated lookup by tenant/user/account IDs

2) Index the columns used in filtering and joins

PostgreSQL is strongest when indexes match WHERE and JOIN conditions.

Typical examples:

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON api_keys (user_id);

If queries filter on multiple columns together:

CREATE INDEX ON orders (tenant_id, status);

Rule of thumb:

• Put the most selective or commonly constrained columns first only if it matches query patterns
• For composite indexes, the leftmost prefix matters

3) Make composite indexes match common query shapes

If your API frequently runs:

SELECT * FROM events
WHERE tenant_id = $1 AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

A good index is often:

CREATE INDEX ON events (tenant_id, created_at DESC);

This helps with both filtering and ordering.

If the query also filters by status:

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

But don’t add columns just because you can. Every extra index slows inserts/updates/deletes.

4) Use covering indexes when they avoid heap reads

If a query only needs a few columns, a covering index can help.

Example:

CREATE INDEX ON users (tenant_id, email) INCLUDE (id, created_at);

This can enable index-only scans if visibility map conditions are favorable.

Good for:

• “list” endpoints
• lookup endpoints returning a small projection
• read-heavy tables

5) Use partial indexes for common filtered subsets

If many API queries only target a subset of rows, partial indexes are very effective.

Example:

CREATE INDEX ON orders (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;

Or:

CREATE INDEX ON jobs (status, run_at)
WHERE status = 'pending';

This reduces index size and write overhead.

6) Be careful with low-selectivity columns

Columns like status, boolean flags, or is_active alone often don’t benefit much from standalone indexes unless:

• the table is very large
• the filter is very selective in practice
• you use a partial index

Example:

CREATE INDEX ON users (is_active);

This may be useless if almost all rows are active.

Better:

CREATE INDEX ON users (tenant_id, is_active);

or a partial index:

CREATE INDEX ON users (tenant_id)
WHERE is_active = true;

7) Optimize pagination queries

API-heavy apps often page through data.

Avoid offset pagination on large tables:

LIMIT 50 OFFSET 50000;

Prefer keyset pagination:

WHERE tenant_id = $1 AND created_at < $last_seen
ORDER BY created_at DESC
LIMIT 50;

Index:

CREATE INDEX ON events (tenant_id, created_at DESC, id DESC);

This gives stable, fast pagination.

8) Watch out for over-indexing

Too many indexes hurt:

• write performance
• vacuum cost
• storage usage
• planner choices

Signs of over-indexing:

• many indexes on the same table with overlapping columns
• indexes never used in pg_stat_user_indexes
• slow writes but no corresponding read gain

Periodic cleanup:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

9) Check whether queries can use indexes at all

Common reasons indexes are ignored:

• functions on columns without expression indexes
• mismatched data types
• leading wildcards in LIKE '%foo'
• poor selectivity
• outdated statistics

Examples:

• Instead of WHERE LOWER(email) = LOWER($1), create:

  CREATE INDEX ON users (LOWER(email));
  

• For prefix search:

  CREATE INDEX ON products (name text_pattern_ops);
  

• For %term% search, consider trigram indexes:

  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  CREATE INDEX ON products USING gin (name gin_trgm_ops);
  

10) Keep statistics fresh

The planner needs good stats to choose indexes.

Make sure autovacuum/analyze is keeping up, especially on fast-changing tables.

If needed:

ANALYZE your_table;

For skewed columns, increase statistics target:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

11) Use the right index type

Most API workloads mainly need B-tree indexes, but sometimes others help:

• B-tree: equality, range, sort
• GIN: arrays, JSONB containment, full-text, trigram search
• GiST: geospatial, ranges
• BRIN: huge append-only tables with naturally clustered data

Examples:

CREATE INDEX ON documents USING gin (tags);
CREATE INDEX ON audit_logs USING brin (created_at);

12) Tune for your endpoint patterns

Common API patterns and index ideas:

Lookup by ID

SELECT ... FROM users WHERE id = $1;

Primary key is enough.

Tenant-scoped reads

WHERE tenant_id = $1 AND id = $2

Composite index:

CREATE INDEX ON table_name (tenant_id, id);

Recent items per tenant

WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX ON table_name (tenant_id, created_at DESC);

Soft deletes

WHERE tenant_id = $1 AND deleted_at IS NULL

Partial index:

CREATE INDEX ON table_name (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;

Search by email

WHERE email = $1

Unique index:

CREATE UNIQUE INDEX ON users (email);

If case-insensitive:

CREATE UNIQUE INDEX ON users (LOWER(email));

13) Measure before and after

For each candidate index:

1. identify the query
2. run EXPLAIN (ANALYZE, BUFFERS)
3. add the index
4. compare execution time and buffer hits
5. monitor write latency and index usage

14) Consider index creation impact in production

On large tables:

CREATE INDEX CONCURRENTLY ...

This avoids blocking writes, but takes longer and has caveats.

---

Practical checklist

For an API-heavy app, prioritize:

• primary keys and foreign keys
• composite indexes matching top WHERE + ORDER BY + LIMIT queries
• partial indexes for soft deletes / status subsets
• covering indexes for common read endpoints
• trigram/GIN indexes for search
• keyset pagination indexes
• removal of unused/duplicate indexes

---

If you want, I can help you tune a specific table and query set if you paste:

• table schema
• 3–5 slow queries
• EXPLAIN (ANALYZE, BUFFERS) output