PGSQL Phriday

Invitation from Pavlo Golub

To finish out 2023, Pavlo is inviting us to think about PostgreSQL events and write about our experiences. While the world of in-person events started to open up again in 2022, there’s no doubt that 2023 has been really busy in all parts of the world, especially for our beloved PostgreSQL database.🐘

As a retrospective, and in anticipation of things to come, Pavlo’s invitation includes many different prompts to help you get started. And there’s no doubt that PostgreSQL users old and new will benefit from hearing your experience of how to get the most value out of attending a conference.

Pavlo’s invitation…

As PostgreSQL enthusiasts, we all know that the community’s heartbeat lies in PostgreSQL Events, whether local meetups, big conferences, or virtual gatherings, that have become prevalent during the COVID-19 pandemic.

During a thrilling conference season, the PostgreSQL community is abuzz with activity. We’ve just wrapped up the PASS Data Community Summit, which featured a dedicated PostgreSQL presence that left us inspired and eager for more. The DOAG conference is underway, where numerous PostgreSQL experts are sharing their insights. And on the horizon, we eagerly anticipate the upcoming PostgreSQL Conference Europe in just a couple of weeks.

I invite you to share your PostgreSQL Events experiences in your dedicated blog post. Whether you’re a seasoned attendee, a speaker, a trainer, a sponsor, an organizer, or a first-timer, your unique perspective contributes to the canvas of the PostgreSQL community.

Read the rest of his invitation and prepare to publish your post this Friday, December 1, 2023.

Invitation from Chris Ellis

I first met Chris in Brussels for the FOSDEM PGDay, even though we had known each other virtually for a bit. He seems to be everywhere all at once with PostgreSQL stuff right now, including the design of some amazing little LED pins for PGDay.UK a few weeks ago.

Chris has enthusiastically stepped this month at short notice to provide the invitation below. I think it’s a perfect topic! I know I always learn best from the real-world experiences of others!

Usecases and Why PostgreSQL

On Friday, October 6th 2023, publish a post on your blog telling a story about what you (or your team, client) built with PostgreSQL and how PostgreSQL helped you deliver.

I’d love to read about the weird and varied things that people are using PostgreSQL for. If you think your usecase is boring, I’m sure it will be of use to someone. Plus you can always focus more on the story and how PostgreSQL helped to deliver a project, or could have, or didn’t!

• Did things like: Full Text Search, JSONB, PostGIS, etc enable you to build a better application
• Did using PostgreSQL remove the need for other dependencies, or change your approach
• Did you learn some SQL which made you realise stuff has moved along alot since SQL92

Maybe it was that disaster of a project where in hindsight using PostgreSQL would have been a winner. Or maybe PostgreSQL hindered you, so let’s hear about what went wrong.

I’d like to see people focus on telling some real world, practical examples of where PostgreSQL shined like a crazy diamond.

Read the invitation blog and get writing!!

Invitation from Ryan Booz and summary post of all submissions

🎉 Woot! Woot! 🎉

Can you believe it? We’ve made it one complete year! This is the final invitation for the first year of PGSQL Phriday!

I’ll let the invitation blog and challenge summary speak for itself. Suffice it to say, PostgreSQL 16 is just around the corner!!!

The challenge from Ryan

On Friday, September 1, 2023, publish a post on your blog about something (or a group of things) that you’re excited about using when PostgreSQL 16 is released.

Try to give some background on why this feature or improvement is important to you. Maybe reference the `pgsql-hackers` mailing list and look at the discussion that led to this feature being merged into the next release.

What have done before this feature was available that you’ll do differently now? If possible, consider giving a brief example or code sample to help illustrate how it’s used. Get your reading audience excited to try this feature or improvement once they start using PostgreSQL 16!

I know these kinds of posts have already started to show up on various blogs. After all, we’re currently in beta 3 of PostgreSQL 16. But there are so many features and improvements, I think collectively we can shine some light on the value we get from the consistent cadence of PostgreSQL releases.

In other words, I’m hoping we don’t end up with only posts about Logical Replication and the new (awesome) pg_stat_io view. 😉

Go look at the invitation post and start

Invitation from Tomasz Gintowt

We’re a little behind on the invitation this month, so there will certainly be some wiggle room for posting responses over the weekend into Monday. Remember, this is all volunteer work. 😉

Tomasz is a new PostgreSQL friend for me and I love the topic he’s picked: Partitioning vs. Sharding. There are so many approaches in the PostgreSQL community around how to effectively and efficiently keep data light and accessible, including different approaches in various PostgreSQL extensions and database-related projects.

Let’s see how all of you approach this important data management tool!

---

The challenge from Tomasz

I’ve spent last few months on digging into partitioning and I believe it’s natural step when our database is growing. At some point you have to realize that single table it’s not enough for you. What we can do ? Lets use partition or sharding. Many developers still don’t know the difference, use these word interchangeably. I’d like to make a PostgreSQL word a little bit better and share knowledge about partitions and shards.

Let’s put some light on difference between these two concepts. Please focus on:

• difference between architecture
• what business problem are you trying to solve
• performance tips
• how tables should be designed ( relations, primary keys, FK etc)
• best practices

Go look at the post and start working on your responses! When you do, ensure you tag #PGSQLPhriday on one of the social platforms so that it gets noticed.

Invitation from Alicja Kucharczyk and summary post of all submissions

Quick story! Alicja was one of two people that hoped on an impromptu video call with me a few years ago after asking for some help connecting with the PostgreSQL community on Twitter. Since then, she and others like Andreas Scherbaum (host of PGSQL Phriday #002 and PostgreSQL Person of the Week) continue to pour lots of energy into the community.

I’m thankful for their support and efforts. So many of us benefit daily from contributions like theirs!

Now, onto the challenge for this month…

---

The challenge from Alicja

For this month’s PGSQL Phriday, we’re focusing on pgBadger, an exceptional PostgreSQL log analyzer renowned for its swift analysis and detailed reporting capabilities. pgBadger, a handy Perl script, has time and again proven itself an indispensable tool in the realm of database health checks and troubleshooting. 

Your mission, should you decide to accept it, is to pen an enlightening blog post about pgBadger on Friday, July 7th, 2023. 

Here are some thought-provoking questions to help guide your writing: 

• How has pgBadger improved the performance of your PostgreSQL database? 
• How do you leverage the reporting capabilities of pgBadger in your routine tasks? 
• Have you encountered any challenges while using pgBadger, and how did you overcome them? 
• Are there any unique or innovative ways in which you have used pgBadger? 
• How do you use information from specific tabs in the pgBadger report? Is there a particular tab, like ‘Events’, ‘Vacuum’, ‘Locks’, or ‘Top’, that has provided invaluable insights? 
• Could you describe a use case where pgBadger helped you “save the day”? 

You might find it helpful to explore the official documentation, GitHub repository and this sample report as you research and write your blog post. 

Invitation from Dian M Fay and summary post of all submissions

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?