Schedule - PGConf.EU 2011

Improving VACUUM Suction

Date: 2011-10-20
Time: 09:30–10:20
Room: Amsterdam 3
Level: Intermediate

PostgreSQL's transaction visibility model has one major downside. Every block written to the database has to be revisited and rewritten at least once, to perform multiple types of visibility cleanup. Those writes are even more frequent if older items are regularly updated or deleted from the database.

While the default settings in the database work fine for smaller installations, larger ones can find that this vacuum cleanup work is extremely hard to handle. If your PostgreSQL installation reaches the terabyte scale, you will end up grappling with vacuum problems. The only question is how long you can put it off, not whether you'll have to deal with the issue.

And making changes to the configuration parameters involved requires changing some difficult to understand tuning parameters. But there are several options available to help here, particularly if you learn how to track and tune the background activity done by the autovacuum daemon. This session will cover:

• Monitoring if VACUUM is working
• Logging troublesome vacuum work
• Avoiding peak periods with server configuration changes
• Understanding and estimating the activity of cost-based vacuuming
• Improvements proposed for PostgreSQL 9.2 (with backports to earlier versions via source code builds)

Speaker

Greg Smith

Platinum Sponsor

Gold Sponsors

Silver Sponsors