Re: Investigating the reason for a very big TOAST table size
От | Liron Shiri |
---|---|
Тема | Re: Investigating the reason for a very big TOAST table size |
Дата | |
Msg-id | 6B9568EED16BA541BE4A0F3108351E6301BEA904CBF8@il-ex01.ad.checkpoint.com обсуждение исходный текст |
Ответ на | Re: Investigating the reason for a very big TOAST table size (Daniel Farina <daniel@heroku.com>) |
Ответы |
Re: Investigating the reason for a very big TOAST table size
|
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: