Re: db size and VACUUM ANALYZE

In response to Marcin Krol <mrkafk(at)gmail(dot)com>:

> Amitabh Kant wrote:
> > You need to do VACUUM FULL ANALYZE to claim the disk space, but this
> > creates a exclusive lock on the tables.
> >
> > See http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
>
> Aha!
>
> OK but why did the performance degrade so much? The same reason -- lack
> of autovacuuming/vacuum full?

Note that the "correct" disk size for your database is probably closer
to the 1.6G you were seeing before. This allows PG some free space
within the data files to add/remove records. vacuum full removes this
space, and you'll likely find that the files will simply expand to
use it again. Vaccuum (without full) keeps that space at an equilibrium.

As to performance degradation, you'll always see performance hits as
your database size increases. I'm assuming from your need to ask about
this issue that the degradation was significant. In that case, you first
want to make sure that the tables in the database have indexes in
all the right places -- in my experience, this is the biggest cause of
performance issues. Use of EXPLAIN ANALYZE on queries that are performing
slow will usually indicate where indexes can help.

From there, you may simply have too little hardware for the database to
run at the speed you expect. Giving it more RAM is cheap and tends to
work wonders. Any time the system runs out of RAM, it needs to use disk
instead, which significantly hurts performance.

Hope this is helpful.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/