PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development. It is widely adopted, globally developed by hundreds of people, and is continually innovating. Not surprisingly, there are a variety of tools out there to help with using PostgreSQL. To gain a better understanding of the competitive landscape of some popular tools and their adoption rate, we recently conducted a poll on the download thank you page of the EDB website. The survey was run from November 16-18, and the response has been overwhelming; we received nearly 3,000 responses in only three days.
The survey was broken up into three sections: connection pooler, backup tool, and monitoring. Participants provided information about their choice of supporting components for each category, and we’ve shared the results below.
Findings
Connection Poolers
We considered the following connection poolers as potential favorites for maintaining a high availability architecture among PostgreSQL:
- Pgpool, a connection pooler and router software that sits between PostgreSQL servers and a PostgreSQL database client. It provides a wide range of features like load balancing and query routing combined with connection pooling functionality. For connecting pooling, it maintains established connections to the PostgreSQL server and reuses them whenever a new connection with the same properties comes in. Pgpool reduces the connection overhead and can improve the system’s overall throughput.
- PgBouncer is a lightweight connection pooler for PostgreSQL. It’s a very simple utility that focuses specifically on connection pooling. PgBouncer maintains a pool of connections for each database and user combination. When a database connection is made by an authenticated client, PgBouncer reuses a connection from the pool (if available); when a new client connection authenticates, PgBouncer adds a new connection for use by that client. When a client disconnects, Pgbouncer returns the connection to the pool for re-use.
- Pgagroal, a high-performance protocol-native connection pooler for PostgreSQL. It is built upon libev, a high-performance network library, and uses a shared-memory process model together with atomic operations for tracking the state to achieve its high performance.
- Odyssey is a PostgreSQL connection pooler, designed to use a multi-core and multi-thread model for pool. In Odyssey, each worker thread is responsible for authentication and proxying client-to-server and server-to-client requests. All worker threads share global server connection pools.
- Application connection poolers such as Hikari and JDBC connection pooler, manage the connections at the application side instead of managing the connections at the database side. Connection poolers also reduce the overhead of making new connections to the database and increase the application's performance by reusing the available connections.
- Others - please specify.
Connection Pooler Survey Results
Connection poolers are very useful for applications. It reduces the cost of making new connections every time an application needs interaction with the database. Also, it helps in reusing the established connection without adding the overhead of idle connections to the database. 35.9% of responders said that Pgpool is used in their organization for PostgreSQL high availability architecture, followed by application connection pooler (20.6%), PgBouncer (19.8%), Pgagroal (12.2%), and Odyssey (11.4%). It is worth noting that 7.4% of responders chose both Pgpool and Pgbouncer as their choice. It’s quite common to use both together, using PgBouncer to provide connection pooling, which in turn talks to a Pgpool instance that provides high availability and load balancing.
Backup Tools
In the backup tool category, we provided the following tools as a selection choice:
- pgBackrest, a reliable, easy-to-use backup and restore solution that can seamlessly scale-up to the largest databases by utilizing algorithms that are optimized for database-specific requirements.
- pgBarman is an open-source administration tool for disaster recovery of PostgreSQL servers. It allows your organization to perform remote backups of multiple servers in business-critical environments to reduce risk and help DBAs during the recovery phase. pgBarman is maintained by 2ndQuadrant, an EDB company.
- EDB BART (Backup and Recovery Tool) is an administrative utility, developed by the EDB team, providing simplified backup and recovery management for multiple local or remote EDB Postgres Advanced Server and PostgreSQL database servers.
- pg_basebackup, a command that comes with postgreSQL, allows users to take online and consistent file system level backup. These backups can be used both for point-in-time recovery and as the starting point for a log-shipping or streaming-replication standby server.
- Snapshot level backups, a type of backup copy used to recreate the entire architectural instance/copy of an application, disk, or system. A snapshot level backup is used in backup processes to restore the system or disk of a particular device at a specific time. A snapshot backup can also be referred to as image backup.
- Others - please specify.
Backup Tool Survey Results
Nearly ⅓ (33.3%) of the 2803 responders said pg_basebackup is their backup tool of choice for their PostgreSQL database, followed by pgBackrest (22.7%), Snapshot level backups (17.5%), pgBarman (13.3%), and EDB Bart (12%). 0.7% of responders selected the “Others - please specify” option and described using pg_dump for backup.
Monitoring Tools
For monitoring tools, we provided the following tools as a selection choice:
- Postgres Enterprise Manager (PEM), a Postgres management tool to combine monitoring, alerting, and tuning of multiple open source PostgreSQL and/or EDB Postgres Advanced Server clusters, whether on-premise or in the cloud in a single GUI. PEM is provided by EDB.
- Datadog, a monitoring service for cloud-scale applications, providing monitoring of servers, databases, tools, and services through a SaaS-based data analytics platform.
- PGObserver, a monitoring solution for PostgreSQL databases. It covers nearly all metrics provided by the database engine’s internal statistics collector and works out of the box with all PostgreSQL versions (beginning with 9.0) as well as AWS RDS.
- NewRelic, provides a connected, real-time view of all of your operational data in one place. The New Relic PostgreSQL in-house integration receives and sends metrics from your PostgreSQL instance to the New Relic platform, where you can aggregate and visualize key performance metrics.
- Prometheus and Graphana with Postgres data collector - Prometheus is a convenient tool to collect and store metrics of multiple PostgreSQL instances; Grafana is a popular choice for visualization and analytics of time series data.
- Other - please specify
Monitoring Tool Survey Results
Postgres Enterprise Manager received the highest response, with nearly half of the responders (46%) using it to monitor Postgres instances, followed by Datadog (17.2%), Prometheus and Graphana with Postgres data collector (13%), NewRelic (12%), and PGObserver (11.8%).
What’s Next
We hope you find this blog post provides a good overview of the poll the EDB team conducted and the results. Following this blog, we will be publishing a series of blogs that cover a detailed feature comparison and analysis of each selected tool for connecting pooling, backup, and monitoring. Please stay tuned and Power to Postgres.
This blog was co-authored by Hope Jiang and Vibhor Kumar of EDB.