Database Performance Optimization

Connection Pool Lifecycle Flow
 
Application Request
        │
        ▼
┌───────────────────┐
│  Pool.getConnection()  │
└───────┬───────────┘
        │
        ▼
   ┌─────────────┐      No       ┌─────────────────┐
   │ Idle conn   ├───────────────► Pool at max?    │
   │ available?  │               └────────┬────────┘
   └──────┬──────┘                        │
       Yes│                               │ Yes
          │                               ▼
          │                    ┌─────────────────────┐
          │                    │  Wait with timeout  │
          │                    │  (blocks thread)    │
          │                    └─────────┬───────────┘
          │                              │
          │                              │ Timeout?
          │              ┌───────────────┴──────────────┐
          │              │ No                     Yes   │
          │              ▼                              ▼
          │    (conn returns from           ┌───────────────────┐
          │         active set)             │ Throw Exception   │
          │              │                  │ (Connection pool  │
          │              │                  │  exhausted)       │
          ▼              ▼                  └───────────────────┘
    ┌─────────────────────┐
    │ Connection returned │
    │ to application      │
    └─────────┬───────────┘
              │
              ▼
    Application uses connection
              │
              ▼
    ┌─────────────────────┐
    │ Pool.releaseConnection() │
    └─────────┬───────────┘
              │
              ▼
    ┌─────────────────────┐
    │ Validate connection │
    │ (test-on-return)    │
    └─────────┬───────────┘
              │
     Valid?───┴────Invalid?
        │              │
        ▼              ▼
    Return to      Destroy and
    idle pool      create new

Pool Size vs Throughput (empirical data pattern)
 
Transactions/sec
    │
500 │                    ●●●●●●
    │                ●●●       ●●●
400 │            ●●●             ●●●
    │          ●●                   ●●●
300 │        ●●                        ●●●
    │      ●●                             ●●●
200 │    ●●                                  ●●●
    │  ●●      ← Optimal range →                ●●●
100 │●●                                            ●●●
    │
    └────┬────┬────┬────┬────┬────┬────┬────┬────┬────►
         5   10   15   20   25   30   35   40   45   50  Pool Size
 
Key observations:
- Throughput increases rapidly up to optimal point
- Plateau between 15-25 connections (the "sweet spot")  
- Beyond 30 connections, throughput DECREASES
- At 50 connections, often worse than 10 connections
 
This is counterintuitive but demonstrates:
- CPU context switching overhead
- Lock contention in database
- Memory pressure reducing cache efficiency

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

// Node.js: Read/Write split with separate pools
import { Pool } from 'pg';
 
// Writer pool - connects to primary
const writerPool = new Pool({
  host: 'db-primary.internal',
  max: 10,  // Fewer writers needed
});
 
// Reader pool - connects to read replicas
const readerPool = new Pool({
  host: 'db-replica.internal',
  max: 30,  // More readers for read-heavy workload
});
 
type PoolType = 'read' | 'write';
 
function getPool(type: PoolType): Pool {
  return type === 'write' ? writerPool : readerPool;
}
 
// Repository pattern with split pools
class UserRepository {
  async findById(id: string) {
    // Reads go to replica
    const pool = getPool('read');
    const result = await pool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0];
  }
 
  async findByIdForUpdate(id: string) {
    // Read-for-update must go to primary (to avoid replication lag issues)
    const pool = getPool('write');
    const result = await pool.query(
      'SELECT * FROM users WHERE id = $1 FOR UPDATE',
      [id]
    );
    return result.rows[0];
  }
 
  async create(user: User) {
    // Writes go to primary
    const pool = getPool('write');
    const result = await pool.query(
      `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *`,
      [user.name, user.email]
    );
    return result.rows[0];
  }
 
  async updateBalance(id: string, amount: number) {
    const pool = getPool('write');
    await pool.query(
      'UPDATE users SET balance = balance + $1 WHERE id = $2',
      [amount, id]
    );
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Check current connection count
SELECT count(*) FROM pg_stat_activity;
 
-- Check max_connections setting
SHOW max_connections;
 
-- See all connections with details
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    state_change,
    query_start,
    query
FROM pg_stat_activity
WHERE datname = 'myapp'
ORDER BY state_change DESC;
 
-- Find idle connections (potential leaks)
SELECT 
    pid,
    usename,
    application_name,
    state,
    EXTRACT(EPOCH FROM (now() - state_change)) as idle_seconds
FROM pg_stat_activity
WHERE state = 'idle'
  AND datname = 'myapp'
ORDER BY idle_seconds DESC;
 
-- Connections by state
SELECT state, count(*) 
FROM pg_stat_activity 
WHERE datname = 'myapp'
GROUP BY state;
 
-- Connections by application
SELECT application_name, count(*) 
FROM pg_stat_activity 
WHERE datname = 'myapp'
GROUP BY application_name
ORDER BY count DESC;
 
-- Kill idle connections older than 1 hour (emergency)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '1 hour'
  AND pid <> pg_backend_pid();