On Sun, Oct 17, 2021 at 5:12 PM Florents Tselai <florents.tse...@gmail.com> wrote:
> Hello, > > I have a table storing mostly text data (40M+ rows) that has > pg_total_relation_size ~670GB. > I’ve just upgraded to postgres 14 and I’m now eager to try the new LZ4 > compression. > > I’ve altered the column to use the new lz4 compression, but that only > applies to new rows. > > What’s the recommended way of triggering the re-evaluation for > pre-existing rows? > > I tried wrapping a function like the following, but apparently each old > record retains the compression applied. > > text_corpus=(SELECT t.text from ...); > > delete from t where id=; > > insert into t(id, text) values (id, text_corpus); > > > Fttb, I resorted to preparing an external shell script to execute against > the db but that’s too slow as it moves data in&out the db. > > Is there a smarter way to do this ? > > It should be enough to VACUUM FULL the table. (but it has to be VACUUM FULL, not a regular vacuum). Or CLUSTER. -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
