Performance audit p99

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 10 most expensive queries (total time)
aura db slow-queries --limit 10

# Connections and database size (endpoint latencies: Studio › Logs)
aura db stats

The pg_stat_statements extension is enabled by default. It aggregates each normalized query with mean_exec_time, calls, rows. The Studio displays this in a sortable table.

select query, calls, mean_exec_time, total_exec_time
from pg_stat_statements
where query not like '%pg_stat_statements%'
order by mean_exec_time desc
limit 10;

The plan tells you why the query is slow. The 4 signals to look for:

• Seq Scan on table > 10k rows → missing index
• rows (estimated) ≠ rows (actual) by ±10× → obsolete stats, analyze table_name
• Nested Loop on 100k+ lines → hash join preferred, maybe a missing WHERE
• Materialize or Sort Method: external merge → RAM exhausted, work_mem to increase

explain (analyze, buffers, format json)
select * from orders
where customer_id = 'abc' and created_at > now() - interval '30 days';

• B-tree (default): equality, range, order by. 80% of cases.
• GIN: full-text search, JSONB, tables. using gin (to_tsvector('french', body)).
• GiST: geospatial (PostGIS), similarity search (pg_trgm).
• HNSW (pgvector): vector search, > 100k vectors.
• Partial: common conditions, where deleted_at is null for soft delete.
• Covering: include (...) to avoid the lookup on the table.

1. N+1 queries — do a join or use !inner in the SDK rather than a loop
2. Pagination by OFFSET — beyond 1000, use cursor-based (where id > last_seen)
3. SELECT * on large tables — request only the necessary columns
4. Long transactions — these block VACUUM and cause bloat. Keep < 1 second
5. Non-indexed RLS Policies — if your policy uses tenant_id = auth.tenant(), index tenant_id