"Tiago J. Adami" <adamitj@gmail.com> writes:
> The issue topics:
> 1) As the database grows on our customers, lower performance occurs. After
> one week of use, the I/O on database is extremely high. It appears that
> VACUUM FULL and/or VACUUM ANALYZE doesn't work on this databases.
VACUUM FULL is a last-resort utility for recovering from a bad situation. It
shouldn't be necessary under normal operation. The intended mode is to run
VACUUM (or VACUUM ANALYZE) frequently -- possibly several times a day -- to
maintain the data density.
How frequently are you running VACUUM (or VACUUM ANALYZE)? How many updates
and deletes are you executing in that interval?
If you run VACUUM (or VACUUM ANALYZE) interactively what does it print at the
end of the operation?
> 2) We have a very complex view mount on other views. When we cancel a simple
> SELECT on this top-level view (expecting return a max. of 100 rows for
> example) the PostgreSQL process starts a infinite loop (we left more than 4
> days and the loop doesn't stops), using 100% of all processors on the
> server.
That does sound bad. Would it be possible to attach to the process when it's
spinning and get a back trace? Also, what version is this precisely? Have you
taken all the bug-fix updates for the major version you're using?
> 3) On these servers, the disk usage grows very small than the records loaded
> into database. For example, after restoring a backup, the database DIR have
> about 40 Gb (with all indexes created). After one week of use, and about
> 500,000 new records on tables, the database size grows to about 42 Gb, but
> on Windows 2003 Server we can see the high fragmentation of disk (maybe on
> linux this occurs too).
Postgres does extend files as needed and some filesystems may deal better with
this than others. I think this is something we don't know much about on
Windows.
You might find running a CLUSTER on the fragmented tables improves matters.
CLUSTER effectively does a full vacuum too so it would leave you in a good
situation to monitor the growth and vacuum frequency necessary from that point
forward too. The downsides are that CLUSTER locks the table while it runs and
it requires enough space to store a whole second copy of the table and its
indexes.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com