PostgreSQL for the SQL Server DBA: Tooling, Part 2 – It Gets Better

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.

WOW! I honestly didn’t expect to get quite so much notice and feedback on the first iteration of this post. The power of social media at work. 😉

But I’m so thankful that I did! It allowed me to quickly see that the support for PostgreSQL is stronger than ever, there are many opinions on tooling, and I have a ton to learn. That’s a challenge I’m up for!

I particularly appreciated the post that Rob Conery created in response, gently reminding me that when something starts Open Source and Linux, there’s often a plethora of command line tools ready to help. Much like Git (which is always used as the seminal ‘command line is more powerful’ tool example), something like ‘psql’ is really good at helping me get around to the things I often need to know quickly. I need to remember to consider the roots as I try to grow my understanding deeper.

To that end, I’m all for using command line tools when I can and it makes sense, particularly in database administration. But when I’m knee deep in a new feature and trying to help a young team, I’m still on the lookout for a tool we can use together to better develop and manage the PostgreSQL database day-to-day.

And so, I thought a quick update would be valuable based on the feedback I received here and through Twitter. As always, I’m learning alongside everyone else, trying to be as productive as I can for my employer, my teammates, and the larger community. Hopefully this is helpful part of that larger conversation.

Tooling – Part 2

As you’ll see in the previous post and comments, there are tooling options aplenty – something most of us have come to expect from an open source environment. Here’s where things are at two weeks later for me.

Azure Data Studio

What?? Azure… Microsoft… PostgreSQL?

YES!!

To be honest, most of this post was already written and focused on DBeaver before I got wind of the pending update to Azure Data Studio. I’ll have a second blog post in the next few days about my experience so far. That said…

As you might have noticed at the end of the previous post I mentioned that Shay Rojansky was hired by Microsoft recently. I didn’t expect that he’d start working on tooling specifically, but it did signal to me that they were putting more investment and energy towards PostgreSQL.

Microsoft announced today as part of the MVP Summit the beta release of a PostgreSQL extension for Azure Data Studio. I was able to get a sneak peak a few weeks ago and I’m excited about the potential. It’s clear that the ultimate goal is to provide access to the same great features they are building into this cross-platform data management application. I’ll have a separate post soon about some of my first impressions, but two highlights that I think hold the most unique opportunity for ADS to become a great tooling option for PostgreSQL.

  1. Community Extensions: Much like Visual Studio Code, Azure Data Studio is open source and is building out a platform for extensions built by the community. Some initial quick wins could be extensions for better monitoring and tuning… and a graphical query plan visualizer!! (hint, hint…)
  2. SQL Notebooks: I got to see the first examples of these at PASS Summer last year when Vicki Harp and the team presented and I think it’s an AWESOME idea. Having dabbled in and around data science for a few years now, the power of notebooks to store thought process while also displaying results in-line is so much fun. For the first time, you’ll be able to do this with SQL queries in SQL Server or PostgreSQL. YAY!

DBeaver

https://twitter.com/ryanbooz/status/1100612407934832640?ref_src=twsrc%5Etfw
Yes. I had the exact same thought!

After I got past the name, it turns out that DBeaver Community Editionis now my daily driver. I have no idea what the history is (time has been at a premium lately), but the overall layout and functionality comes close to my SSMS “expectations”. I’m not saying SSMS is the standard to reach for, simply that it helps me be more productive quickly because it’s more intuitive… for me.

The navigation layout follows a predictable pattern of feature separation, query editing just feels more intuitive (which is subjective for sure), and most important to me, DBeaver produces accurate DDL when I inspect database objects. As a developer and administrator ramping up on a new project, this kind of detail is especially important for me. Otherwise, I’m left wondering if my latest modifications were applied correctly.

As with most other PostgreSQL tools, DBeaver is cross-platform and able to connect to multiple SQL and NoSQL databases. I can’t currently speak to its performance on other databases, although I will be trying it with some of these platforms in the near future.

DBeaver is built on the Eclipse ecosystem, whatever your feelings on that are. One advantage is that the plugin architecture allows for some great free plugins, like export to Excel. Scoff as you might, it makes sending data to Product Owners, managers and users just a little be easier when non-standard data is involved.

And finally, DBeaver is one of the few options aside from PgAdmin that has a free, open source Community Edition that is very capable, while providing a reasonably priced Enterprise Edition for additional features and support. Oh, I almost forgot! It can be installed with Chocolaty too!

A Few Highlights

Most of the other tools I’ve looked at have variations on these features, so I’m not trying to claim that they are unique to DBeaver at all. But again, for whatever reason, finding and using them is just more intuitive for me (at this point).

Intuitive Server/DB Navigation

Easy Navigation

It might be a small thing, but being able to navigate objects in the database and refer to them consistently with others on the team is immensely helpful. When I talk about Views, Indexes or Functions, there’s no guessing. Again, remember that I’m often thinking about how I can best help others learn to use the DB effectively and having an easy, common way to get about makes this easy.

Also notice a really small thing within each database in DBeaver, access to two simple queries for Sessions and Locks. Again, coming from the SQL Server world, there are so many community contributors teaching and preaching the benefits of knowing some of these essential pieces of day-to-day work. Other tools certainly have something like this I’m sure, but this gave me a quick way to star to explore performance problems on development databases and elsewhere.

DDL/Data Access

I know it seems like I harp on DDL a ton, but one of my main objectives in this new job is to help bring the database into order. If I can’t easily see the data, the schema definition, and know the state of various objects, that is generally a non-starter for me

Easy access to all schema objects

Double-clicking on any object (table, view, triggers, etc.) will open up the edit window. As you can see, when it’s a table, properties and data preview are close at hand, and all parts of the table are visible through the tabs on the left.

Honestly, this seems like a good option for something like SSMS to have, rather than dealing with each of these objects separately… but I digress.

My one gripe at this point is adding columns to a table through the UI. I haven’t found a quick method to do it graphically. Generally I resort to straight SQL for these operations, but when I was creating a prototype this week, I was jumping back and forth wishing I could just quickly tab through some options. It’s minor.

Result Table Filtering

Inline results grid filtering

To be honest the first time I saw this was with DataGrip. I didn’t think it would be useful at all and mostly just assumed it was added as a checkbox to increase the feature count. But, I’m surprised how often I’m in a database and a co-worker asks if something exists and my first reaction is to double-click the table (display data) and then start typing a column and value to filter. It’s not my main method (I can still crank out “SELECT x FROM table WHERE…” as fast as the next guy…), but it is a useful little time saver now and again.

SQL Formatting and Intellisense

Another small detail that doesn’t work as well in other tools I’ve tried. And, it even bests the default SSMS capabilities (although, Red Gate’s SQL Prompt is a great tool that easily bests what’s built into DBeaver!)

When I’m typing, speed is generally more important to me, which means that I don’t use all CAPS for keywords in the SQL language. Yes, it doesn’t matter. But it does look better when things get complicated and I’m trying to show or teach someone about SQL. DBeaver is the first tool out of the box that allows me to set CAPS for auto-complete on intellisense and it generally just works. In other IDE’s, it will only go CAPS if I start the word with a capital letter. Arg.

When I’m typing, speed is generally more important to me, which means that I don’t use all CAPS for keywords in the SQL language. Yes, it doesn’t matter. But it does look better when things get complicated and I’m trying to show or teach someone about SQL. DBeaver is the first tool out of the box that allows me to set CAPS for auto-complete on intellisense and it generally just works. In other IDE’s, it will only go CAPS if I start the word with a capital letter. Arg.

The Others

A few other suggestions made it into the comments of the previous post(and hopefully this one too!). The short list includes:

Feel free to check them out and blog about them if you feel strongly about it. I’m sure someone in the community will be thankful you did!

At this point I don’t plan to look for others. Between DBeaver and Azure Data Studio, I can work more effectively with my team and provide better, faster service to my internal customers with PostgreSQL. I’m sure I’ll take a few minutes from time to time, seeing if any new contenders are making waves.

Stay tuned for more information and feedback about Azure Data Studio.

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?

PostgreSQL for a SQL Server DBA: Triggers

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.

While Triggers have gotten a bad rap in many circles and are often discouraged (great read by Joe Celko), they are still a useful tool in a handful of situations. Trying to implement my first Triggers in PostgreSQL has been one of many learning adventures lately. Here are a few key points I want to remember for next time and share with others.

They are implemented as Functions (Stored Procedures)

This caught me off guard at first. I’ve been working with and dealing with triggers in SQL Server since day 1. They are first-class citizens… objects that have their own code blocks and rules.

PostgreSQL approaches it differently. Any reusable code block, regardless of its true purpose is a Function of varying types. Triggers are no different. Therefore, you write the logic of your trigger in a Function and then call it by adding a trigger to the DML event of a table.

CREATE OR REPLACE FUNCTION user_log()
RETURNS TRIGGER AS
  $BODY$
  BEGIN
    INSERT INTO UserLog (....)
  END;

  $BODY$
LANGUAGE plpgsql;

Once the function is available, you “attach” it to the DML event of the table by creating the trigger.

CREATE TRIGGER ti_users
  AFTER INSERT
  ON Users
  FOR EACH ROW
  EXECUTE PROCEDURE user_log();
Triggers can be run per-row

Again, this threw me a little coming from SQL Server which executes triggers on a batch of rows, even if it’s only one row. This also means that when a trigger is the best place to check or modify data in an application, additional work often has to be done to iterate the data inside of the trigger. In PostgreSQL these batch-level triggers are called a statement triggers and don’t provide the same access to the batch of rows that were inserted/modified that SQL Server does with the INSERTED.* and  DELETED.* special tables

With row-level triggers, however, the function you name is called on every row that is modified. Obviously this comes with it’s own caution as well, but in the right circumstances it means that the data is easily accessible because the function is working on one row of data at a time. Row-level triggers also give you access to the row data that you’re used to, but in PostgreSQL you refer to the NEW.* and OLD.* tables.

CREATE OR REPLACE FUNCTION user_log()
RETURNS TRIGGER AS
  $BODY$
  BEGIN
    INSERT INTO UserLog (Username, Message) VALUES (NEW.Username,  NEW.Message)
  END;

  $BODY$
LANGUAGE plpgsql;
Triggers can have predicates!

This is actually one of the few things I like about triggers in PostgreSQL that SQL Server doesn’t have. While it might add confusion, it also makes it very easy to limit when the trigger function is actually called.

In SQL Server, the appropriate trigger is always called when the DML event is performed. We often have to jump through a bunch of hoops (SELECT… EXCEPT) to see if the data we care about has changed before we do some kind of work.

In PostgreSQL it is so much easier. When you create the trigger that attached the function to the table DML, you add the appropriate predicate(s).

CREATE TRIGGER tu_users
AFTER UPDATE
ON Users
FOR EACH ROW
WHEN (OLD.FirstName IS DISTINCT FROM NEW.FirstName)
EXECUTE PROCEDURE user_log();

It’s that simple, and you can add more predicates to the WHEN clause. They just all have to be Boolean in nature.

It’s not all bad

In the end, I’m still missing most of what SQL Server offers in power, tooling and evolving technology. That said, Triggers in PostgreSQL has been one of the bright spots when they’re the right tool for the job.

PostgreSQL for a SQL Server DBA: A Series

I recently switched jobs, leaving a nearly 15-year career working with SQL Server. Although I have been using it that long, it has only been in the last 4 years that I finally realized how much I didn’t know about my favorite data technology stack, taking every opportunity to dig deeper and get more connected to the #SqlFamily.

Unfortunately, two months before joining KCF Technologies they transitioned to PostgreSQL because of the Aurora database offering from Amazon Web Services. The sales pitch behind Aurora is great and it has, in fact, saved them a ton of money. And at this point the database backing the application isn’t the complicated part of the application, but rather the the millions of real-time data points that stream into the service every minute (using a separate time-series database).

That said, it has been quite a transition trying to re-learn (or unlearn as the case may be) the PostgreSQL way of doing things and actually missing the huge economy of tooling that has grown up around the SQL Server community… tooling that’s largely missing in the Opensource DB world. Don’t get me wrong, I had some similar feelings 15 years ago when I started using SQL Server after spending a few years with PostgreSQL (of all things)… but this has certainly felt more challenging.

As I’m doing the learning, spending hours on Stackoverflow and a handful of other sites trying to get my bearings, I thought it would be good to write some of it down for my future-self and others that have the need to cross over at some level. PostgreSQL isn’t going away as an alternative, and both AWS and Microsoft have provided “one-click” startup options to use it.

There’s no specific agenda, but the first several posts will certainly share many of my frustrations trying to make the leap back(wards). But as I get my mind wrapped around each piece, I promise to share some positive things too. 😉


  1. PostgreSQL for the SQL Server DBA: Triggers
  2. PostgreSQL for the SQL Server DBA: The Tooling
  3. PostgreSQL for the SQL Server DBA: Tooling, Part 2 – It Gets Better