This post is part of an impromptu series about PostgreSQL and things I am learning coming from a SQL Server background. A complete list of posts can be found on this post.
UPDATE: In response to this article and much of the feedback, I’ve created a second post on two other tools. Check that out for more context and options… and keep watching for more information on PostgreSQL tools.
So here’s the deal.
Just. Stop. Looking.
No, really, stop looking for more user friendly tools that provide even 25% of the features as SQL Server Management Studio, Azure Data Studio or even Visual Studio (for database projects). They don’t exist.
Obviously this is my current opinion. (YMMV)
Why Bad Tooling is a Problem
As I’ve mentioned at various times, I come from a teaching background and so I tend to be hyper-aware of things that unnecessarily interfere with learning something. When it comes to technology, that can be a lack of documentation, bad tooling, or good tooling that’s simply too hard to figure out. And yes, SSMS could fall into the later category at times.
In the case of PostgreSQL, I’ve quickly come to the conclusion that bad tooling is one of the main reasons the uptake is so much more difficult and convoluted coming from the SQL Server community. Even the devs I’m currently working with that have no specific affinity for databases at all recognize that PostgreSQL just feels like more of a black box then the limited experience they had previously with SQL Server.
/* Begin Brief Soapbox*/
Honestly, this is by far one of my biggest grips about Open Source software now that I’m older, busier, and don’t want to spin my wheels trying to make something simple work. When the tools make it hard to dig in and work effectively with the database, most developers and shops will default to code-first/ORM only development. In nearly 20 years of software development and leading multiple teams, I’m still surprised how little most developers really care about effectively using a database of any kind. During most interviews only about 30% of applicants can ever answer a few basic SQL questions. And now I think I’m starting to understand why. Most of them have been relegated to an Open Source world with Open Source tooling when it comes to SQL. Yes, it’s cheap and allows projects to spin up quickly, but once those students get past their little pizza ordering app from CompSci 402, they’ll be lost in the real world.
/* End Brief Soapbox */
Quick Aside: Lest anyone misunderstand me, my grip is not with Open Source software at all. I LOVE OPEN SOURCE SOFTWARE! Really! I’ve been using Linux since 1998, built my first kernel in 1999 on a bus headed to North Dakota (long story) and I literally wouldn’t be where I am without OSS. This conversation is specifically about tooling for SQL platforms in the OSS world.
The current contenders
All of the griping and soap-boxing aside, I really do want to provide a short list for others to consider that might be in a similar position. Below is a short list of tools I’ve tried or (eventually) purchased to make using and managing PostgreSQL easier and more straightforward. As with most OSS projects, there are certainly many more than this. But based on feedback from others and doing my own testing, this where things currently sit for me.
PgAdmin is the web-based GUI for PostgreSQL and certainly the most often used tool. To “run” PgAdmin the application starts a local web server process and then triggers open a browser. From an enterprise perspective, it’s honestly pretty laughable to ask developers to do real day-to-day work managing a database through a web application. Sorry, I’m not going to sugar coat it. Too often I feel like it’s one refresh away from loosing my work and I’m reminded every time I use PgAdmin of an incident 15 years ago whenI dropped tables in a client database using phpMyAdmin because of a refresh bug which reset the context in the database. And all of these years later, I lost work three days into the new job because something caused things to refresh and everything was reset.
To be fair, PgAdmin is a pretty remarkable undertaking and has some useful features. Three quick highlights of basic things PgAdmin provides which I’d expect from any tool include:
- Easy hierarchical navigation that actually shows you all of the major database object types in context (see all triggers/indexes/columns in context of the table).
- Quickly displaying the DDL of objects. For instance, it’s the only tool that can easily show me the DDL of a Trigger after it is created, including the predicate.
- A visual query planner of some sort. Yes, I know about EXPLAIN ANALYZE, but honestly, I want to focus on making queries better, not remembering how to decipher the text plan each time.
So yes, there is a surprisingly good set of features given the delivery method, but it’s just not enterprise ready by any stretch. One potentially bright spot is that a co-worker recently mentioned that there was rumor of some investment capital in the PgAdmin project in some way. Maybe there’s hope of something better? Maybe Microsoft will figure out how to allow Azure Data Studio to become part of the mix?
At this point, the best option I’ve found for Windows is JetBrains DataGrip. They happened to be at PASS Summit last year and a technical sales lead stated that their goal is to replace SSMS as the database management tool of choice. (I’m not even paraphrasing!). While that’s a noble goal, they have a long way to go… but it’s still much better than PgAdmin for most tasks.
A couple of “quick win” useful features include:
- There are some handy features in DataGrip for viewing and filtering data with an easy double-click of the table.
- Multiple-tab support is fairly good, however, seeing the settings for the connection on our current tab isn’t as obvious (again making me fear I’ll run something in the wrong database)
- Managing backups is easier, overall, than PgAdmin or the command-line
One thing that I think is holding me back from using DataGrip more effectively is the lack of good training or documentation. I remember sitting at the JetBrains booth at PASS Summit thinking “those are some interesting features…”, but I can only remember one of them at this point. The only training video I found is a few years old and is essentially a 10-minute technical sales demo. If there were more of those, maybe I’d have a different opinion.
DataGrip is cross-platform, although I haven’t tried the Mac version yet. If you’re on a Mac, Brent Ozar recommended Postico when I asked him what tools his team uses to manage and work with their Aurora instance.
Navicat for PostgreSQL
I also gave Navicat a try for their 14-day trial. In the end, there wasn’t enough value to justify spending another $300 after I had already purchased DataGrip.
One thing I will say is that it is much faster and (seemingly) lightweight in a good way. DataGrip seems to run up against a JVM memory issue often whenever I try to do larger tasks (like run a backup). I didn’t experience any of that slowness when testing Navicat. Unfortunately, there wasn’t enough overwhelming wins to make that one issue a deal-breaker.
Why Hide the Schema?
One particularly annoying thing about both DataGrip and Navicat is that neither of them show all of the major schema objects within the navigation tree… and each shows a different set of objects.
DataGrip shows most of the major schema object within the tree under each table as I’m used to. Unfortunately, interacting with the objects for editing or inspection never works out quite how you’d expect and it doesn’t consistently show the true DDL of the objects.
Navicat? Every object has its own section. What indexes do I have on table ABC? Yeah, you have to go to the “index” module and hope you named your indexes well because they aren’t used in context of the tree.
In total, this just feels like it strengthens my opening monologue about the usability and accessibility a tool provides
A brighter future?
My one hope for improving the tooling is that Microsoft just hired the maintainer/owner of the .Net PostgreSQL driver, Shay Rojansky. While I know that doesn’t translate directly, I can only hope it means they’re thinking about how to improve the tooling to go along with the programming support.
What about you? Any tooling choices that have been a net positive in your transition to PostgreSQL?