Performance Insights

Real-time query performance monitoring using PostgreSQL's pg_stat_statements extension. See which queries are slow, monitor cache efficiency, and identify lock contention.

Enable pg_stat_statements

Include it in the provisioning request parameters:

curl -X POST http://localhost:24005/api/provision \
  -H "Content-Type: application/json" \
  -d '{
    "projectName": "my-db",
    "orgId": "myorg",
    "databaseType": "POSTGRESQL",
    "tier": "FREE",
    "parameters": {
      "shared_preload_libraries": "pg_stat_statements",
      "pg_stat_statements.max": "10000",
      "pg_stat_statements.track": "all"
    }
  }'

This adds pg_stat_statements to the CNPG Cluster's shared_preload_libraries array field (not parameters — CNPG requires the dedicated field).

Then create the extension:

curl -X POST http://localhost:24005/api/provision/my-db/performance/enable

Performance Summary

curl http://localhost:24005/api/provision/my-db/performance/summary

{
  "available": true,
  "activeConnections": 3,
  "totalConnections": 9,
  "cacheHitRatio": 99.83,
  "databaseSize": "7548 kB",
  "slowQueryCount": 0,
  "avgQueryTimeMs": 1.03
}

Metric	Source	What it means
`cacheHitRatio`	`pg_stat_database`	Percentage of reads served from shared buffers (>99% is good)
`activeConnections`	`pg_stat_activity`	Queries currently executing
`totalConnections`	`pg_stat_activity`	All connections including idle
`slowQueryCount`	`pg_stat_statements`	Queries with avg execution > 1 second
`databaseSize`	`pg_database_size()`	Total size on disk

Top Queries

curl "http://localhost:24005/api/provision/my-db/performance/top-queries?limit=10"

[
  {
    "query": "SELECT * FROM users WHERE email = $1",
    "calls": 1523,
    "totalExecTimeMs": 4521.3,
    "avgExecTimeMs": 2.97,
    "minExecTimeMs": 0.1,
    "maxExecTimeMs": 45.2,
    "rows": 1523
  }
]

Ordered by totalExecTimeMs descending — the queries consuming the most total time appear first.

Wait Events

curl http://localhost:24005/api/provision/my-db/performance/wait-events

[
  { "waitEventType": "IO", "waitEvent": "DataFileRead", "count": 5 },
  { "waitEventType": "Lock", "waitEvent": "relation", "count": 2 }
]

Wait events help identify:

IO waits — database hitting disk instead of cache (increase shared_buffers)
Lock waits — queries blocking each other (review transaction patterns)
LWLock — internal PostgreSQL contention (usually transient)

When Unavailable

If pg_stat_statements is not enabled, the summary returns:

{
  "available": false,
  "unavailableReason": "pg_stat_statements extension is not enabled"
}

The dashboard shows a gate UI with an "Enable" button that triggers the extension creation and cluster restart.

How It Works

All queries are executed via client-go remotecommand — running psql inside the primary pod:

Go Server → client-go remotecommand.StreamWithContext()
  → Pod exec: psql -U postgres -t -A -c "SELECT ... FROM pg_stat_statements"
  → Parse pipe-delimited output
  → Return as JSON

No external tools or sidecar containers needed.