Schedule - PGDay Napoli 2025

Back

New kid on the block: Introduction to PostgreSQL Support in ProxySQL 3

Date: 2025-09-25
Time: 10:10–11:00
Room: Delle Colonne
Level: Advanced

ProxySQL is a well-known, high-performance, feature-rich proxy for MySQL. With the upcoming release of version 3, ProxySQL will add support for PostgreSQL.

In this presentation, we will explore the key features and potential use cases of ProxySQL’s PostgreSQL support and compare it with existing alternatives.

Introduction

ProxySQL offers a common proxy solution for the most popular open-source databases, as it can handle PostgreSQL and MySQL traffic. This simplifies infrastructure, reduces operational complexity, and provides a unified architecture across database platforms.

Architecture

Architecture Overview

ProxySQL employs a multi-layered, event-driven architecture with dynamic configuration capabilities. Its core components include:

  • Connection Pooling: Manages persistent connections to PostgreSQL backends, mitigating the overhead of PostgreSQL’s process-per-connection model.
  • Query Processing Engine: Inspects, routes, and caches queries using user-defined rules (e.g., regex-based routing, read/write splitting).
  • Embedded Configuration Database: Stores runtime settings in SQLite, enabling on-the-fly changes without service restarts.
  • Metrics and Monitoring: Tracks performance metrics (latency, throughput) to inform load balancing and failover decisions.

PostgreSQL Support

PostgreSQL-Specific Features

  • Full proxy functionality for PostgreSQL databases. Implementation of Plain Text, MD5, and SCRAM-SHA-256 authentication methods.
  • SSL connection support is available for both frontend and backend PostgreSQL connections.
  • Advanced features that include connection multiplexing and connection pool reuse.
  • Query caching.
  • Query rewriting.
  • Read-write splitting.

Configuration and Monitoring

ProxySQL 3.0 introduces PostgreSQL-specific configuration tables and monitoring tools: - Configuration Tables: New tables such as pgsql_servers, pgsql_users, and pgsql_query_rules allow fine-grained control over PostgreSQL traffic management. - Monitoring Tools: Enhanced statistics tables provide detailed insights into PostgreSQL connections, query performance, and error tracking, facilitating proactive system management.

Use Cases

  • ProxySQL’s PostgreSQL supports various use cases: Hybrid Database Environments: Organisations running both MySQL and PostgreSQL can now manage traffic through a single proxy solution, simplifying architecture and operations.
  • High Availability and Load Balancing: ProxySQL’s advanced routing and failover capabilities enhance the resilience and scalability of PostgreSQL deployments.
  • Security and Compliance: With robust authentication methods and SSL support, ProxySQL ensures secure data transmission and compliance with security standards.

Alternatives

In this section, we will compare ProxySQL with different alternatives, such as PgBouncer, Pgpool, and HAProxy.

Speaker

Pep Pla