Modifying TOAST policy will not affect the way existing data is stored ?

From: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Modifying TOAST policy will not affect the way existing data is stored ?
Date: 2021-10-27 19:48:40
Message-ID: CAB-JLwZdPxPzQ0JaxefqC_mEQQ3fA7WvbTdwN9FvMmO6RY=rYw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 ?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2021-10-27 20:10:36 Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)
Previous Message Justin Pryzby 2021-10-27 19:26:11 Re: Isn't it better with "autovacuum worker...." instead of "worker took too long to start; canceled" specific to "auto