On Monday 11th July the pgAdmin Development Team opened the first pgAdmin user survey which we then ran for three weeks, closing it on Monday 1st August. The aim of the survey was to help us understand how users are using pgAdmin to help us shape and focus our future development efforts.
We had a fantastic response with 278 people taking the time to complete the survey - far exceeding our expectations. Responses were generally positive as well, with a number of people expressing their appreciation for the work of the development team, which is always nice to hear.
In this blog post I'll go through the high level topics of the survey and attempt to summarise what has been reported, and draw some initial conclusions. If you would like to take a look at the results yourself you can download a spreadsheet containing the raw responses, or a PDF containing the (somewhat hacked about for readability) report that Google Forms has generated. In the interests of full disclosure, I've redacted one comment in which one respondent entered nothing but their email address.
Installation Environment
The vast majority of users (96%) install pgAdmin onto a desktop or server computer. Just 6% deploy into the cloud, the majority of which use AWS. Roughly another 6% of users make use of some kind of container or private cloud infrastructure. Note with this - and a number of the following questions - respondents could select multiple answers, so the percentages may not add up to 100.
97% of users run pgAdmin on 64bit Intel/AMD hardware, with 13% using ARM64 and 1% using PowerPC hardware. Clearly focussing on x86_64 primarily makes sense, although I suspect we'll see the number for ARM64 grow in the future as people make more use of ARM based containers and Macs are replaced with newer machines sporting Apple Silicon.
There's a much more even distribution of users of the popular operating systems; 57% run pgAdmin on Linux, 18% on macOS, and 63% on Windows. That's roughly in line with what I would expect to see.
Looking at the packages used, the landscape is less clear as there are various package options that can be used on the same operating system; for example, pgAdmin comes bundled with the EDB PostgreSQL installers for Windows and macOS, plus you can download standalone pgAdmin packages for both. Here are the responses:
For container users, the vast majority of users make use of orchestration technology, with Kubernetes being the most popular (56%), followed by Docker Swarm (24%) and then OpenShift (14.5%).
Perhaps the biggest surprise in this section is the relatively small number of people who get their pgAdmin packages from Docker Hub, or who said they deploy the pgAdmin container. It's extremely hard to get pgAdmin download statistics, as the pgAdmin/PostgreSQL websites don't keep access logs for more than a few days for diagnostic purposes. However, we can get stats from some sites, and they tell us quite a different story:
Chocolatey (all time): 450744
Docker Hub (all time): 203357292
Homebrew (last 365 days): 30104
PyPi (last 6 months): 33988
======================================
Total: 203872128
Docker claims we've had over 200 million pulls of the container, whilst we see far smaller numbers of downloads for Chocolatey, Homebrew, and PyPi. Is this because Docker is counting every time a user pings Docker Hub to see if there's an update available? If not, then that would imply that we could expect to have had 1.25 billion downloads from our website, and APT/YUM repositories. Clearly that's certainly not the case! It seems to me that the Docker numbers are the outlier here, and we're really seeing much lower numbers of "real" downloads - I would guess, based on other numbers I know we get for some PostgreSQL distributions, in the order of a few million per year in total for all distributions.
Regardless, it would appear that most of our users are using the Windows, macOS, and Linux packages we provide, with the majority of Linux users preferring APT (Debian/Ubuntu and friends). Docker does seem to be very popular as well though.
Finally in this section we asked what other types of packages/distributions we should consider supporting, with FlatPak and Snap coming out almost equally on top, followed by AppImage and NuGet. Adding new types of packages is a significant commitment in terms of the additional testing and maintenance, so whilst we probably won't create any new packages yet, we do have a much better idea of what people want.
Desktop Deployments
87% of respondents run pgAdmin in desktop mode, and the majority have multiple installations in their organisation. Perhaps most surprisingly, 7.5% of respondents told us they have more than 50 deployments within their organisation. The majority had between two and five:
42% of users make use of the server import/export features, suggesting that it's common to share server definitions either with colleagues or different machines that might be in use.
We also asked how users authenticate with the PostgreSQL servers they manage. "Password" was the most common selection by far, but it was surprising to see over 8% of users using "Trust" authentication - hopefully those servers are local development instances! LDAP and Kerberos are also used, and PAM tied with Kerberos which wasn't expected.
Just under 10% of users told us they have modified the pgAdmin config_*.py configuration files for their installations; most commonly to disable the Master Password feature used to securely encrypt saved passwords (and in at least one case, to disable password saving as well), but also to adjust log locations, disable upgrade checks, and make other miscellaneous behavioural changes.
Server Deployments
34% of respondents deploy pgAdmin in server mode, so statistically we can tell that there's a non-trivial number of users who run both modes. As one might expect, a good number (46%) of users had a single server deployment, but that of course meant that more than half have two or more. 6% had more than ten deployments which suggests that one usage pattern might be to have pgAdmin as part of an application deployment (i.e. in Kubernetes), or perhaps to have departmental deployments.
Most deployments had just a handful of users, with only one respondent reporting that they had 51-100 users on their largest. Two to five was the most common answer, again suggesting that departmental or per-application deployments are likely common.
Most deployments seem to use pgAdmin's internal user database for authentication, however a good number do make use of Kerberos, LDAP, and OAuth, with Active Directory being the most common authentication provider for both Kerberos and LDAP users, with MIT Kerberos and OpenLDAP coming up in second place. OAuth providers seem to be relatively varied:
It was also interesting to see that 34 respondents are using the relatively new support for two factor authentication.
The method used to host pgAdmin in server mode is also quite varied. Whilst containers were used by most people, use of the pgAdmin APT/YUM packages is not far behind. What is perhaps more surprising is the number of people who are configuring their own deployments manually, either using Apache HTTPD with mod_wsgi (as our packages do), or using some other combination of Nginx/Lighttpd and Gunicorn/uWSGI. Perhaps there is an opportunity for additional example configurations in the documentation here?
Approximately half of the users of server mode also employed a proxy server in front of pgAdmin:
Use of server import/export wasn't too dissimilar from what was seen with desktop mode users, with approximately half using it. Authentication with PostgreSQL was also similar to the desktop mode users, with most utilising "Password" authentication, and smaller numbers using LDAP, Kerberos, PAM, and Trust. Use of "Trust" in server mode is somewhat more surprising than desktop mode, as it seems very likely that a PostgreSQL server running in an environment where pgAdmin is in server mode, would be accessible over the network.
Finally, configuration customization in server mode seemed to be much more focussed on authentication configuration as might be expected.
Features
The vast majority of users use pgAdmin to manage PostgreSQL (100% in fact), with 4% using it with EDB Postgres Advanced Server. A number of individual users also reported using it with cloud based services such as Amazon RDS and Aurora, and Google Cloud SQL. What I didn't expect to see added as "other" options were Neon and Citus. It may be that in the future we should consider adding official support for them.
When we look at the key features used by people, I was surprised to see that only 90% of people use the Query Tool, which I always assumed essentially everyone used. Even more of a surprise was that only 72% used the Schema Browser and object Create/Property Dialogues - I can only assume that that was down to a misunderstanding of our naming there, as it's virtually impossible to do anything in pgAdmin without using the Schema Browser. Perhaps some users were limiting their response to the dialogues.
Import/Export Data was the third most used tool, to which we've just made some improvements that hopefully people will enjoy. Other key features used were not too surprising; perhaps the two that stand out the most are 42% of people using the integrated PSQL tool, and 3% of users using the very new Cloud Deployment functionality which is nice to see so early in its life.
The Core Information tabs showed usage roughly in line with what I expected to see, albeit with slightly more use of the Dependents and Dependencies tabs than I imagined. The vast majority of users (94%) use the SQL tab, which I personally find the most useful of all:
We asked what additional object types people would like to see supported. When this question was written, it was intended to refer to PostgreSQL objects such as Conversions or Operators etc; those that are represented by nodes in the Schema Browser treeview. Given the number of "other" options selected for this question it's clear that we didn't phrase this one very well. That said, it's also clear that we should consider adding support for Statistics nodes, and for Default Privileges, which would likely be an addition to the properties for Databases and Schemas.
The Query Tool is almost certainly the most generic tool in pgAdmin, and thus has a wide range of features of its own. All of the options listed are used by multiple people, with Auto-complete and Explain Statistics coming out as the most popular. This might point to another badly-worded question on our part, as the Graphical Explain feature is reportedly used by far fewer people than Explain Statistics, however, you cannot see the statistics without first using the Graphical Explain! It's also possible that people took Graphical Explain to mean just the plan visualisation tab, and not the feature as a whole.
Regardless, we have already made improvements to Auto-complete in v6.12 of pgAdmin as a result of early responses to this survey, having made the auto-complete menu automatically appear on key up in the editor as a number of people requested.
Finally we asked about use of the new Cloud Deployment features. Currently pgAdmin supports RDS on AWS, Azure Database for PostgreSQL, and the relatively new EDB BigAnimal. Unsurprisingly RDS was most commonly used, followed by Azure and then BigAnimal. The most wanted additional clouds requested were Google, followed by Heroku, and then Digital Ocean. We are already planning to add support for Google Cloud SQL for PostgreSQL.
Documentation
Just 33% of users read the pgAdmin documentation from within pgAdmin, with the majority using the website instead (much as your humble author tends to do). Nearly 20% confessed to never reading documentation!
Very few users download the documentation, however of those that do, PDF is the most popular format. Over 5% do use the ePub format though, which surprised me. It is essentially zero effort to produce though, so I doubt we'll stop doing so any time soon, even if relatively few people use it.
Comments
At the end of each section of the survey we provided a comments box so that respondents could free-type any additional comments they may have. In hindsight that may not have been the best idea because we saw a myriad of comments on different topics throughout, but especially at the beginning of the survey flow. This is likely because you couldn't see if there was going to be another comment section later on.
That said, we did get a lot of feedback in the form of comments, some of which I'll summarise here:
- Some users (presumably only ones that are using desktop mode) expressed a desire to go back to having a traditional desktop application as pgAdmin III was. I'm sorry, but that isn't going to happen. It's clear that we have a lot of people running in server mode which was exactly what we hoped for when we designed pgAdmin 4, and supporting both a desktop and server mode isn't feasible with a native desktop application.
- There were some complaints about the speed in desktop mode. Those that used very early versions of pgAdmin 4 may recall we had some nasty speed related issues initially, and we worked hard to make things much faster. The speed (especially at startup) is always going to be slower than with a native desktop application, but we continue to work to speed things up even more with recent changes being committed to remove the plugin architecture that has never been used, as well as housekeeping to remove old code for unsupported PostgreSQL versions for example.
- On more positive notes, there were various comments praising modern versions of pgAdmin such as:
- good job done to rework pgadmin 4 from pg admin 3 !
- Thank you to the team for providing such a useful tool , please keep it updated with new features
- It is a great tool, keep improving.
- Keep pgADmin rolling - we use it every day !
- Thanks for such an awesome and FREE software!!
- Congratulations once more for your efforts
- Generally speaking, I use it daily and would be a huge productivity loss without it. Thanks to the developers.
- We have a database serving data storage needs for our weather broadcast system. It is essential that we have visual access to the approximately 250 tables and 30 or so stored procedures distributed amongst 6 schemas. pgAdmin has been absolutely critical in saving countless man-hours in this role. I use pgAdmin4 weekly and I cannot imagine life without this fantastic database visualization tool.
- A number of people mentioned a known bug where pgAdmin will crash if using external Query Tool windows, and right-clicking in them. Unfortunately this is a bug in NWjs which has yet to be fixed, but we will make a greater effort to come up with a workaround in our code as I don't think we realised it was such a wide-spread issue.
- People seem to like the ERD Tool, and have made various suggestions for further enhancements.
- The Master Password feature remains an annoyance for some users, who consider it unnecessary. This is a difficult problem, because we have a "secure by default" policy, and need to be able to support corporate installations in which the choice to enable/disable security features must be down to the local IT/security team, and not end users. We have noted at least one improvement we can make in this area, and will continue to look for more. I'm afraid it will not be disabled by default however, because that would be insecure.
Conclusions
Both the number of respondents to this survey, and the length of this summary blog post are a surprise to me! I think in general what we see is that we continue to head in the right direction as we build new features and enhancements into pgAdmin. The results from the survey help validate that we're focussing our energy and resources in the right direction, and have revealed some areas in which greater attention is required.
Our ultimate aim is to continue to build the leading open source management tool for PostgreSQL, and to balance the often differing needs of all our users. Sometimes of course, that means some people are not going to be happy, but we will keep doing our best to ensure that we always work on the things that will benefit the greatest number of users.
Finally, I'd like to thank all those who have taken the time to help us out with this survey!