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.

03-pgvector-hnsw