OK, I didn't know Postgres did it this way. I was hoping it would retain
the old rec and update in place (if the updated values could fit). I
guess not.
I can rewrite the DB loading algorithm to get those values in advance,
load into program memory, and reference at the time of the initial load.
Thanks for the advanced warning about problems with vaccuum !
-dave
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Wednesday, November 14, 2007 11:13 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] reserving space in a rec for future update
On Wed, Nov 14, 2007 at 10:28:30AM -0500, Gauthier, Dave wrote:
> null. My concern has to do with record fragmentation at the time of
> update because there's no room to "expand" them to accept the non-null
> data. (BTW, the columns are floating point).
You have a mistaken idea about how this works.
> Is there a way to initially insert nulls, but reserve space for the
> future update (and avoid record fragmentation)?
No.
> Is my record fragmentation concern unfounded?
Sort of.
The way this will work in Postgres is that, when you UPDATE the row, the
old
row will be marked dead, and a _new_ row will be written out with the
new
data. You will need to perform VACUUM in order to keep the table from
bloating. You'll want to read the manual carefully about this topic, in
order to keep your table from getting so bloated that your free space
map
becomes useless. One of the weakest areas for PostgreSQL is its
behaviour
under this sort of "most rows updated" scenario, and it is wise to plan
carefully how you will accomplish these sorts of activities without
causing
yourself extreme pain.
A
--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
