PGSQL Phriday

Invitation from Dian M Fay

The host for this month’s event is Dian Fay, someone I’ve only met virtually because of some excellent content Dian has published for previous PGSQL Phriday blogging events.

For me, that speaks to the power of these little monthly challenges, prompting a group of people to write about a common topic because we all learn so much from one another. I’ve been really blessed by the detail and thought process that Dian provides in each blog post.

For the challenge this month, Dian is asking about a topic near and dear to me, managing database changes!

---

The challenge from Dian

This month’s topic is database change management, aka schema evolution. I’ve been doing this in one form or another, using one framework or another (and on one less-memorable-than-you’d-think occasion writing my own in a thousand lines of Ant XML) for almost as long as I’ve worked in software. If you interact with databases in more than a read-only capacity, you’ve probably done your share of it as well. It’s common, it’s necessary, it’s not very glamorous.

Some starting points:

• how does a change make it into production? Do you have a dev-QA-staging or similar series of environments it must pass through first? Who reviews changes and what are they looking for?
• what’s different about modifying huge tables with many millions or billions of rows? How do you tackle those changes? Do you use the same strategy for smaller tables?
• how does Postgres make certain kinds of change easier or more difficult compared to other databases?
• do you believe that “rolling back” a schema change is a useful and/or meaningful concept? When and why, or why not?
• how do you validate a successful schema change? Do you have any useful processes, automated or manual, that have helped you track down problems with rollout, replication, data quality or corruption, and the like?
• what schema evolution or migration tools have you used? What did you like about them, what do you wish they did better or (not) at all?
• tales of terror in the Kletzian mode are also of course very welcome!

Invitation from Michael Christofides and summary post of all submissions

Believe it or not, we’re now eight months into this PGSQL Phriday thing and I couldn’t be happier with the community support. So many really smart, thoughtful, and community-minded folks learning from and helping one another! 🎉

This month, Michael Christofides of pgMustard is our host and he’s chosen pg_stat_statements as the topic.

I’m really excited for Michael to host this month because he’s been such an encouragement to me throughout my PostgreSQL journey. Not long after I started using PostgreSQL again full-time, I went looking for tools that could help me understand EXPLAIN output better. When I happened upon pgMustard, I didn’t just find a great tool, but a sincerely kind chap that continues to work (pretty silently) to help the PostgreSQL community in many ways. He’s one half of the weekly Postgres.fm podcast as well. Well worth a listen!

Check out the full invitation and “rules” for participating! I can’t wait to hear all of the ways folks are using pg_stat_statements to improve their usage of PostgreSQL!

---

The challenge from Michael

I picked pg_stat_statements as the topic for a few reasons! Firstly, I wanted a topic I could easily contribute a post on myself… but more importantly, I wanted one that people with all sorts of different backgrounds and experience levels could weigh in on. It’s also the most common answer to the question “What’s your favourite extension?” in Andreas’ excellent PostgreSQL Person of the Week series.

So, please feel free to take your post in any direction you want, as long as it has something to do with pg_stat_statements!

Invitation from Lætita Avrot and summary post of all submissions

Lætita is a long-time PostgreSQL DBA, developer, psql evangelist, and all-round 💯 contributor to the Postgres community. I always appreciate her approach to helping people learn about PostgreSQL and encouragement for folks to get involved.

**Trigger warning**: This challenge is about… triggers! (sorry, I couldn’t resist)

Check out the full invitation and “rules” for participating! I can’t wait to hear both sides of the debate!

---

The challenge from Lætita

Your mission, should you choose to accept it, is to write a blog post by Friday the 7th of April 2023.

I’m very excited because I choose a very controversial topic: triggers! Do you love them? Do you hate them? Do you sometimes love them sometimes hate them? And, most importantly, why? Do you know legitimate use cases for them? How to mitigate their drawbacks (if you think they have any)?

Invitation from Grant Fritchey and summary post of all submissions

Grant is someone I’ve looked up to in the SQL Server and #SQLFamily community for a long time. It’s an honor to now work alongside him at Redgate.

Grant has a long career and deep experience with SQL Server. Much like I struggled 5 years ago, he’s now trying to learn the ins and outs of PostgreSQL, matching the pieces up against what he already knows. The request to share your best PostgreSQL learning tip is listed below from his invitation post.

As an aside, one of the reasons I suggested starting an event like #PGSQL Phriday is because I know many people that end up in the same spot; trying to learn how to use PostgreSQL well but not knowing where to start based on where they are coming from. Your tidbit… your “AH HA!” moment… could be the thing that helps someone overcome their hurdle they’re currently struggling to overcome.

Please consider contributing a blog on March 3, 2023. See the rules in Grant’s invitation post or check out the “Bloggers” section of the rules page.

---

The challenge from Grant

I will not even attempt to hide it, I’m very much in the learning phase of my PostgreSQL journey. As such, I’m constantly picking up new facts. However, I’m interested in that one thing that you wish someone had told you. That tiny bit of “AH HA!!!” information that you have now, but it took you a while to find. Just that teeniest, tiniest slice of the behavior of PostgreSQL that proved to be a game-changer for you.

In short, help me and all the others out there trying to learn this stuff. Make learning PostgreSQL just a little bit faster & easier for us. Please.

Invitation from Ryan Lambert and summary post of all submissions

Ryan is a🧙 with PostGIS and OpenStreetMap data, and an all around awesome person, too! It didn’t surprise me to see that he would choose something around mapping or non-relational data and how it’s used in Postgres. The challenge listed from his invitation post is below.

Please consider contributing a blog on February 3, 2023. See the rules in Ryan’s invitation post or check out the “Bloggers” section of the rules page.

---

The challenge

How are you using Postgres? For relational data only, a mix of relational and non-relational, or primarily non-relational data?

• What non-relational data do you store in Postgres and how do you use it?
• Have you attempted non-relational uses of Postgres that did not work well? What was the problem?
• What are the biggest challenges with your data, whatever its structure?
• Bonus: How do you define non-relational data?

Invitation blog post by Henrietta Dombrovskaya and summary post of all submissions

Quick aside from Ryan: In my opinion Hettie picked the perfect topic for the start of a new year! Please read the full invitation post linked above (it’s a great read!) and then contribute a post this month! We all have something to share and learn about more effectively managing and learning PostgreSQL!

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?

Invitation by Pat Wright and summary post of all submissions

For the third installment of PGSQL Phriday, Pat is looking ahead to 2023 and what community means to… well… the PostgreSQL community. In his words…

---

What is the PostgreSQL community to you?  

I personally am relatively new to the PG community. I only started attending events and working with PG about 5 years ago.  I’ve talked to a lot of different people in the community and I’ve found many different people have different ideas of what the community is and where it is.  

I would love to hear more varied views and opinions on this topic. Here are some ideas if you need help getting started with a blog post.  

• List of resources you commonly use in the community? 
• Favorite event(s) that you attend to make you closely connected with the community? 
• A story of help/work provided by you or someone else in the community? 
• How would you get started in the community?  

Invitation by Andreas Scherbaum and summary post of all submissions

Your #PGSQLPhriday task

Describe how you do backups for your PostgreSQL databases.

• Which tool(s) are you using, where do you store backups, how often do you do backups?
• Are there any recommendations you can give the reader how to improve their backups?
• Any lesser known features in your favorite backup tool?
• Any new and cool features in a recently released version?

Bonus question: Is pg_dump a backup tool?

Invitation by Ryan Booz and summary post of all submissions

For this first event, I want you to write a blog post that shares three of your go-to Postgres best practices, but write it in the form of the game Two truths and a lie.

For example, perhaps there are three configuration settings you always modify whenever setting up a new cluster. You could share why these settings are important to modify and how you find the correct values, but for one of them, use Ilya’s comical approach to teach us the correct way to set the value. (ie. “I never set shared_buffers to more than 128MB to conserve server resources.” )

Maybe you have three go-to ways of monitoring your cluster or multiple SQL options to overcome a tricky query problem many PostgreSQL developers face. You’re limited only by your imagination.

And obviously, at the end of the blog, be sure to clearly identify which of the three tips was a lie and how someone should actually modify the setting or write that tricky SQL statement.