# PgCat — PostgreSQL Connection Pooler with Sharding and Load Balancing

> A high-performance PostgreSQL connection pooler written in Rust that supports query-level load balancing, read/write splitting, automatic failover, and sharding across multiple database instances.

## Install

Save in your project root:

# PgCat — PostgreSQL Connection Pooler with Sharding and Load Balancing

## Quick Use
```bash
# Install from source
git clone https://github.com/postgresml/pgcat.git
cd pgcat
cargo build --release

# Or pull the Docker image
docker pull ghcr.io/postgresml/pgcat:latest

# Configure and start
cp pgcat.toml.example pgcat.toml
# Edit pgcat.toml with your database connection details
./target/release/pgcat pgcat.toml

# Connect via PgCat (default port 6432)
psql -h 127.0.0.1 -p 6432 -U myuser mydb
```

## Introduction
PgCat is an open-source PostgreSQL connection pooler and proxy written in Rust, developed by the PostgresML team. It sits between your application and PostgreSQL servers, managing connection pools while adding load balancing, read/write splitting, and automatic failover — capabilities that go well beyond traditional poolers.

## What PgCat Does
- Pools PostgreSQL connections to reduce the overhead of opening and closing database connections
- Balances read queries across multiple replicas for horizontal read scaling
- Splits reads and writes automatically, routing writes to the primary and reads to replicas
- Detects failed database instances and reroutes traffic to healthy ones
- Supports sharding queries across multiple PostgreSQL instances by partition key

## Architecture Overview
PgCat is an async Rust application built on Tokio that acts as a transparent PostgreSQL proxy. It speaks the PostgreSQL wire protocol, so applications connect to PgCat as if it were a regular PostgreSQL server. Internally, PgCat maintains pools of server connections organized by role (primary or replica) and shard. A query parser inspects incoming SQL to determine routing: writes go to the primary, reads go to replicas in round-robin. Health checks run periodically against all backends to detect failures. Configuration is loaded from a TOML file and can be reloaded at runtime without dropping connections.

## Self-Hosting & Configuration
- Deploy as a single binary or Docker container between your application and PostgreSQL
- Configure database pools in pgcat.toml with host, port, role, and pool size per backend
- Enable transaction or session pooling mode depending on your application requirements
- Set up health check intervals and thresholds for automatic failover behavior
- Reload configuration at runtime by sending SIGHUP — no restart or connection drop needed

## Key Features
- Written in Rust with async I/O for minimal latency overhead (sub-millisecond per query)
- Query-level load balancing distributes individual queries rather than entire sessions
- Automatic primary/replica detection using pg_is_in_recovery() health checks
- Admin interface for monitoring pool statistics, active connections, and server states
- Zero-downtime configuration reloads for adding or removing backends

## Comparison with Similar Tools
- **PgBouncer** — the standard PostgreSQL pooler, but lacks load balancing, failover, and sharding; PgCat adds all three
- **Odyssey** — Yandex pooler with multi-threading; PgCat offers similar performance with a simpler configuration model
- **Supavisor** — Supabase's Elixir-based pooler designed for multi-tenant SaaS; PgCat is a general-purpose pooler
- **HAProxy** — TCP-level load balancer; PgCat understands the PostgreSQL protocol for smarter routing
- **Citus** — sharding extension that modifies PostgreSQL itself; PgCat shards at the proxy layer without database changes

## FAQ
**Q: Can I use PgCat as a drop-in replacement for PgBouncer?**
A: In most cases, yes. PgCat supports the same pooling modes (session and transaction). Some PgBouncer-specific admin commands differ, but application connections work without changes.

**Q: Does PgCat support prepared statements?**
A: Yes, in both session and transaction pooling modes. PgCat tracks prepared statements per server connection and re-prepares them when needed.

**Q: How does PgCat decide which queries go to replicas?**
A: By default, queries inside a read-only transaction or explicit SELECT statements are routed to replicas. Write operations and DDL always go to the primary.

**Q: What happens when a replica goes down?**
A: PgCat's health checker detects the failure and removes the replica from the pool. Queries are redistributed to remaining healthy replicas. When the replica recovers, it is automatically added back.

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

---
Source: https://tokrepo.com/en/workflows/asset-d8cf927e
Author: AI Open Source