Quick answer
Database query performance is the amount of useful database work required to complete a user journey within its correctness and latency objectives. Diagnose it as an evidence chain, not as a contest to make one SQL statement look fast. Start with the affected route and time window, separate application queueing and connection-pool wait from database execution, identify normalized query families, and then inspect waits, execution plans, estimate accuracy, buffers, temporary I/O, locks, transaction age, maintenance state, and replica lag.
A good remediation changes the smallest proven cause while preserving business invariants. That might mean correcting stale or incomplete planner statistics, rewriting a predicate, adding a workload-shaped index, shortening a transaction, restoring partition pruning, or staging a compatible schema change. It is not automatically “add an index,” “increase the pool,” or “force the old plan.” Each action consumes write capacity, storage, memory, WAL, lock budget, or operational attention.
Success requires more than a faster local benchmark. Recheck the user SLI, query latency distribution, admitted workload, lock and pool waits, estimate-to-actual differences, I/O, retries, and correctness under representative data. A plan that is fast for one literal, one cache state, or one tenant is evidence about that experiment, not proof about the production population.
Shared order-history incident stage
The shared incident begins after the order-history API adds filters for tenant, status, and creation time, followed by descending pagination and a small join to order items. Flash-sale traffic and a historical backfill have changed the data distribution: one tenant now owns a disproportionate share of recent orders, and status is correlated with creation time. The release is syntactically correct and returns correct rows, but its latency rises sharply for the dominant tenant.
Planner statistics do not yet represent the new correlation. The query plan estimates a small result and chooses work that looks inexpensive under that estimate. At execution time, many more rows pass the predicates. Repeated inner work, heap visits, sorting, and temporary I/O grow. The existing single-column indexes are individually plausible but do not support the combined filter, order, and projection. This is a cardinality and workload-shape problem before it is an “index missing” slogan.
A reporting session is also left idle in a long transaction. Its old snapshot delays cleanup of obsolete row versions while the write-heavy orders table changes. More pages must be visited, maintenance falls behind, and index-only opportunities decline. Concurrent writers wait behind locks, application connections stay occupied, pool wait rises, and retries amplify demand. A date expression prevents expected partition pruning, so more partitions participate than the team assumed.
The incident is visible at several clocks: user duration, application queue time, pool acquisition, database execution, lock wait, and replica delay. Each article in this cluster examines one causal layer, while Production Slow Query Troubleshooting reunites them into one response sequence.
Core mechanism and evidence boundary
Use five boundaries: user, application, session, statement, and plan node. The user boundary answers which journey failed and whether a rejection, timeout, stale read, or wrong result counts against its SLI. The application boundary accounts for queueing, pool wait, retries, serialization, and response construction. The session boundary identifies transaction state, lock waits, server role, and cancellation. The statement boundary groups normalized query shapes. The plan-node boundary explains how the database attempted the work.
For low-cardinality aggregation, use stable dimensions such as service.name, deployment.environment.name, normalized route, named operation, and the finite database_role values primary or replica. Keep trace_id, span_id, and request_id in restricted logs or traces for correlation. A normalized query fingerprint or PostgreSQL queryid can help diagnostic grouping, but it is not a safe unbounded metric label and is not a durable business identifier.
Teaching fields in this cluster include query_duration_seconds, lock_wait_seconds, pool_wait_seconds, transaction_age_seconds, replication_lag_seconds, plan_rows, actual_rows, plan_loops, shared_blks_hit, shared_blks_read, temp_blks_written, and dead_tuple_ratio. These names define a compatible learning vocabulary. They are not a stable OpenTelemetry semantic convention. Database semantic conventions and PostgreSQL 18 system views have separate version and stability contracts.
PostgreSQL-specific evidence includes pg_stat_activity, pg_locks, pg_stat_statements, EXPLAIN output, and catalog statistics. Cumulative views can lag active work; statement statistics can reset; query text visibility depends on privileges; estimates are approximate; and EXPLAIN ANALYZE executes the statement. Treat every source according to its collection boundary. Do not infer database-wide demand from sampled traces or infer user impact from one plan alone.
Time alignment matters. Application metrics may use wall-clock buckets, a trace may begin before the sampled database span, and a PostgreSQL statistics snapshot may be transaction-scoped or flushed after activity changes. Record the collection timestamp, window, reset time, server role, and deployment. Compare compatible windows instead of joining values merely because their charts appear beside one another. A statement total since a statistics reset cannot be divided by a five-minute request count without first restricting both sides to the same population.
Separate demand, service, and waiting. Calls received describe demand. Calls admitted to a database session describe a narrower population. Execution time describes service only after a statement starts, while pool and lock wait describe queued ownership of scarce capacity. A timeout can occur in any layer, and later work might continue unless cancellation reaches a cooperative boundary. This separation prevents a lower database duration after aggressive client timeouts from being mistaken for a healthier user journey.
Minimal reproducible PostgreSQL 18 example
Use synthetic data in a disposable PostgreSQL 18 database. The schema captures the incident shape without production identifiers:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_bucket integer NOT NULL,
status text NOT NULL CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
created_at timestamptz NOT NULL,
total_cents integer NOT NULL
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id),
line_no integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (order_id, line_no)
);
CREATE INDEX orders_tenant_idx ON orders (tenant_bucket);
CREATE INDEX orders_created_idx ON orders (created_at);Load skewed, non-sensitive fixtures and run ANALYZE. First inspect without executing:
EXPLAIN (COSTS, VERBOSE, FORMAT JSON)
SELECT o.id, o.created_at, o.total_cents
FROM orders AS o
WHERE o.tenant_bucket = 7
AND o.status = 'PAID'
AND o.created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;In a disposable database, a read-only transaction can gather execution evidence:
BEGIN READ ONLY;
SET LOCAL statement_timeout = '3s';
SET LOCAL lock_timeout = '250ms';
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
SELECT o.id, o.created_at, o.total_cents
FROM orders AS o
WHERE o.tenant_bucket = 7
AND o.status = 'PAID'
AND o.created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;
ROLLBACK;The read-only boundary prevents data-changing SQL in this example, but it does not make a heavy read cheap. The statement can still consume CPU, buffers, temporary space, and I/O. Test with production-like distributions and a safe capacity envelope. Never paste a production write statement into EXPLAIN ANALYZE merely because the word “explain” sounds passive.
Failure modes and dangerous misconceptions
Starting from the plan instead of the user. A slow-looking node may run outside the affected route, while pool acquisition or an external call owns most latency. Anchor the investigation to a user journey, time range, deployment, and database role.
Treating every sequential scan as a defect. Reading a small table or a large fraction of a table sequentially can be cheaper than random heap access through an index. Compare actual work, selectivity, cache state, and concurrency rather than node names.
Adding indexes until the plan changes. Every index costs storage, WAL, cache, vacuum effort, and write latency. Overlapping indexes can make the incident worse. Design from representative predicates, order, projection, constraints, and write volume, then validate.
Increasing the connection pool. More connections do not create more database CPU, memory, I/O, or lock throughput. They can move waiting into PostgreSQL, increase contention, and lengthen recovery. Pool size is an admission-control decision.
Trusting a single fast execution. Warm cache, one tenant, one literal, recent ANALYZE, or an unloaded system can hide the real distribution. Compare multiple representative classes and record cache and concurrency conditions.
Killing sessions without side-effect analysis. Cancellation is cooperative. It does not undo a committed transaction, and terminating an application session may trigger retries. Identify ownership, transaction state, idempotency, and rollback consequences first.
Comparing unlike query populations. The same normalized statement can serve cheap and expensive tenant classes, and a deployment can change how literals map to plans. Aggregate averages hide multimodal behavior. Segment only with bounded, non-sensitive workload classes and preserve the total denominator so a fast minority cannot conceal an impaired majority.
Forcing a plan indefinitely. A hint, disabled planner method, or pinned configuration may reduce immediate latency, but it also freezes an assumption about cardinality, memory, cache, and hardware. If used as an emergency control, document scope, expiry, evidence, and removal criteria. The permanent repair should restore reliable selection under representative distributions.
Security, privacy, capacity, and cost implications
SQL text and bound values can contain PII, tenant data, email addresses, search terms, payment references, or authorization predicates. Execution plans may reveal schema names, table sizes, predicates, and operational topology. Restrict diagnostic roles, redact values before export, apply short retention, and audit access to pg_stat_activity, pg_stat_statements, logs, traces, and plan repositories.
Never use raw SQL, user IDs, order IDs, tenant identifiers, request IDs, trace IDs, PostgreSQL PIDs, or query identifiers as metric labels. Cardinality can grow without bound and disclose sensitive workload structure. Aggregate metrics on bounded route, operation, environment, and database role; keep high-cardinality correlation in access-controlled diagnostic systems.
Performance fixes redistribute capacity. A covering index may reduce reads but enlarge cache footprint and write amplification. Higher statistics targets improve estimates but increase analysis and planning cost. More work_mem applies per operation and can multiply across concurrent sessions. Partitioning adds objects, metadata, maintenance jobs, and planning work. Concurrent DDL reduces some blocking but uses I/O, WAL, CPU, and time.
Budget the remediation as a change: expected resource use, lock timeout, statement timeout, replica/WAL impact, stop conditions, rollback or roll-forward path, and user guardrails. Cost includes engineering and operational complexity, not only cloud storage.
Testing and production validation
Build a representative fixture with skew, correlated columns, recent and old rows, realistic row widths, and enough volume to exercise the expected plan. Record the PostgreSQL version, configuration differences, schema, statistics age, cache condition, and concurrency. Compare results for dominant, typical, and rare tenant/status combinations.
Test correctness before speed: returned rows, ordering, pagination continuity, authorization scope, read-after-write requirements, and transaction invariants. Then compare p50, p95, and p99 user latency; pool wait; database duration; lock wait; plan estimate-to-actual ratios; buffers; temporary blocks; WAL; CPU; and retries. A faster mean with worse tail latency or increased write cost is not an unconditional improvement.
Stage index and schema changes against concurrent reads and writes. Set bounded lock and statement timeouts. Observe progress and invalid objects. Exercise cancellation and retry behavior. If a replica is part of the path, measure lag and confirm whether the user journey permits stale reads.
After production rollout, compare an explicit pre-change and post-change window at similar demand. Confirm the intended query family received the change, other write and read workloads did not regress, maintenance remains healthy, and the user SLI recovered. Keep a rollback or roll-forward decision point until the evidence window is complete.
Test plan stability across data growth rather than requiring byte-identical plans. Seed additional recent rows, change tenant skew, and repeat analysis after statistics refresh. The important contract is bounded user impact and resource use, not preservation of a particular node name. A plan change can be healthy when the distribution changes; a visually identical plan can still become expensive when its loops or rows grow.
Include failure tests. Let a statement hit its local timeout, introduce a bounded lock wait, cancel a read, and simulate a failed concurrent maintenance command in staging. Confirm that the application releases its connection, reports the right outcome, does not retry without remaining deadline and idempotency, and leaves no invalid object or partially applied compatibility state unnoticed.
Operations checklist
- Identify the affected user journey, SLI, deployment, route, operation, database role, and time window.
- Split request duration into application queue, pool wait, database execution, lock wait, and response work.
- Group normalized query families without exporting sensitive parameter values.
- Check active waits, blockers, transaction age, pool occupancy, retries, and replica lag before changing plans.
- Capture a non-executing plan first; use execution analysis only inside a proven safe boundary.
- Compare estimated rows, actual rows, loops, buffers, temporary I/O, WAL, and returned rows.
- Check statistics freshness, skew, correlated predicates, and prepared-plan behavior.
- Evaluate indexes against filtering, ordering, projection, constraints, writes, storage, and maintenance.
- Check dead tuples, vacuum progress, old snapshots, visibility, table size, and index size.
- Verify partition pruning in the actual plan rather than assuming it from the schema.
- Put lock, duration, capacity, correctness, and invalid-object stop conditions around every DDL action.
- Revalidate user SLI, tails, errors, retries, pool wait, database resource use, maintenance, and correctness.
Official sources
- PostgreSQL 18: Using EXPLAIN — plan structure and execution evidence; accessed 2026-08-04.
- PostgreSQL 18: Monitoring Database Activity — activity, statistics, locks, and progress views; accessed 2026-08-04.
- PostgreSQL 18: pg_stat_statements — normalized statement statistics and privilege boundaries; accessed 2026-08-04.
- OpenTelemetry Database Semantic Conventions — database telemetry vocabulary and stability status; accessed 2026-08-04.
- Prometheus Instrumentation Practices — metric and label design; accessed 2026-08-04.
Continue the learning path
When this evidence belongs to a Spring workload, Spring Service Layers and Transaction Boundaries Explained identifies which application work should retain the connection, and Spring Data JPA Query Performance Explained maps ORM fetch choices back to SQL and plans. Use Production Spring Boot Incident Troubleshooting when the query evidence must guide a reversible live mitigation without losing order-state correctness.
Start evidence collection with PostgreSQL EXPLAIN and Query Plans, then investigate estimate quality in Query Planner Statistics and Cardinality Estimation. Connect database time to application saturation through Database Connection Pooling and to end-to-end evidence through Observability for Backend Systems. The ordered course is available in System Design, and Topics provides the broader learning map.