الـ Connection Pooling و PgBouncer: connections أقل، requests أكتر

import pg from "pg";

const { rows } = await new pg.Pool({ connectionString: process.env.DATABASE_URL }).query("SHOW max_connections");
console.log("max_connections:", rows[0].max_connections);

// 6 نسخ من الـ app، كل واحدة pool بـ max 20 = 120 connection
const instances = Array.from({ length: 6 }, () => new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 20 }));
const queries = instances.flatMap((pool) =>
  Array.from({ length: 20 }, () => pool.query("SELECT pg_sleep(0.3)")),
);
const results = await Promise.allSettled(queries);
const failed = results.filter((r) => r.status === "rejected");
console.log("نجح:", results.length - failed.length, "| فشل:", failed.length);
console.log("أول error:", failed[0]?.reason.message);
await Promise.all(instances.map((p) => p.end()));

$ node main.js
max_connections: 100
نجح: 96 | فشل: 24
أول error: remaining connection slots are reserved for roles with the SUPERUSER attribute

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 2,
  connectionTimeoutMillis: 500, // ما تستناش connection أكتر من كده
  idleTimeoutMillis: 10000, // اقفل اللي فاضية بعد 10 تواني
});

const slow = Array.from({ length: 5 }, (_, i) =>
  pool.query("SELECT pg_sleep(0.4), $1::int AS i", [i]).then(() => `#${i} ✓`, (e) => `#${i} ✗ ${e.message}`),
);
await new Promise((r) => setTimeout(r, 50));
console.log({ total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount });
console.log(await Promise.all(slow));
await pool.end();

$ node main.js
{ total: 2, idle: 0, waiting: 3 }
[
  '#0 ✓',
  '#1 ✓',
  '#2 ✓',
  '#3 ✓',
  '#4 ✗ timeout exceeded when trying to connect'
]

import pg from "pg";

// نفس الـ 6 نسخ، بس بيكلموا PgBouncer على 6432
const url = "postgres://app@localhost:6432/app";
const instances = Array.from({ length: 6 }, () => new pg.Pool({ connectionString: url, max: 20 }));
const results = await Promise.allSettled(
  instances.flatMap((pool) => Array.from({ length: 20 }, () => pool.query("SELECT pg_sleep(0.3)"))),
);
console.log("نجح:", results.filter((r) => r.status === "fulfilled").length);

// ⚠️ transaction mode: SET بيتنسي بين الـ queries (كل query ممكن تروح لـ server connection مختلفة)
const c = new pg.Client(url);
await c.connect();
await c.query("SET statement_timeout = 1234");
const busy = await Promise.all(instances.map((p) => p.query("SELECT pg_sleep(0.05)")));
console.log("statement_timeout:", (await c.query("SHOW statement_timeout")).rows[0].statement_timeout);
await c.query("BEGIN");
await c.query("SET LOCAL statement_timeout = 1234");
console.log("SET LOCAL جُوّه transaction:", (await c.query("SHOW statement_timeout")).rows[0].statement_timeout);
await c.query("COMMIT");
await c.end();
await Promise.all(instances.map((p) => p.end()));

$ bash bouncer.sh
bouncer.sh: line 3: pgbouncer: command not found
نجح: 0
node:internal/modules/run_main:107
    triggerUncaughtException(
    ^

Error: connect ECONNREFUSED 127.0.0.1:6432
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1645:16) {
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 6432
}

Node.js v24.14.1
psql: error: connection to server at "localhost" (127.0.0.1), port 6432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
cat: pgbouncer.pid: No such file or directory
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]

import pg from "pg";

// 3 workers، كل واحد بيعمل transactions كتير على PgBouncer
const url = "postgres://app@localhost:6432/app";


const pool = new pg.Pool({ connectionString: url, max: 30 });
await pool.query("DROP TABLE IF EXISTS accounts; CREATE TABLE accounts (id int PRIMARY KEY, balance int); INSERT INTO accounts VALUES (1, 100000), (2, 100000);");

let peak = 0;
async function transfer(amount) {
  const c = await pool.connect();
  try {
    await c.query("BEGIN");
    await c.query("SET LOCAL statement_timeout = 2000");
    await c.query("UPDATE accounts SET balance = balance - $1 WHERE id = 1", [amount]);
    await c.query("UPDATE accounts SET balance = balance + $1 WHERE id = 2", [amount]);
    const { rows } = await c.query(
      "SELECT count(*)::int AS n FROM pg_stat_activity WHERE usename = 'app' AND backend_type = 'client backend'",
    );
    peak = Math.max(peak, rows[0].n);
    await c.query("COMMIT");
  } catch (err) {
    await c.query("ROLLBACK");
    throw err;
  } finally {
    c.release();
  }
}

const results = await Promise.allSettled(Array.from({ length: 300 }, () => transfer(1)));
console.log("نجح:", results.filter((r) => r.status === "fulfilled").length);
const { rows } = await pool.query("SELECT id, balance FROM accounts ORDER BY id");
console.log(rows.map((r) => `${r.id}=${r.balance}`).join(" "));
console.log("server connections <=", peak);
await pool.end();

$ bash bouncer.sh
bouncer.sh: line 3: pgbouncer: command not found
/home/you/my-backend/node_modules/pg-pool/index.js:45
    Error.captureStackTrace(err)
          ^

Error: connect ECONNREFUSED 127.0.0.1:6432
    at /home/you/my-backend/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:104:5)
    at async file:///home/you/my-backend/main.js:8:1 {
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 6432
}

Node.js v24.14.1
psql: error: connection to server at "localhost" (127.0.0.1), port 6432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
cat: pgbouncer.pid: No such file or directory
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]

[databases]
app = host=localhost port=5432 dbname=app

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = trust
auth_file = userlist.txt
admin_users = app
pool_mode = transaction
default_pool_size = 10
max_client_conn = 1000
max_prepared_statements = 100
logfile = pgbouncer.log
pidfile = pgbouncer.pid

"app" ""

#!/bin/bash
# شغّل PgBouncer في الخلفية، وبعدين الـ app، وبعدين اسأله عن الـ pools، وفي الآخر اقفله
pgbouncer -d pgbouncer.ini && sleep 0.5
node main.js
psql -h localhost -p 6432 -U app pgbouncer -At -c 'SHOW POOLS' |
  awk -F'|' '$1 == "app" { print "pool app: servers idle=" $10 ", active=" $7 ", mode=" $16 }'
kill $(cat pgbouncer.pid)