PGSQL Phriday #004: PostgreSQL and Software Development

Thanks to Ryan Booz, we now have the #PGSQLPhriday blogging series. When Ryan shared the idea with me in October, I enthusiastically agreed to host the January discussion without even reading the instructions in advance! The most challenging part of these instructions was to write a short blog post! Well… I will try to be as concise as possible, although I can’t promise! 

Without further ado, welcome to the New Year edition of #PGSQLPhriday series! Today’s topic is:

PostgreSQL and Software Development

As much as we all enjoy writing SQL and “doing everything in Postgres,” we are very well aware that databases do not live in a vacuum and were not invented for our pleasure and enjoyment. We may come up with new extensions and innovative indexes; we may improve the query planner and speed up backups, but if application developers don’t use these features, nobody will appreciate our efforts.

Those of you who, like I, often interact with application developers know how often these developers are upset with us, with PostgreSQL, and with databases in general. In addition to the usual complaints that “databases are slow” and “database developers are slow,” a frequent complaint is that database developers are horrible engineers!  

And you know what – I agree! 

There are shining examples like pgbackrest, where the source code is stored in GitHub; the tests are there, and installation instructions are clear and specific. But for most of the “ordinary us,” it’s that script.

Why is it that when application developer wants to share their work, they would say: let me share this library with you. And in similar circumstances, a database developer, a DBA, and even a consultant would say: let me give you a script!  

A script is copied from an email to the command line, from the command line to Data Grip, and from there to the local file, and when nobody can remember where this file is, we go back to the original email. A script is something you can’t “install” in the database, and nobody guarantees that it will work in the next version of Postgres. So how can application developers trust us?

— How can I find the size of the bloat? – There is a script; let me find it…

— How can I find out all object permissions for a user?- There is a script somewhere on the internet; let me Google it…

— Is there anything in Postgres that can be used in place of Oracle $ views? – No, but there are scripts…

And the list goes on!

Now that I have expressed my frustration, here is what I would love to hear from other people in the World of PostgreSQL:

• Do you have any of your own scripts which make your life as a DBA/Database developer/consultant easier? What do they do? Do you share them with others, or are they hidden in your secret toolbox?
• Do you store your SQL code in GitHub (except when your company requires it)?
• Do you use pgTAP? Do you think it’s a good practice to have pgTAP tests in your repo, or does it not add value? 
• There are many “scripts” to do all sorts of things in PostgreSQL that are stored everywhere and nowhere specifically (like the abovementioned bloat scripts, lists of unused indexes, you name it). Do you think they should be a part of the PostgreSQL official documentation and re-verified for each new major version?
• Did you ever have (frustrating) interactions with application developers? Did they end with some truce?
• Anything else related to that topic you would like to share?

How does #PGSQLPhriday work?

• Post your blog post about “PostgreSQL, you, and application developers” by Friday, January 6th.
• Include “PGSQL Phriday #004” in the title or first paragraph of the blog posting.
• Link back to this blog posting. If you don’t link back to the invite, you won’t be included in the summary list of blog posts.
• Announce your blog post in one or any of these places:
  • Twitter, using the #PGSQLPhriday hashtag
  • LinkedIn
  • PostgreSQL Slack in the #pgsqlphriday channel
  • Responding to the thread on the pgsql-general email list that announced this month’s blog topic
• Interact with other blog posts about this topic! We hope to see conversations happening on LinkedIn, Twitter, or in the blog comments.

Looking forward to your blog posts!