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
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.
As you work through vacuuming Postgres databases, you’ll get to apply what you’ve learned from within your browser so that there's no need to use your own machine to do the exercises. The Python environment inside of this course includes answer checking so you can ensure that you've fully mastered each concept before learning the next concept.
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
9. Next Steps