Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C7F822E02EF for ; Fri, 7 Mar 2008 10:31:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 86632-05 for ; Fri, 7 Mar 2008 10:31:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C97512E02F2 for ; Fri, 7 Mar 2008 10:31:25 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m27EVNx9013189; Fri, 7 Mar 2008 09:31:23 -0500 (EST) To: "Pavel Rotek" cc: pgsql-performance@postgresql.org Subject: Re: Toast space grows In-reply-to: <3556c08c0803070611p542eb317vfaf9b783172b6c3@mail.gmail.com> References: <3556c08c0803070035p6d8b4aa8m6318f54728f61022@mail.gmail.com> <47D10722.4080508@archonet.com> <3556c08c0803070129y6ffabf32i1c8bae445ec460e0@mail.gmail.com> <20080307065314.1248665b.wmoran@collaborativefusion.com> <3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com> <11848.1204896799@sss.pgh.pa.us> <3556c08c0803070611p542eb317vfaf9b783172b6c3@mail.gmail.com> Comments: In-reply-to "Pavel Rotek" message dated "Fri, 07 Mar 2008 15:11:55 +0100" Date: Fri, 07 Mar 2008 09:31:23 -0500 Message-ID: <13188.1204900283@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/165 X-Sequence-Number: 29119 "Pavel Rotek" writes: > 2008/3/7, Tom Lane : >> You mean that you build up the 5MB log entry by adding a few lines at a >> time? That's going to consume horrid amounts of toast space, because >> each time you add a few lines, an entire new toasted field value is >> created. > well, this will be the main problem... But... do uncomitted trasactions > affect toast space? Sure. Where do you think the data goes? It's gotta be stored someplace. Every UPDATE operation that changes a toasted field will consume space for a fresh copy of that field, whether it ever commits or not. You need VACUUM to reclaim the space eaten by no-longer-accessible copies. regards, tom lane