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