On Wed, Aug 31, 2005 at 10:21:20AM +1200, Ralph Mason wrote: > This is a wild and crazy thought which I am sure is invalid for some > good reason. > > But why can't postgres just vacuum itself as it goes along? > > When a row is orphaned it's added to a list of possibly available rows. > When a new row is needed the list of possible rows is examined and the > first one with a transaction id less then the lowest running transaction > id is chosen to be the new row? These rows can be in a heap so it's > really fast to find one. > > Like magic - no more vacuuming. No more holes for people to fall into.
Yes please. :-) > Is this an oversimplification of the problem? But, yeah. It's probably not that easy, especially with really big databases. Where is this free list stored? How efficient is it to keep track of the lowest running transaction at all times? How does one synchronize access to this free list, to ensure that processes don't block up waiting for access to the free list? Is the fre list journalled to prevent corruption, and the accidental re-use of a still in use row? And, there would be a cost to scanning this list on every insert or update. As an outsider (like you?) I see the current model as a design flaw as well. A neat and tidy model on paper. Not so nice in real life. The need to vacuum in batch mode, to keep the database from dying, seems intuitively bad. I think there must be answers to this problem. Even simple optimizations, such as defining a table such that any delete or update within a table, upon commit, will attempt to vacuum just the rows that should not be considered free for any new transactions. If it's in use by an active transaction, oh well. It can be picked up by a batch run of vacuum. If it's free though - let's do it now. I think any optimizations we come up with, will be more happily accepted with a working patch that causes no breakage... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings