feat(cnpg): Custom monitoring queries (pg_stat_statements)

[Starefossen]

Context

With the CNPG PodMonitor and PrometheusRule alerts now in place, the built-in CNPG metrics exporter provides baseline PostgreSQL observability. However, application teams may benefit from additional custom queries.

Proposal

Add support for custom monitoring queries via CNPG's CustomQueriesConfigMap mechanism. This would expose metrics like:

• pg_stat_statements — Top queries by total time, calls, rows
• pg_stat_user_tables — Table-level stats (seq scans, index scans, dead tuples)
• Connection pool metrics — PgBouncer stats from the Pooler

Design Questions

• Should custom queries be configurable per-cluster via the Postgres CR, or should there be a standard set applied to all CNPG clusters?
• If configurable, what's the right API surface? A list of named query sets (e.g. monitoring.queries: [pg_stat_statements, table_stats])?
• Should we provide a default ConfigMap with sensible queries that all clusters get?

Implementation Notes

• CNPG supports Spec.Monitoring.CustomQueriesConfigMap — a list of ConfigMap references containing query definitions
• The ConfigMap format follows the cnpg-collector query format
• Consider whether to create the ConfigMap per-cluster or share a single ConfigMap across all clusters in a namespace

Related

• PodMonitor + alerts implemented in the cnpg-observability branch