Connection management

CloudBase PostgreSQL provides two connection entry points: connect directly to the database instance, or connect through the platform connection pool. They differ in how many connections are consumed and in session semantics, so choosing the wrong one can exhaust connections or break expected behavior.

Before choosing, determine which scenario applies. Use a direct connection when the number of clients is controllable and session state matters. Use the pool when there are many clients with short-lived, request-dense connections.

Direct connections and pools

The two entry points:

• Direct connection: the client connects to the database instance over the PostgreSQL protocol, with no intermediary.
• Pooled connection: the client connects to the platform connection pool, which in turn connects to the database instance. The pool reuses its connections to the instance, so many clients can share a small number of real connections.

The difference is the connection target, not the application code. The console provides the corresponding endpoint and port, and the direct and pooled addresses differ. Use the values shown in the console.

Aspect	Direct connection	Pooled connection
Connection target	Database instance	Platform connection pool
Real connections on the instance	One per client connection	Determined by the pool's connections to the instance
Session continuity	Same session while the connection is held	Not guaranteed across requests
Bound by the instance connection limit	Directly bound	Client count decoupled from instance connections
Typical scenarios	Migration, operations, long transactions, session-dependent features	Web APIs, Cloud Functions, high-concurrency short requests

In code, usually only the endpoint changes:

import pg from "pg";

// Direct connection
const direct = new pg.Pool({
  host: process.env.PG_DIRECT_HOST,
  port: Number(process.env.PG_DIRECT_PORT),
  database: process.env.PGDATABASE,
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
});

// Pooled connection
const pooled = new pg.Pool({
  host: process.env.PG_POOL_HOST,
  port: Number(process.env.PG_POOL_PORT),
  database: process.env.PGDATABASE,
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
});

When to connect directly

Operations that depend on session state. Prepared statements, session-level variables, temporary tables, cursors, and advisory locks are all bound to the current session. A direct connection is held for its lifetime, so this state persists across statements.

Long transactions and bulk processing. Data migration, historical backfills, and bulk import or export need explicit control over transaction boundaries and run for a long time, so a direct connection makes progress and rollback easier to manage.

Operations and diagnostics. Troubleshooting with psql or the console data management tool requires seeing the real connections and execution plans on the instance, which only a direct connection reflects accurately.

Long-running services with a controllable client count. When the number of service instances is fixed and each opens only a few connections, the quota consumed by direct connections is predictable.

Keep in mind that direct connections consume the instance connection quota, and the limit is determined by the instance specification. When the number of clients is not controllable, or the service scales elastically, use the pool instead.

When to use a connection pool

The pool reuses connections on the server side, decoupling the client count from the real connection count on the instance.

• Many or uncontrollable clients. Web APIs, collaboration tools, and public gateways see client connections grow with traffic, and direct connections quickly approach the instance limit.
• Short-lived runtime environments such as Cloud Functions. Instances are created and destroyed repeatedly, and each setup consumes quota. The pool smooths out this fluctuation.
• Request-dense traffic with short per-request duration. Query-dominant, fast-returning requests benefit most from connection reuse.
• Reserving headroom for traffic spikes. The pool acts as a buffer so peaks do not hit the instance directly.

When using the pool, keep in mind:

• Session continuity is not guaranteed. Session-level state may not carry over to the next request. Refer to the connection guidance shown in the console for the exact behavior.
• All statements in a transaction must use the same client connection, otherwise the transaction boundary breaks.

Client pools and server-side pools

The pool described above is a server-side pool and acts as a connection entry point. An application can also maintain a client-side pool. The two solve problems at different levels:

Level	Purpose	Scope
Server-side pool	Reuses connections to the instance, lowering the real connection count	The instance connection quota
Client-side pool	Reuses client connections to the target endpoint, reducing setup cost	Connections within a single application process

They can be combined: connect through the pool while maintaining pg.Pool in the application. Either layer can also be used alone.

Reuse connections

The following applies to the client-side pool. In Node.js, define the 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

This section refers to the client-side pool. Bigger is not always better. Estimate the total client connections by deployment scale:

total client 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 open 100 client connections.

With a direct connection, these connections consume the instance connection quota directly and must be checked against the instance specification. With a pooled connection, the server-side pool absorbs them and they do not translate one-to-one into instance connections, but an oversized client pool still causes request queuing.

Release connections

Clients manually acquired from the client-side 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. With a direct connection you see client connections; with a pooled connection you see the pool's connections to the instance. The two counts mean different things.

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 the client pool size and idle timeout.
• Queued queries: check slow queries, lock waits, and pool limits.
• Intermittent connection failures: check the network, the instance connection limit, and service scaling.
• Instance connections saturate with few clients: check whether many short-lived connections bypass the pool and connect directly.
• Prepared statement errors or lost session state: the current entry point may not preserve sessions across requests. Consider a direct connection.