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

We do not use in-database operators to modify the toasted data.
The update operations we perform on the problematic table are in the form of

UPDATE foo SET field='value' WHERE nid = to_uid(#objId#)

-----Original Message-----
From: Daniel Farina [mailto:daniel(at)heroku(dot)com]
Sent: Thursday, August 30, 2012 11:11 AM
To: Liron Shiri
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 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#BA
> CKUP-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

Scanned by Check Point Total Security Gateway.