Skip site navigation (1) Skip section navigation (2)

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

From: Daniel Farina <daniel(at)heroku(dot)com>
To: Liron Shiri <lirons(at)checkpoint(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Investigating the reason for a very big TOAST table size
Date: 2012-08-30 08:10:30
Message-ID: CAAZKuFZ8aOs2tamAD7DBChGLEHmUbm1TemYTp=07wZ7MQ7fT1w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Markus InnerebnerDate: 2012-08-30 08:13:39
Subject: Question about caching on full table scans
Previous:From: EileenDate: 2012-08-30 06:34:56
Subject: JDBC 5 million function insert returning Single Transaction Lock Access Exclusive Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group