PostgreSQL performance: query plans, indexes and connection pools

Who is this for?

Mobile backend developers using PostgreSQL.

Before you start

Representative staging data, the query and measurable endpoint latency.

1. Break down endpoint latency

Connection waiting, external services, serialization and N+1 queries can add time beyond SQL execution. Record stage timings and query shapes without sensitive values. A list that fires one query per item may need a structural fix before an index adjustment.

2. Read the query plan

EXPLAIN describes the selected plan. EXPLAIN ANALYZE actually executes the statement, so do not casually run writes in production. Use representative staging data to compare estimates and actual rows. Sequential scans can be reasonable for small tables.

-- Adapt to your staging schema. ANALYZE executes the query.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM notes
WHERE project_id = 42
ORDER BY created_at DESC, id DESC LIMIT 20;

3. Test an index matching the query

Filtering and ordering both matter for recent project records. This candidate serves the example, not every schema. Indexes also consume storage and add write work. Compare before/after plans instead of adding them indiscriminately.

CREATE INDEX notes_project_recent_idx
ON notes (project_id, created_at DESC, id DESC);

4. Bound pagination and relationship work

Use a stable ordering with a unique tie-breaker and a maximum page size. Batch related reads or return selected fields to avoid N+1 patterns. Excessive JSON also costs network bandwidth and mobile memory.

5. Budget total connections

Each API process and worker may own a pool. Multiply pool size by process count and retain room for administration. Track wait time and long transactions. More connections do not necessarily increase throughput.

6. Measure the complete workflow

Compare the same dataset and traffic mix before and after. Record p95, errors, database resources and write latency. Large-table index creation can affect locks or load, so plan the installation method and maintenance window. Do not report only the fastest isolated query.