tutorial

Monitoring PostgREST with Vigilmon

PostgREST turns your PostgreSQL schema into a REST API automatically — but a schema cache miss or pool exhaustion silently degrades all API consumers. Here's how to monitor PostgREST end-to-end with Vigilmon.

PostgREST is the Haskell web server that generates a fully RESTful API directly from your PostgreSQL schema — no API code required. It powers Supabase's core REST layer and is used by teams who want to expose PostgreSQL data via HTTP without writing and maintaining custom endpoints. The tradeoff is that PostgREST's simplicity hides subtle failure modes: a stale schema cache serves old endpoints after DDL changes, pool exhaustion queues all requests silently, and authentication failures spike when JWT keys rotate. Vigilmon monitors PostgREST's own health, database connectivity, request throughput, latency, and security posture continuously.

What You'll Set Up

  • PostgREST server health monitor
  • PostgreSQL connectivity check
  • API request throughput and latency monitor
  • Authentication failure rate alert
  • Schema cache staleness detector
  • PostgreSQL connection pool utilization check
  • Row Level Security enforcement monitor
  • API error rate per endpoint

Prerequisites

  • PostgREST 12.x running as a binary or Docker container (default port 3000)
  • PostgreSQL accessible from your monitoring host
  • psql available on the monitoring host
  • A free Vigilmon account

Step 1: Monitor PostgREST Server Health

PostgREST responds to GET / with the OpenAPI specification when healthy. An HTTP 200 response confirms the server is running and connected to PostgreSQL.

  1. Log in to vigilmon.online and click Add Monitor.
  2. Set Type to HTTP / HTTPS.
  3. Enter: http://localhost:3000/.
  4. Set Expected HTTP status to 200.
  5. Under Keyword check, add "openapi" to verify the spec is actually returned.
  6. Set Check interval to 1 minute.
  7. Click Save.

For a more targeted health probe, call a known lightweight view or table:

curl -s http://localhost:3000/health_check \
  -H "Accept: application/json"

Create a health_check view in PostgreSQL:

-- Run as your PostgREST schema owner
CREATE VIEW health_check AS
  SELECT 1 AS status, now() AS checked_at;

-- Grant access to the anon role PostgREST uses
GRANT SELECT ON health_check TO anon;

Then add a Vigilmon monitor targeting http://localhost:3000/health_check with a keyword check for "status":1.

Step 2: Monitor PostgreSQL Connectivity

PostgREST maintains a persistent connection to PostgreSQL. If the connection drops, every API request returns a 503. This is the highest-priority failure mode.

cat > /usr/local/bin/check-postgrest-db.sh << 'EOF'
#!/bin/bash
PGHOST=${PGHOST:-localhost}
PGPORT=${PGPORT:-5432}
PGDATABASE=${PGDATABASE:-postgres}
PGUSER=${PGUSER:-authenticator}

START=$(date +%s%N)
RESULT=$(psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" \
  -c "SELECT 1" -t 2>&1)
END=$(date +%s%N)

if echo "$RESULT" | grep -q "1"; then
  LATENCY_MS=$(( (END - START) / 1000000 ))
  if [ "$LATENCY_MS" -gt 500 ]; then
    echo "WARNING: PostgreSQL query latency ${LATENCY_MS}ms"
    exit 1
  fi
  echo "OK: PostgreSQL healthy, latency ${LATENCY_MS}ms"
  exit 0
fi
echo "CRITICAL: PostgreSQL connection failed"
exit 1
EOF
chmod +x /usr/local/bin/check-postgrest-db.sh

Serve this via an HTTP exporter and add a Vigilmon Keyword check for OK:.

Step 3: Monitor API Request Throughput and Latency

PostgREST exposes Prometheus metrics at /metrics when configured with server-timing-enabled = true. Use these to track request rates and latency.

# Check PostgREST metrics endpoint
curl -s http://localhost:3000/metrics | grep postgrest
# pgrst_db_pool_available{} 5
# pgrst_db_pool_max{} 10
# pgrst_schema_cache_loads_total{} 3

For p95 latency monitoring, parse the nginx or Caddy access log in front of PostgREST:

cat > /usr/local/bin/check-postgrest-latency.sh << 'EOF'
#!/bin/bash
LOG_FILE=${ACCESS_LOG:-/var/log/nginx/postgrest-access.log}
THRESHOLD_MS=${LATENCY_THRESHOLD_MS:-500}

if [ ! -f "$LOG_FILE" ]; then
  echo "OK: no access log found (new deployment)"
  exit 0
fi

# Extract response times from last 5 minutes of requests (nginx $request_time in seconds)
P95=$(tail -n 1000 "$LOG_FILE" \
  | awk '{print $NF}' \
  | sort -n \
  | awk 'BEGIN{c=0} {lines[c++]=$0} END{print lines[int(c*0.95)]}')

P95_MS=$(echo "$P95 * 1000" | bc | cut -d. -f1 2>/dev/null)
P95_MS=${P95_MS:-0}

if [ "$P95_MS" -gt "$THRESHOLD_MS" ]; then
  echo "WARNING: p95 latency ${P95_MS}ms exceeds ${THRESHOLD_MS}ms"
  exit 1
fi
echo "OK: p95 latency ${P95_MS}ms"
exit 0
EOF
chmod +x /usr/local/bin/check-postgrest-latency.sh

Step 4: Monitor Authentication Failure Rate

PostgREST validates JWTs for authenticated requests. A spike in 401 responses indicates misconfigured clients or a JWT key rotation that wasn't propagated to all consumers.

cat > /usr/local/bin/check-postgrest-auth.sh << 'EOF'
#!/bin/bash
LOG_FILE=${ACCESS_LOG:-/var/log/nginx/postgrest-access.log}
WINDOW_MINUTES=${WINDOW:-5}
THRESHOLD_RATE=${THRESHOLD:-10}

# Count 401 responses in the last N minutes
RECENT_TOTAL=$(tail -n 2000 "$LOG_FILE" | wc -l)
RECENT_401=$(tail -n 2000 "$LOG_FILE" | awk '$9 == "401"' | wc -l)

if [ "${RECENT_TOTAL:-0}" -eq 0 ]; then
  echo "OK: no recent requests"
  exit 0
fi

RATE=$(( RECENT_401 * 100 / RECENT_TOTAL ))
if [ "$RATE" -gt "$THRESHOLD_RATE" ]; then
  echo "WARNING: ${RATE}% of recent requests returned 401 (${RECENT_401}/${RECENT_TOTAL})"
  exit 1
fi
echo "OK: 401 rate ${RATE}% (${RECENT_401}/${RECENT_TOTAL})"
exit 0
EOF
chmod +x /usr/local/bin/check-postgrest-auth.sh

A 401 rate above 10% of requests is a strong signal that JWT key rotation happened without updating clients.

Step 5: Detect Schema Cache Staleness

PostgREST caches the PostgreSQL schema in memory. After DDL changes (new columns, new tables, changed RLS policies), the cache must be refreshed via NOTIFY pgrst, 'reload schema' or a server restart. A stale cache means the API serves outdated endpoints.

cat > /usr/local/bin/check-postgrest-schema.sh << 'EOF'
#!/bin/bash
PGHOST=${PGHOST:-localhost}
PGDATABASE=${PGDATABASE:-postgres}
PGUSER=${PGUSER:-authenticator}

# Check how many times the schema cache has been loaded
# from PostgREST /metrics endpoint
CACHE_LOADS=$(curl -s http://localhost:3000/metrics 2>/dev/null \
  | grep 'pgrst_schema_cache_loads_total' | awk '{print $2}')

CACHE_LOADS=${CACHE_LOADS:-0}
CACHE_FILE=/tmp/postgrest-schema-loads

if [ -f "$CACHE_FILE" ]; then
  PREV=$(cat "$CACHE_FILE")
  if [ "$CACHE_LOADS" -eq "$PREV" ]; then
    # Check if any DDL changes happened since last reload
    DDL_CHANGES=$(psql -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE" -t \
      -c "SELECT count(*) FROM pg_stat_activity WHERE query ILIKE '%ALTER TABLE%' OR query ILIKE '%CREATE TABLE%'" 2>/dev/null | tr -d ' ')
    # This is a simplified proxy check — in production, track DDL via event triggers
  fi
fi

echo "$CACHE_LOADS" > "$CACHE_FILE"
echo "OK: schema cache loaded ${CACHE_LOADS} times"
exit 0
EOF
chmod +x /usr/local/bin/check-postgrest-schema.sh

For a more robust approach, set up a PostgreSQL event trigger that logs DDL changes to a table, then check whether PostgREST reloaded its schema after each DDL event:

-- Track DDL events
CREATE TABLE IF NOT EXISTS _monitoring.ddl_events (
  id SERIAL PRIMARY KEY,
  event_time TIMESTAMPTZ DEFAULT now(),
  command_tag TEXT,
  schema_name TEXT
);

CREATE OR REPLACE FUNCTION _monitoring.log_ddl()
RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO _monitoring.ddl_events (command_tag, schema_name)
  VALUES (tg_tag, tg_schema);
END;
$$;

CREATE EVENT TRIGGER log_ddl_trigger ON ddl_command_end
  EXECUTE FUNCTION _monitoring.log_ddl();

Step 6: Monitor PostgreSQL Connection Pool Utilization

PostgREST's connection pool handles concurrent API requests. Pool exhaustion causes new requests to queue and eventually return 503 errors.

cat > /usr/local/bin/check-postgrest-pool.sh << 'EOF'
#!/bin/bash
# Read pool stats from PostgREST /metrics
METRICS=$(curl -s http://localhost:3000/metrics 2>/dev/null)
AVAILABLE=$(echo "$METRICS" | grep 'pgrst_db_pool_available' | awk '{print $2}')
MAX=$(echo "$METRICS" | grep 'pgrst_db_pool_max' | awk '{print $2}')

AVAILABLE=${AVAILABLE:-0}
MAX=${MAX:-10}

if [ "$MAX" -eq 0 ]; then
  echo "OK: could not read pool size"
  exit 0
fi

IN_USE=$(( MAX - AVAILABLE ))
UTILIZATION=$(( IN_USE * 100 / MAX ))

if [ "$UTILIZATION" -gt 80 ]; then
  echo "WARNING: pool ${UTILIZATION}% utilized (${IN_USE}/${MAX} connections in use)"
  exit 1
fi
echo "OK: pool ${UTILIZATION}% utilized (${IN_USE}/${MAX})"
exit 0
EOF
chmod +x /usr/local/bin/check-postgrest-pool.sh

Step 7: Verify Row Level Security Is Enabled

PostgREST relies entirely on PostgreSQL RLS for row-level authorization. If RLS is accidentally disabled on a sensitive table, all rows become accessible to the anon role.

cat > /usr/local/bin/check-postgrest-rls.sh << 'EOF'
#!/bin/bash
PGHOST=${PGHOST:-localhost}
PGDATABASE=${PGDATABASE:-postgres}
PGUSER=${PGUSER:-postgres}  # Must be superuser or table owner

# List tables that should have RLS enabled (configure this list)
PROTECTED_TABLES=${PROTECTED_TABLES:-"users,orders,invoices,payments"}

MISSING_RLS=$(psql -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE" -t << SQL
SELECT string_agg(tablename, ', ')
FROM pg_tables
WHERE tablename = ANY(string_to_array('${PROTECTED_TABLES}', ','))
  AND schemaname = 'public'
  AND NOT rowsecurity;
SQL
)

MISSING_RLS=$(echo "$MISSING_RLS" | tr -d ' \n')

if [ -n "$MISSING_RLS" ]; then
  echo "CRITICAL: RLS disabled on sensitive tables: ${MISSING_RLS}"
  exit 1
fi
echo "OK: RLS enabled on all protected tables"
exit 0
EOF
chmod +x /usr/local/bin/check-postgrest-rls.sh

Set PROTECTED_TABLES to a comma-separated list of tables that must always have RLS enabled. This is a security-critical check — a failed RLS policy can leak data to any API consumer.

Step 8: Set Up the Monitoring HTTP Exporter and Heartbeat

Combine all checks into one HTTP exporter:

cat > /etc/systemd/system/postgrest-monitor.service << 'EOF'
[Unit]
Description=PostgREST monitoring HTTP exporter
After=network.target

[Service]
ExecStart=/usr/bin/socat TCP-LISTEN:9970,fork,reuseaddr \
  EXEC:"/usr/local/bin/postgrest-all-checks.sh"
Restart=always

[Install]
WantedBy=multi-user.target
EOF

Create a combined check script at /usr/local/bin/postgrest-all-checks.sh:

#!/bin/bash
DB=$(/usr/local/bin/check-postgrest-db.sh 2>&1)
POOL=$(/usr/local/bin/check-postgrest-pool.sh 2>&1)
AUTH=$(/usr/local/bin/check-postgrest-auth.sh 2>&1)
RLS=$(/usr/local/bin/check-postgrest-rls.sh 2>&1)

if echo "$DB$POOL$AUTH$RLS" | grep -q "CRITICAL\|WARNING"; then
  STATUS="DEGRADED"
else
  STATUS="OK"
fi

printf "HTTP/1.1 200 OK\r\nContent-Type: text/plain\r\n\r\n"
echo "STATUS: $STATUS"
echo "DB: $DB"
echo "POOL: $POOL"
echo "AUTH: $AUTH"
echo "RLS: $RLS"

Add a Vigilmon monitor with Keyword check for STATUS: OK.

Step 9: Configure Alert Channels

  1. Go to Alert Channels in Vigilmon and connect your notification system.
  2. Set alert thresholds:
    • Server health: 2 consecutive failures (brief restart grace)
    • PostgreSQL connectivity: 1 failure (DB down = all API down)
    • RLS disabled: 1 failure (security-critical, alert immediately)
    • Auth failure rate: 3 failures (wait for sustained spike, not a single bad request)
    • Pool utilization: 3 failures (allow burst traffic to resolve naturally)
  3. Use a Maintenance window during PostgREST schema reload operations.

Summary

| Monitor | Target | Alert Condition | |---|---|---| | Server health | GET /health_check keyword check | HTTP error or keyword missing | | PostgreSQL connectivity | DB query latency check | Connection failure or latency > 500ms | | Request throughput | Access log analysis | Throughput drop > 50% from baseline | | p95 API latency | Access log p95 | Latency > 500ms | | Auth failure rate | 401 rate in access log | 401 rate > 10% of requests | | Schema cache | Cache load count | Stale cache after DDL event | | Connection pool | /metrics pool utilization | Pool > 80% utilized | | RLS enforcement | Table RLS status | RLS disabled on protected tables |

PostgREST's zero-code API generation is powerful, but it shifts the complexity from application code into PostgreSQL itself — and PostgREST's health is only as good as its database connectivity, schema freshness, and RLS policies. With Vigilmon monitoring every layer from the HTTP health endpoint through pool utilization to RLS enforcement, you catch degradation before your API consumers do.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles