Grant Fritchey: Learning PostgreSQL With Grant: Introducing VACUUM

While there are many features within PostgreSQL that are really similar to those within SQL Server, there are some that are unique. One of these unique features is called VACUUM. In my head, I compare this with the tempdb in SQL Server. Not because they act in any way the same or serve similar purposes. They absolutely do not. Instead, it’s because they are both fundamental to behaviors within their respective systems, and both are quite complex in how they work, what they do, and the ways in which we can mess them up. VACUUM is a complex, deep, topic, so this article will only act as an introduction. I’ll have to follow up with more articles, digging into the various behaviors of this process. However, let’s get started. VACUUM, and the very directly related, ANALYZE, are vital processes in a healthy PostgreSQL environment. Most of the time these will be running in an automated fashion, and you’ll never deal with them directly. However, since these processes are so important, I am going to introduce them now. The PostgreSQL VACUUM Process At its core, VACUUM is pretty simple. PostgreSQL doesn’t actually, physically, remove the data when you issue a DELETE statement. Instead, that data is logically marked as deleted internally and then won’t show up in queries against the table. For an UPDATE statement, a new row is added and the old row is logically marked as deleted. As you can imagine, if nothing is done, your database will eventually fill your disk (unless you define a TABLESPACE for the tables and limit its size, and that’s another article). The first function then of VACUUM is to remove those rows from the table. That’s it. Nice and simple. Well, of course it’s not that simple. VACUUM has a second behavior called ANALYZE. The ANALYZE process examines the tables and indexes, generating statistics, and then stores that information in a system catalog (system table) called pg_statistic. In short, VACUUM ANALYZE is the PostgreSQL equivalent of UPDATE STATISTICS in SQL Server. I told y[...]