"Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx> writes: > Well this is a guess, but: > > Set existing column to storage external > update existing column with existing data: > > UPDATE foo SET bar = bar; Well, not quite. That would actually reuse the toast pointer without decompressing it. We try to be clever about not decompressing and duplicating toast pointers unnecessarily on updates -- in this case too clever. You could do this: postgres=# ALTER TABLE foo ALTER bar TYPE bytea, ALTER bar SET STORAGE external; ALTER TABLE (Note that you have to include the 'ALTER bar SET STORAGE external' in the same command or the storage will get reset to the default 'extended' for bytea even if it was previously set to 'external'.) When I tested this though I noticed it did *not* decompress compressed data which was small enough to store internally. This may actually be desirable for your case since anything small enough to be stored internally is probably not worth bothering decompressing so it can be streamed out. It will still not be compressed next time you update it so it's not really helpful for the long term. If you want to decompress everything you have to do something like: postgres=# ALTER TABLE foo ALTER bar TYPE bytea USING t||'', ALTER bar SET STORAGE external; ALTER TABLE However note that this will require extra memory for both the decompressed original value and the new value after "appending" the empty string. Another option would be to update only the records which need to be decompressed with something like UPDATE foo SET bar=bar||'' WHERE length(bar) > pg_column_size(bar) This at least gives you the option of doing them in small groups or even one by one. I would suggest vacuuming between each update. I do have to wonder how you're getting the data *in* though. If it's large enough to have to stream out like this then how do you initially load the data? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(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
