Postgres Pro
Downloads
Home   >   mailing lists

Modifying TOAST policy will not affect the way existing data is stored ? - Mailing list pgsql-hackers

From Marcos Pegoraro
Subject Modifying TOAST policy will not affect the way existing data is stored ?
Date
Msg-id CAB-JLwZdPxPzQ0JaxefqC_mEQQ3fA7WvbTdwN9FvMmO6RY=rYw@mail.gmail.com
Whole thread Raw
Responses Re: Modifying TOAST policy will not affect the way existing data is stored ?  (Marcos Pegoraro <marcos@f10.com.br>)
List pgsql-hackers
Tree view
Postgres weekly came today with this interesting article:
https://hakibenita.com/sql-medium-text-performance

The problem it explains is when you have no one records being stored on toast, even if they are toastable. That is because the size of that column fits on toast_tuple_target size, so it does not toast. 
What it talks fits perfectly on some tables of mine, so I want them this way, but how ?

First I did, as article mentioned ...
alter table ... set (toast_tuple_target=128);
alter table ... alter mycol set storage external;

Then I tried vacuum full, cluster, create another column and update its value with old column and last test was creating a new table with those definitions and insert from select old table and none of these methods I could move data from that column to toast. why ?

pg_column_size gives me what number exactly ? Its size is before compression or later ?

That table has 2.5 million records, average of pg_column_size is 100 bytes but 100 thousand records have more than 500, 50 thousands have more than 1.000 using pg_column_size

How can I change existing records from table to toast ? Or I cannot ?

pgsql-hackers by date:

Previous
From: "Bossart, Nathan"
Date:
Subject: Re: Isn't it better with "autovacuum worker...." instead of "worker took too long to start; canceled" specific to "auto
Next
From: Robert Haas
Date:
Subject: Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)