Sign in

A Comprehensive Guide to Database Pooling with PostgreSQL, TypeORM, and pgBouncer

Rajkumar

Rajkumar

— 6 min read
A Comprehensive Guide to Database Pooling with PostgreSQL, TypeORM, and pgBouncer

In modern backend applications, efficient database connection management is critical for performance, stability, and scalability. One of the core solutions to these challenges is database pooling the practice of reusing a limited set of established database connections instead of creating a new one for each request.

This guide covers:

  1. Limitations of Direct Database Connections
  2. How Database Pooling Works
  3. Why Use Database Pooling?
  4. Database Pooling with an ORM (TypeORM)
  5. TypeORM’s Built-In Connection Pooling
  6. Setting Up Pooling in TypeORM
  7. pgBouncer: An External Connection Pooler for PostgreSQL
  8. Production-Ready Tips & Best Practices
  9. Conclusion

By the end, you’ll understand how to integrate database pooling using PostgreSQL, TypeORM (including its built-in pool capabilities), and pgBouncer for large-scale environments.

1. Limitations of Direct Database Connections

1.1 High Overhead and Resource Usage

  • One connection per request: Spinning up a new database connection for every incoming request causes a lot of overhead.
  • Database constraints: Databases like PostgreSQL have a finite limit on how many concurrent connections they can handle efficiently.

1.2 Scalability Roadblocks

  • In containerized or microservices environments, multiple application instances opening new connections in parallel can max out the database’s connection limit quickly.

1.3 Idle Connection Wastage

  • Establishing and tearing down connections repeatedly wastes CPU time, memory, and network resources both on the client (application) and on the database server.

2. How Database Pooling Works (and Why It’s Like a Queue)

Database pooling functions much like a queue of ready-to-use connections:

  1. Initialization: The application starts up and creates a pool of pre-established connections.
  2. Borrow from the Pool: When your code needs to run a query, it requests (borrows) a connection from this pool.
  3. Execute Query: The query executes on that borrowed connection.
  4. Return to the Pool: After the query is done, the connection is released back, making it available for the next request.

Because each borrowed connection eventually returns to the pool, the pool ensures you never exceed the configured maximum number of active connections, helping avoid hitting the database’s max_connections limit.

3. Why Use Database Pooling?

  1. Performance Gains: Reusing existing connections avoids the overhead of re-establishing them repeatedly.
  2. Resource Efficiency: A limited or managed number of connections prevents overwhelming the database.
  3. Scalability: It’s easier to handle traffic spikes if connections are already established.
  4. Predictability: By setting minimum and maximum connections, you have more predictable resource usage.

4. Database Pooling with an ORM (TypeORM)

TypeORM is a popular Node.js ORM that makes it simpler to interact with a database using entities and repositories rather than raw SQL.

  • Automatic connection management: Instead of explicitly connecting to the database and releasing each connection, TypeORM handles these details behind the scenes.
  • Pooling: By default, TypeORM can leverage the underlying database driver’s pooling mechanism (e.g., the pg package for PostgreSQL).

4.1 Common Features in ORMs (Like TypeORM)

  • Entities: Classes that map to database tables.
  • Repositories: High-level APIs to query and manipulate entity records.
  • Transactions: A way to ensure multiple queries either all succeed or all roll back.
  • Migrations: Version-controlled schema changes.

When configured correctly, TypeORM will maintain a single pool of connections in the background, automatically acquiring and releasing connections for each query.

5. TypeORM’s Built-In Connection Pooling

While many developers talk about using the Node.js pg library directly (e.g., Pool from pg), TypeORM already utilizes these pooling features under the hood.

5.1 How Does TypeORM Provide Pooling?

  • When you set up a DataSource (or a Connection in older TypeORM versions), you can pass pooling options to the underlying driver through the extra property (for PostgreSQL).
  • At runtime, TypeORM initializes a single connection pool using these settings.
  • Every repository method (find(), save(), delete(), etc.) acquires a connection from this pool, runs the query, then automatically releases it back to the pool. You never have to manually call client.release()—TypeORM does it for you.

5.2 Why Built-In Pooling is Sufficient in Many Cases

  • Easy Setup: You don’t have to manually instantiate or manage a separate pool object.
  • Automatic Release: Prevents common pitfalls like forgetting to release a connection, which can lead to connection leaks.
  • Centralized Control: You configure your pool size, timeouts, and other options in one place (the DataSource configuration).

6. Setting Up Pooling in TypeORM

Let’s walk through a practical example using TypeORM 0.3+ with PostgreSQL. We’ll assume your project is set up with TypeScript, Node.js, and the necessary dependencies:

bashCopyEditnpm install typeorm pg

6.1 Example Data Source Configuration

// data-source.ts
import 'reflect-metadata';
import { DataSource } from 'typeorm';

export const AppDataSource = new DataSource({
  type: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: Number(process.env.DB_PORT) || 5432,
  username: process.env.DB_USER || 'myuser',
  password: process.env.DB_PASS || 'mypassword',
  database: process.env.DB_NAME || 'mydatabase',
  entities: [__dirname + '/entities/*.ts'],
  synchronize: false, // never use true in production
  logging: false,

  // Pass pooling options to the underlying driver:
  extra: {
    max: 10,                  // Maximum number of connections
    idleTimeoutMillis: 30000, // Close idle connections after 30s
    connectionTimeoutMillis: 2000, // Error if no connection in 2s
  },
});

AppDataSource.initialize()
  .then(() => console.log('Data Source has been initialized!'))
  .catch((err) => console.error('Error initializing Data Source', err));

In this example:

  • max = 10 indicates the maximum connections allowed in the pool.
  • idleTimeoutMillis = 30000 closes any idle connection after 30 seconds.
  • connectionTimeoutMillis = 2000 fails if the pool can’t provide a connection within 2 seconds.

6.2 Using the Pool in Your Code

Once initialized, you can obtain repositories (or the entity manager) and perform queries:

// user.service.ts
import { AppDataSource } from './data-source';
import { User } from './entities/user.entity';

export async function getUserById(userId: number) {
  const userRepository = AppDataSource.getRepository(User);
  return await userRepository.findOne({ where: { id: userId } });
}

export async function createUser(data: Partial<User>) {
  const userRepository = AppDataSource.getRepository(User);
  const newUser = userRepository.create(data);
  return await userRepository.save(newUser);
}

TypeORM takes care of:

  1. Acquiring a connection from the pool.
  2. Running the query.
  3. Releasing the connection back to the pool once the query finishes.

7. pgBouncer: An External Connection Pooler for PostgreSQL

While TypeORM or the Node.js pg library provides pooling, pgBouncer is a lightweight, standalone connection pooler for PostgreSQL.

7.1 Why Use pgBouncer?

  • Multiple Services: If many microservices connect to the same database, centralizing pooling at pgBouncer can be more efficient.
  • Lightweight: Typically uses less memory than having many large pools across all services.
  • Transaction Pooling: pgBouncer can hold connections only during actual transactions and free them for others when idle, further reducing overhead on the DB server.

7.2 Basic pgBouncer Configuration

  1. Install pgBouncer on a server that can communicate with your PostgreSQL instance.
  2. Configure pgbouncer.ini:
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
  1. userlist.txt (with hashed passwords):
"myuser" "md5HASHEDPASSWORD"
  1. Start pgBouncer and update your application to connect to localhost:6432 (pgBouncer) instead of 5432 (PostgreSQL).

7.3 Using TypeORM with pgBouncer

You can choose to also keep a small application-level pool (max=5, for instance), or even set max=1 if you want pgBouncer to manage almost all concurrency. This approach minimizes the risk of “double pooling” causing too many overall connections.

export const AppDataSource = new DataSource({
  type: 'postgres',
  host: 'pgbouncer-host',
  port: 6432,
  username: 'myuser',
  password: 'mypassword',
  database: 'mydatabase',
  // ...
});

8. Production-Ready Tips & Best Practices

8.1 Minimize Over-Pooling (Double Pooling)

  • If you rely heavily on pgBouncer, consider lowering TypeORM’s max connections. Having large pools at both the application and pgBouncer levels can result in oversubscription and performance issues.
  • Alternatively, if you only have a small number of app instances, you might rely solely on TypeORM’s built-in pool or only on pgBouncer for concurrency control.

8.2 Set Reasonable Pool Sizes

  • Application-level: max=10 is a common starting point; adjust after load testing.
  • pgBouncer: default_pool_size=20 might suffice for many apps.
  • Monitor and tweak these settings based on real-world usage and resource limits.

8.3 Connection Timeouts

  • Keep connectionTimeoutMillis or acquireTimeout short so you don’t block indefinitely.
  • Use idleTimeoutMillis or an equivalent to avoid holding idle connections too long.

8.4 Graceful Shutdown

  • In Node.js, handle signals like SIGTERM and call AppDataSource.destroy() or pool.end() so you close all connections properly.
  • For pgBouncer, similarly ensure you shut it down gracefully if your environment expects rolling updates or container restarts.

8.5 Security Measures

  • Use SSL/TLS if connecting over untrusted networks.
  • Restrict inbound connections so only your application or pgBouncer servers can access PostgreSQL.
  • Rotate credentials periodically.

9. Conclusion

Yes, TypeORM has built-in pooling capabilities, leveraging the underlying driver (such as pg for PostgreSQL). For many projects, this built-in approach is sufficient: you configure the pool once in your DataSource, and TypeORM automatically acquires and releases connections.

However, for high-concurrency architectures—particularly in a microservices environment—an external connection pooler like pgBouncer might still be beneficial. By placing it between your database and your applications, you can consolidate connection management, reduce overhead on the database, and more easily handle spikes in traffic. Just remember:

  • Avoid over-pooling: If both your application and pgBouncer maintain large pools, you could oversubscribe the database.
  • Tweak and monitor: Production environments vary, so load testing and metrics are essential for finding the right pool sizes and timeouts.
  • Graceful shutdown and security best practices apply to both your application-level pool and any external pooler.

Following these guidelines will help you build a robust, high-performance, and scalable database access layer—whether you rely on TypeORM’s built-in pooling, an external pooler, or a combination of both.

Read more