Connection management

Each PostgreSQL connection consumes database resources. In elastic environments such as Cloud Functions and CloudBase Run, poor connection management can exhaust connections or queue requests.

Reuse connections

In Node.js, define the connection pool at module scope so the same runtime instance can reuse connections.

import pg from "pg";

export const pool = new pg.Pool({
  host: process.env.PGHOST,
  port: Number(process.env.PGPORT || 5432),
  database: process.env.PGDATABASE,
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
  max: 5,
});

Do not create a new pool inside every request.

Pool size

Bigger pools are not always better. Estimate total connections by deployment scale:

total connections = pool max per instance x maximum instance count

If a Cloud Function can scale to 20 instances and each instance has max=5, it may consume 100 database connections.

Release connections

Clients manually acquired from the pool must be released.

const client = await pool.connect();

try {
  const result = await client.query("select now()");
  return result.rows;
} finally {
  client.release();
}

For simple queries, use pool.query() so the pool borrows and returns connections automatically.

Timeout settings

Set reasonable timeouts so slow queries do not occupy connections for too long.

set statement_timeout = '5s';

You can also set request timeouts and retry policies in the application. Retries are suitable only for idempotent operations. For writes, confirm whether retries may duplicate data.

Monitor connections

Use pg_stat_activity to inspect current connections.

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

Find long-running queries:

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

Common issues

• Connections keep growing: check whether each request creates a new pool or manual clients are not released.
• Many idle connections: reduce pool size and idle timeout.
• Queued queries: check slow queries, lock waits, and pool limits.
• Intermittent connection failures: check network, database connection limits, and service scaling.