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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andrew Sullivan Sent: Wednesday, November 14, 2007 11:13 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
