At 03:00 06/04/29, Bruno Wolff III wrote:
On Fri, Apr 28, 2006 at 17:37:30 +0000, Bealach-na Bo <bealach_na_bo@xxxxxxxxxxx> wrote: > >The above shows that the indexes contained 10M rows and 160M of dead > >space each. That means you weren't vacuuming nearly enough. > > How is it that a row in the table can grow to a size far exceeding the sum > of the maximum sized of the fields it consists of? Because unless you run vacuum, the old deleted rows are not reused. Those rows cannot be deleted immediately, because the rows may be visible to other transactions. Periodic vacuums are used to find deleted rows which are no longer visible to any transactions. You probably want to read the following: http://developer.postgresql.org/docs/postgres/routine-vacuuming.html
Would recycling dead tuples on the fly (mentioned in p.14 in the article http://www.postgresql.org/files/developer/transactions.pdf ) significantly reduce the need for periodic vacuums?
Without knowing the internals, I have this simplistic idea: if Postgres maintains the current lowest transaction ID for all active transactions, it probably could recycle dead tuples on the fly. The current lowest transaction ID could be maintained in a doubly linked list with maximum <max_connections> entries. A backward link in the tuple header might be needed too.
Any comments? Cheers, KC.
