PostgreSQL, also known as Postgres, is the most popular and advanced enterprise-grade open-source relational database software. It's a widely-used version of SQL. Without some form of editor or graphical user interface (GUI), the default means of interacting with PostgreSQL is through running SQL queries.
Traditionally you'd use the psql command-line interface (CLI) to create, update, query, and delete data from PostgreSQL or to manage the database. However, that approach can be inconvenient because:
- Information retrieved for a query involving several table columns is difficult to read, especially when displayed in the terminal window.
- There’s a considerable learning curve to get the most out of the PostgreSQL database. For example, you need to be an advanced user to optimally display table columns in an easy-to-read manner like the example above.
- It involves the repeated execution of multiple commands to list databases, tables, and views, query metadata information of indexes, and monitor databases using the CLI tool.
While you can use all the powerful capabilities of PostgreSQL through "raw" queries alone, using a PostgreSQL editor can create a more user-friendly experience that requires less in-depth knowledge of PostgreSQL.
Some users still prefer CLIs for interaction with PostgreSQL databases, particularly in the production environment. However, these numbers are diminishing as more GUI-based tools arise to serve the needs of PostgreSQL developers and administrators.
This article highlights two GUI tools: pgAdmin and DBeaver. We'll walk through how they can help you interact with and manage PostgreSQL databases as well as compare and contrast the two. We'll also quickly touch on an alternative platform called Airplane that can be used to make interacting with PostgreSQL databases even easier.
pgAdmin versus DBeaver
Before we dive into some of the differences between pgAdmin and DBeaver and when to use each, let's quickly walk through what each of these tools does.
What is pgAdmin?
pgAdmin is a free and open source GUI-based software that provides the following:
- PostgreSQL administration capabilities
- Monitoring tools such as dashboards for tracking metrics of configured PostgreSQL servers
- SQL query tool for faster data entry
- Debugger for troubleshooting
- and more
The beauty of pgAdmin is that it provides a user-friendly data administration interface for running SQL queries, entering data, and tracking metrics without using command line prompts.
This is what the pgAdmin UI looks like on the Dashboard page:
The source code and installable binaries of pgAdmin are accessible online since it's an open-source tool and distributed through the PostgreSQL license.
Additionally, pgAdmin software binaries are available for Windows, macOS, Ubuntu, Debian OS, Red Hat, and other Linux distributions. Even a containerized form of pgAdmin is available in the Docker Hub registry. You can install pgAdmin as a desktop-based GUI client for individual use or deploy it to a web server for browser-based access by multiple users.
As of September 2022, pgAdmin 4 is the latest distribution developed using Python and JavaScript/jQuery.
What is DBeaver?
DBeaver is another open-source database management tool that offers a powerful SQL editor with many features. It's built based on the Eclipse RCP platform. One of its most popular features is support for creating and managing SQL templates. These templates help you define and use recurring SQL statements in a script more productively.
DBeaver also offers PostgreSQL administration capabilities, the ability to generate mock data for testing, a dashboard and other monitoring tools, and features to easily compare schemas in the database. The screenshot below shows the DBeaver UI:
DBeaver is a universal database management tool that’s well-suited to developers who frequently work with data from different databases. Currently, DBeaver supports working with data from over 80 databases and is available for Windows, macOS, Ubuntu, Debian OS, Red Hat, and other Linux distributions.
DBeaver is developed using Java, and the user interface is based on the Eclipse platform. It's installed as a desktop-based GUI client tool. By default, DBeaver supports connectivity and working with any database based on JDBC drivers.
Comparing pgAdmin and DBeaver
In this next section, we'll walk through pgAdmin and DBeaver's similarities and differences focusing on their performance, features, ease of use, use cases, and costs. We've included a quick summary of when to use each at the end of the section.
Performance
Typically users consider DBeaver to have better performance than pgAdmin which tends to be slower. One of the reasons for this is that pgAdmin requires more cores and memory than a typical desktop app. Its dashboard is continuously updated with health metric values by default. You can help reduce this load by adjusting the refresh interval to a longer duration.
Additionally, as more users access pgAdmin through web browsers, the required server capacity will also increase. Meanwhile, DBeaver is more highly performant and doesn't have these issues.
Features
pgAdmin has a greater range of free features than DBeaver, but DBeaver has more sophisticated features for development and testing on their paid plans.
By default, DBeaver’s dashboard features three widgets:
- Server sessions: The total number of idle and active connections to the PostgreSQL database.
- Transactions per second: The total transactions, commits, and rollbacks that occur per second on the database.
- Block IO: The number of blocks read from the database server filesystem or cache.
pgAdmin offers the same three widgets we just described in addition to the following:
- Tuples in: The number of inserts, updates, and deletes that occur on the database.
- Tuples out information: The number of records fetched from the database.
- Database activity: Displays detailed information on sessions, locks, prepared transactions, and the selected server configuration.
Additionally, the Grant Wizard in pgAdmin, which is available under the Tools menu option, lets you easily choose the database objects and grant privileges to users in the database. This UI-based feature is unavailable in DBeaver. When using DBeaver, you'd have to grant privileges using the PostgreSQL Data Control Language (DCL) commands.
A sample DCL command to grant a select privilege on a table to a user looks like this:
GRANT SELECT ON TABLE schema_name.table_name TO user_name;0
If you're willing to pay for DBeaver’s Enterprise and Ultimate editions, you can generate mock data for your database tables, which is especially helpful during development and testing. With this mock data generation feature, you can generate realistic data for emails, credit/debit cards, person names, and more. pgAdmin doesn't support this functionality.
Ease of use
Overall, both pgAdmin and DBeaver are easy to use — especially for the basic operations outlined below. One major difference, however, is that pgAdmin is a web application and DBeaver is a desktop application.
You can complete the following operations easily with both tools:
- Define PostgreSQL server and database connection details
- Browse the database and its objects
- Define and execute SQL queries and stored procedures
- View the results of SQL statement executions
- Store frequently used commands as script files
- Import/export data from or to the database
- Monitor database health metrics
However, one differentiator is that pgAdmin can be deployed as a web application, making it more accessible. It only needs to be installed once on a server and then used by multiple users from different workstations through a browser.
Use cases
Accessibility: pgAdmin is the better choice when you have multiple users interacting with the tool since it's a web app. You can install pgAdmin on a server, which can be accessed by multiple users in the organization via their workstation browsers.
Security: When it comes to database connectivity security, both pgAdmin and DBeaver offer viable security options like basic username and password-based authentication, SSL certificates, and Kerberos authentication for secure database connections. However, DBeaver supports additional security standards by facilitating integration with AWS IAM and GCP authentication to support secured cloud deployments.
Database types: If you’re working with multiple database types, such as PostgreSQL, MySQL, Oracle, SQL Server, and NoSQL databases, DBeaver is the better option. It supports over 80 databases, whereas pgAdmin works only with the PostgreSQL database.
Cost and licensing
pgAdmin is entirely free to use with a PostgreSQL license. You can get access to the entire pgAdmin feature suite for free. DBeaver also offers a free plan, however, it follows a freemium model with multiple editions and licensing types for more advanced features.
The Community edition of DBeaver is free to use, while the Lite, Enterprise, and Ultimate editions come with different features and pricing. If you're looking to determine which version of DBeaver is best for you, they offer a 14-day free trial of all commercial versions.
Summary of pgAdmin vs. DBeaver
pgAdmin is better if you:
- Work with PostgreSQL only
- Prefer a web app over a desktop app and have multiple users
- Are okay with slower performance
- Are looking for an entirely free tool
DBeaver is better if you:
- Work with multiple database types like PostgreSQL, MySQL, Oracle, SQL Server, and NoSQL databases
- Care about better performance
- Are looking for more sophisticated features that you're willing to pay for, such as generating mock data for your database tables, which is especially helpful during development and testing
While pgAdmin and DBeaver are good open-source options for database management, Airplane makes running SQL queries and database operations even easier.
Introducing Airplane: transform SQL queries into apps in minutes
Airplane is a developer platform that transforms queries, scripts, APIs and more into high-quality internal applications in minutes.
Airplane makes it incredibly easy to take SQL queries and turn them into apps (called "tasks") that you can share with your team. With Airplane, you can define a connection for the PostgreSQL database through resources and then build UIs and workflows on top of that. Airplane then layers on a web UI, role-based access controls, audit logging, schedules, approval flows, and more out of the box. All you need to do is write your SQL query and Airplane takes care of the rest - no installation, deployment, or other configuration required. You can run through a 5-min tutorial here.
Airplane also supports connectivity to multiple databases like PostgreSQL, MySQL, MongoDB, Snowflake, Microsoft SQL Server, Redshift, and BigQuery.
A database operation to update a user's email might look like the below in Airplane:
Airplane was built to help companies operate safely and securely. You can easily configure approval flows on database operations to ensure that changes to your production data are made safely. When you give a user "requestor" access rather than "executor" access, they'd instead see the below form for the same task, which can be approved via Slack, email, or the Airplane Inbox.
Build full-featured UIs and workflows
Airplane supports many use cases outside of running database operations. For example, you can build full-featured UIs called "views" on top of your operations. Views can be used to quickly spin up customer dashboards and admin panels using React components. You can also compose multi-step workflows for customer onboarding, user management, GDPR deletion requests, and more using Airplane runbooks.
If you'd like to try Airplane out, you can sign up for a free account or say hello at [email protected] to learn more.
