A PostgreSQL proxy or connection pooler is an intermediary application that sits between your client applications and one or more PostgreSQL servers. It speaks the PostgreSQL network protocol, which allows any standard client (for example, your web server or an application in Java/Python/Go) to connect to the proxy believing it is talking directly to a PostgreSQL server.

Unlike MySQL, where proxies are often used for read/write splitting (R/W split) or caching, in the PostgreSQL world the main task of a proxy is efficient connection management.

The problem: PostgreSQL’s “Process per Connection”

PostgreSQL historically uses a process-per-connection model, where each new client connection spawns a separate postgres process. This is reliable but resource-intensive: each process consumes roughly 10–20 MB RAM, plus overhead from fork/exec.

If your application opens hundreds or thousands of short-lived connections (for example, microservices, FaaS, web apps), the DB server becomes overloaded: CPU load from fork increases, memory is exhausted, connections get “stuck.”

The solution is connection pooling. This is what PgBouncer, Pgpool-II, Odyssey and their peers do.

How a PostgreSQL proxy works

In a basic configuration the pooler simply forwards requests from the client to the PostgreSQL server. But its key feature is connection multiplexing:

1. Accept — the client application connects to the pooler (e.g., PgBouncer).

2. Idle pool — the pooler maintains a ready pool of real connections to the PostgreSQL server (already authenticated).

3. Routing — when the client issues a request:

   • the pooler takes a free connection from the pool;
   • executes the client’s transaction;
   • returns the connection back to the pool, resetting its state.

4. Response — the result is returned to the client.

Result: thousands of client “lightweight” connections are handled by dozens of “heavy” connections to the DB. Resource savings are an order of magnitude.

Main functions and responsibilities

Some proxies also support query caching, metrics collection and Prometheus exporters.

Types of PostgreSQL proxies

1. Lightweight connection poolers (Layer 7, not SQL-aware)

They understand the PostgreSQL protocol but do not parse SQL. The main goal is fast multiplexing with minimal latency (<1 ms).

Pooling modes:

• Session — the connection is held until disconnect (convenient but does not save resources).
• Transaction — the connection is given for the duration of a transaction (BEGIN…COMMIT). Optimal balance.
• Statement — the connection is used for a single statement (maximum savings, but strict limitations).

2. SQL-aware proxies (they parse SQL)

They inspect SQL traffic, enabling R/W split, caching and query analysis.

3. TCP/Layer 4 proxies (do not understand the protocol)

Configuration examples

PgBouncer (transaction pooling)

[databases]
my_app_db = host=127.0.0.1 port=5432 dbname=real_db_name

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction  ; Рекомендуемый режим
max_client_conn = 2000
default_pool_size = 20   ; Подбирайте под RAM/CPU (~10–50 на ядро)
reserve_pool_size = 5

Pgpool-II (R/W split and HA)

listen_addresses = '*'
port = 9999

enable_pooling = on
num_init_children = 32
max_pool = 4

backend_hostname0 = 'primary_host'
backend_port0 = 5432
backend_weight0 = 1.0

backend_hostname1 = 'replica_host'
backend_port1 = 5432
backend_weight1 = 1.0

load_balance_mode = on
primary_node_id = 0
watchdog_enable = on

Odyssey (multithreaded YAML config)

daemonize: false
pid_file_path: /tmp/odyssey.pid

listeners:
  - host: 0.0.0.0
    port: 6432
    database: my_app_db
    users:
      - name: app_user
        password: secret
        pool_size: 20
        pool_mode: transaction

storage:
  - name: postgres
    type: remote
    hosts:
      - host: 127.0.0.1
        port: 5432

How to choose a tool

Best Practices

• Monitoring: use SHOW STATS; (PgBouncer) or Prometheus exporters.
• Pool size: default_pool_size = (RAM / 20MB) / databases, but no more than 100–200 per server.
• Security: enable sslmode=require and terminate TLS at the pooler.
• Load: test with pgbench, aiming for <5 ms latency.
• Kubernetes: deploy PgBouncer as a sidecar, HAProxy as a DaemonSet.

Conclusion

In the PostgreSQL ecosystem a connection pooler is not an option but a necessity for any high-load system (100+ RPS).

• Start with PgBouncer — it solves 90% of connection problems.
• Use Pgpool-II if you need built-in R/W split and query cache.
• Use Odyssey or PgCat for modern large-scale setups.
• Add HAProxy + Patroni if HA and replication at the TCP level are required.

With the right proxy your PostgreSQL will handle thousands of clients and remain calm as an elephant 🐘.