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

Em qua., 27 de out. de 2021 às 16:48, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
escreveu:

> 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 ?
>

then, more some tests, but no one explain.

table_size - toast_size - toast_percent - table
570.146.816 - 2.105.344 - 0.00369% - original_table
564.060.160 - 2.072.576 - 0.00367% - insert_from_select_from_original
551.553.408 - 21.241.856 - 0.03851% - export_to_script_and_import

So, my original table has only 0,003% of size on its toast table.
If I cluster, vacuum full or create an additional field moving old data,
nothing happens
If I create another table and move data to it with insert from select,
nothing happens too.
Then, if I create an external script and then run that insert of 2.5
million rows, cool, then 10 times more of info were moved to toast. why ?

all tables I did a vacuum full as a last step.