Here's two ideas I had for optimizing vacuum, I apologize in advance
if the ideas presented here are niave and don't take into account
the actual code that makes up postgresql.
================
#1
Reducing the time vacuum must hold an exlusive lock on a table:
The idea is that since rows are marked deleted it's ok for the
vacuum to fill them with data from the tail of the table as
long as no transaction is in progress that has started before
the row was deleted.
This may allow the vacuum process to copyback all the data without
a lock, when all the copying is done it then aquires an exlusive lock
and does this:
Aquire an exclusive lock.
Walk all the deleted data marking it as current.
Truncate the table.
Release the lock.
Since the data is still marked invalid (right?) even if valid data
is copied into the space it should be ignored as long as there's no
transaction occurring that started before the data was invalidated.
================
#2
Reducing the amount of scanning a vaccum must do:
It would make sense that if a value of the earliest deleted chunk
was kept in a table then vacuum would not have to scan the entire
table in order to work, it would only need to start at the 'earliest'
invalidated row.
The utility of this (at least for us) is that we have several tables
that will grow to hundreds of megabytes, however changes will only
happen at the tail end (recently added rows). If we could reduce the
amount of time spent in a vacuum state it would help us a lot.
================
I'm wondering if these ideas make sense and may help at all.
thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
