Re: relpages sudden increase

[Richard Harley <raharley0@xxxxxxxxx>]

On 09/05/12 00:00, Tomas Vondra wrote:

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?

No I wasn't doing any maintenance work compared to the days and weeks previously when size was increasing by a few mb a day.

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?

I mean the number of live rows. So the size on disk went up unexpectedly but the rows increase was normal and the data in the rows was normal - just like previous days.

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?

The same table. I imported the db dump that suddenly got bigger into a test db and the table in question has 1088 relpages. In the live db, same table, we're back down to 132 and no vacuuming has taken place ..

Thanks for your help

Rich