On 8.5.2012 19:27, Richard Harley wrote: > I currently do nightly database dumps on a ton of small dbs that are > increasing around 2-3mb per day. Suddenly, in a recent backup file, one > db in particular jumped from 55mb to 122mb overnight. Well, I wouldn't call that 'massive size' but in my experience such sudden changes in sizes are caused by one of these two things (a) modification patterns + slow vacuuming (b) batch updates (e.g. update of a column in the whole table) Given that this is a one-time issue, I'd guess it's (b). Were you doing any such updates or something like that? > I did some investigation - > > One table increased from 8mb to 31mb during a 24hr period. The table is > just text so this is highly unusual given that the number of rows DIDN'T > increase any more than normal. What do you mean by 'number of rows'? Is that number of live rows, i.e. the number you get from SELECT COUNT(*) FROM ... or the number you get from pg_class as reltuples? > pg_toast increased from 8mb to 27mb during the same period. > > The relpages for the table in question increased from 164 to 1088 during > the 24hr period. On the live db, the relpages is back to 164 but the > size of the table remains massive. Hmmm, I wonder how the number of pages could drop, because that does not happen unless you run VACUUM FULL / CLUSTER or such commands. And that does not happen regularly. Also, how could the table size remain massive when the number of pages dropped to 164? Did you mean a different table or the whole database? > Using Centos5/postgres 8.1.21 at the moment. Planning to upgrade over > summer but not had issues like this before on 8.1. > > What gives?! Good idea. 8.1 is not supported for a long time, plus there are so many improvements since then? Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
