Understanding Database Connection Pooling in Production (Conceptual Guide)

Introduction

When building a backend system—especially one using Node.js, Express, PostgreSQL, and PostGIS—one of the most misunderstood topics is connection pooling.

Many developers assume:

• 500 users online = 500 database connections

• Increasing max makes the system faster

• min affects traffic handling

These assumptions are incorrect.

This article explains the core concepts clearly and conceptually.

1. What Is a Database Connection?

A database connection is like a dedicated communication pipe between your application and PostgreSQL.

Important rule:

One connection can execute only ONE query at a time.

If 20 queries must run simultaneously, you need 20 connections.

2. What Is a Connection Pool?

A connection pool is a container that:

• Creates connections

• Reuses them

• Limits how many run at once

Instead of creating a new connection per request (very expensive), we reuse existing ones.

Think of it like:

Bank counters:

• Customers = queries

• Counters = connections

• Bank manager = connection pool

3. Meaning of Pool Settings

max

max: 15

Means:

Maximum 15 queries can run at the same time for that database.

If 25 queries arrive simultaneously:

• 15 run immediately

• 10 wait in queue

It does NOT mean:

• 15 users total

• 15 users per day

• 15 users per session

It means 15 simultaneous active database operations.

min

min: 2

Means:

Keep at least 2 connections always ready.

It does NOT control traffic capacity.
It only prevents cold-start delay.

If min is 1 or 2, difference is usually small.

4. 500 Users Online ≠ 500 Database Connections

This is the biggest misconception.

Even if 500 users are online:

• Not all are making requests at the same millisecond

• Not all requests hit the database

• Not all queries are heavy

In reality:

500 concurrent users might produce only 40–80 active database queries at peak.

Database concurrency is much lower than user concurrency.

5. Waiting vs Slow Query

There are two types of delays.

1. Slow Query

Query itself takes long time because:

• No indexes

• Heavy spatial joins

• Large geometries

Even one user will experience slowness.

This is a query optimization issue.

2. Pool Queue Waiting

Query is fast (300ms), but must wait 200ms for a free connection.

This is controlled concurrency, not slowness.

The database is being protected from overload.

6. Why Increasing max Does NOT Always Improve Speed

Many developers think:

"If 15 connections work, 100 connections must be better."

Wrong.

PostgreSQL does not create more CPU power when you increase connections.

If you have:

• 8 CPU cores

And you allow:

• 100 heavy spatial queries simultaneously

You create:

• CPU contention

• Context switching

• Memory pressure

• Cache thrashing

Result:

All queries become slower.

Sometimes fewer concurrent queries = better performance.

7. The Chef Analogy

Imagine:

• 8 chefs in a kitchen (8 CPU cores)

• 15 orders → manageable

• 100 orders at same second → chaos

More orders do not create more chefs.

The pool limits how many orders reach the kitchen at once.

It protects stability.

8. How to Choose max Conceptually

Pool size should depend on:

1. CPU cores of DB server

2. Query complexity (especially PostGIS)

3. Number of Node instances

4. Number of databases (if using per-theme DBs)

5. PostgreSQL max_connections

Rule of thumb for spatial workloads:

max per DB: 10–20 is often stable.

Not 50.
Not 100.

9. Per Database vs Overall Connections

If you have:

• 5 theme databases

• max: 15

• 1 Node instance

Total possible connections:

5 × 15 = 75

If you run 2 Node instances:

2 × 5 × 15 = 150

Always calculate globally.

10. Realistic Server Requirements

For 300–500 concurrent users (WebGIS with PostGIS):

Recommended:

• 8 CPU cores

• 16 GB RAM

• NVMe SSD

Database performance depends more on:

• Proper spatial indexing (GIST)

• Optimized queries

• Reasonable pool limits

Not on extreme connection counts.

11. Core Concept Summary

max = traffic control (concurrency limit)
min = readiness (warm connections)

500 users online does not mean 500 connections needed.

Connection pooling is not about increasing speed.
It is about controlling load and protecting stability.

In production systems, stability always beats uncontrolled concurrency.

Final Thought

A well-tuned pool with optimized queries will always outperform a large pool with unoptimized queries.

Connection pooling is not a scaling shortcut.
It is a load management strategy.

Understanding this concept separates beginner backend development from production-grade system design.