In message <24849.1160940026@xxxxxxxxxxxxx>, Tom Lane writes: >Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes: >> Using the same page for an updated tuple is generally a useful >> optimization, so I don't think you have much hopes for having it >> disabled. > >Especially not since there's no very reasonable way for anything as >low-level as heap_update to know that "the table is way beyond its >nominal size". What's nominal size anyway? Thanks - yes, I don't know enough about PostgreSQL internals to suggest a good approach, but thought there might be a small change possible that would cause the table size to eventually recover itself without manual intervention. For example even if the same-page optimisation was only disabled on the very last page and for 1 in 10 updates then the table size would start to shrink. Even better would be a way for this to happen more aggressively when the table is very sparsely populated relative to the target fill factor. Just to explain a bit more about the original access pattern, we were only performing UPDATEs (no INSERT/DELETEs), so the reason that the table gets bloated to begin with is that vacuuming is ineffective during long transactions such as backups. In our particular case, full vacuuming is not an acceptable option due to the exclusive locking, so to handle the rare table bloat problems we currently have to just switch all our UPDATEs to use slower INSERT/DELETE operations instead. >Actually, the recent thinking in this area has been to try to *increase* >the usage of same-page UPDATE, so as to prevent table bloat in the first >place ... If you mean some kind of in-place update, then that would solve the problem we were seeing too if it can prevent table bloat of high UPDATE churn tables during long trasactions such as backups. Thanks, Ian
