postgresml/pgcat
Pgcat
Pgcat es una solución de middleware para PostgreSQL que ofrece agrupación de conexiones (connection pooling), sharding, balanceo de carga y gestión de conmutación por error (failover). Funciona como un proxy que optimiza el uso de recursos y la escalabilidad gestionando sesiones de base de datos y distribuyendo consultas entre múltiples instancias.
El sistema destaca por su capacidad de duplicación de tráfico (traffic mirroring), que replica consultas de producción en tiempo real hacia bases de datos de prueba para su validación. También implementa un proxy de sharding para distribuir datos y consultas entre múltiples instancias de base de datos basándose en claves de particionamiento.
El proyecto cubre la alta disponibilidad mediante failover automatizado y monitoreo del estado de los servidores. Incluye enrutamiento de tráfico para separar operaciones de lectura y escritura, seguridad en la capa de transporte (TLS) para comunicaciones cifradas y una base de datos administrativa virtual para la gestión del sistema y la exportación de métricas de rendimiento.
El servicio se distribuye como un contenedor Docker para garantizar un despliegue consistente en diferentes entornos.
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.
