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