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:
  • 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!


Filed under publications and discussions

12 responses to “PGSQL Phriday #004: PostgreSQL and Software Development

  1. Pingback: PGSQL Phriday #004 – PGSQL Phriday

  2. Pingback: PGSQL Phriday #004: PostgreSQL and Software Development | ads' corner

  3. Pingback: PGSQL Phriday reminder | The World of Data

  4. Pingback: Henrietta Dombrovskaya: PGSQL Phriday reminder - - Latest Web Hosting News

  5. Pingback: Henrietta Dombrovskaya: PGSQL Phriday reminder - - Latest Web Hosting News

  6. Pingback: Henrietta Dombrovskaya: PGSQL Phriday reminder - - Latest Web Hosting News

  7. Pingback: And one more #PGSQLPhriday reminder!!! | The World of Data

  8. Pingback: PGSQL Phriday #004 Postgresql And Software Development – Sql Insane Asylum (A Blog by Pat Wright)

  9. Pingback: PGSQL Phriday #004: Sequence Survey | the gabriellephant

  10. Pingback: PGSQL Phriday #004 Recap | The World of Data

  11. Pingback: Database DevOps and Code management with PostgreSQL – Software and Booz

  12. Pingback: Postgres and Software Development: PGSQL Phriday #004 - Blog @ RustProof Labs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s