postgresml/pgcat
Pgcat
Pgcat est une solution de middleware PostgreSQL offrant le pooling de connexions, le sharding, l'équilibrage de charge et la gestion du basculement (failover). Il fonctionne comme un proxy qui optimise l'utilisation des ressources et la scalabilité en gérant les sessions de base de données et en distribuant les requêtes sur plusieurs instances.
Le système se distingue par le mirroring de trafic, qui duplique les requêtes de production en temps réel vers des bases de données de test pour validation. Il implémente également un proxy de sharding pour distribuer les données et les requêtes sur plusieurs instances de base de données en fonction de clés de sharding.
Le projet assure la haute disponibilité grâce au basculement automatisé et à la surveillance de l'état des serveurs. Il inclut le routage de trafic pour séparer les opérations de lecture et d'écriture, le chiffrement TLS pour les communications, et une base de données administrative virtuelle pour la gestion du système et l'exportation de métriques de performance.
Le service est packagé sous forme de conteneur Docker pour garantir un déploiement cohérent dans différents environnements.
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.
