Overview

Part 1 (this post) looks at some principles and introduces some of the tools in your arsenal.

Part 2 covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or not.

Part 3 dives deeper into identifying, investigating and mitigating slow queries.

Part 4 covers indexing.

Part 5 covers rewriting queries.

TL;DR

• Scaling up or out is more expensive than spending a couple of hours optimising
• Get answers, not data
• Lazy load lookups
• Eliminate non-essential queries
• Don’t use SERIALIZABLE isolation
• Make sure auto-analyze is running and have a manual ANALYZE schedule as a safety net
• Denormalising is usually the wrong solution to performance issues

Introduction

Welcome to this series of blog posts which will hopefully walk you through everything you need to know about improving query performance, specifically in PostgreSQL. The basic principles apply to all databases, though, even if implementation differences mean that different databases find different things painful.