ProxySQL 3.0 is Here: Top Features for MySQL & PostgreSQL Users

Managing high-traffic databases is never just about scaling hardware. One poorly optimized query can degrade performance, overload your primary node, and impact user experience across your application.

This is exactly where ProxySQL 3.0 features come into play.

Whether you're running ProxySQL for MySQL, exploring ProxySQL for PostgreSQL, or managing hybrid environments, ProxySQL acts as a smart SQL-aware proxy that enables routing, caching, rewriting, and load balancing—without requiring any application changes.

At Mafiree, we’ve implemented ProxySQL across 50+ production environments—helping teams resolve real-world challenges like sudden query spikes, inefficient read scaling, and unpredictable database load. Our experience shows that the right ProxySQL strategy can prevent downtime, improve performance, and give teams much better control over their database traffic.

If you're evaluating proxysql features for production workloads, this guide will help you understand where it fits, how it performs, and when to use it.

What’s New in ProxySQL 3.0? PostgreSQL Support + MySQL Enhancements

The biggest shift in ProxySQL 3.0 features list is clear:
➡️ Native PostgreSQL support (Beta)

This means you can now use ProxySQL + PostgreSQL alongside MySQL within the same architecture.

Key Enhancements:

• PostgreSQL protocol support (beta)
• Authentication: Plain Text, MD5, SCRAM-SHA-256
• Connection pooling & multiplexing
• SSL for secure communication
• Multi-statement query support
• Backend health checks
• Transaction handling (SAVEPOINT, ROLLBACK)
• COPY FROM STDIN support

MySQL Enhancements:

• Dual password authentication (zero-downtime rotation)
• Advanced query logging with parameter tracking
• Real-time SQL auditing using in-memory engine

? Read more: https://www.mafiree.com/readBlog/proxysql-features
? Explore routing use cases: https://www.mafiree.com/readBlog/dynamic-query-routing-with-proxysql

Understanding Query Routing: The Core Feature Explained

At the heart of all proxysql features lies one powerful capability: query routing.

How Query Routing Works (Technical Foundation)

ProxySQL inspects incoming SQL queries and routes them based on:

• Query patterns (regex rules)
• User/application
• Schema/database
• Transaction state

This allows full control over how traffic flows across your database cluster.

Read-Write Split: Most Common Query Routing Pattern

A classic example in postgresql load balancing:

• SELECT → Read Replica
• INSERT/UPDATE/DELETE → Primary

This is the foundation of any postgresql load balancing cluster.

Even something like:

SELECT * FROM orders LIMIT 10; 
or 
SELECT * FROM users LIMIT 13;

These common postgresql limit example (10, 13) queries can be automatically routed to replicas to reduce primary load.

PostgreSQL Support: Game-Changing Addition to ProxySQL

For PostgreSQL DBAs, this is huge.

ProxySQL for PostgreSQL brings SQL-aware routing—something traditionally missing in most PostgreSQL proxy layers.

Authentication Methods in ProxySQL 3.0

• Plain Text
• MD5
• SCRAM-SHA-256 (recommended)

SSL & Secure PostgreSQL Connections

Security is production-critical:

• TLS encryption between client ↔ ProxySQL ↔ backend
• Secure credential handling
• Compatible with enterprise security standards

Advanced Features That Drive Business Outcomes

These aren’t just technical features—they directly impact performance and cost.

Connection Multiplexing: Reduce Connection Overhead by 70%+

Instead of opening thousands of DB connections:

• ProxySQL reuses backend connections
• Reduces memory + CPU usage
• Prevents connection storms

Query Caching: Accelerate Repeated Queries

Frequently executed queries are served from memory:

• Reduces database load
• Improves response time dramatically
• Ideal for dashboards, reports, APIs

Intelligent Load Balancing Across Replicas

Unlike traditional tools, ProxySQL is SQL-aware:

• Routes based on query type
• Avoids stale replicas (lag-aware routing)
• Enables efficient postgresql load balancing

High Availability & Automatic Failover

ProxySQL continuously:

• Monitors backend health
• Removes failed nodes
• Redirects traffic automatically

ProxySQL 3.0 vs Alternatives: When to Use ProxySQL

Choosing the right tool matters.

ProxySQL vs HAProxy: SQL-Aware vs Layer 4 Proxy

• Use HAProxy for network-level routing
• Use ProxySQL for database intelligence

ProxySQL vs MySQL Router: Performance & Features

Real-World Use Cases: When ProxySQL 3.0 Shines

E-Commerce: Handling High Traffic with Read-Write Split

Heavy read traffic?
ProxySQL routes reads to replicas automatically.

SaaS Platforms: Scaling Without Code Changes

No need to rewrite your app:

• Add ProxySQL
• Define rules
• Scale instantly

Reporting Databases: Dedicated Read Replicas

Offload reporting queries:

• Protect primary performance
• Improve analytics speed

Mafiree’s Consulting Experience: Why We Chose ProxySQL for 50+ Deployments

At Mafiree, we've implemented ProxySQL across 50+ production environments.

Real Incident:

A developer released a heavy query that caused production slowdown.

Instead of:

• Rolling back code
• Restarting database

We:

• Identified query pattern
• Routed it to a replica
• Restored system performance within minutes

This is the real power of proxysql + postgresql in production.

Getting Started: ProxySQL 3.0 Setup & Configuration

How to connect to the Postgres DB?

When you use ProxySQL to manage PostgreSQL traffic, it typically uses two TCP ports to separate admin and client traffic,

Installation Steps (MySQL & PostgreSQL)

• Install ProxySQL
• Start service
• Access admin interface (Port 6132)

Step-by-Step Guide to Configure ProxySQL for PostgreSQL (Using Ports 6132/6133)

1. Connect to ProxySQL Admin Interface (port 6132)

psql -u admin -padmin -h 127.0.0.1 -P 6132

2. Add Backend PostgreSQL Servers

INSERT INTO pgsql_servers (hostgroup_id, hostname, port, status, comment) 
VALUES 
(1, '172.17.0.9', 5432, 'ONLINE', 'primary'),
(2, '172.17.0.3', 5432, 'ONLINE', 'replica');

3. Add PostgreSQL Application User

INSERT INTO pgsql_users (username, password, default_hostgroup) 
VALUES ('appuser', 'appuser', 1);

The password must match the user's password in the actual PostgreSQL backend.
 

4. Save to Runtime and to Disk
Apply the changes so they take effect:

-- Save changes to runtime
LOAD PGSQL SERVERS TO RUNTIME;
LOAD PGSQL USERS TO RUNTIME;

-- Persist to disk
SAVE PGSQL SERVERS TO DISK;
SAVE PGSQL USERS TO DISK;

5. View Current Config

select * from pgsql_servers;
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 172.17.0.9 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | primary |
| 2            | 172.17.0.3 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | replica |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

select * from pgsql_users;
+---------------+----------+--------+---------+-------------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username      | password | active | use_ssl | default_hostgroup | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+---------------+----------+--------+---------+-------------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| appuser       | appuser  | 1      | 0       | 1                 | 1                      | 0            | 1       | 1        | 10000           |            |         |
+---------------+----------+--------+---------+-------------------+------------------------+--------------+---------+----------+-----------------+------------+---------+

Connecting PostgreSQL Client Through ProxySQL and Querying Data

1. Connect PostgreSQL Client to ProxySQL

Use psql to connect through ProxySQL’s PostgreSQL listener port 6133:

psql -h 127.0.0.1 -p 6133 -U appuser -d postgres

2. Run a Sample Query

Once connected, you can execute any SQL query, just like you'd do directly against PostgreSQL:

Mafiree@Proxy_Machine:~$ psql -h 127.0.0.1 -p6133 -U appuser -d postgres -c "select * from sports;"
Password for user appuser: 
id | sport_name | team_name | player_name | position | team_city | founded_year | championships_won 
----+------------+-----------------------+--------------------+---------------+---------------+--------------+-------------------
1 | Basketball | Los Angeles Lakers | LeBron James | Forward | Los Angeles | 1947 | 17
2 | Basketball | Golden State Warriors | Stephen Curry | Guard | San Francisco | 1946 | 7
3 | Football | New England Patriots | Mac Jones | Quarterback | Foxborough | 1959 | 6
4 | Football | Kansas City Chiefs | Patrick Mahomes | Quarterback | Kansas City | 1960 | 3
5 | Baseball | New York Yankees | Aaron Judge | Right Fielder | New York | 1903 | 27
6 | Baseball | Los Angeles Dodgers | Mookie Betts | Right Fielder | Los Angeles | 1883 | 7
7 | Soccer | Manchester United | Bruno Fernandes | Midfielder | Manchester | 1878 | 20
8 | Soccer | FC Barcelona | Robert Lewandowski | Striker | Barcelona | 1899 | 26
9 | Hockey | Toronto Maple Leafs | Auston Matthews | Center | Toronto | 1917 | 13
10 | Hockey | Chicago Blackhawks | Patrick Kane | Right Wing | Chicago | 1926 | 6
11 | Hockey | Toronto Raptors | James Winner | Forward | Toronto | 2000 | 1
12 | Hockey | Toronto Raptors | James Winner | Forward | Toronto | 2000 | 1
(12 rows)

Basic Configuration: Backends & Users

• Define backend servers
• Create users
• Assign hostgroups

Read-Write Split Configuration (Step-by-Step)

• Create query rules
• Match SELECT queries
• Route to replica hostgroup

Major and minor enhancements for MySQL introduced in ProxySQL

In addition to PostgreSQL features, we will now review the major and minor enhancements for MySQL introduced in ProxySQL.

1. Dual Password Support

You can now configure two passwords for the same MySQL user—a primary and a secondary. This is extremely useful for zero-downtime password rotation in production environments.
 

• No need to update the password everywhere at once
• Avoids breaking running connections
• Seamless switchover without affecting the app

mysql> INSERT INTO mysql_users (
-> username,
-> password,
-> attributes,
-> default_hostgroup,
-> transaction_persistent,
-> active
-> ) VALUES (
-> 'appuser',
-> CACHING_SHA2_PASSWORD('new_pass'), -- New password
-> JSON_OBJECT('additional_password', HEX(CACHING_SHA2_PASSWORD('old_pass'))), -- Old password
-> 1,
-> 1,
-> 1
-> );
Query OK, 1 row affected (0.01 sec)

Test connection with current password

mysql -u appuser -p -h 172.17.0.4 -P 6033
# enter password: new_pass

Test connection with new password

mysql -u appuser -p -h 172.17.0.4 -P 6033
# enter password: old_pass

It should also connect successfully because of the additional_password attribute.

2. Enhanced Event and Query Logging

Logging has been completely upgraded in 3.0. ProxySQL now uses:

• A circular buffer for real-time capture
• An in-memory SQLite engine for fast search
• Optional persistence to disk

This is perfect for:

• Real-time auditing
• Debugging slow queries
• Tracking what’s happening in production without hitting the database

3. Prepared Statement Parameter Logging

Previously, if your app used prepared statements, the logs only showed the query skeleton—not the actual values.
Now in 3.0, ProxySQL logs the real parameter values, so you can:

• See the exact SQL being executed
• Debug easily
• Spot problematic inputs in real-time

{
"hostgroup_id": 3,
"thread_id": 196854,
"event": "COM_STMT_EXECUTE",
"username": "Mysql_app",
"schemaname": "Sports",
"client": "192.168.72.44",
"server": "172.17.0.4",
"rows_affected": 3,
"query": "SELECT * FROM sports.players WHERE age > ?",
"starttime_timestamp_us": 1758000000000000,
"starttime": "2025-06-02 08:30:00.000000",
"endtime_timestamp_us": 1758000000100000,
"endtime": "2025-06-02 08:30:00.100000",
"duration_us": 100000,
"digest": "0xabcdef1234567890",
"client_stmt_id": 101,
"parameters": [
{
"type": "INT",
"value": "25"
}
]
}

ProxySQL Best Practices & Performance Tuning

Connection Pool Sizing & Optimization

• Avoid too many backend connections
• Tune per workload

Query Caching Strategy for Your Workload

• Cache only stable queries
• Avoid transactional queries

Health Checks & Monitoring for Reliability

• Monitor replication lag
• Enable query logs
• Track slow queries

What Can Go Wrong with ProxySQL (And How to Prevent It)

Common mistakes:

• Misconfigured query rules → wrong routing
• Over-aggressive caching → stale data
• Ignoring replica lag → inconsistent reads

Solution:

• Proper testing
• Monitoring
• Gradual rollout

ProxySQL 3.0 vs PostgreSQL vs SQL Server Performance

When comparing postgresql vs sql server performance, ProxySQL adds an extra layer of optimization:

• Better read scaling via replicas
• Query-level routing
• Reduced connection overhead

This often gives PostgreSQL clusters a performance edge in read-heavy workloads.

Ready to Implement ProxySQL 3.0?

Common Implementation Challenges & How Mafiree Solves Them

• Query routing complexity → Pre-built rule templates
• Performance tuning → Workload-based optimization
• Failover handling → Automated health checks