Built-in Connection Manager Turns Key  PostgreSQL Weakness into a Strength

We recently announced the release of YugabyteDB 2.19, which turbocharges PostgreSQL with a number of powerful new innovations. Bookmark our site because in the coming weeks, we’ll take a deeper look into each of these new features, starting with a closer look at a major improvement to connection pooling.

The latest release of YugabyteDB, version 2.19.2, previews a new built-in Connection Manager that turns a known PostgreSQL weakness into a major strength—improving performance and simplifying the application architecture. YugabyteDB’s Connection Manager enables up to 20x faster connection creation times and increases the number of supported connections up to 5x—all while maintaining a constant throughput.

Connection Manager is currently available for the YSQL API and is available as a technical preview in 2.19. Read on for more details, or watch our brief demo video showcasing the new connection manager in action.

Known Connection Challenges with PostgreSQL

If you’ve worked with PostgreSQL for a while, then you’re probably familiar with the well-documented challenges that arise when managing connections to the database.

Standard PostgreSQL creates one backend process for every connection to the database. These backend processes consume memory and CPU, limiting the number of connections the database can support.

In the past, Yugabyte’s YSQL API faced these same challenges because, by reusing the PostgreSQL query layer for our upper half, we inherited the same limiting backend processes.

The primary way you address this PostgreSQL issue now is by using an external connection pooler. This allows for the multiplexing of multiple client connections to a smaller number of actual server connections. As a result, you can support a larger number of connections from applications. PgBouncer and Odyssey are popular PostgreSQL-based server-side connection pooling mechanisms and are both fully compatible with YugabyteDB.

However, these products have some limitations:

• PgBouncer does not support prepared statements in the transaction pooling mode
• Both Odyssey and PgBouncer do not support SET statements in the transaction pooling mode

Read: Improving PostgreSQL. Overcoming the Tough Challenges with YugabyteDB Solutions>>>

New Built-in YSQL Connection Manager

In a first for a distributed SQL database, YugabyteDB 2.19 introduces a built-in connection pooler, the YSQL Connection Manager. This transforms a well-known PostgreSQL challenge into a major advantage by providing the same connection pooling advantages as other pooling solutions, but without the mentioned limitations.

The new native connection pooling uses logical connections to guarantee consistent database connectivity for applications. Since the manager is bundled with the product, it is convenient to manage, monitor, and configure the server connections without additional third-party tools. When combined with Smart Drivers, YugabyteDB greatly simplifies application architecture and accelerates developer productivity.

Key Features YSQL Connection Manager

The new Connection Manager is a modified version of the open source connection pooler Odyssey. It uses Odyssey in the transaction pooling mode and has been modified at the wire protocol level for tighter integration with YugabyteDB to overcome some SQL limitations.

YSQL Connection Manager has the four key features:

1. No SQL limitations: Unlike other pooling solutions running in transaction mode, YSQL Connection Manager supports SQL features such as TEMP TABLE, WITH HOLD CURSORS, and more.
2. Single pool per database: PgBouncer and Odyssey create a pool for every combination of users and databases. This significantly limits the number of users that can be supported, impacting scalability. In contrast, YSQL Connection Manager creates one pool per database. All connections trying to access the same database share the same single pool meant for that database.
3. Support for session parameters: YSQL Connection Manager supports SET statements, which are not supported by other connection poolers.
4. Support for prepared statements: Odyssey supports protocol-level prepared statements and YSQL Connection Manager inherits this feature.

Testing Connection Pooling: YugabyteDB vs. PostgreSQL

As part of the keynote at Distributed SQL Summit 2023, we demonstrated the new Connection Manager and showcased the results of a number of tests.

One test measured how many concurrent connections could be supported by YugabyteDB 2.19 compared to standard PostgreSQL. Both were run on the same hardware and tested with the same workload.

In the first test, we measured how long it took PostgreSQL and YugabyteDB to create different numbers of connections. With 250 connections, YugabyteDB created the connections about 5x faster. As that number scaled up, the newly built-in connection manager showed increasingly better results, creating 6,000 connections in our final test 20x faster than PostgreSQL.

Another test involved spinning up four workloads on the two different databases. The workloads were started one after the other over time to examine if the databases could deliver the necessary connections. The results showed that PostgreSQL maxed out at around 6,000 connections. As additional workloads were added, PostgreSQL reported errors and was unable to scale further.

YugabyteDB Advantage Compared to PostgreSQL

YugabyteDB demonstrated two key advantages in this test, which you can see in the results chart below.

1. YugabyteDB was able to scale faster than PostgreSQL to the first 6,000 connections needed for Workload One.
2. As the additional workloads were run, YugabyteDB seamlessly increased the number of connections. It delivered up to 5x more connections than standard PostgreSQL.

Finally, while performing these tests we also tracked the throughput being handled by the system. The throughput fluctuated only slightly as the connection count increased. You can see from the chart below that YugabyteDB maintained a steady throughput while going from 100 concurrent connections to 25,000.

How to Use the YSQL Connection Manager

To start a YugabtyeDB cluster with YSQL Connection Manager, set the yb-tserver flag enable_ysql_conn_mgr flag to true.

When enable_ysql_conn_mgr is set, each YB-TServer starts the YSQL Connection Manager process along with the PostgreSQL process. You should see one YSQL Connection Manager process per YB-TServer.

To create a single-node cluster with YSQL Connection Manager using yugabyted, use the following command:

./bin/yugabyted start --tserver_flags "enable_ysql_conn_mgr=true,allowed_preview_flags_csv=enable_ysql_conn_mgr" --ui false

Because enable_ysql_conn_mgr is currently a preview flag only, to use it, add the flag to the allowed_preview_flags_csv list (that is, allowed_preview_flags_csv=enable_ysql_conn_mgr).

Learn More

Please read the YSQL Connection Manager documentation for more information.

If you’re interested in testing these new features available in YugabyteDB 2.19.2, you can download the open source code today. For additional insights into YugabyteDB 2.19, check out our keynote from  Distributed SQL Summit 2023.