Skip to content
Aurabase Logo
aurabase

Performance audit p99

Identify slow queries, understand EXPLAIN ANALYZE, indexing strategies, and the 5 anti-patterns that kill Postgres in production.

How-to guide14 min readLevel advancedRevised on Apr 15, 2026

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.

Info

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.

clibash
# 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.

top-10bash
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 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
debugsql
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 null for soft delete.
  • Covering: include (...) to avoid the lookup on the table.
Attention

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#

  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

To go further#