Message-ID: <481B8160.2090106@emolecules.com>
Date: Fri, 02 May 2008 14:02:24 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>	
	<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>	
	<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>	
	<dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>	
	<21403.1209759217@sss.pgh.pa.us>	
	<72E02D29-848B-467A-AE6B-401568010254@gmail.com>
	<dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
In-Reply-To: <dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On Fri, May 2, 2008 at 2:26 PM, Alexy Khrabrov <deliverable@gmail.com> wrote:
>  I naively thought that if I have a 100,000,000 row table, of the form
> (integer,integer,smallint,date), and add a real coumn to it, it will scroll
> through the memory reasonably fast.

In Postgres, an update is the same as a delete/insert.  That means that changing the data in one column rewrites ALL of the columns for that row, and you end up with a table that's 50% dead space, which you then have to vacuum.

Sometimes if you have a "volatile" column that goes with several "static" columns, you're far better off to create a second table for the volatile data, duplicating the primary key in both tables.  In your case, it would mean the difference between 10^8 inserts of (int, float), very fast, compared to what you're doing now, which is 10^8 insert and 10^8 deletes of (int, int, smallint, date, float), followed by a big vacuum/analyze (also slow).

The down side of this design is that later on, it requires a join to fetch all the data for each key.

You do have a primary key on your data, right?  Or some sort of index?

Craig