Logs and monitoring

Logs and monitoring help diagnose connection failures, slow queries, lock waits, permission errors, and resource bottlenecks. The exact CloudBase PostgreSQL monitoring entry depends on the console.

Metrics to watch

Metric	Description
CPU / memory	Indicates compute or memory pressure
Connections	Shows whether connection pool settings are reasonable
QPS / TPS	Shows traffic changes and write pressure
Slow queries	Identifies SQL that needs optimization
Lock waits	Shows whether transactions block other requests
Storage	Tracks data and index growth

View current connections

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

View running SQL:

select pid, usename, state, now() - query_start as duration, query
from pg_stat_activity
where state <> 'idle'
order by duration desc
limit 20;

View lock waits

select
  blocked.pid as blocked_pid,
  blocking.pid as blocking_pid,
  blocked.query as blocked_query,
  blocking.query as blocking_query
from pg_stat_activity blocked
join pg_locks blocked_locks on blocked_locks.pid = blocked.pid
join pg_locks blocking_locks
  on blocking_locks.locktype = blocked_locks.locktype
 and blocking_locks.database is not distinct from blocked_locks.database
 and blocking_locks.relation is not distinct from blocked_locks.relation
 and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
 and blocking_locks.pid <> blocked_locks.pid
join pg_stat_activity blocking on blocking.pid = blocking_locks.pid
where not blocked_locks.granted;

If lock waits happen often, check long transactions, bulk updates, and DDL operations.

Slow query diagnostics

Slow queries are often caused by missing indexes, large result sets, expensive sorting, complex RLS policies, or lock waits.

Suggested flow:

1. Record the slow SQL and parameters.
2. Use EXPLAIN to inspect the plan.
3. Check indexes, statistics, and filters.
4. Validate optimized SQL in staging.
5. Observe latency and resource changes after launch.

For optimization methods, see Query optimization.

Business logs

Database logs only describe SQL-level behavior. Business systems should also log request ID, user ID, key parameters, database latency, and error codes so frontend errors, backend logs, and database behavior can be correlated.

Do not log passwords, full tokens, ID numbers, or other sensitive information.