Azure Database for PostgreSQL - Latency comparison between PGBouncer and Npgsql

[HEADING=1]Introduction:[/HEADING]

Azure Database for PostgreSQL Flexible Server comes with PGBouncer - a built-in connection manager which you can leverage for connections. Npgsql is an open-source ADO .NET Data provider for PostgreSQL, it allows programs written in C# to access Azure Database for PostgreSQL Flexible Server and offers similar functionality as PGBouncer. It is a free and open source resource.

Recently one of our customers came with the question: Should I leverage the built in connection pooler (PGBouncer) or should I use the connection pooling provided by Npgsql - .NET access for 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.

[HEADING=1]The setup:[/HEADING]

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.

[HEADING=1]Test C# code setup snippet:[/HEADING]

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();

[HEADING=1]Test 1: No connection pool (Baseline)[/HEADING]

[HEADING=1]Test 2: Using PGBouncer as connection pooling method:[/HEADING]

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.

[HEADING=1]Test 3: Using Npgsql as connection pooling method:[/HEADING]

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

[HEADING=1]Results[/HEADING]

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.

[HEADING=1] [/HEADING]

[HEADING=1]Feedback and Suggestions[/HEADING]

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!

Continue reading...