Diagnostics

autopg ships with an optional web dashboard for finding the queries that are costing you. It reads from pg_stat_statements and the system catalogs, so there is nothing to instrument in your application. It is off by default, because it runs an extra web server inside the container and ships without authentication of its own. You should turn it on deliberately and keep it behind a tunnel or your own auth rather than exposing it on every production box.

What it shows

The dashboard pulls together the things you would otherwise dig out of the catalogs by hand:

Sequential scans. Tables being read end to end, the usual sign of a missing index.
Index usage. Which indexes are pulling their weight and which are never hit.
Time per query. Average and aggregate time spent, so you can see where the database actually spends its day.
Running queries. What is executing right now.
Index sizes. How much disk each index costs.

For each slow query it generates a ready-to-run EXPLAIN ANALYZE so you can reproduce the plan in seconds instead of reconstructing the statement yourself.

Enabling the dashboard

Set AUTOPG_ENABLE_WEBAPP=true and expose the port. The dashboard is a small FastAPI app served from inside the container.

services:
  postgres:
    image: ghcr.io/piercefreeman/autopg:pg18-latest
    environment:
      AUTOPG_ENABLE_WEBAPP: "true"
      AUTOPG_WEBAPP_HOST: 0.0.0.0
      AUTOPG_WEBAPP_PORT: 8000
      AUTOPG_DB_PASSWORD: changeme
    ports:
      - 5432:5432
      - 8000:8000

The dashboard relies on pg_stat_statements, which autopg enables by default. If you turned it off, turn it back on for diagnostics to have data to read.

Connection settings

The dashboard connects back to Postgres to read its stats. By default it targets the local instance, but every part of the connection is configurable.

Variable	Default	Description
`AUTOPG_ENABLE_WEBAPP`	`false`	Turn the dashboard on.
`AUTOPG_WEBAPP_HOST`	`127.0.0.1`	Bind address for the dashboard.
`AUTOPG_WEBAPP_PORT`	`8000`	Port the dashboard listens on.
`AUTOPG_DB_HOST`	`localhost`	Postgres host to read stats from.
`AUTOPG_DB_PORT`	`5432`	Postgres port.
`AUTOPG_DB_NAME`	`postgres`	Database to inspect.
`AUTOPG_DB_USER`	`postgres`	User the dashboard connects as.
`AUTOPG_DB_PASSWORD`	unset	Password for that user.

Bind the dashboard to 127.0.0.1 and reach it over an SSH tunnel, or put it behind your own auth before exposing it publicly.

Reading the data

Start with sequential scans on large tables. Those are almost always the fastest wins. Cross-reference the index usage view to confirm an index would help and that you are not paying to maintain indexes nobody queries. Then take the generated EXPLAIN ANALYZE for your worst queries and confirm the plan changes once the index is in place.