pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedtoast useless
7+ messages / 4 participants
[nested] [flat]
* toast useless
@ 2022-09-13 11:45 Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Marcos Pegoraro @ 2022-09-13 11:45 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
@ 2022-09-13 12:06 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
0 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2022-09-13 12:06 UTC (permalink / raw)
To: Marcos Pegoraro <marcos@f10.com.br>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-09-13 12:28 ` Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 13:18 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Marcos Pegoraro @ 2022-09-13 12:28 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
@ 2022-09-13 13:18 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 17:13 ` Re: toast useless Ron <ronljohnsonjr@gmail.com>
2023-01-31 12:34 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
0 siblings, 2 replies; 7+ messages in thread
From: Simon Riggs @ 2022-09-13 13:18 UTC (permalink / raw)
To: Marcos Pegoraro <marcos@f10.com.br>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 13:18 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-09-13 17:13 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Ron @ 2022-09-13 17:13 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 9/13/22 08:18, Simon Riggs wrote:
> 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.
Why are they *essential*?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 13:18 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
@ 2023-01-31 12:34 ` Marcos Pegoraro <marcos@f10.com.br>
2023-01-31 15:03 ` Re: toast useless Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 7+ messages in thread
From: Marcos Pegoraro @ 2023-01-31 12:34 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: toast useless
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 13:18 ` Re: toast useless Simon Riggs <simon.riggs@enterprisedb.com>
2023-01-31 12:34 ` Re: toast useless Marcos Pegoraro <marcos@f10.com.br>
@ 2023-01-31 15:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2023-01-31 15:03 UTC (permalink / raw)
To: Marcos Pegoraro <marcos@f10.com.br>; Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-01-31 15:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-13 11:45 toast useless Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 12:06 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 12:28 ` Marcos Pegoraro <marcos@f10.com.br>
2022-09-13 13:18 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-09-13 17:13 ` Ron <ronljohnsonjr@gmail.com>
2023-01-31 12:34 ` Marcos Pegoraro <marcos@f10.com.br>
2023-01-31 15:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox