Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

PostgreSQL native backend protocol

ProxySQL 3.1.12 and 4.0.12 use the native PostgreSQL backend protocol by default for new TCP connections to PostgreSQL. This lets applications fetch through named portals and receive asynchronous notifications while retaining ProxySQL’s query routing and connection management. ProxySQL 3.0 uses the libpq backend.

The setting controls the connection from ProxySQL to PostgreSQL. Applications can continue using ordinary PostgreSQL drivers, including libpq, to connect to ProxySQL. Backend addresses using Unix-domain sockets continue to use libpq.

Check and configure the backend preference

Run these commands through ProxySQL Admin, normally on port 6032:

SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name='pgsql-use_native_backend_protocol';

SELECT variable_name, variable_value
FROM runtime_global_variables
WHERE variable_name='pgsql-use_native_backend_protocol';

To enable the preference and retain it after restart:

SET pgsql-use_native_backend_protocol='true';
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

Existing pooled connections keep the protocol with which they were opened. Changing the setting does not convert them. When enabling native mode on an existing deployment, plan to drain and replace old backend connections before relying on native-only features. Reconnecting the application alone can reuse an old pooled connection. A planned ProxySQL restart replaces the pool but also interrupts other ProxySQL traffic.

Before disabling native mode, finish active named portals and shut down event subscribers. Their operations also depend on the current preference; keeping an old native connection open is not sufficient for a safe mode change.

Keep credentials and TLS configured

The backend still needs an ONLINE entry in pgsql_servers, suitable credentials in pgsql_users, and a PostgreSQL pg_hba.conf rule permitting the connection from ProxySQL. Keep your intended authentication policy when changing the backend preference. A stored MD5 secret cannot authenticate to a backend that requires SCRAM or the original password. The default frontend authentication policy requires SCRAM; do not weaken it simply to reuse an MD5 secret.

For SCRAM pass-through, the stored SCRAM secrets in ProxySQL and every target server must match exactly, including their salts, and the client must complete SCRAM authentication. Setting the same plaintext password independently can produce different salted secrets. Native authentication failures do not automatically retry through libpq. See PostgreSQL user management.

Native connections use the existing server use_ssl setting, per-server TLS parameters, and global TLS defaults. Enabling native mode does not enable TLS or hostname verification by itself. Configure certificate paths, client keys, and certificate trust using the PostgreSQL TLS settings. Existing connections retain their previous TLS configuration too.

Named portals

A PostgreSQL portal holds a bound statement’s parameters and execution position. For a driver that uses a named portal and a positive row limit, ProxySQL retains the backend while the result is suspended. The next fetch must use the same frontend connection and portal name.

Use an explicit transaction when the driver fetches across separate protocol Sync boundaries:

  1. Begin the transaction on the application connection.
  2. Use the driver’s extended-query API to bind a named portal and fetch the first batch.
  3. Consume each batch, then request the next rows from that same portal.
  4. Close the portal and commit, or roll back on failure.

A protocol Sync ends an implicit transaction when autocommit is in use; PostgreSQL then destroys its portals. A later fetch cannot resume that portal. An explicit transaction keeps it available across those boundaries. The unnamed portal does not provide resumable bounded fetching through this feature.

Choose a batch size and transaction duration that leave capacity for other clients. A client processing its last batch can still occupy a backend connection and hold a transaction open. Release both promptly when the job finishes. See Extended Query Protocol for prepared-statement and message behavior.

LISTEN and NOTIFY

Use a dedicated application connection for an event consumer. For example, connect to your PostgreSQL listener, replacing the endpoint, user, and database:

psql -h proxysql.internal -p 6133 -U event_worker -d app

On that connection:

LISTEN order_events;

From a second connection routed to the same database on the same PostgreSQL instance:

NOTIFY order_events, 'order-ready';

Commit the publisher’s transaction if it is not using autocommit. Configure routing so subscriptions and publishers reach the intended instance; notifications do not propagate to a separate replica through replication.

ProxySQL keeps the subscribed backend attached and forwards notifications even while the client has no active query. The application’s driver must continue reading the connection and consuming notifications. Keep the listener outside a long-running transaction so PostgreSQL can deliver notifications promptly.

To remove subscriptions and allow the connection to be pooled again, when no other session state prevents it:

UNLISTEN *;

Removing one channel with UNLISTEN channel_name leaves ProxySQL’s subscription pin in place. Account for each subscriber as an occupied backend connection, even when it is idle.

Recover from disconnects

Portal execution positions and subscriptions belong to a particular backend connection. ProxySQL does not migrate them to a replacement server after a failure. Restart interrupted exports according to the application’s retry policy. Event consumers must reconnect, subscribe again, and recover missed work from durable application data.

If LISTEN is rejected as unsupported, check the runtime preference and whether the endpoint uses Unix sockets or an old libpq connection from the pool. Finish existing work and replace that connection before retrying.