Re: Fwd: Tweaking PG (again)

> Thanks Tomas.
>
>
>> The table may still be bloated - the default autovacuum parameters may
>> not
>> be agressive enough for heavily modified tables.
>
>
> My autovacuum settings:
>
>
> autovacuum = on
> autovacuum_vacuum_cost_delay = 20
> vacuum_cost_delay = 20
> autovacuum_naptime = 10
> stats_start_collector = on
> stats_row_level = on
> autovacuum_vacuum_threshold = 75
> autovacuum_analyze_threshold = 25
> autovacuum_analyze_scale_factor = 0.02
> autovacuum_vacuum_scale_factor = 0.01
> checkpoint_warning = 3600
> random_page_cost = 1
>
> Is this not aggressive enough?

The settings seems fine to me ...

> And I reindexed all my indexes on the main "books" table, and then ran
> a vacuum verbose, but I still see this:

It's not about about indexes in the first place, it's about the table. If
there are dead rows, the table will occupy more space (and so will the
index).

Try clustering the table according to the primary key - that will reclaim
the free space occupied by dead rows, and sort it according to the index.
But be careful, as it is quite expensive and an ACCESS EXCLUSIVE lock is
acquired on the table (both reads and writes are blocked).

> ----
> INFO: "links": found 475 removable, 8684150 nonremovable row versions
> in 472276 pages
> DETAIL: 95 dead row versions cannot be removed yet.
> There were 2132065 unused item pointers.
> 529 pages contain useful free space.

I thought you had problems with the "books" table, right? But this output
suggests it's related to "links" table, not "books" (see the first INFO
table).

Anyway, the table has about 25% of reclaimed space - that's the 'unused
item pointers' value. There are 8.6 million of rows in total, 2.1 million
of them are reclaimed by VACUUM and marked as free (but the space is still
occupied by the relation). I believe this is related to max_fsm_pages /
max_fsm_relations, but this is beyond my knowledge.

But I don't understand why there is so much free space - why it is not
reused for new rows etc.

> 95 dead rows are an improvement, but after a fresh reindex shouldn't I
> have none? Each reindex took about 600 seconds on average (some
> longer) so the tables data may have changed, but how can I have
> "2132065 unused item pointers"?

The 95 dead rows are not a problem - AFAIK it just means the row was
modified (updated / deleted), but may not be reclaimed yet (maybe the
transaction that modified the row is still running).

regards
Tomas