eysql

Topology-aware load balancing and failover for epgsql, for YugabyteDB and PostgreSQL.

YugabyteDB ships "smart drivers" for Java, Go, Python, Node.js, C#, Rust and Ruby. They discover the cluster's servers, spread connections across them, prefer servers in your zone, and skip servers that fail. Nothing like that existed for Erlang. eysql adds it on top of epgsql, without replacing epgsql: your connections are ordinary epgsql connections.

• Discovery. With load_balance on, reads yb_servers() at start, on a timer and after any connection failure.
• Load balancing. Opens each connection to the server where this client holds the fewest connections.
• Topology preference. Prefers servers in the zones or regions you list, falling back level by level.
• Failover. Leaves a server it cannot connect to out until a refresh finds it back, and replaces a dropped connection on another server.
• A pool. Keeps a fixed number of connections open. It recycles them after a maximum lifetime and rebalances them when servers join or leave. Like the drivers, it sends no queries of its own.
• Transactions. Retries serialization failures and deadlocks, and reports an unknown COMMIT outcome instead of guessing.
• PostgreSQL. Works with the configured hosts, and can pick the writable primary among several.

It is written in plain Erlang with epgsql as its only dependency, so Erlang, Elixir and Gleam projects can all use it.

Install

rebar3:

{deps, [{eysql, "~> 0.1"}]}.

Mix:

{:eysql, "~> 0.1"}

Gleam:

gleam add eysql

Use

{ok, Pool} = eysql:start_link(#{hosts => ["yb-tservers.db.svc.cluster.local"],
                               username => <<"app">>,
                               password => <<"secret">>,
                               database => <<"app">>,
                               load_balance => true,
                               topology_keys => "gcp.us-east1.us-east1-b:1,gcp.us-east1.*:2"}),

%% One statement.
{ok, _Columns, Rows} = eysql:equery(Pool, "SELECT id, name FROM accounts WHERE realm = $1", [Realm]),

%% Several statements on one connection. eysql_conn:equery/3 and squery/2
%% are epgsql's, bounded by socket_timeout; epgsql's own functions work on
%% the connection too, without the bound.
eysql:with_connection(Pool, fun(Conn) ->
    {ok, _, _} = eysql_conn:equery(Conn, "SELECT ...", []),
    eysql_conn:equery(Conn, "SELECT ...", [])
end),

%% A transaction, retried on serialization failure.
{ok, Id} = eysql:transaction(Pool, fun(Conn) ->
    case eysql_conn:equery(Conn, "INSERT INTO t (name) VALUES ($1) RETURNING id", [Name]) of
        {ok, 1, _, [{Id}]} -> {ok, Id};
        {error, _} = Error -> Error
    end
end).

Under a supervisor, eysql:child_spec(my_db, Options) gives a pool registered as my_db.

Elixir:

{:ok, pool} = :eysql.start_link(%{hosts: [~c"yb-tservers"], username: "app", password: "secret", database: "app",
                                  load_balance: true})
{:ok, _cols, rows} = :eysql.equery(pool, "SELECT 1", [])

Options

The load-balancing options keep the smart drivers' names and units (seconds). Every other duration is in milliseconds. An option with a counterpart in the YugabyteDB JDBC driver defaults as it does there, so load balancing is off until you set load_balance.

Text, such as username, a host or topology_keys, is a string or a UTF-8 binary. A binary that is not UTF-8 is an invalid option.

There are no health check options. health_check_interval and health_check_timeout are unknown options, and naming one is an error. Set socket_timeout so a query on a server that has died silently fails instead of hanging.

TLS

ssl => required encrypts every connection and fails if the server does not offer TLS. ssl => true goes on unencrypted when the server does not offer TLS, as libpq's sslmode=prefer does. When the server does offer TLS, a failed certificate check fails the connection with either setting.

eysql passes ssl_opts to epgsql, adding only the name to check, and never turns certificate checks off. The ssl application in OTP 26 and later verifies the server's certificate by default, so TLS needs a CA to check it against:

ssl => required,
ssl_opts => [{cacertfile, "/etc/ssl/certs/yugabyte-ca.crt"}]

For the operating system's trust store, use {cacerts, public_key:cacerts_get()} instead. Without a CA, OTP refuses to connect, and every connection fails with:

{error, {ssl_negotiation_failed, {options, incompatible, [{verify, verify_peer}, {cacerts, undefined}]}}}

With a CA, OTP checks the certificate chain, then the server's name, as libpq's and pgjdbc's sslmode=verify-full do: the certificate must list the host each connection dialled as a subject alternative name. That is the seed host for the first connection, and each server's host or public_ip from yb_servers() after that, so on YugabyteDB every server's certificate lists its own name and the seed's. A certificate can list several names; the check passes when the one dialled is among them. A host given as an IP address is checked against the address, so the certificate must list that address.

epgsql starts TLS on a TCP connection it has already opened, which leaves OTP knowing the server only by its address. eysql therefore passes the host it dialled, when that is a name, as the connection's server_name_indication, which OTP both sends and checks. A certificate that fails the check fails the connection with a TLS alert that includes hostname_check_failed.

To check something else, set server_name_indication in ssl_opts yourself. It then applies to every server:

• {server_name_indication, "yb.example.com"} checks that one name on every server, so it suits a certificate the servers share.
• {server_name_indication, disable} checks the chain only, as libpq's sslmode=verify-ca does.

To encrypt without verifying, as libpq's sslmode=require does, set {verify, verify_none} yourself. The connection then accepts any certificate, so nothing stops a man in the middle.

PostgreSQL

With PostgreSQL, yb_servers() does not exist. With load_balance off, the default, eysql never asks for it. With it on, eysql notices on the first discovery and uses the configured hosts from then on, in the order given, whatever the mode. With several hosts and target_session_attrs => read_write, it connects to the first that accepts writes.

Preparing connections

after_connect runs on each connection the pool opens, before the pool hands it to anyone. On YugabyteDB a new connection is a new backend, and its first statements wait while it loads the catalog entries they need from the masters. A hook that runs the application's hot statements once keeps that wait off the first caller.

{ok, Pool} = eysql:start_link(#{hosts => ["yb-tservers"],
                               load_balance => true,
                               after_connect => {my_db, warm, []}}).

%% In my_db. Each statement runs once, with parameters that match no row,
%% so the backend parses and plans it: it loads what the planner reads, such
%% as the table's indexes and statistics, as well as the names the parser
%% looks up. epgsql:parse/2 alone would warm only the parser's lookups.
warm(Conn) ->
    lists:foreach(fun({Sql, Params}) -> {ok, _, _} = eysql_conn:equery(Conn, Sql, Params) end,
                  [{"SELECT id, doc FROM accounts WHERE id = $1", [0]},
                   {"SELECT id FROM devices WHERE account_id = $1 AND name = $2", [0, <<>>]}]).

• It runs once on every connection the pool opens: the first ones, and those that replace a connection that died, reached max_lifetime or was moved by a rebalance. It does not run on the connections that discovery and probes open, nor on those eysql_cluster:connect/1 opens.
• It runs in a process of its own, once the connection has opened and statement_timeout is set, so statement_timeout applies to its statements. socket_timeout does not, since nothing has checked the connection out. after_connect_timeout bounds the whole hook instead. Its default of a minute leaves room for tens of statements that each wait on the masters, and frees the connection's place in the pool within a minute when a server hangs during the hook.
• Until it returns, stats/1 counts the connection as opening, and checkouts wait for it.
• ok, or a tuple whose first element is ok, such as {ok, _} or epgsql's {ok, Columns, Rows}, lets the connection into the pool. Anything else fails it: {error, Reason}, an exception, another value, or still running after after_connect_timeout. So does a connection the hook leaves closed or inside a transaction. A multi-statement squery returns a list of results, so a hook that ends with one fails; end it with ok.
• On a failure eysql closes the connection. The failure's reason, Reason below, is the reason the hook returned, {Class, Reason} for an exception, {bad_return, Value}, {after_connect_timeout, Ms}, {connection_lost, _} or {transaction_status, Status}.
• A failing hook marks no server: the failure is the application's, not the server's, and leaving servers out for it would let one bad statement take every server out of rotation. The pool steers its own connections instead, but only between the servers it would choose among anyway. A hook failure never changes the topology level, node type or host order a connection goes to.
• For failed_host_reconnect_delay_secs after a hook fails on a server, at least a second, the pool's new connections go to the other servers of that same choice, and the failed connection is replaced at once on one of them. Rebalancing moves no connection towards the server meanwhile. After the delay, new connections and rebalancing try it again: a rebalance moves up to rebalance_batch connections towards it, which open elsewhere if the hook still fails there and stay once it passes. A hook that fails on one of several servers to choose among thus costs, after each delay, a few connects there, and closes up to rebalance_batch idle connections elsewhere to make them, while no checkout fails for it. With the defaults that is about four connects a minute for as long as the hook keeps failing there.
• With a single server to choose, as with load_balance off, where every connection goes to the first host that works, or with one server in the preferred topology level, there is nothing to steer to, and the pool keeps retrying that server. The first failure of a run is replaced at once; from the second on, each failure is a failed open: the pool opens another a second later, as after a failed connect, and a checkout with no other connection to wait for fails with {error, {after_connect, Reason}}.
• The pool logs the first failure on a server as a warning with the reason, and for an exception the hook's own stack frames, without their arguments. It logs no further failures there until a connection to the server has passed the hook and a minute has gone by since the last failure, and then logs the end of the run at info.
• In a pool that lives across code upgrades, give {Module, Function, Args} or fun Module:Function/1. An anonymous fun fails with badfun once the module that made it has been upgraded twice, and from then on every new connection fails its hook. The pool prints such a hook as {Module, Function, redacted} in its status and crash reports, so a credential in Args stays out of them.

Without a pool

eysql_cluster does placement and failover for code that manages its own connections. connect/1 waits for the first discovery, then tries hosts as above, and returns the last connect error, or {error, {no_node_available, Type}} in the modes that refuse:

{ok, Config} = eysql_config:normalize(Options),
{ok, Cluster} = eysql_cluster:start_link(Config),
{ok, Conn} = eysql_cluster:connect(Cluster).   % linked to the caller, like epgsql:connect/1

Tests

rebar3 eunit                 # logic, against a fake driver
integration/run.sh           # PostgreSQL and a three-zone YugabyteDB cluster in Docker

The integration suite includes stopping a YugabyteDB node under load and starting it again. On both engines it also checks that a connection left inside a transaction, or a failed one, is closed rather than handed to the next holder, that a query in flight when its pool stops returns an error, that socket_timeout cuts off a pg_sleep and the pool recovers, and that every pooled connection, replacements included, runs after_connect before it serves a query. eysql_driver is the behaviour the fake driver implements; the driver option swaps it in.

Compatibility

• OTP 26 to 29.
• epgsql 4.7 and later.
• YugabyteDB: any release with yb_servers(), the function the smart drivers use. Tested against 2026.1.
• PostgreSQL: tested against 17.

Licence

Apache-2.0. See LICENSE.