Azure Database for PostgreSQL - Latency comparison between PGBouncer and Npgsql

Introduction:

In the process of migrating from Oracle to PostgreSQL, one of the critical considerations for customers is the management of database connections, especially in a cloud environment like Azure. Azure Database for PostgreSQL Flexible Server introduces PGBouncer, a built-in connection manager, which can be utilized for managing database connections efficiently. Additionally, Npgsql, an open-source ADO.NET Data provider for PostgreSQL, allows programs written in C# to access Azure Database for PostgreSQL Flexible Server and offers functionality akin to PGBouncer. It is a free and open-source resource that supports connection pooling.

Recently, we encountered a scenario where a customer, in the midst of migrating from Oracle to PostgreSQL, was evaluating their connection pooling options. They were uncertain whether to leverage the built-in connection pooler (PGBouncer) provided by Azure or to utilize the connection pooling capabilities of Npgsql for .NET applications interacting with PostgreSQL.

We decided to run some tests to see what is the latency impact of both options in order to better answer the question posed and we would like to share the results in this post.

The setup:

We are using an Azure Database for PostgreSQL instance created in AZ1 in the West Europe region.

The .NET 6.0 application is also deployed on an Azure VM in AZ1 in the West Europe region.

Test C# code setup snippet:

using Npgsql;

var connstring = "Server=serverconnection;Username=username;Database=postgres;Port=port;Password=pass;SSLMode=Prefer;Pooling=False";

Console.WriteLine(connstring);

using (var conn1 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn1.Open();
    Console.WriteLine("Time spent waiting for the 1 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);

}

using (var conn2 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn2.Open();
    Console.WriteLine("Time spent waiting for the 2 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);  

}

using (var conn3 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn3.Open();
    Console.WriteLine("Time spent waiting for the 3 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);   

}

using (var conn4 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn4.Open();
    Console.WriteLine("Time spent waiting for the 4 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);   

}

using (var conn5 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn5.Open();
    Console.WriteLine("Time spent waiting for the 5 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);  

}

using (var conn6 = new NpgsqlConnection(connstring))
{
    var timer = System.Diagnostics.Stopwatch.StartNew();
    conn6.Open();
    Console.WriteLine("Time spent waiting for the 6 connection to open: {0}ms {1} elapsed time", timer.ElapsedMilliseconds, timer.Elapsed);   

}

Console.ReadLine();

Test 1: No connection pool (Baseline)

Test 2: Using PGBouncer as connection pooling method:

You can connect to PGBouncer on Azure Database for PostgreSQL Flexible Server by connecting through port 6432. You can read more about PGBouncer here and you can find default connection pool settings for PGBouncer in Azure Database for PostgreSQL Flexible Server here.

Test 3: Using Npgsql as connection pooling method:

You can use this connection method by setting the Pooling Flag in the connection string to True.

Results

From the test runs above we can see that the latency is lower using Npgsql as the connection polling method and this is expected as the connection pool resides directly in the same server as the application. PGBouncer however may be a more viable choice if the slightly higher latency is acceptable, as it is seamlessly integrated with Azure Database for PostgreSQL Flexible Server, so there is no need for a separate installation as is the case with Npgsql.

A few other considerations would be:

1. If there are multiple apps connecting to the database, then pgbouncer would be a better choice as you can share connections between apps.
2. In case of failover, pgbouncer would be automatically restarted in the standby.

No matter which option you decide to use, you should thoroughly test your application and ensure you are using one of the above methods in order to not encounter connection exhaustion issues.

Feedback and Suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!