How to Monitor Postgrex with Vigilmon

Postgrex is the pure Elixir PostgreSQL driver that powers Ecto's PostgreSQL adapter. It manages connection pools, handles prepared statements, and negotiates the PostgreSQL wire protocol. Most Elixir developers interact with Postgrex indirectly through Ecto — but its health is fundamental to your application's health.

When Postgrex can't connect to Postgres, your entire data layer stalls. When the connection pool is exhausted, queries queue silently. When a reconnect loop misfires, the pool appears healthy while all connections are in a dead state.

External monitoring catches what your app's internal state can't see. This tutorial covers:

• A health endpoint that validates Postgrex connectivity
• HTTP uptime monitoring with Vigilmon
• Pool health and query latency monitoring
• Slack alerts for connection failures

Step 1: Set up Postgrex (direct usage)

Most Elixir apps use Postgrex through Ecto. If you're using it directly:

# mix.exs
defp deps do
  [
    {:postgrex, "~> 0.18"},
    {:db_connection, "~> 2.7"}
  ]
end

Start a connection pool in your supervision tree:

# lib/my_app/application.ex
def start(_type, _args) do
  db_config = [
    hostname: System.get_env("DB_HOST", "localhost"),
    username: System.get_env("DB_USER", "postgres"),
    password: System.get_env("DB_PASS"),
    database: System.get_env("DB_NAME", "my_app_prod"),
    port: String.to_integer(System.get_env("DB_PORT", "5432")),
    pool_size: 10,
    queue_target: 50,    # ms before considering the pool overloaded
    queue_interval: 1000 # ms window for measuring overload
  ]

  children = [
    {Postgrex, db_config ++ [name: :db]},
    MyAppWeb.Endpoint
  ]

  Supervisor.start_link(children, strategy: :one_for_one)
end

Step 2: Build a health endpoint that validates Postgrex

The key insight: a health check should test the full round-trip through Postgrex to the database, not just whether the pool process is alive.

# lib/my_app_web/plugs/health_check.ex
defmodule MyAppWeb.Plugs.HealthCheck do
  import Plug.Conn

  def init(opts), do: opts

  def call(%Plug.Conn{request_path: "/health"} = conn, _opts) do
    checks = run_checks()
    status = if all_ok?(checks), do: 200, else: 503

    conn
    |> put_resp_content_type("application/json")
    |> send_resp(status, Jason.encode!(checks))
    |> halt()
  end

  def call(conn, _opts), do: conn

  defp run_checks do
    db = database_check()
    %{
      status: if(db.status == "ok", do: "ok", else: "degraded"),
      database: db
    }
  end

  defp database_check do
    start = System.monotonic_time(:millisecond)

    result =
      try do
        case Postgrex.query(:db, "SELECT 1 AS probe, version() AS version", [], timeout: 5_000) do
          {:ok, %{rows: [[1, version]]}} ->
            elapsed = System.monotonic_time(:millisecond) - start
            pg_version = version |> String.split(" ") |> List.first()
            %{
              status: "ok",
              latency_ms: elapsed,
              postgres_version: pg_version,
              pool: pool_info()
            }

          {:ok, other} ->
            %{status: "error", reason: "unexpected result: #{inspect(other)}"}

          {:error, %DBConnection.ConnectionError{} = e} ->
            %{status: "error", reason: "connection error: #{Exception.message(e)}"}

          {:error, reason} ->
            %{status: "error", reason: inspect(reason)}
        end
      catch
        :exit, {:timeout, _} ->
          %{status: "error", reason: "query timeout after 5000ms"}
        kind, reason ->
          %{status: "error", reason: "#{kind}: #{inspect(reason)}"}
      end

    result
  end

  defp pool_info do
    # DBConnection pool stats — available for inspection
    case DBConnection.ConnectionPool.get_connection_metrics(:db) do
      {:ok, %{ready: ready, checkout: checkout}} ->
        %{ready: ready, checked_out: checkout}
      _ ->
        %{}
    end
  rescue
    _ -> %{}
  end

  defp all_ok?(checks), do: checks.status == "ok"
end

For Ecto users, the pattern is the same but simpler:

defp database_check do
  start = System.monotonic_time(:millisecond)

  case Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1", [], timeout: 5_000) do
    {:ok, _} ->
      elapsed = System.monotonic_time(:millisecond) - start
      %{status: "ok", latency_ms: elapsed}
    {:error, %DBConnection.ConnectionError{} = e} ->
      %{status: "error", reason: "connection error: #{Exception.message(e)}"}
    {:error, reason} ->
      %{status: "error", reason: inspect(reason)}
  end
end

Register the plug before your router:

# lib/my_app_web/endpoint.ex
plug MyAppWeb.Plugs.HealthCheck
plug MyAppWeb.Router

Test it:

curl http://localhost:4000/health | jq .
# {
#   "status": "ok",
#   "database": {
#     "status": "ok",
#     "latency_ms": 2,
#     "postgres_version": "PostgreSQL",
#     "pool": {"ready": 9, "checked_out": 1}
#   }
# }

The latency_ms field in the response is surfaced by Vigilmon's response time history. Gradual latency increases often predict connection pool exhaustion days before it causes actual failures.

Step 3: Monitor with Vigilmon

1. Sign up at vigilmon.online
2. Click New Monitor → HTTP
3. Enter https://yourdomain.com/health
4. Set check interval: 1 minute
5. Add a keyword check: body must contain "status":"ok"
6. Set a response time alert: alert if response time exceeds 2 seconds (database latency early warning)
7. Save

The response time alert is particularly valuable for Postgrex monitoring. PostgreSQL query times creep up before they cause timeouts. A 2-second alert on a health check that normally responds in 10ms gives you early warning of connection pool pressure, slow queries, or database host issues.

Step 4: Monitor connection pool exhaustion

Pool exhaustion is a common and dangerous failure mode. When all connections are checked out, new queries queue. When the queue fills, DBConnection.ConnectionError starts appearing. By the time you see errors, users have already been waiting.

Add pool depth monitoring to your health check and a separate alert threshold:

defp database_check do
  case Postgrex.query(:db, "SELECT count(*) FROM pg_stat_activity WHERE datname = $1",
    [System.get_env("DB_NAME", "my_app_prod")], timeout: 5_000) do
    {:ok, %{rows: [[active_connections]]}} ->
      # Compare active connections to your configured pool_size
      pool_size = Application.get_env(:my_app, :db_pool_size, 10)
      utilization_pct = round(active_connections / pool_size * 100)

      status = cond do
        utilization_pct >= 90 -> "warning"
        true -> "ok"
      end

      %{
        status: status,
        active_connections: active_connections,
        pool_size: pool_size,
        utilization_pct: utilization_pct
      }

    {:error, reason} ->
      %{status: "error", reason: inspect(reason)}
  end
end

In Vigilmon, add a keyword alert for "status":"warning" on your health monitor. You'll get notified when pool utilization exceeds 90% — before the first timeout fires.

Step 5: Heartbeat monitoring for critical database workflows

A health check validates connectivity. A heartbeat validates that your critical database workflows are completing end-to-end. Add heartbeats for any workflow where silent failure has business impact:

# lib/my_app/db_heartbeat.ex
defmodule MyApp.DbHeartbeat do
  use GenServer

  require Logger

  @interval :timer.minutes(5)

  def start_link(_), do: GenServer.start_link(__MODULE__, %{}, name: __MODULE__)

  def init(state) do
    schedule()
    {:ok, state}
  end

  def handle_info(:ping, state) do
    case run_probe_transaction() do
      :ok ->
        ping_vigilmon()
      {:error, reason} ->
        Logger.error("DB heartbeat probe failed: #{inspect(reason)}")
    end

    schedule()
    {:noreply, state}
  end

  defp run_probe_transaction do
    # A lightweight write-and-delete cycle to verify write path
    MyApp.Repo.transaction(fn ->
      timestamp = DateTime.utc_now()
      result = MyApp.Repo.query!(
        "INSERT INTO health_probes (inserted_at) VALUES ($1) RETURNING id",
        [timestamp]
      )
      [[probe_id]] = result.rows
      MyApp.Repo.query!("DELETE FROM health_probes WHERE id = $1", [probe_id])
      :ok
    end)
    |> case do
      {:ok, :ok} -> :ok
      {:error, reason} -> {:error, reason}
    end
  end

  defp ping_vigilmon do
    url = Application.get_env(:my_app, :vigilmon)[:db_heartbeat_url]
    if url, do: Req.get(url, receive_timeout: 5_000)
  end

  defp schedule, do: Process.send_after(self(), :ping, @interval)
end

Create the probe table in a migration:

# priv/repo/migrations/YYYYMMDDHHMMSS_create_health_probes.exs
defmodule MyApp.Repo.Migrations.CreateHealthProbes do
  use Ecto.Migration

  def change do
    create table(:health_probes) do
      add :inserted_at, :utc_datetime_usec, null: false
    end
  end
end

In Vigilmon, create a Heartbeat Monitor:

1. Click New Monitor → Heartbeat
2. Set expected interval: 10 minutes
3. Copy the ping URL and set it as VIGILMON_DB_HEARTBEAT_URL

This catches the subtle failure where reads succeed (the pool is partially healthy) but writes fail due to replication lag, disk full, or WAL issues.

Step 6: Slack alerts and status page

Slack alerts:

1. In Vigilmon, go to Notifications → New Channel → Slack
2. Paste your Slack webhook URL
3. Enable it on your health monitor, pool utilization check, and heartbeat monitor

When the database is unreachable:

🔴 DOWN: yourdomain.com/health
Status: 503 Service Unavailable
Detected: EU-West, US-East
1 minute ago

When pool utilization spikes:

⚠️ ALERT: yourdomain.com/health
Keyword "status":"warning" detected
Pool utilization: 92%

Status page:

1. Go to Status Pages → New Status Page
2. Add all your monitors
3. Share the URL with your team

Engineers checking the status page during an incident can see immediately whether it's the database layer causing the problem.

What you've built

| What | How | |------|-----| | Database health probe | SELECT 1 with latency measurement | | Connection pool monitoring | pg_stat_activity count vs. pool_size | | HTTP uptime monitoring | Vigilmon HTTP monitor + keyword check | | Response time alerting | Vigilmon 2s threshold alert | | Write path heartbeat | Transaction-based probe + heartbeat monitor | | Slack alerts | Vigilmon Slack notification channel | | Status page | Vigilmon public status page |

Postgrex keeps your data flowing. Vigilmon tells you when it stops.

Next steps

• Add separate Vigilmon monitors for read replicas if you use them for query offloading
• Use Vigilmon's response time history to build a baseline — alert when latency deviates more than 3× the baseline
• Monitor pg_stat_replication lag in your health endpoint if you have standbys

Get started free at vigilmon.online.