# PgCat — PostgreSQL Pooler with Sharding and Load Balancing

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

## Install

Save as a script file and run:

# PgCat — PostgreSQL Pooler with Sharding and Load Balancing

## Quick Use
```bash
# Install from source or Docker
docker run -v $(pwd)/pgcat.toml:/etc/pgcat/pgcat.toml 
  ghcr.io/postgresml/pgcat:latest

# Connect through PgCat like a normal PostgreSQL server
psql -h 127.0.0.1 -p 6432 -U myuser mydb
```

## 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/

---
Source: https://tokrepo.com/en/workflows/asset-30d2ad67
Author: Script Depot