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:
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.
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div><b>Gesendet:</b> Dienstag, 13. September 2022 um 19:13 Uhr
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Von:</b> "Ron" <ronljohnsonjr@gmail.com><br/>
<b>An:</b> pgsql-general@lists.postgresql.org<br/>
<b>Betreff:</b> Re: toast useless</div>
<div name="quoted-content">On 9/13/22 08:18, Simon Riggs wrote:
<blockquote>
<pre class="moz-quote-pre">On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro <a class="moz-txt-link-rfc2396E" href="mailto:marcos@f10.com.br" onclick="parent.window.location.href='mailto:marcos@f10.com.br'; return false;" target="_blank"><marcos@f10.com.br></a> wrote:
</pre>
<blockquote>
<blockquote>
<pre class="moz-quote-pre">What problem do they cause you?
</pre>
</blockquote>
<pre class="moz-quote-pre">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
</pre>
</blockquote>
<pre class="moz-quote-pre">They might be optimized away one day, but for now, they are essential.</pre>
</blockquote>
<div><br/>
> Why are they <b>essential</b>?<br/>
</div>
<div>Essential to proper operation of the database code as of now.</div>
<div> </div>
<div>Best, Karsten</div>
</div>
</div>
</div>
</div></div></body></html>
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]/messages/by-id/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru 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]: /messages/by-id/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru
/messages/by-id/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru
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, okThey 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] /messages/by-id/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru
Leave them. They won't bother you, and they won't occupy much space.
Yours,
Laurenz Albe