PGSQL Phriday

Invitation from Grant Fritchey

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.