How to Read the Buffers Numbers in Explain Plans
You’re tuning a Postgres query and you’ve been told to run EXPLAIN with the BUFFERS option, like this: PgSQL EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) 1 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)…
Query Exercise Answer: Here’s Why The Index Gets Smaller with Updates.
In last week’s Query Exercise, I asked you why updating a table reduced its index sizes. Our table had a date column called last_update_date, and when we set that date…
Query Exercise Challenge: Why Is This Index Getting Smaller?
This exercise can be done on any version of Postgres, and doesn’t require any other data – just runs by itself. Let’s start by creating a table with an index:…
Smart Postgres News and Techniques 2025 Vol 1
Idle transactions cause table bloat, as Umair Shahid explains, because Postgres has to leave dead tuples in place. At first this looks like a ChatGPT-generated post, but hang in there,…
Smart Postgres News and Techniques 2024 Vol 15
If you wanna learn about Postgres data types, the first several modules of Aaron Francis’ Mastering Postgres class are free. While you’re at it, PGconf.eu’s videos from October are up…
Smart Postgres News and Techniques 2024 Vol 14
If you want to learn database concepts in free YouTube videos that sound like you’re listening to a casual friend chat over drinks, check out Hussein Nasser’s database engineering playlist.…
Using Materialized Views to Make Queries Run Faster
Say you’ve got a reporting query that takes a long time to run. I’ll use the Stack Overflow database as an example: PgSQL select u.displayname, u.location, u.id, sum(1) as acceptedanswers…
Announcing Fundamentals of Vacuum Early Access
When I first started working with Postgres back in 2017, one of the first big surprises for me was the vacuum process. Our query performance kept degrading, the database kept…
The Fundamentals of Select: An Introduction to Postgres Performance
You’re a developer who stores data in PostgreSQL. You’re comfortable writing queries, perhaps making indexes. But you’re UNcomfortable explaining why a query runs slow, so let’s fix that in this…
Smart Postgres News and Techniques 2024 Vol 13
Struggling with vacuuming? Got a table where you’re constantly deleting the oldest data, and vacuuming can’t keep up? It’s time to investigate partitioning with Lee Asher. Want a production PostgreSQL…