Multi-pool orchestration

One app, many PostgreSQL pools (one per domain DB).

1. Why split

• Domain isolation
• Separate roles
• Different backup cadences
• Coexistence with external SaaS (Supabase CLI on its own port)

2. Pool singletons

import { Pool } from "pg";

function requireEnv(name: string) {
  const v = process.env[name];
  if (!v) throw new Error(`env ${name} missing`);
  return v;
}

export const blogPool = new Pool({
  host: requireEnv("BLOG_DB_HOST"),
  port: Number(process.env.BLOG_DB_PORT ?? 5432),
  database: requireEnv("BLOG_DB_NAME"),
  user: requireEnv("BLOG_DB_USER"),
  password: requireEnv("BLOG_DB_PASSWORD"),
  max: 10,
});

3. Thin helpers

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

4. Transaction wrapper

export async function withPoolClient<T>(pool: Pool, fn: (c: any) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const r = await fn(client);
    await client.query("COMMIT");
    return r;
  } catch (e) {
    await client.query("ROLLBACK"); throw e;
  } finally { client.release(); }
}

5. SSL config

function sslConfig(mode?: string, conn?: string) {
  if (conn?.includes(".supabase.")) return { rejectUnauthorized: false };
  if (mode === "require") return { rejectUnauthorized: true };
  return false;
}

6. Graceful shutdown

const pools = [blogPool, marketPool, cachePool];
process.once("SIGTERM", async () => {
  await Promise.all(pools.map(p => p.end()));
  process.exit(0);
});

7. Routing

/api/blog/*   → blogPool
/api/market/* → marketPool
/api/search   → cross-domain

8. Cross-cutting pool

Audit logs, FCM tokens, sessions — keep in one pool.

9. Tuning with pg_stat_activity

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

• All idle → lower max
• Many active → find slow queries
• idle in transaction → leak

10. Gotchas

• Typed env vars → requireEnv catches
• max too high → PG max_connections exceeded
• Long transactions → separate admin pool
• Scripts without pool.end() hang

Closing

Start with "pool count = domain count + 1–2 cross-cutting". Split further only after real bottlenecks.

Next

• 03-pgvector-hnsw