MISSION 261

Vacuuming Postgres Databases

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.

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.

Objectives

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

Mission 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

optimizing-postgres-databases-data-engineering

Course Info:

Intermediate

The median completion time for this course is 4.7 hours. ​View Details​​​

This course is free and includes five missions. It is the second course in the Data Engineer path.

START LEARNING FREE

Take a Look Inside