PostgreSQL — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— PostgreSQL Fundamentals
📋Pool
⚙️Transaction
🔄Query
✅Release
🎯 Next: Test →
Step 1 / 4
Speed
Connection Pool
Pool manages 10 reusable connections — avoids connection overhead per query💡 Analogy
Like a taxi fleet — cabs wait at the stand instead of driving from home every time.
Creating a new database connection takes ~50-100ms (TCP + auth). A pool keeps connections open and reuses them — query overhead drops to <1ms.
Console Output
> Pool created: max=10 connections
> Initial: 0 connections (lazy)
> First query: 1 connection opened and cached
> Query 11: waits for free connection
●postgresql.ts
Connection PoolL3–7
1import { Pool } from 'pg';
2 
3const pool = new Pool({
4  connectionString: process.env.DATABASE_URL,
5  max: 10,               // connection pool size
6  idleTimeoutMillis: 30000,
7});
8 
9// Parameterized query — prevents SQL injection
10async function getUserOrders(userId: string) {
11  const client = await pool.connect();
12  try {
13    await client.query('BEGIN');
14 
15    const { rows: user } = await client.query(
16      'SELECT id, name, email FROM users WHERE id = $1',
17      [userId]
18    );
19 
20    const { rows: orders } = await client.query(
21      `SELECT o.id, o.total, o.created_at,
22              json_agg(oi) AS items
23       FROM orders o
24       JOIN order_items oi ON oi.order_id = o.id
25       WHERE o.user_id = $1
26       GROUP BY o.id
27       ORDER BY o.created_at DESC`,
28      [userId]
29    );
30 
31    await client.query('COMMIT');
32    return { user: user[0], orders };
33  } catch (err) {
34    await client.query('ROLLBACK');
35    throw err;
36  } finally {
37    client.release();    // always return to pool
38  }
39}
39 lines
Timeline — 4 Steps
Connection Pool
Pool manages 10 reusable connections — avoids connection overhead per query💡 Analogy
Like a taxi fleet — cabs wait at the stand instead of driving from home every time.
Creating a new database connection takes ~50-100ms (TCP + auth). A pool keeps connections open and reuses them — query overhead drops to <1ms.
Console Output
> Pool created: max=10 connections
> Initial: 0 connections (lazy)
> First query: 1 connection opened and cached
> Query 11: waits for free connection

Was this page helpful?

Discussion

Pool manages 10 reusable connections — avoids connection overhead per query

💡 Analogy

Like a taxi fleet — cabs wait at the stand instead of driving from home every time.

Creating a new database connection takes ~50-100ms (TCP + auth). A pool keeps connections open and reuses them — query overhead drops to <1ms.

Console Output

> Pool created: max=10 connections
> Initial: 0 connections (lazy)
> First query: 1 connection opened and cached
> Query 11: waits for free connection

Pool manages 10 reusable connections — avoids connection overhead per query

💡 Analogy

Like a taxi fleet — cabs wait at the stand instead of driving from home every time.

Creating a new database connection takes ~50-100ms (TCP + auth). A pool keeps connections open and reuses them — query overhead drops to <1ms.

Console Output

> Pool created: max=10 connections
> Initial: 0 connections (lazy)
> First query: 1 connection opened and cached
> Query 11: waits for free connection