> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
