In the previous lesson on advanced indexing in Postgres, we learned that creating a multi-column index will speed up commonly executed queries. A multi-column index is used when you filter on multiple columns at once. This can reduce query speeds from O(n) complexity to O(log n)  which is similar to a single column index.

In this lesson, we will be introducing another concept, vacuuming, to improve table querying speeds for Postgres databases. This new concept will rely on a counter-intuitive understanding of Postgres destructive queries such as UPDATE and DELETE.

In addition to understanding what it means by vacuuming Postgres databases and how it decreases query speeds, you will learn about a set of properties called ACID as well as multi-version control. You will also learn about transaction blocks and how they're fundamental to Postgres.



  • Learn about database vacuuming
  • Learn about a transaction block
  • Learn what ACID means for database transactions

Lesson Outline

1. Destructive Queries
2. ACID and Dead Rows
3. Counting Dead Rows
4. Vacuuming Dead Rows
5. Transaction Blocks
6. Updating Statistics
7. Full Vacuum
8. Autovacuum
9. Next Steps
10. Takeaways