Slow queries with pg_stat_statements

​

Enable it (one time per server)

1. Preload the library. pg_stat_statements only collects data when it is in shared_preload_libraries. First check whether it is already there:
   SHOW shared_preload_libraries;
   SHOW pg_stat_statements.track;
   
   If pg_stat_statements is missing, add it: Azure portal → the server → Server parameters → shared_preload_libraries → add PG_STAT_STATEMENTS → Save. This parameter is static: the server restarts. With no HA, that restart takes api-events’ database offline for a minute or two, so do it in a quiet window. Set pg_stat_statements.track to top if it is none. That parameter is dynamic, so no restart.
2. Allow the extension. Azure refuses CREATE EXTENSION for anything that is not in azure.extensions. That list is managed by Terraform (var.postgres_extensions in infra/modules/database, default PG_TRGM,BTREE_GIN), so change it there, not in the portal, or the next apply reverts it:
   postgres_extensions = "PG_TRGM,BTREE_GIN,PG_STAT_STATEMENTS"
   
   azure.extensions is dynamic. Applying it does not restart the server.
3. Create the extension in the soundcheck database, as the admin role:
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   
   This is not in a Prisma migration on purpose. Local and CI databases (postgres:14-alpine) do not preload the library, and the extension only works on a server that does.

​

Read it

cd api-events
DATABASE_URL="postgresql://…/soundcheck?sslmode=require" \
  npm run perf:pg-stat-statements -- --limit 20 --order total

​

From a slow route to a statement

1. The per-route alert, or the “p95 latency by route” tile on the <env> api-events latency dashboard, names the route, e.g. GET /api/v1/organizations/:orgId/offers.
2. In Cloud Logging, filter jsonPayload.event="api_request" AND jsonPayload.route="GET /api/v1/organizations/:orgId/offers" and sort by jsonPayload.duration_ms to see how slow it is and when. The same records, minus /health and /, are in PostHog Logs (service soundcheck-api-events, message api_request), so you can rank routes with SQL:
   SELECT attributes['route'] AS route,
          count() AS requests,
          quantile(0.95)(toFloat(attributes['duration_ms'])) AS p95_ms
     FROM logs
    WHERE service_name = 'soundcheck-api-events'
      AND message = 'api_request'
      AND resource_attributes['deployment.environment'] = 'production'
      AND timestamp > now() - INTERVAL 1 DAY
    GROUP BY route
    ORDER BY requests * p95_ms DESC
    LIMIT 20
   
   Set API_REQUEST_LOG_POSTHOG=false on api-events to stop the PostHog copy without touching the Cloud Logging line.
3. Run the report with --order total, then with --order mean --min-calls 50. Match statements to the route’s operation in api-events/src/systems/<domain>/ (table names and WHERE shapes are usually enough).
4. Check the candidate with EXPLAIN (ANALYZE, BUFFERS) on a local seeded database first. Any fix goes through a normal migration (see Database).