How postgresml/pgcat works
Project Type and Purpose
• PgCat is a high-performance, multi-threaded PostgreSQL proxy and connection pooler written in Rust. It accepts client connections, pools and routes them to one or more PostgreSQL servers, and provides features such as load balancing, failover, sharding, mirroring, authentication, metrics, and live configuration reloading.
Key Architectural Domains
Break the system into four main domains:
a. Client Tier
b. Pooler/Proxy Service (PgCat)
c. PostgreSQL Backend Tier
d. Observability & Deployment
Core Components of PgCat
• Listener & Connection Acceptor (src/server.rs)
• Authentication Module (src/auth_passthrough.rs, src/scram.rs)
• Connection Pool Manager (src/pool.rs)
• Query Router & Parser (src/query_router.rs, sqlparser crate)
• Sharding Engine (src/sharding.rs)
• Mirroring & Proxy Plugins (src/plugins/*)
• Health & Failover Checker (built into query routing and pool logic)
• Metrics & Admin Interface
• Admin DB endpoint (pgbouncer/pgcat databases) (src/admin.rs)
• HTTP /metrics for Prometheus (src/prometheus.rs)
• Configuration Loader & Hot-Reload (src/config.rs & SIGHUP support)
• TLS Layer (src/tls.rs)
• Logging (src/logger.rs)
Relationships and Data Flow
• Clients issue SQL → Listener → Authentication → Pooled Connection
• Query text flows into Query Router → decides:
• Primary vs. replica (load balancing)
• Shard selection via explicit SET SHARD or comment parsing or automatic SQL parse
• Mirroring (duplicate to multiple backends if enabled)
• Connection Pool Manager dispatches the query on a worker thread (Tokio runtime)
• Worker thread forwards query to chosen Postgres server(s) over TCP/TLS
• Responses flow back to the client
• Health Checker runs periodic “ping” queries on each server and updates pool state
• Configuration changes (pgcat.toml) trigger hot-reload; Plugins and pool settings update on the fly
• Metrics collector exposes counters/gauges via HTTP and in-DB tables for Grafana/Prometheus
External Systems
• PostgreSQL Instances: Primary, Replicas, Shards
• Prometheus scrapes /metrics endpoint
• Grafana imports the provided dashboard JSON (grafana_dashboard.json)
• Kubernetes/Helm (charts/pgcat) or Docker Compose (docker-compose.yml, dev/docker-compose.yaml) for deployment
Architectural Patterns & Design Principles
• Asynchronous, multi-threaded event loop (Tokio)
• Modular plugin architecture (Rust traits in src/plugins)
• Configuration as code (TOML file, live reloading)
• Separation of concerns: authentication, routing, pooling, health, metrics
• Fail-fast and self-healing: automatic ban and re-enable of unhealthy servers