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@heroku.com]
Sent: Thursday, August 30, 2012 11:11 AM
To: Liron Shiri
Cc: pgsql-performance@postgresql.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@checkpoint.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
theyuse 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
ifyou do not write queries that directly use such modification operators) , but in principle any operation that does
notcompletely overwrite the value may be susceptible, or so the information I have would indicate. What do you think,
doesthat 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
pathologicalway sometimes, and obviously I think it might be workload dependent.
--
fdr
Scanned by Check Point Total Security Gateway.