Companies looking at PostgreSQL as a production database alternative to Oracle typically want their web applications to scale quickly as those expensive commercial alternatives. The competitive modern business landscape – most notably the fickle online customer — demands high performance, and will look elsewhere when encountering a slow eCommerce site. Ensuring high scalability when using Postgres is a must!
Thankfully, the robust open source community around PostgreSQL developed two tools aimed to boosting the database’s performance on high traffic websites. Called PgBouncer and Pgpool, they need to be considered as part of any Postgres implementation. Let’s take a closer look.
PgBouncer serves as a connection pooler for Postgres. It is a lightweight tool known for a small footprint with minimal overhead. The program simply caches connections to different database servers. Its ability to manage a pool of database connections limits the number of actual connections to each Postgres instance; boosting the overall performance during a high traffic scenario.
Focusing specifically on connection pooling is one of the reasons PgBouncer is able to keep its small footprint. As such, it is not usable for other vital web database functionality, such as load balancing or replication. Pgpool is an option for handling those functions in addition to connection pooling.
Pgpool – now in its second major iteration, so Pgpool-II – is more of a Swiss Army knife of Postgres middleware functionality. In addition to connection pooling, it also performs load balancing, parallel query processing, replication, and failover handling – vital features for any scalable web application database. All client access to a PostgreSQL instance (or multiple instances) is managed through the Pgpool middleware.
Replication and load balancing are essential for any web application receiving high volumes of traffic. Load balancing lets Pgpool-II seamlessly distribute SELECT statements across multiple replicated database servers; improving overall system throughput as a result. As middleware, it mimics the API of Postgres, so a client application connects with Pgpool-II in a similar manner as any PostgreSQL server.
Many system architects and/or database administrators probably wonder whether PgBouncer or Pgpool makes the most sense in their database application. Maybe using both is the best solution? The following database administration blog looks at this question while providing an overview of both tools.
PgBouncer by itself is the wisest choice if connection pooling is the only major need. Maybe a separate load balancer is already in place? This rule also applies to applications hosted in resource-constrained environments where quick database calls with low throughput happen frequently.
Pgpool naturally makes sense if you need its additional features, like replication and load balancing, combined with connection pooling functionality. Both tools work well together in applications where many database connections are expected and PgBouncer’s lower overhead in regards to pooling helps the overall performance.
Whether choosing Pgpool, PgBouncer, or a combination of the two, there’s no denying both tools remain valuable options for any high traffic PostgreSQL application.
Keep returning to the Betica Blog for additional information, advice, and insights from the rich world of software development. Thanks for reading!