On Mon, 2010-11-08 at 13:28 -0800, John R Pierce wrote:
> On 11/08/10 10:50 AM, Jason Long wrote:
> > I currently have Postgres 9.0 install after an upgrade. My database is
> > relatively small, but complex. The dump is about 90MB.
> >
> > Every night when there is no activity I do a full vacuum, a reindex, and
> > then dump a nightly backup.
> >
> > Is this optimal with regards to performance? autovacuum is set to the
> > default.
>
>
> if you have frequently updated tables that are accessed mostly from
> their primary key, it may pay to CLUSTER those tables on said index
> rather than doing the full vacuum.
>
> VACUUM FULL is usually not recommended, btw.
>
> Also, if you have tables that get lots of updates that only affect data
> and not indexed columns, setting a FILL FACTOR of, say, 70 or 80 (its in
> %) might help with performance by better facilitating HOT updates (HOT
> is a internal feature added to pg 8.3 to speed up these sorts of updates)
>
>
>
Thanks for the tip on CLUSTER.
My application has a couple hundred tables that all have an int8 for
their primary key. They are joined heavily on their primary key from
views and dynamically generated SQL. I am going to looking into
clustering the most frequently updated tables. Thanks for the tip.
Currently my performance problems are reads to display data.
