VACUUM Strategy: Autovacuum, FSM, Visibility Map Selena Deckelmann End Point Corporation @selenamarie Hi! • Postgres consultant for End Point • User Group Liaison for postgresql.org • Conference Organizer P g C o n 2 0 0 9 What we’ll cover • VACUUM basics: MVCC, FULL • The old Free Space Map (8.0->8.3) • The new Free Space Map (8.4-> the FUTURE) • B.A.: Before Autovacuum • A.A.: After Autovacuum • Autovacuum best practices P g C o n 2 0 0 9 Why VACUUM sucks • Lots of I/O • Long running VACUUM queries that annoy sysadmins, clients, DBAs • Different than other databases (manual) P g C o n 2 0 0 9 Why do we have VACUUM anyway? • Normal maintenance for dead rows • Extreme maintenance for table bloat • Preventing transaction wrap-around P g C o n 2 0 0 9 VACUUMing for normal maintenance Let’s start with: MVCC Multi Version Concurrency Control P g C o n 2 0 0 9 VACUUMing for normal maintenance MVCC - Pessimistic rollback behavior Old versions of rows stored in the same relation space P g C o n 2 0 0 9 A very simple example • SIMPLE. ABSTRACTED. • I am not explaining HOT today. • See Pavan’s presentation from PgCon 2008: http://www.pgcon.org/2008/schedule/ events/105.en.html P g C o n 2 0 0 9 How MVCC works Table “Cats are not very cute.” P g C o n 2 0 0 9