PgHero

A performance dashboard for Postgres, as a Phoenix library or a Docker image.

This is an Elixir port of ankane/pghero 4.x.

Docker

docker build -t pghero .
docker run --rm -ti -e DATABASE_URL=postgres://user:password@hostname:5432/dbname -p 8080:8080 pghero

Then visit http://localhost:8080. See guides/Docker.md for auth, host-machine databases, and stats capture.

Without Docker:

DATABASE_URL=postgres://user:pass@localhost/dbname mix pghero.server

Installation (Phoenix)

Add to mix.exs:

def deps do
  [
    {:pghero, "~> 0.1.0"}
  ]
end

Point it at your Ecto repo in config/config.exs:

config :pghero, repo: MyApp.Repo

Mount it in router.ex behind your own authentication:

import PgHeroWeb.Router

scope "/" do
  pipe_through [:browser, :require_admin]
  pghero "/pghero"
end

Then open /pghero.

Dummy app

A host Phoenix app lives in dummy/ so you can see the mount without wiring your own project:

cd dummy
docker compose up -d
mix setup
mix phx.server

Then open http://localhost:4000/pghero.

Authentication

PgHero does not authenticate by itself when you mount it in a Phoenix pipeline. Put it behind your admin plug, as in the example above.

Optional HTTP basic auth (in addition to, or instead of, your pipeline):

config :pghero,
  repo: MyApp.Repo,
  username: "link",
  password: "hyrule"

Or pass credentials at mount time:

pghero "/pghero", username: "link", password: "hyrule"

Do not expose this dashboard on the public internet without auth. It can show query text and kill backends.

Multiple databases

config :pghero,
  databases: [
    primary: [repo: MyApp.Repo],
    analytics: [url: System.get_env("ANALYTICS_DATABASE_URL"), name: "Analytics"]
  ]

Each entry accepts :repo, :url, and :name.

What you get

Same dashboard as the Rails engine:

Overview (connections, vacuums, sequences, invalid indexes, slow queries)
Queries (pg_stat_statements)
Space
Connections
Live queries (with kill)
Maintenance
Explain
Tune

Requires PostgreSQL 14+.

Query stats

Enable pg_stat_statements in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Restart Postgres, then enable the extension from the Overview page (or CREATE EXTENSION pg_stat_statements).

Historical stats

Create the tables with an Ecto migration:

defmodule MyApp.Repo.Migrations.CreatePgheroStats do
  use Ecto.Migration

  def up, do: PgHero.Migrations.up()
  def down, do: PgHero.Migrations.down()
end

Capture on a schedule (Oban, Quantum, or cron):

PgHero.capture_query_stats()  # every 5 minutes
PgHero.capture_space_stats()  # daily

Or mix tasks:

mix pghero.capture_query_stats
mix pghero.capture_space_stats

Configuration

config :pghero,
  repo: MyApp.Repo,
  long_running_query_sec: 60,
  slow_query_ms: 20,
  slow_query_calls: 100,
  total_connections_threshold: 500,
  explain: true,          # true | false | "analyze"
  disable_kill: false,
  username: nil,
  password: nil

Environment variables from the original project still work (PGHERO_USERNAME, PGHERO_PASSWORD, PGHERO_DATABASE_URL, and the threshold vars).

Permissions

Use a dedicated Postgres role. See guides/Permissions.md.

Not in this port yet

Suggested indexes (pg_query)
AWS RDS / GCP Cloud SQL system charts
Query text filtering via pg_query

Development

mix deps.get
mix test
just all-tests

just all-tests starts Postgres (via dummy/docker-compose.yml), creates pghero_test, and runs unit plus integration tests.

License

MIT. Original work by Andrew Kane; Elixir/Phoenix port of the same dashboard.

Next Page → Changelog