I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed form, it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).
What would cause such a spike in disk usage? And why would it stay at such a high level for 2 days?
This is postgresql 8.1.4 on a slightly modified RHEL4 system (2.6.17 kernel).
At the time the spike occurred (about 2pm), I may have been running some simple queries against various tables to determine usage statistics (eg, how many rows in table a, sum of column b in table c, etc (maximum of 100,000 rows in the tables, <10 columns), no nasty joins or anything). Also, I may have extracted a couple of large objects in full (not more than 200MB).
Otherwise, the database processes a few (< 20) small (< 1k data) inserts/updates/deletes per minute. This would have been the only traffic to the database after friday afternoon when I went home.
Autovacuum is turned on and the system had finished importing a large amount of data (50GB) earlier (4am) that morning. Is it possible the spike was caused by autovacuum re-indexing the pg_largeobject table?
Thanks,
Geoff.