Category: All posts
Nov 28, 2024
Posted by
Ryan Booz
The State of PostgreSQL 2022 survey closed a few weeks ago, and we're hard at work cleaning and analyzing the data to provide the best insights we can for the PostgreSQL community.
In the database community, however, there are usually two things that drive lots of discussion year after year: performance and tooling. During this year's survey, we modified the questions slightly so that we could focus on three specific use cases and the PostgreSQL tools that the community finds most helpful for each: querying and administration, development, and data visualization.
Absolutely nothing! As evidenced by the majority of respondents (69.4 %) that mentioned using psql for querying and administration, it's the ubiquitous choice for so many PostgreSQL users and there is already good documentation and community contributed resources (https://psql-tips.org/ by Leatitia Avrot is a great example) to learn more about it.
So that got us thinking. What other tools did folks bring up often for interacting with PostgreSQL along the three use cases mentioned above?
I'm glad we asked. 😉
As we just said, psql is by far the most popular tool for interacting with PostgreSQL. 🎉
It's clear, however, that many users with all levels of experience do trust other tools as well.
pgAdmin (35 %), DBeaver (26 %), Datagrip (13 %), and IntelliJ (10 %) IDEs received the most mentions. Most of these aren't surprising if you've been working with databases, PostgreSQL or not. The most popular GUIs (pgAdmin and DBeaver) are open source and freely available to use. The next more popular GUIs (Datagrip and IntelliJ) are licensed per seat. However, if your company or team already uses JetBrain's tools, you might have access to these popular tools.
What I was more interested in were the mentions that happened just after the more popular tools I expected to see. Often, it's this next set of PostgreSQL tools that has gained enough attention from community members that there's obviously a value proposition to investigate further. If they can be helpful to my (or your) development workflow in certain situations, I think it's worth digging a little deeper.
First on the list is pgcli, a Python-based command-line tool and one of many dbcli tools created for various databases. Although this is not a replacement for psql, it provides an interactive, auto-complete interface for writing SQL and getting results. Syntax highlighting and some basic support for psql backslash commands are included. If you love to stay in the terminal but want a little more interactivity, the dbcli tools have been around for quite some time, have a nice community of support, and might make database exploration just a little bit easier sometimes.
Introduced as a beta in December 2017 by the Microsoft database tooling team, Azure Data Studio has been built on top of the same Electron platform as Visual Studio Code. Although the primary feature set is currently geared towards SQL Server (for obvious reasons), the ability to connect to PostgreSQL has been available since 2019.
There are a couple of unique features in Azure Data Studio (ADS) that work with both SQL Server and PostgreSQL connections that I think are worth mentioning.
First, ADS includes the ability to create and run SQL-based Jupyter Notebooks. Typically you'd have to wrap your SQL inside of another runtime like Python, but ADS provides the option to select the "SQL" kernel and deals with the connection and SQL wrapping behind the scenes.
Second, ADS provides the ability to export query results to Excel without any plugins needed. While there are (seemingly) a thousand ways to quickly get a result set into CSV, producing a correctly formatted Excel file requires a plugin with almost any other tool. Regardless of how you feel about Excel, it is still the tool of choice for many data analysts, and being able to provide an Excel file easily does help sometimes.
Finally, ADS also provides some basic charting capabilities using query results. There's no need to set up a notebook and use a charting library like plotly if you just need to get some quick visualizations on the data. I've had a few hiccups with the capabilities (it's certainly not intended as a serious data analytics tool), but it can be helpful to get some quick chart images to share while exploring query data.
For anyone using MacOS, Postico is a GUI application that's been recommended in many of my circles. Many folks prefer the native MacOS feel, and some of the unique usability and editing features that make working with PostgreSQL simple and intuitive.
We'll leave it to you to look through the data and see what other GUI/query tools fellow PostgreSQL users are also using that might be of interest to you, but there are a few that were mentioned multiple times and even caused me to hit Google a few times to find out more. Some are free and open source, while others require licenses but provide interesting features like built-in data analytics capabilities. Whether you end up using any of these or not, it's good to see continued innovation within the tooling market, something that doesn't seem to be slowing down decades into our SQL journey.
Although the GUI/administration landscape is certainly as active as ever, one of the most impactful features of PostgreSQL is how extensible it is. If the core application doesn't provide exactly what your application needs, there's a good chance someone (or some company) is working to provide that functionality.
The total distinct number of tools mentioned was similar to GUI/administration tools and generally fell into four categories: management features, cluster monitoring, query plan insights, and database DevOps tooling. For this blog post, we're going to focus on the first three areas.
It's not surprising that the most popular third-party PostgreSQL tools tend to be focused on daily management tasks of some sort. Two of the most popular tools in this area are mainstays in most self-hosted PostgreSQL circles.
pgBouncer
PostgreSQL creates one new process (not thread) per connection. Without proper tuning and a right-sized server, a database can quickly become overwhelmed with unplanned spikes in usage. pgBouncer is an open-source connection pooling application that helps manage connection usage for high-traffic applications.
If your database is self-hosted or your DBaaS doesn't provide some kind of connection pooling management for you, pgBouncer can be installed anywhere that makes sense with respect to your application to provide better connection management.
pgBackRest
Database backups are essential, obviously, and PostgreSQL has always had standard tooling for backup and restore. But as databases have grown in size and application architectures have become more complex, using pg_dump and pg_restore can make it more difficult than intended to perform these tasks well.
The Crunchy Data team created pgBackRest to help provide a full-fledged backups and restore system with many necessary features for enterprise workloads. Multi-threaded backup and compression, multiple repository locations, and backup resume are just a few features that make this a common and valuable tool for any PostgreSQL administrator.
The second area of third-party PostgreSQL tools that show up often focuses on improved database monitoring, which includes query monitoring in most cases. There are a lot of folks tackling this problem area from many different angles, which demonstrates the continued need that many developers and administrators have when managing PostgreSQL.
pgBadger
PostgreSQL has a lot of settings that can be tuned and details that can be logged into server logs, but there is no built-in functionality for holistically analyzing that data cohesively. This is where pgBadger steps in to help generate useful reports from all of the data your server is logging.
pgBadger is one of a few popular PostgreSQL tools written in Perl (which surprises me for some reason), but the developer has gone to great lengths to not require lots of Perl-specific modules for drawing charts and graphs, instead relying on common JavaScript libraries in the rendered reports.
There's a lot to look at with pgBadger, and the larger PostgreSQL community often recommends it as a helpful, long-term debugging tool for server performance issues.
pganalyze
pganalyze has grown in popularity quite a lot over the last few years. Lukas Fittl has done a great job adding new features and capabilities while also providing a number of great PostgreSQL community resources across various platforms.
pganalyze is a fee-based product that uses data provided by standard plugins (pg_stat_statements for example) which is then consumed through a collector that sends the data to a cloud service. If you use pganalyze to query log information as well (e.g., long-running queries), then features like example problem queries and index advisor could be really helpful for your development workflow and user experience.
No discussion about PostgreSQL would be complete without mentioning tools that help you understand EXPLAIN output better. This is one area so many people struggle with, particularly based on what their previous experience is with another database, and a small cache of common, helpful tools have been growing in popularity to help with this essential task.
Depesz EXPLAIN and Dalibo EXPLAIN
Both Depesz and Dalibo EXPLAIN provide a quick, free platform for taking a PostgreSQL explain plan and providing helpful insights into which operations are causing a slow query and, in some cases, providing helpful hints to help speed things up. Also, if you let them, both tools provide a permalink to the output for you to share with others if necessary.
pgMustard
One of my favorite EXPLAIN tools is pgMustard, created and maintained by Michael Christofides. This is a for-fee tool, but there are a lot of unique insights and features that pgMustard provides that others currently don't. Michael is also doing great work within the community, even recently starting a PostgreSQL podcast with Nikolay Samokhvalov, with whom we recently talked about all things SQL.
The final tooling question on the State of PostgreSQL survey asked about visualization tools that folks used. Without a doubt, Grafana was the top vote-getter, but that's something we could have probably guessed pretty easily.
I was surprised that the next two top vote-getters were for pgAdmin and DBeaver, both popular database GUI tools we mentioned earlier. In both cases, visualization capabilities are somewhat limited, so it's hard to tell exactly what kind of features are being used that would categorize them as visualization tools.
The next group of tools is more interesting to me and I wanted to highlight a few that might pique your interest to investigate further.
QGIS is a desktop application that's used to visualize spatial data, whether from PostGIS queries or other data sources. As I've had the pleasure of learning about GIS data and queries from Ryan Lambert over the past few years, I've seen him use this tool for lots of valuable and interesting spatial queries. If you rely on PostGIS for application features and you store spatial data, take a look at how QGIS might be able to help your analysis workflow.
There are a number of data visualization and dashboarding alternatives in the market, and PostgreSQL support is universally expected regardless of the tool. Superset is an open-source option that also has commercial support and hosting options available through Preset.io. With more than 40 chart types and a vibrant community, there's a lot to explore in the Superset ecosystem.
For those developers that use Python for most of their data analysis and visualizations, Streamlit is another popular choice that can easily fit into your existing workflow. Streamlit isn't a drag-and-drop UI for creating dashboards, but rather a programmatic interface for building and deploying data analysis applications using Python. And as of July 2022, you can deploy public data apps using Streamlit.io.
There were so many interesting answers and suggestions provided by the community to these three questions. It's clear that there are a lot of people around the world working to help developers and database professionals be more productive across many common tasks.
Are there any surprises in this list or tools that you think didn't make the list? Hit us up on Slack, our Forum, or Twitter (@timescaleDB) to share other tools that are important to your daily PostgreSQL workflow!
Now that we’ve given you a taste of our survey results, are you curious to learn more about the PostgreSQL community? If you’d like to know more insights about the State of PostgreSQL 2022, including why respondents chose PostgreSQL, their opinion on industry events, and what information sources they would recommend to friends and colleagues, don’t miss our complete report. Click here to read it and learn firsthand what the State of PostgreSQL is in 2022.