Re: Question on REINDEX
От | Tom Lane |
---|---|
Тема | Re: Question on REINDEX |
Дата | |
Msg-id | 21097.1113919600@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Question on REINDEX ("Tambet Matiisen" <t.matiisen@aprote.ee>) |
Ответы |
Re: Question on REINDEX
(Alvaro Herrera <alvherre@dcc.uchile.cl>)
|
Список | pgsql-performance |
"Tambet Matiisen" <t.matiisen@aprote.ee> writes: > Is my current understanding correct: > 1) VACUUM defragments each page locally - moves free space to the end of > page. > 2) VACUUM FULL defragments table globally - tries to fill up all > partially free pages and deletes all resulting empty pages. Both versions of VACUUM do within-page defragmentation. Also, both versions will remove entirely-empty pages at the end of a table. The difference is that VACUUM FULL actively attempts to make pages at the end empty, by moving their contents into free space in earlier pages. Plain VACUUM never does cross-page data movement, which is how come it doesn't need as strong a lock. BTW, VACUUM FULL does the data movement back-to-front, and stops as soon as it finds a tuple it cannot move down; which is a reasonable strategy since the goal is merely to make the file shorter. But it's entirely likely that there will be lots of empty space left at the end. For instance the final state could have one 4K tuple in the last page and up to 4K-1 free bytes in every earlier page. > 3) Both VACUUM and VACUUM FULL do only local defragment for indexes. > 4) If you want indexes to become fully defragmented, you need to > REINDEX. I don't think "defragment" is a notion that applies to indexes, at least not in the same way as for tables. It's true that there is no cross-page data movement in either case. In the last release or two we've been able to recognize and recycle entirely-empty pages in both btree and hash indexes, but such pages are almost never returned to the OS; they're put on a freelist for re-use within the index, instead. If you allow the table to grow to much more than its "normal" size, ie, you allow many dead tuples to be formed, then getting back to "normal" size is going to require VACUUM FULL + REINDEX (or you can use CLUSTER or some varieties of ALTER TABLE). This is not the recommended maintenance process however. Sufficiently frequent plain VACUUMs should generally hold the free space to a tolerable level without requiring any exclusive locking. > Hmm, thanks for a tip. BTW, is output of > select count(1), sum(relpages) from pg_class where relkind in > ('r','i','t') > good estimate for max_fsm_relations and max_fsm_pages? Within that one database, yes --- don't forget you must sum these numbers across all DBs in the cluster. Also you need some slop in the max_fsm_pages setting because of quantization in the space usage. It's probably easier to let VACUUM VERBOSE do the calculation for you. regards, tom lane
В списке pgsql-performance по дате отправления: