When you wanna make big database deployments with as little downtime as possible, it’s time to learn to use Amazon’s fully managed blue/green deployments. If you’re not familiar with the topic, start with this topic primer before reading Amazon’s docs, then read these testimonials on HackerNews.

When you need to read data before you modify it, don’t. Hahaha, easy for me to say, right? Craig Ringer explains why in more detail, and gives alternatives including single-statement updates and isolation level hints.

10 underrated features of PostgreSQL by Chris Travers. The post over a decade old, and yet there are still gems in here that don’t get enough use today, like functional indexes and partial indexes.

When you want to make a query run faster, the first place to start is by reading its explain plan. That page is a good primer on how to get a query’s explain plan and interpret the output.

Technique of the Week:
Getting Tuning Help

When you need help tuning a query, and you’re okay with sharing the query publicly, get the explain plan by putting this before the query, and then running it:

Execute the query, and instead of getting query results, you’ll get the plan in JSON format. Copy that, and then open explain.dalibo.com and paste it into the plan box. Copy/paste the query into the query box as well. When you hit submit, your query plan will be shown publicly – that’s important, because you don’t wanna share anything that your company wouldn’t wanna see online. (If you don’t like those graphical plans, another plan-sharing option is explain.depesz.com.)

Save that link, and then go to a Q&A site like:

• DBA.StackExchange.com – like Stack Overflow, but this is where database administrators hang out.
• The #sql-help channel of the Postgres Discord
• The #dba-help channel of the Postgres Slack
• StackOverflow.com – but I’d actually treat this as a last resort for tuning questions because the number of questions is so high.

Then keep your browser open to that page for at least 30 minutes. In the first 30 minutes, comments often come in fast & furious – not necessarily answers, but comments that are trying to help you refine the question and brainstorm different solutions. The more interactive you are in those first 30 minutes, the better answer you’ll eventually get.