PostgreSQL for a SQL Server DBA: The Tooling Stinks

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

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?

DataGrip

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?

17 thoughts on “PostgreSQL for a SQL Server DBA: The Tooling Stinks”

  1. Hello! I am Max, the product marketing manager of DataGrip and I think I was the person you talked to at PASS Summit. We were there with Keith Kinney, our sales representative in US, perhaps it was him.

    Anyway, when we were talking about replacing SSMS, we said “DataGrip is the replacement of SSMS” which is the shortest way to explain what it is about. Because you might know, that in SQL Server world people don’t even consider using some other tool for SQL. While DataGrip can be a replacements for SQL developers, it is of course not for database administrators, because native tool is the native tool, you know. We believe that the best value in many cases can be brought by the simultaneous using of SSMS and DataGrip.

    Let me comment a couple of things.

    >>>again making me fear I’ll run something in the wrong database
    We provide the control of what and where you can run. If you find that top-right switcher not very visible, you can use the following things:
    – Mark you data source as read-only. So you can use particular databases in it and it will be just impossible to run anything there. IDE will always warn you about that. Even if you run a procedure that runs a procedure that runs a procedure that affects data. 🙂
    – Color your data source. You will always see the needed background color (i.e. red for production) when you’re writing SQL.

    >>>One thing that I think is holding me back from using DataGrip more effectively is the lack of good training or documentation.
    Perhaps that’s reason why the features we are really proud of are not in your list 🙂 Can I ask you what was your scenario of searching? Because we have the detailed and easy-to-navigate feature list just on the web-site: https://www.jetbrains.com/datagrip/features/
    That video you mentioned might be: https://www.youtube.com/watch?v=Xb9K8IAdZNg
    If yes, despite its age it still shows what DataGrip is about. But we’re now considering making short video tutorials.

    >>>>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.
    This is not very clear, can you please explain what was not like you expected? About DDLs: the main goal of DataGrip is to generate useful DDL, but from 2018.3 you can choose “definition from DBMS server” in SQL-generator (Ctrl+Alt+G) and DataGrip will show you “native” DDL.

    Anyway, we would be happy to help you with any kind of issues or discuss feature requests. We are here: [email protected]m

    1. Max,

      Thank you so much for the quick follow-up and thoughts. It was you that I spoke with and I can see the passion you have for the product just as I could at PASS Summit! 🙂 There’s a lot I like about what you’ve done with DataGrip and I certainly know that I’m a small voice in the midst of a very big, fast-moving data evolution. The fact that you want to engage speaks volumes!

      As I said at the beginning of the post, this is my current perspective having been thrown into a Postgres world after spending years with SQL Server and the tooling. In all fairness (and I tried to say/imply this)… SSMS hasn’t always been a bed of roses either. However, the last few years of consistent updates and listening to the SQL Server community has proven to provide some great tooling which makes it hard to compare with when that’s your lens.

      One area that SSMS hasn’t been great at for years (but is getting better) is intellisense. In that realm, DataGrip does a good job at helping me quickly write SQL and even suggests aliases, etc. Well done! And yes, some of the configuration to set databases as read-only and so forth are great.

      But as a database admin and developer, selecting and seeing data isn’t usually my problem. I’m pretty quick at selecting and filtering the data I need to see (thank you 7th grade typing class), even if it’s from one table. Instead the few high-level annoyances that have kept me in the ‘meh’ camp with DataGrip (and all of the current tools honestly) include:

      – The “console” tabs and the results tabs are disjointed. I get lots of results and console tabs at the bottom that I think are generally tied to the console tabs, but they don’t move in harmony and I just get disoriented.

      – Saving files/scripts seem to always default to the same folder deep in the program folder. Even within a single session, if I change folders it is not remembered. Again, this seems small but it’s a huge productivity killer when you’re making edits and exporting various files in a longer process. Most programs tend to remember that selection after the first go, at least within a session.

      – The DDL conversation is important, even for simpler development. In PostgreSQL, for instance, you can add predicates to triggers. When I show the “native” DDL in DataGrip (as you mentioned above), it ignores the predicate on my triggers when they exist. This is a HUGE issue because, in some cases, it would mean that if I use that to pass to someone or to redo something in the database, the trigger will now run on every DML operation which could mean corruption (or at least some big-time, unintended load). And, even if I don’t use the generated SQL to update a database, there is literally no way for me to know what the state of my database is. To me, at least, that’s a pretty big issue.

      As for videos and training, that’s just hard. I completely get it. The video you pointed to was what the one that I found and it’s a great teaser, but I’d love to have at least a “top 10” or something to point other developers at. If I’m going to spend the money on the product, I need them to learn how to use it more effectively. Without that, we’ve just spent a lot of money for a more expensive PgAdmin. 🙂

  2. Thank you again for the detailed feedback you’ve provided! I’ll answer you by points.

    1. Initially, DataGrip should correctly switch between console query tab and the console output, where results are shown. I see now that if the tab (console or file) doesn’t have the result output, the “old” result output is still shown, but it seems to be OK, because there is no “new” result tab to be displayed. I cannot reproduce the situation when the switching isn’t correct. So, if you have step-by-step way to reproduce the behaviour you don’t like, please, tell us about that.

    2. We understand that this can be a usability problem. And I think it should be solved when we will a little bit rework the whole concept about consoles and files. Please, follow the corresponding ticket: https://youtrack.jetbrains.com/issue/DBE-5854
    Now, if you do want to work with files which are going to be saved, please, consider working with files, not with consoles.
    Now we are treating consoles like temporary SQL editors. And if you’ll write SQL in files, observe them in Files tool window, everything should work like user expected. Please, see this tutorial for more information: https://blog.jetbrains.com/datagrip/2019/01/30/working-with-files-in-datagrip/
    Again, we understand that writing SQL in console files which are attached to the correct data source by default is handy. So, your concern obviously makes sense.

    3. Am I right that we are speaking about something like WHEN condition in triggers? If yes, then we do miss it in generated DDL. And here is the ticket:
    https://youtrack.jetbrains.com/issue/DBE-7247
    At the same time we show it if you choose “definition from DBMS server” in SQL-generator (Ctrl+Alt+G). I just checked it:
    https://habrastorage.org/webt/9e/pw/qs/9epwqsgwx-2zp-iqvvvq-exut_i.png

    4. About the learning content. I think if you want to have something to point other developers too, the features page can be a good place. https://www.jetbrains.com/datagrip/features/
    Anyway, I got you point and we’ll pay more attention into video content. Thanks!

    By the way, since you provided the detailed feedback, and we do value this, you can always ask for a free 1 year subscription of DataGrip. Just write at [email protected] (I handle this email as well 🙂 and we’ll send you the 100% discount code.

    Max.

  3. Hi Ryan – appreciate the write up, Postgres visual tooling is indeed far behind other toold, especially other tools that you’re very used to. PgAdmin is horrendous.

    Most PG DBAs that I know use psql 95% of the time and a tool like Navicat when they want to lean into the visual bits. I would offer to you that you’re missing sooooo much by looking for buttons and dialog boxes when the power of psql is right there for you.

    Indeed it’s an investment and a mind-shift, but the ex SQL Server folks I know don’t miss the visual stuff at all. I wrote a post about this, inspired by yours. Hopefully it’s helpful:
    https://rob.conery.io/2019/03/04/postgresql-tools-for-the-visually-inclined/

    1. Wow, thanks for the feedback and the detailed article, Rob! (I mean, is Rob Conery seriously responding to a blog post of mine?!) I’ve followed your content off/on for a number of years and appreciate the response.

      I don’t disagree with the varied worlds and I’m honestly interested to see how the next few years go as Microsoft blurs the lines a bit between Windows and Linux and the two user bases come together in some areas. I’ve spent the better part of 25 years with a terminal open on a screen somewhere and it’s just so interesting (and surreal) to see some of it converging.

      The overall thoughts and direction of your post are things I completely agree with… and it was good to be reminded of what’s available. If it wasn’t obvious, the new job relies on AWS Aurora, so I haven’t had to deal with the guts of Postgres that much yet. I’m focused on a database that needs help (and is completely code-first designed) and queries that take 25 seconds in the UI (or timeout as the case may be). Jumping into the deep-end of a new project that has a few years of history… well, I am looking for some tools that can help me make the jump a bit more easily. When it comes to digging in and tuning, I’m struggling to find tools comparable to visual Execution Plans, Query Store, and my recent favorite for development… Database Projects (SSDT) in Visual Studio. While I want these tools to help me be more productive, they also allow me to more easily host “lunch & learn” sessions to teach others.

      Anyway, I’m not saying anything earth shattering, but I do appreciate being reminded to know all of my options, make sure I know the basics of the intended tooling, and not be afraid to try new things.

      All the best!

  4. Hi Ryan. Thanks for the write up. Neil here, the person behind https://www.postgrescompare.com, a schema comparison tool for PostgreSQL.

    The usability of PostgresCompare is very important to me and I would be interested to get your, or your readers’ takes on how it’s coming along. I’m very purposefully trying to build a tool that can be comparable to those that people who are migrating to PostgreSQL are used to.

    Also, DBeaver is an option that competes favourably with those you’ve listed!

    1. Neil,

      I’m excited to give Postgres Compare a look. I’ve honestly never done a large code-first project and, as the team grows, I don’t see it being very sustainable. Without something like Database Projects (or an expensive Data Modeling Tool), doing DB-first design with some sort of schema compare to do upgrades seems to be the best next option.

      And yes, DBeaver has quickly become my daily driver for most needs. At this point, none of the others have provided the set of features this does to help me get my day-to-day done.

      Thanks again!

  5. Hello Ryan, great post. I understand exactly what you mean. I am Oracle DBA and 20 years ago MSSQL came into my world. Recently about 5 years Postgres started appearing shyly in the middle of large ERPs not as the main database because they do not endorse Postgres for several reasons.

    I had no great difficulty using postgres in an advanced way, since I already knew Oracle. But I have to recognize that there are no tools like MSSQL tools. I usually use a tool that you have not commented on in your post, the DbVisualizer. I use this tool every day because I need to connect to several different databases on several different clients and it lets me connect with quality in any database including postgres.

    I agree 100% “The Postgres Tooling Stinks”

    check this tool maybe you find it interesting: https://www.dbvis.com/

  6. PGManage is pretty good (https://github.com/pgmanage/pgmanage). A far site stronger than the pgadmin4 rewrite. Also, if people have switched to Jetbrains Rider, the db support in there is excellent. I have a feeling it’s the DataGrip code, or a subset of. In any case, you get it in the box.

    For the record, if anyone’s thinking of jumping from SQL to PG, don’t let the tooling put you off. I’ve been with SQL for 20 odd years and PG for only a couple. The first major greenfields project I’ve done with PG has just gone live – and PG was incredible. There are things that it brings to the table where SQL doesn’t even get close – for example, but definitely not limited to, JSONB. Take the plunge. It’s worth it.

    One other thing. While, yes, I agree that SMSS is ‘the best in the world’, it is missing one massive feature … a dark theme!!

    1. Damien, Great thoughts… and yes… no dark theme indeed. 🙂

      I hope that the upcoming posts about my dive back into Postgres come across a little more even-handed. I’m not trying to say that it’s all bad, but tooling has specifically made the query tuning job harder than I expected after getting used to what SQL Server has to offer. Well, that, and something like SSDT for doing a true “database as code” workflow to manage the overall development and deployment.

      Thanks again!

  7. Hi Ryan,

    I too have been a SQL Server dev/dba since 2001. We recently migrated from SQL Server to AWS Aurora PostgreSQL flavor. I ported the stored procedures/functions to PL/pgSQL and helped with Rails apps. One of things you haven’t blogged about yet is the “environment” in which you do “server programming” (not of the C / dblib type but of the T-SQL or PL/pgSQL/etc type). What I mean by this is that in SQL Server, T-SQL and SQL coexist much better than PL/pgSQL and SQL in PostgreSQL. Actually, they don’t/can’t, you must “pick one”. But PostgreSQL has many more dynamic language options to chose from (Python, Perl, Tcl/Tk, etc). I much prefer what Microsoft has done, I prefer not having to know about this aspect or be aware of it. There are anonymous blocks which I find myself writing mostly these days and previously, I just fire up SSMS and write some T-SQL and be done. PL/pgSQL isn’t the same as T-SQl and it has it’s own (many more?) nuances.

    I am writing to let you know about the GUI I found after a two year search-and-try-to-use-as-many-products as I could. This is my opinion but all the products you have mentioned are horrible (my fall back is pgAdmin3). You can’t even use pgAdmin3 for PostgreSQL 10+, you must pgAdmin4 (and it’s “more” horrible). Yes, there are folks who have patches and BigSQL has jumped in… but still, it took a lot of folks by surprise to cut off pgAdmin3 support at PostgreSQL 10.

    Right now, I mostly use Aqua Data Studio (https://www.aquafold.com/aquadatastudio) from AquaFold. It’s the closest thing I have found to SSMS such that I can be productive after learning/reading the ADS documentation (and asking for help from support). I have started to pay for and use (but not very often) DBeaver but it’s similar to SQL Workbench but has more features (and it’s an Eclipse app).

    I can get multiple record sets in ADS and it even has a “GO”. I can get returned recordsets directly into Excel with a single click. And it has a pretty good data import/export “wizard”. It isn’t as nice around the edges as SSMS but I am glad I found it (everything else bad IMHO). The drawback is that when I write my scripts, I must remember to remove the ADS specific stuff (e.g. “GO”) to permit them to run via psql or JDBC/libpq (we use another tool for migrations).

    Good luck on your journey. I do miss the SQL Server days but I am glad I made it PostgreSQL, especially AWS Aurora’s flavor of it. I must say that the PostgreSQL community is very, very good. The resources I find and the depth amaze me.

    So I don’t think it’s all bad in PostgreSQL land, it’s just the natives don’t know anything but psql. 😉 It’s hard to find a UI tool worth using.

    1. Allen,

      I’m continually amazed how many have thoughtfully responded to this post! Yours is no exception and I’m thankful. I do plan to put an update out soon, but time has been short the last week.

      We too, are using AWS Aurora for this project… so I understand exactly where you’re coming from.

      I really appreciate your insights on the language piece of this puzzle. This, again, leans very heavily on the Open Source nature of the whole thing. Everyone gets their preferred choice and, to some extent, feels like it slows other innovations. That’s something I did honestly come to appreciate about staying within a paid stack like Microsoft… and admittedly I don’t have experience with something comparable like Oracle. Because they’ve chosen to support one language, for instance, they can innovate in other areas more quickly. At least, that’s how it feels.

      As for client, I’ve been using DBeaver for the last week and it is the best I’ve found so far. Are features like Excel export and “GO” only part of the paid version?

      Thanks!
      Ryan

  8. Just saw this – thanks for the kind words Ryan 🙂 Unfortunately I’m not really involved in UI tooling development at Microsoft, but rather in lower-level database access and O/RM work… Unfortunately UI tooling is definitely one place where PostgreSQL is clearly inferior to SQL Server, I’ve heard this complaint in many other places. But I definitely think there’s hope for improvement in this area for the near future, stay tuned…

    1. Shay, Thanks for the comment!! I didn’t expect your focus to change, but I can only assume your hire does mean some additional investment in PostgreSQL overall from Microsoft… which is a win-win for everyone in my book.

      Excited to see what the future will hold in EF and other Npgsql areas. 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.