Re: Investigating the reason for a very big TOAST table size

On Mon, Aug 27, 2012 at 11:24 PM, Liron Shiri <lirons(at)checkpoint(dot)com> wrote:
> There were no "hot standby" configuration, but the DB has start grow fast after restoring from a base backup as described in http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP

I'm trying to confirm a theory about why this happens. Can you answer
a question for me?

I've just seen this happen twice. Both are involving toasted columns,
but the other critical thing they share is that they use in-database
operators to modify the toasted data.

For example, here is something that would not display pathological
warm/hot standby-promotion bloat, if I am correct:

UPDATE foo SET field='value';

But here's something that might:

UPDATE foo SET field=field || 'value'

Other examples might include tsvector_update_trigger (also: that means
that triggers can cause this workload also, even if you do not write
queries that directly use such modification operators) , but in
principle any operation that does not completely overwrite the value
may be susceptible, or so the information I have would indicate. What
do you think, does that sound like your workload, or do you do full
replacement of values in your UPDATEs, which would invalidate this
theory?

I'm trying to figure out why standby promotion works so often with no
problems but sometimes bloats in an incredibly pathological way
sometimes, and obviously I think it might be workload dependent.

--
fdr