Technologies/PostgreSQL/postgresql.backends

PostgreSQLMetric

postgresql.backends

Active backends

Dimensions:None

Available on:

Datadog (1)Native (1)

OpenTelemetry (1)

Prometheus (1)

Interface Metrics (4)

Datadog

postgresql.connections

The number of active connections to this database.

Dimensions:None

Native

numbackends

Number of backends currently connected to this database

Dimensions:None

OpenTelemetry

postgresql.backends

The number of backends.

Dimensions:None

Prometheus

stat_database_numbackends

Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset.

Dimensions:None

Sources

postgresql.connectionsgithub.com

numbackendsgithub.com

postgresql.backendsgithub.com

stat_database_numbackendsgithub.com

Technical Annotations (62)

Configuration Parameters (9)

pool_moderecommended: transaction

Returns connection after each transaction, recommended for most use cases

default_pool_sizerecommended: 20

Actual database connections per database/user pair

max_client_connrecommended: 1000

Maximum application connections to pooler

query_timeoutrecommended: 30

Kill queries running longer than 30 seconds to prevent connection starvation

max_connectionsrecommended: 200 (or 100 with pooler)

each connection uses 5-10MB RAM before work_mem

superuser_reserved_connectionsrecommended: 3

reserve connections for admin access during exhaustion

portrecommended: 5432

default port for PostgreSQL connections, must match client configuration

autovacuum_max_workersrecommended: 3

Default concurrent autovacuum processes; can only be set at server start

autovacuum_naptimerecommended: 1min

Minimum delay between autovacuum runs per database; balance with worker availability

Error Signatures (7)

LWLock:lock_managerlog pattern

LWLock:LockManagerlog pattern

Too Many Connectionserror code

FATAL: sorry, too many clients alreadylog pattern

could not connect to server: No such file or directorylog pattern

Is the server running locally and accepting connectionslog pattern

FATAL: remaining connection slots are reserved for non-replication superuser connectionslog pattern

CLI Commands (22)

pgbench -c 100 -j 10 -n -f transaction.sql -T 900diagnostic

SELECT count(*) as total_connections, count(*) filter (where state = 'active') as active, count(*) filter (where state = 'idle') as idle, count(*) filter (where state = 'idle in transaction') as idle_in_transaction FROM pg_stat_activity;

diagnostic

SELECT count(*) FROM pg_stat_activity;diagnostic

SELECT setting FROM pg_settings WHERE name = 'max_connections';diagnostic

SELECT * FROM pg_stat_activitydiagnostic

sudo service postgresql-12 statusdiagnostic

sudo service postgresql-12 status | grep portdiagnostic

SELECT count(*), state FROM pg_stat_activity GROUP BY state;diagnostic

SELECT pid, usename, application_name, state, query_start, state_change FROM pg_stat_activity WHERE state = 'idle';diagnostic

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND state_change < NOW() - INTERVAL '1 hour';

remediation

psmonitoring

topmonitoring

iostatmonitoring

vmstatmonitoring

SELECT state, COUNT(*) AS connection_count, MAX(EXTRACT(EPOCH FROM (now() - state_change))) AS max_age_seconds FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY state;

diagnostic

SELECT max_conn, used, max_conn - used AS available, ROUND((used::float / max_conn) * 100, 2) AS usage_percent FROM ( SELECT (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn, (SELECT COUNT(*) FROM pg_stat_activity) AS used ) AS conn_stats;

diagnostic

SELECT count(*) AS active_autovacuum_workers FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';diagnostic

SELECT pid, query, now() - query_start AS duration FROM pg_stat_activity WHERE query LIKE 'autovacuum:%' ORDER BY query_start;

diagnostic

gcloud sql instances patch MY_INSTANCE --database-flags=autovacuum_max_workers=6remediation

SELECT pid, datname, relid::regclass AS table_name, phase, heap_blks_total, heap_blks_scanned, ROUND(heap_blks_scanned::numeric / NULLIF(heap_blks_total, 0) * 100, 1) AS pct_complete, index_vacuum_count FROM pg_stat_progress_vacuum;

monitoring

gcloud sql instances describe INSTANCE_NAME --format='get(settings.tier)'Get machine type to calculate vCPU count and actual connection limit

SELECT COUNT(*) as current_connections, setting::int as max_configured FROM pg_stat_activity, pg_settings WHERE pg_settings.name = 'max_connections' GROUP BY setting;

Compare current connections to configured max connections (may not reflect Cloud SQL vCPU limit)

Technical References (24)

CloudWatch Database Insightscomponentaverage active sessionsconceptpgbenchcomponentPgBouncercomponent/etc/pgbouncer/pgbouncer.inifile pathconnection poolingconceptmax_connectionscomponentpgBouncercomponentpg_stat_activitycomponentpg_terminate_backend()component/var/run/postgresql/.s.PGSQL.5432file pathUnix domain socketprotocolpostgresql.conffile pathPgpool-IIcomponenttransaction modeconceptpgpool-IIcomponentpg_stat_replicationcomponentpg_stat_databasecomponentpg_stat_iocomponentEXPLAINcomponentcumulative statistics systemconceptpg_stat_activity_countcomponentautovacuum workerscomponentpg_stat_progress_vacuumcomponent

Related Insights (16)

Lock manager contention reduces throughput by up to 34 percent under high concurrencywarning

▸

Connection limit exhaustion causes memory pressure and context-switching overheadcritical

▸

Excessive max_connections consumes memory and requires connection poolingwarning

▸

High connection count approaching limits degrades monitoring performancewarning

▸

Connection exhaustion prevents new client connectionscritical

Each PostgreSQL connection consumes 5-10MB RAM via fork model. At 200 connections: 1-2GB RAM overhead before queries run. At 500+ connections: excessive context-switching dominates query execution time. Database becomes unresponsive under concurrent load.

▸

PostgreSQL server process not running causes connection refusalcritical

▸

Connection slots exhausted preventing new client connectionscritical

▸

Connection spikes without pooling exhaust backend workerscritical

▸

Connection limit approaching maximum causes application timeoutscritical

▸

Connection failure when requests exceed default 100 connection limitcritical

▸

Connection exhaustion blocks new client connectionscritical

▸

Statistics collection views provide core monitoring data for PostgreSQL operationsinfo

▸

Connection exhaustion prevents new client connectionscritical

▸

High connection count triggers alert thresholdwarning

▸

Autovacuum worker starvation delays vacuum on high-churn tableswarning

▸

Cloud SQL per-vCPU connection limits silently override PostgreSQL max_connectionscritical

Cloud SQL enforces hard connection limits based on instance vCPU count (25 connections per vCPU) that override PostgreSQL's configured max_connections parameter. Applications hitting this limit receive 'FATAL: sorry, too many clients already' errors even when PostgreSQL metrics show connections well below max_connections.

▸