PGSQL Phriday

Invitation from Ryan Booz

Many of the PostgreSQL newbies that I’ve engaged with lately are coming from other databases often because of company priorities to start adopting Postgres and migrating projects. Inevitably, one of the first things they want to learn once data starts flowing is how to tune queries. They typically have a lot of experience doing this type of work previously, just not with Postgres.

With that as the backdrop, the invitation for March is presented below.

The Challenge

For this month’s PGSQL Phriday, I’m asking you to discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.

For this post, try to think beyond the standard answers, otherwise we’ll have a lot of similar posts that reference EXPLAIN plans, stats views, and a few online plan viewing/analysis tools. You can absolutely talk about those things (they are essential after all) but try to focus on something about your process when a query is about to bring the server down.

• How did you identify that this was the problematic query?
• Where do you start when trying to dig in to get things running again?
• Could you solve this particular problem with configuration changes rather than query changes?
• How do you get a representative EXPLAIN plan and with what options?
• How did you test your modified plan or server configuration?
• If you don’t have access to Production, how could you verify things?
• Do you use a product like Postgres.ai or Neon to branch the database for quick and easy iterations without impacting production?
• { insert whatever question you think is valuable to answer! }

I’d be delighted to have a group of posts to point new users to around the topic of query tuning. While there are many presentations and resources on the pieces that help get the job done, making it personal and talking about your specific environment, challenges, and “ah ha!” moments to solve the query tuning case! And who knows, your post might be the foundation of a great conference talk in the future. (hint, hint… 😉)

Remember the (simple) rules

1. Write your article and post it Friday, March 8 any time. We’re pretty lenient here, so if you post it early or sometime over the following weekend, that’s OK.
2. Use “PGSQL Phriday #016” or “#PGSQLPhriday 016” in the title or first paragraph of your post.
3. Link back to this invitation post at softwareandbooz.com. If you don’t, I may not include it in the final summary post.
4. Announce your post somewhere on social media or the #pgsqlphriday channel on the PostgreSQL Slack. Use the hashtag #pgsqlphriday to get noticed and included.

Invitation from Lætitia Avrot

Lætitia is hosting for the second time, and the topic is sure to get a great discussion going – just like the first time she hosted and the conversation really Triggered some healthy debate. 🙂

Lætitia’s invitation and challenge

It’s sometimes difficult to find a great topic and I like the kind of topics where there is no consensus. This one is a little particular: in my humble opinion, the database community has a consensus, it’s that developers don’t agree.

Let’s organize a debate between database people and developers! I hope this will lead to great conversation and better understanding between those two groups!

The challenge

Without further ado, here is the topic of the month: UUDI! In particular, I’d like to hear about (feel free to embrace the subject and not follow this guide):

• Your use case (why do you absolutely need them)
• The problems you encountered (including performance issues and how you solved them)
• what kind of UUID do you use?
• what are your internal rules about them (all UUIDs, mix between regular IDs and UUIDs, no IDs) and how and why you came up with
• UUIDs with sharding
• sorting UUIDs
• extension you might use
• any other thing related to UUIDs

Invitation from Pavlo Golub and summary post

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)?