postgresml/pgcat
Pgcat
Pgcat is a PostgreSQL middleware solution providing connection pooling, sharding, load balancing, and failover management. It functions as a proxy that optimizes resource usage and scalability by managing database sessions and distributing queries across multiple instances.
The system distinguishes itself through traffic mirroring, which duplicates live production queries to test databases for validation. It also implements a sharding proxy to distribute data and queries across multiple database instances based on sharding keys.
The project covers high availability through automated failover and server health monitoring. It includes traffic routing to separate read and write operations, transport layer security for encrypted communications, and a virtual administrative database for system management and performance metrics export.
The service is packaged as a Docker container to ensure consistent deployment across different environments.
Features
- PostgreSQL Pool Managers - Manages a pool of database connections specifically tailored for the PostgreSQL wire protocol to optimize resource usage.
- Database Connection Pooling - Manages a pool of reusable backend sessions to reduce the overhead of establishing new database connections.
- SQL Comment Based Routing - Routes queries to specific data partitions using sharding keys extracted from SQL comments or hashing functions.
- Query Routing - Directs queries to specific data partitions using automatic key detection, SQL comments, or custom syntax.
- Horizontal Database Scaling - Distributes data across multiple database instances to scale storage and throughput through horizontal expansion.
- PostgreSQL Database Sharding - Splits data across multiple PostgreSQL servers to scale storage and processing by routing queries via sharding keys.
- PostgreSQL Load Balancers - Provides a protocol-aware proxy to distribute read queries across multiple PostgreSQL backend instances.
- Traffic Routing - Analyzes incoming SQL to automatically direct read-only requests to replicas and write operations to the primary server.
- Replica Load Balancers - Distributes incoming queries across a cluster of available replicas to prevent single-instance bottlenecks.
- Read Load Balancers - Distributes read requests across multiple replicas using random or least-connection algorithms.
- Sharding Proxies - Acts as a routing layer that distributes queries across multiple database instances based on sharding keys.
- Database Failover Routers - Redirects traffic to a healthy standby instance when a primary node becomes unavailable to maintain uptime.
- Database High Availability - Coordinates PostgreSQL nodes to ensure continuous service availability through automated failover and health monitoring.
- Database Failover Management - Monitors backend server health and automatically reroutes traffic to standby instances during primary node failure.
- Health-Based Traffic Rerouting - Monitors backend server availability via periodic probes and automatically reroutes traffic away from unresponsive nodes.
- Upstream Health Monitoring - Performs periodic health checks on backend servers and temporarily blocks unresponsive nodes to ensure stability.
- Database Query Mirroring - Duplicates incoming production queries to test databases to validate new versions or warm up caches.
- Database Connection Encryption - Encrypts communication between clients, the proxy, and database servers using TLS certificates.
- Database Authentication - Validates client identities using secure hashing or external query passthrough to manage database access.
- Transport Layer Security - Secures data in transit between clients and backend servers using certificates and private keys.
- Administrative Command Engines - Executes system management tasks and inspects pool status through a dedicated administrative interface.
- Virtual Administrative Databases - Provides a dedicated virtual administrative database for executing system commands and inspecting pool health.
