PgCat — PostgreSQL Pooler with Sharding and Load Balancing

Introduction

PgCat is a PostgreSQL connection pooler and proxy written in Rust. It goes beyond simple pooling by offering built-in sharding, read/write query splitting, load balancing across replicas, and automatic failover, making it suitable for scaling PostgreSQL horizontally.

What PgCat Does

• Pools client connections to reduce backend connection overhead
• Routes read queries to replicas and writes to the primary automatically
• Shards data across multiple PostgreSQL instances using configurable strategies
• Performs health checks and automatic failover when a backend goes down
• Supports both transaction-mode and session-mode pooling

Architecture Overview

PgCat runs as a single async Rust binary using Tokio. It accepts PostgreSQL wire protocol connections from clients and maintains pools of backend connections to one or more PostgreSQL servers. A router layer inspects queries to determine read vs write intent and selects the appropriate backend. Sharding logic hashes a configurable key to route queries to the correct shard.

Self-Hosting & Configuration

• Deploy via Docker or compile from source with cargo build --release
• Configure in pgcat.toml with pools, users, shards, and replicas
• Set pool mode to transaction (default) or session per pool
• Enable query routing with primary_reads_enabled and replica settings
• Monitor via the built-in admin database accessible through SQL commands

Key Features

• Transaction-mode pooling with up to 10,000+ client connections per instance
• Automatic read/write splitting without application changes
• Sharding support with consistent hashing or range-based routing
• Health checks with configurable intervals and automatic ban/unban of failing backends
• Zero-downtime configuration reloads via SIGHUP

Comparison with Similar Tools

• PgBouncer — industry standard pooler but no sharding, no read/write splitting
• Odyssey — multi-threaded pooler by Yandex, no built-in sharding
• pgpool-II — feature-rich but complex configuration and single-threaded query parsing
• Supavisor — Elixir-based pooler from Supabase, focused on multi-tenant cloud
• HAProxy — generic TCP load balancer, no PostgreSQL protocol awareness

FAQ

Q: Can PgCat replace PgBouncer? A: Yes for most use cases. PgCat supports transaction-mode pooling like PgBouncer and adds features like sharding and read/write splitting.

Q: How does automatic failover work? A: PgCat health-checks backends at configurable intervals. If a primary fails health checks, it is banned and clients receive errors until it recovers or a new primary is promoted.

Q: Does PgCat support prepared statements? A: Yes in session mode. Transaction mode has limited prepared statement support due to connection multiplexing constraints.

Q: What is the performance overhead? A: Minimal. PgCat adds sub-millisecond latency per query due to its async Rust architecture with zero-copy parsing.

Sources

• https://github.com/postgresml/pgcat
• https://postgresml.org/docs/open-source/pgcat/