Hello list. I know all the theory about vacuuming. I've got log tables that get periodically pruned. The pruning is... quirky, though. It's not so much deleting data, as summarizing many (thousands) of rows into one single row. For that, a combination of deletes and updates are used. In essence, the tables are write-only except for the summarization step for old data. Many tables are becoming increasingly bloated, which is somewhat expected due to this usage pattern: I had expected table size to be about constant, holding recent data plus archived old data (which is really small compared to full recent logs), with some constant-sized bloat due to daily summarization updates/deletes. What I'm seeing, though, is not that, but bloat proportional to table size (always stuck at about 65% bloat). What's weird, is that vacuum full does the trick of reducing table size and bloat back to 0%. I haven't had time yet to verify whether it goes back to 65% after vacuum full (that will take time, maybe a month). Question is... why isn't all that free space being used? The table grows in size even though there's plenty (65%) of free space. I've got autovacuum severely crippled and that could be a reason, but I do perform regular vacuum runs weekly that always run to completion. I also do routine reindexing to stop index bloat on its tracks, yet freshly-reindexed indexes get considerably reduced in size with vacuum full. Is there some case in which regular vacuum would fail to reclaim space but vacuum full would not? I'm running postgresql 9.2.5. If this was 8.x, I'd suspect of the free space map, but AFAIK there's no such limit in 9.2. Relevant non-default settings are: "archive_mode";"on" "autovacuum_analyze_scale_factor";"0.05" "autovacuum_analyze_threshold";"500" "autovacuum_max_workers";"1" "autovacuum_naptime";"900" "autovacuum_vacuum_cost_delay";"80" "autovacuum_vacuum_cost_limit";"100" "autovacuum_vacuum_scale_factor";"0.1" "autovacuum_vacuum_threshold";"500" "bgwriter_lru_maxpages";"200" "checkpoint_completion_target";"0.9" "checkpoint_segments";"64" "checkpoint_timeout";"1800" "maintenance_work_mem";"1048576" "vacuum_cost_delay";"20" "wal_level";"archive" [0] http://labs.omniti.com/labs/pgtreats/browser/trunk/tools/pg_bloat_report.pl -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
