From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
Date: Fri, 02 May 2008 14:02:24 -0700
Message-ID: <481B8160.2090106@emolecules.com> (raw)
In-Reply-To: <dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: two memory-consuming postgres processes
In-Reply-To: <481B8160.2090106@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox