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