Re: problems with large table

On 13/09/2007, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> 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.