Orchestrating multiple PostgreSQL pools

An admin console or back office may need one process to access separate content, catalog, and operations databases. Direct pools can be useful, but connection budgets, permissions, and failure isolation must be designed together.

1. Why split pools

• Domain isolation — a catalog backup should not block content editing
• Separate permissions — database roles map to domain roles
• Different capacity and backup cadence — a collection DB may run daily while content runs weekly
• Managed database coexistence — an external pooler and a local container can have different lifecycles

Schemas such as content and catalog may be enough. Split pools when container, backup, or permission boundaries also need to be independent.

2. Singleton pools — node-postgres

import { Pool } from 'pg';

export const contentPool = new Pool({
  host: process.env.CONTENT_DB_HOST!,
  port: Number(process.env.CONTENT_DB_PORT ?? 5432),
  database: process.env.CONTENT_DB_NAME!,
  user: process.env.CONTENT_DB_USER!,
  password: process.env.CONTENT_DB_PASSWORD!,
  ssl: sslConfig(process.env.CONTENT_DB_SSL_MODE),
  max: 10,
});

export const catalogPool = new Pool({ /* CATALOG_DB_* */ });
export const operationsPool = new Pool({ /* OPERATIONS_DB_* */ });

Domain-prefixed variables make .env readable. Create one pool per process and check that the sum of all max values stays below PostgreSQL's connection budget.

3. Thin query helpers

export async function queryContent<T>(
  sql: string,
  params: unknown[] = [],
): Promise<T[]> {
  const { rows } = await contentPool.query<T>(sql, params);
  return rows;
}

export async function queryOneContent<T>(
  sql: string,
  params: unknown[] = [],
): Promise<T | null> {
  return (await queryContent<T>(sql, params))[0] ?? null;
}

The function name reveals the selected database, while one boundary can enforce parameters and result types.

4. Transactions — connect() + try/finally

const client = await contentPool.connect();
try {
  await client.query('BEGIN');
  const { rows } = await client.query(
    'INSERT INTO posts (...) VALUES (...) RETURNING id',
    [...],
  );
  await client.query('COMMIT');
  return rows[0].id;
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

Missing release() starves a pool. A withPoolClient() helper and a transaction timeout reduce both mistakes and long-held connections.

5. SSL and shutdown

• Local Docker traffic may disable SSL according to the environment
• Cloud connections should verify a CA and default to rejectUnauthorized: true
• External pooler certificate policy belongs to its provider-specific adapter

process.on('SIGTERM', async () => {
  await Promise.all([contentPool, catalogPool, operationsPool].map((pool) => pool.end()));
  process.exit(0);
});

6. Routing rules

• /api/content/** → contentPool
• /api/catalog/** → catalogPool
• /api/operations/** → operationsPool
• Audit logs and sessions → one fixed operations pool

Keep the path-to-pool table explicit. A workflow spanning pools is not a distributed transaction: use an outbox, compensation, and a retry state instead.

7. Gotchas

Typoed environment variable — use requireEnv() so a missing host fails immediately instead of silently becoming localhost.

Pool total too high — max: 10 per pool multiplies by replica count. Compare replicas × Σmax + maintenance headroom with the database limit.

Long transactions — isolate batch work in a worker or small dedicated pool so list requests do not wait behind it.

Scripts that never exit — call await pool.end() at the end of one-off commands.

Closing

Multiple pools cost more operational attention than one database. Use them only when domain backup, permission, or failure boundaries justify the cost, and write down the connection budget and recovery path first.

Next

• postgres-first
• postgres-deep
• backend/09-audit-log-pattern

References: node-postgres · PostgreSQL connection management.