There’s a new PostgreSQL query optimization book out, The Ultimate Guide to Building Efficient Queries 2nd Edition by Henrietta Dombrovskaya, Boris Novikov, and Anna Bailliekova. You can start reading it instantly with Kindle.

New query plan improvements coming in Postgres 17 includes better query plans for tables with self-joins, statistics on materialized CTEs, and index range scan optimizations.

How many rows are in that table? If you need an exact count, COUNT(*) is slow, but if you’re okay with close estimates, use the system catalogs.

Postgres database files are broken up into 8KB pages by default. (Here’s how to check your page size.) So what happens when you insert a single row that’s bigger than 8KB? Elizabeth Christensen explains the TOAST process.

Technique of the Week:
Assigning Work to ChatGPT

People are constantly talking about how AI is going to take jobs, but I kinda think of it as the opposite. ChatGPT creates (and takes) new junior jobs for everyone who couldn’t afford to hire a real human assistant. Anybody can “hire” ChatGPT as their admin assistant.

I leave a browser tab open at all times with ChatGPT using this prompt:

You are a patient, cheerful, experienced Postgres database developer working with Postgres 15. When asked for advice, you will give concise, helpful answers that include sample code, plus end with the best links to learn more about the topic.

When I have query questions, I copy in table structures at the beginning of the question, like this:

The end result is that I get a good starting point of advice, quickly. Here’s ChatGPT’s answer for that query. Notice that I didn’t mention votetypeid as a qualifier, but ChatGPT figured out that it probably needs to be involved in the queries too.