> ## Documentation Index
> Fetch the complete documentation index at: https://docs.soundchecklive.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Slow queries with pg_stat_statements

> How to enable pg_stat_statements on the Azure Flexible Server Postgres behind api-events, and how to read it with the read-only report script.

Per-route latency (the `api_request` log line and the `<env>-api-events-request-latency` metric in Cloud Monitoring) tells you **which route** is slow. `pg_stat_statements` tells you **which SQL** is slow. Use the route alert to find the route, then this page to find the statement.

The production database is Azure Database for PostgreSQL Flexible Server `prod-cus-core-postgres` (PostgreSQL 16, `B_Standard_B2s`: 2 burstable vCores, 4 GiB), managed by `infra/modules/database`.

## Enable it (one time per server)

Enabling it takes two server settings and one `CREATE EXTENSION` in the application database.

1. **Preload the library.** `pg_stat_statements` only collects data when it is in `shared_preload_libraries`. First check whether it is already there:

   ```sql theme={null}
   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:

   ```hcl theme={null}
   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:

   ```sql theme={null}
   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.

The extension costs little: each statement updates one entry in a shared-memory hash (`pg_stat_statements.max`, default 5000 entries). Query text is normalized: literals become `$1`, `$2`, so the view holds query shapes, not customer values.

## Read it

`api-events/scripts/perf/pgStatStatements.ts` prints the top statements for the current database. It only reads. It connects with `default_transaction_read_only=on`, runs inside `BEGIN READ ONLY`, rolls back, and never calls `pg_stat_statements_reset()`.

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

| Flag | Default | Meaning |
| - | - | - |
| `--limit N` | `20` | How many statements to show |
| `--order total\|mean` | `total` | `total` finds what uses the most database time overall; `mean` finds the slowest single calls |
| `--min-calls N` | `1` | Hide statements called fewer than N times (use with `--order mean` to skip one-off admin queries) |
| `--width N` | `160` | Truncate the one-line query text |

Output columns:

| Column | Meaning |
| - | - |
| `calls` | Executions since the last stats reset |
| `total_ms` / `mean_ms` / `max_ms` | Execution time (planning excluded) |
| `%shown` | Share of `total_ms` among the rows shown |
| `rows` | Rows returned or affected, summed over all calls |
| `hit%` | Shared-buffer cache hit ratio. Under \~95% on a hot query means it reads from disk, usually a missing index or a scan over a large table |

The script exits `2` with setup steps when the extension is missing, and `1` on any other error.

Use a read-only database role for this, not the admin login. The `pg_read_all_stats` role is enough to see every user's statements.

> [!WARNING]
>
> Do not point it at production from a laptop unless you mean to. Get the connection string from Key Vault (`prod-cus-core-kv`), prefer a read-only role, and never paste it into logs, tickets, or PRs.

## 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:

   ```sql theme={null}
   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](https://docs.soundchecklive.io/engineering/backend/database.md)).

The stats add up from the last reset or server restart. To measure one change, note the numbers before deploying and compare after, instead of resetting shared stats.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.