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
- 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.
- Use “PGSQL Phriday #016” or “#PGSQLPhriday 016” in the title or first paragraph of your post.
- Link back to this invitation post at softwareandbooz.com. If you don’t, I may not include it in the final summary post.
- Announce your post somewhere on social media or the #pgsqlphriday channel on the PostgreSQL Slack. Use the hashtag #pgsqlphriday to get noticed and included.