postgresml/pgcat
Pgcat
Pgcat ist eine PostgreSQL-Middleware-Lösung für Connection Pooling, Sharding, Load Balancing und Failover-Management. Sie fungiert als Proxy, der die Ressourcennutzung und Skalierbarkeit optimiert, indem er Datenbanksitzungen verwaltet und Abfragen auf mehrere Instanzen verteilt.
Das System zeichnet sich durch Traffic-Mirroring aus, bei dem Live-Produktionsabfragen zur Validierung an Testdatenbanken dupliziert werden. Zudem implementiert es einen Sharding-Proxy, um Daten und Abfragen basierend auf Sharding-Keys auf mehrere Datenbankinstanzen zu verteilen.
Das Projekt deckt Hochverfügbarkeit durch automatisiertes Failover und Server-Health-Monitoring ab. Es umfasst Traffic-Routing zur Trennung von Lese- und Schreiboperationen, Transport Layer Security für verschlüsselte Kommunikation sowie eine virtuelle administrative Datenbank für das Systemmanagement und den Export von Leistungsmetriken.
Der Dienst wird als Docker-Container bereitgestellt, um eine konsistente Bereitstellung in verschiedenen Umgebungen zu gewährleisten.
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.
