On 13/09/2007, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Phoenix Kiula" <phoenix.kiula@xxxxxxxxx> writes: > > Apart from creating a new table, indexing it, then renaming it to > > original table -- is there an alternative to CLUSTER that doesn't > > impose a painful ACCESS EXCLUSIVE lock on the table? We are on > > Postgres 8.2.3 and have a heavy duty table that starts showing its > > limits after a week or so. Autovacuum is on and working. FSM etc is > > fine, maintenance_work_mem is 256MB. But cluster still takes upwards > > of 30 minutes, which is unacceptable downtime for our web service. > > Thanks for any tips! > > If you're seeing steady bloat then FSM isn't as fine as you think. > I am not sure if there's steady bloat. Of the two databases we have, the VACUUM ANALYZE VERBOSE shows about 133,000 pages on one and about 77,000 on the other. My max_fsm_pages is 250,000 -- well above that total limit. Other possibly related settings: vacuum_cost_delay = 10 stats_start_collector = on stats_row_level = on autovacuum_vacuum_threshold = 300 autovacuum_analyze_threshold = 100 wal_buffers=64 checkpoint_segments=64 checkpoint_timeout=900 Anything wrong with these? I tried a CLUSTER on one index and it was on for about an hour without completion. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
