Performance audit p99
Identify slow queries, understand EXPLAIN ANALYZE, indexing strategies, and the 5 anti-patterns that kill Postgres in production.
Measure before optimizing#
An audit starts with numbers. The Studio exposes p50/p95/p99 by endpoint and by query. Capture a 7-day baseline before any change.
The aura db slow-queries and aura db stats CLI commands query pg_stat_statements and database pool/size metrics on your linked project, scoped to your project (also available in Studio › Database and Logs). aura db inspect indexes|tables|locks lists index usage (with the unused ones flagged), table sizes and dead rows, and blocking locks — the diagnostics that /raw cannot run because it refuses system catalogs.
# The 10 most expensive queries (total time)
aura db slow-queries --limit 10
# Connections and database size (endpoint latencies: Studio › Logs)
aura db stats
pg_stat_statements in real time#
pg_stat_statements is installed and preloaded on every new project. It aggregates each normalized query (no literal values are stored) with mean_exec_time, calls and rows. /raw does not read it directly — the view is cluster-wide — so use the commands above, which return only your project's queries, or Studio › Database.
aura db slow-queries --limit 10 --json
aura db inspect indexes # idx_scan = 0 and not unique/primary → unused
Read the plan like a pro#
A query that runs longer than the project's statement timeout (10 s) is cancelled with STATEMENT_TIMEOUT (HTTP 504) / « canceling statement due to statement timeout » in aura db raw: reduce the work or split it.
The plan tells you why the query is slow. The 4 signals to look for:
Seq Scanon table > 10k rows → missing indexrows (estimated) ≠ rows (actual)by ±10× → obsolete stats,analyze table_nameNested Loopon 100k+ lines → hash join preferred, maybe a missing WHEREMaterializeorSort Method: external merge→ RAM exhausted,work_memto increase
explain (analyze, buffers, format json)
select * from orders
where customer_id = 'abc' and created_at > now() - interval '30 days';
When, what, how#
- B-tree (default): equality, range, order by. 80% of cases.
- GIN: full-text search, JSONB, tables.
using gin (to_tsvector('french', body)). - GiST: range types, exclusion constraints, similarity search (
pg_trgm). - HNSW (pgvector): vector search, > 100k vectors.
- Partial: common conditions,
where deleted_at is nullfor soft delete. - Covering:
include (...)to avoid the lookup on the table.
Each index slows down INSERT/UPDATE/DELETE (~5-15% per index). Drop unused indexes — aura db inspect indexes lists them (never scanned, not unique, not primary).
What kills Postgres in production#
- N+1 queries — do a join or use
!innerin the SDK rather than a loop - Pagination by OFFSET — beyond 1000, use cursor-based (
where id > last_seen) - SELECT * on large tables — request only the necessary columns
- Long transactions — these block VACUUM and cause bloat. Keep < 1 second
- Non-indexed RLS Policies — if your policy uses
tenant_id = auth.tenant(), indextenant_id