Re: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum

On 5/12/2009 7:03 AM, Andreas Thiel wrote:
> Hi All,
>
>
> Maybe some questions are quite newbie ones, and I did try hard to scan
> all the articles and documentation, but I did not find a satisfying
> answer.

> ### My Issue No. 1: Index Size
> Is such disk usage for indexes expected? What can I do to optimize? I
> could not run yet a VACUUM on result_orig, as I hit into max_fsm_pages
> limit

You'll like 8.4 then, as you no longer have to play with max_fsm_pages.

The fact that you're hitting max_fsm_pages suggests that you are
probably going to be encountering table bloat.

Of course, to get to 8.4 you're going to have to go through a dump and
reload of doom...

> ### My Issue No. 2: relpages and VACUUM
> I have another table "test" which is - as starting point - created by
> INSERTs and then UPDATE'd. It has the same columns and roughly the same
> number of rows as table test_orig,  but consumes 160 times the number of
> pages. I tried VACUUM on this table but it did not change anything on
> its relpages count. Maybe this is just because VACUUM without FULL does
> not re-claim disk space, i.e. relpages stays as it is? I did observe
> that after VACUUM, a REINDEX on this table did considerably shrink down
> the size of its indexes (test_test_id, test_lt_id).

CLUSTER is often convenient for re-writing a highly bloated table.
You'll need enough free disk space to hold the real rows from the table
twice, plus the dead space once, while CLUSTER runs.

--
Craig Ringer