Обсуждение: toast useless
create table test(x integer, y integer, w varchar(50), z varchar(80));
When a table has well defined sizes all their data is stored on heap, so they doesn´t need to be toasted, and no toast table is created. Fine.
But then I want to store last modification of that record, so I do ...
alter table test add audit_last_record jsonb;
And a trigger to store old.* on that field.
So I'm sure that jsonb will fit on heap too because their size is all other fields converted to jsonb, never bigger than that.
But as soon as I´ve created that field, a toast table is created too. Even if I set storage MAIN to that field, reltoastrelid on pg_class still exists.
alter table test alter audit_last_record SET STORAGE MAIN
So, there is a way to eliminate these useless toast tables ?
thanks
Marcos
On Tue, 13 Sept 2022 at 12:45, Marcos Pegoraro <marcos@f10.com.br> wrote: > So, there is a way to eliminate these useless toast tables ? What problem do they cause you? -- Simon Riggs http://www.EnterpriseDB.com/
What problem do they cause you?
They don't cause any problem, I was just trying to get my database as clean as possible.
I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them
But if there is no way, ok
On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro <marcos@f10.com.br> wrote: >> >> What problem do they cause you? > > > They don't cause any problem, I was just trying to get my database as clean as possible. > I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them > But if there is no way, ok They might be optimized away one day, but for now, they are essential. -- Simon Riggs http://www.EnterpriseDB.com/
On 9/13/22 08:18, Simon Riggs wrote:
Why are they essential?
On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro <marcos@f10.com.br> wrote:What problem do they cause you?They don't cause any problem, I was just trying to get my database as clean as possible. I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them But if there is no way, okThey might be optimized away one day, but for now, they are essential.
Why are they essential?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Simon Riggs <simon.riggs@enterprisedb.com> escreveu:
On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro <marcos@f10.com.br> wrote:
>>
>> What problem do they cause you?
>
>
> They don't cause any problem, I was just trying to get my database as clean as possible.
> I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them
> But if there is no way, ok
They might be optimized away one day, but for now, they are essential.
When version 16 comes in it´ll be possible to define [1] field STORAGE on CREATE TABLE step.
I have some thousands of toast files which are completely useless on my database, so how to remove them.
pg_upgrade ? Those unneeded toast files will be dropped automatically ?
If not, if I do "alter table x alter column y set storage main" then toast file will still exist ?
On Tue, 2023-01-31 at 09:34 -0300, Marcos Pegoraro wrote: > Simon Riggs <simon.riggs@enterprisedb.com> escreveu: > > On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro <marcos@f10.com.br> wrote: > > > > > > > > What problem do they cause you? > > > > > > > > > They don't cause any problem, I was just trying to get my database as clean as possible. > > > I have some thousands of these toast tables with 8k bytes, so I was trying to eliminate them > > > But if there is no way, ok > > > > They might be optimized away one day, but for now, they are essential. > > When version 16 comes in it´ll be possible to define [1] field STORAGE on CREATE TABLE step. > I have some thousands of toast files which are completely useless on my database, so how to remove them. > > pg_upgrade ? Those unneeded toast files will be dropped automatically ? > If not, if I do "alter table x alter column y set storage main" then toast file will still exist ? > > [1] https://www.postgresql.org/message-id/flat/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru Leave them. They won't bother you, and they won't occupy much space. Yours, Laurenz Albe