Re: new to postgres (and db management) and performance

Hi, Thomas,

me(at)alternize(dot)com wrote:
>> Try a), b), and c) in order on the "offending" tables as they address
>> the problem at increasing cost...
>
> thanks alot for the detailed information! the entire concept of vacuum
> isn't yet that clear to me, so your explanations and hints are very much
> appreciated. i'll defenitely try these steps this weekend when the next
> full vacuum was scheduled :-)

Basically, VACUUM scans the whole table and looks for pages containing
garbage rows (or row versions), deletes the garbage, and adds those
pages to the free space map (if there are free slots). When allocating
new rows / row versions, PostgreSQL first tries to fit them in pages
from the free space maps before allocating new pages. This is why a high
max_fsm_pages setting can help when VACUUM freqency is low.

VACUUM FULL additionally moves rows between pages, trying to concentrate
all the free space at the end of the tables (aka "defragmentation"), so
it can then truncate the files and release the space to the filesystem.

CLUSTER basically rebuilds the tables by copying all rows into a new
table, in index order, and then dropping the old table, which also
reduces fragmentation, but not as strong as VACUUM FULL might.

ANALYZE creates statistics about the distribution of values in a column,
allowing the query optimizer to estimate the selectivity of query criteria.

(This explanation is rather simplified, and ignores indices as well as
the fact that a table can consist of multiple files. Also, I believe
that newer PostgreSQL versions allow VACUUM to truncate files when free
pages happen to appear at the very end of the file.)

HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org