pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
toast 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