/ #Databases #Erlang 

Erlang Postgres Connection Pool with Episcina

Almost exactly a year ago I was looking to merge the many forks of Will Glozer’s Postgres client for use in a project at Heroku. Instead Semiocast released their client, I gave it a try and never looked back. (But note that David Welton, a braver person than me, is working on merging the forks of epgsql at this time). I found Semiocast’s client to be clean, stable and I liked the interface better.

At the same time I was in the need of a connection pooler. Many have relied on poolboy or pooler for this purpose but neither actually fits the use case of connection pooling that well. Luckily Eric and Jordan were in the need at the same time and created the Erlware project episcina, which they based off of Joseph Wecker’s fork of Will Glozer’s epgsql pool. Episcina differs in that it is purely for connection pooling, it is not for pooling workers and it is not for pooling generic processes.

Here I’ll show how I combined the two in a simple example.

To start we have a sys.config file to configure episcina:

{episcina, [{pools, [{primary,
[{size, 10},
{timeout, 10000},
{connect_provider, {pp_db, open,
[[{host, “localhost”}
,{database, “postgres_pool”}
,{port, 5432}
,{user, “postgres”}
,{password, “password”}]]}},
{close_provider, {pp_db, close, []}}]}]

A key thing to note here is the connect and close providers are function calls to modules within the project and not the Postgres client. Episcina requires a return value of {ok, pid()} and the Semiocast client returns {pgsql_connection, pid()}, so we wrap the connection calls to get around that:
-spec get_connection(atom()) -> {pgsql_connection, pid()} | {error, timeout}.
get_connection(Pool) ->
case episcina:get_connection(Pool) of
{ok, Pid} ->
{pgsql_connection, Pid};
{error, timeout} ->
{error, timeout}

-spec return_connection(atom(), {pgsql_connection, pid()}) -> ok.
return_connection(Pool, {pgsql_connection, Pid}) ->
episcina:return_connection(Pool, Pid).

-spec open(list()) -> {ok, pid()}.
open(DBArgs) ->
{pgsql_connection, Pid} = pgsql_connection:open(DBArgs),
{ok, Pid}.

-spec close(pid()) -> ok.
close(Pid) ->
pgsql_connection:close({pgsql_connection, Pid}).

And here is the query function to get a connection and return it to the pool after completion:

query(string()) -> tuple().
query(Query) ->
C = get_connection(primary),
pgsql_connection:simple_query(Query, [], infinity, C)
return_connection(primary, C)

This example project uses relx to build a release which will start episcina on boot:
{release, {postgres_pool, “0.0.1”},

{sys_config, “./config/sys.config”}.
{dev_mode, true}.

{include_erts, true}.
{extended_start_script, true}.

Boot the release to an interactive shell and play around:

λ _rel/bin/postgres_pool console  
([email protected])1> pp_db:query("SELECT 1").