PgPool

Giriş

Şu işleri yapıyor

1. Connection pooling

2. Replication and Load Balancing

3. Automated Failover

4. Query Caching

Mimarisel Özellikler

Şöyle. 

• Primary purpose: Pooling, load balancing, HA, caching
• Architecture: Multi-process, one process per connection
• Memory per connection: ~3–5 MB (per child process)
• Configuration complexity: Complex (multiple config files)
• Codebase size: ~200k+ lines of C
• Protocol support: PostgreSQL wire protocol + SQL parsing

Kaynak kullanımı şöyle. 

• Query overhead per statement: 0.5–2 milliseconds (includes SQL parsing)
• Max clients on 2 GB RAM: ~400 (limited by child processes)
• Memory at 500 clients: ~1.5–2.5 GB
• Connection storm (1000 simultaneous): Needs 1000 child processes or queues
• CPU usage under load: Higher (process per connection + SQL parsing)

Kullanım Şekli

Açıklaması şöyle

A common production pattern is Patroni managing PostgreSQL replication and failover, PgBouncer providing connection pooling and HAProxy routing traffic to the correct server. This approach gives you best-in-class tooling for each concern but requires more components to configure and maintain. Pgpool-II combines these functions, trading some performance for operational simplicity.

Connection Routing

Açıklaması şöyle

Pgpool-II parses SQL queries to decide where to route them (reads to replicas, writes to primary). This parsing adds overhead but enables features that PgBouncer can't offer. PgBouncer passes queries through without inspecting them.

Açıklaması şöyle

It parses SQL queries and automatically routes SELECT statements to read replicas while sending writes to the primary. This read/write splitting is transparent to the application with no code changes needed. 

High Availability

Açıklaması şöyle

When the primary server goes down, Pgpool-II can promote a standby and reconfigure itself automatically using a failover script, so the application doesn't need to know about the topology change.

Beyond routing and failover, Pgpool-II continuously monitors backend servers and removes unhealthy ones from the pool through health checking. It can also run in a clustered mode with its watchdog feature, where multiple Pgpool-II instances monitor each other and one takes over with a virtual IP if another fails.

Database Monitoring

Açıklaması şöyle

Beyond routing and failover, Pgpool-II continuously monitors backend servers and removes unhealthy ones from the pool through health checking. It can also run in a clustered mode with its watchdog feature, where multiple Pgpool-II instances monitor each other and one takes over with a virtual IP if another fails.

PgPool ve PgBouncer 

Açıklaması şöyle. Yani PgBouncer  daha basit kalıyor

A lightweight connection pooler for PostgreSQL that can help manage connections and improve performance, but lacks advanced features like replication and failover support. PGBouncer is a good option if you need a simpler solution focused primarily on connection pooling.

Açıklama şöyle

Pgpool-II uses a different model. It maintains a pool of child processes, each handling one client. The child process holds a cached connection to the backend and reuses it across requests from the same client. Pgpool-II doesn't support transaction-level pooling in the same sense as PgBouncer. Its pooling is closer to session-level, where connections are cached per child process and reused when the same user/database combination connects again.

Bağlantı

Örnek

Şöyle yaparız

postgres://admin:xxx@pgpool.default:5432/example_db

Ini Dosyası

Açıklaması şöyle

Pgpool-II configuration involves multiple files: pgpool.conf for general settings, pool_hba.conf for authentication, pcp.conf for management commands and potentially failover scripts. The main config file has hundreds of parameters covering pooling, load balancing, replication, health checking and watchdog clustering.

Örnek

Şöyle yaparız

# Pgpool-II load balancing configuration
load_balance_mode = on
backend_hostname0 = 'primary.db.local'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY'
backend_hostname1 = 'replica1.db.local'
backend_port1 = 5432
backend_weight1 = 2
backend_hostname2 = 'replica2.db.local'
backend_port2 = 5432
backend_weight2 = 2