Обсуждение: What's the difference between SET STORAGE MAIN and EXTENDED?
Hi, according to http://www.postgresql.org/docs/8.2/interactive/storage-toast.html: EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big. MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough.) At the end of the day, the behaviour is the same, isn't it? -- ---------------------------------- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/
Zoltan Boszormenyi <zb@cybertec.at> writes: > At the end of the day, the behaviour is the same, isn't it? No, there's a difference in terms of the priority for pushing this column out to toast storage, versus pushing other columns of the row out to toast. Normally we push the widest (remaining) column out, and repeat, until the tuple is small enough. But MAIN columns stay in, until there are no EXTENDED columns left. regards, tom lane
Tom Lane írta: > Zoltan Boszormenyi <zb@cybertec.at> writes: > >> At the end of the day, the behaviour is the same, isn't it? >> > > No, there's a difference in terms of the priority for pushing this > column out to toast storage, versus pushing other columns of the row > out to toast. Normally we push the widest (remaining) column out, > and repeat, until the tuple is small enough. But MAIN columns stay > in, until there are no EXTENDED columns left. > > regards, tom lane > Thanks very much for clarifying. I was thinking of a binary data that wouldn't fit into the maximum inline tuple size. In this case both MAIN and EXTENDED end up compressed and out-of-line. I didn't consider having multiple bytea or text columns filled with small amount of data. Best regards, Zoltán Böszörményi -- ---------------------------------- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/
Zoltan Boszormenyi <zb@cybertec.at> writes: > Tom Lane =EDrta: >> Zoltan Boszormenyi <zb@cybertec.at> writes: >>> At the end of the day, the behaviour is the same, isn't it? >> >> No, there's a difference in terms of the priority for pushing this >> column out to toast storage, versus pushing other columns of the row >> out to toast. > Thanks very much for clarifying. > I was thinking of a binary data that wouldn't fit > into the maximum inline tuple size. In this case > both MAIN and EXTENDED end up compressed > and out-of-line. I didn't consider having multiple > bytea or text columns filled with small amount of data. It'd be pretty unwise to mark a column MAIN if it's likely to contain wide values ("wide" meaning more than 1K or so). As you say, it'll still get toasted --- but not until after everything else in the row has been toasted, even quite narrow values that happen to be of toastable types. regards, tom lane
On 9/7/2007 11:45 AM, Tom Lane wrote: > Zoltan Boszormenyi <zb@cybertec.at> writes: >> Tom Lane =EDrta: >>> Zoltan Boszormenyi <zb@cybertec.at> writes: >>>> At the end of the day, the behaviour is the same, isn't it? >>> >>> No, there's a difference in terms of the priority for pushing this >>> column out to toast storage, versus pushing other columns of the row >>> out to toast. > >> Thanks very much for clarifying. > >> I was thinking of a binary data that wouldn't fit >> into the maximum inline tuple size. In this case >> both MAIN and EXTENDED end up compressed >> and out-of-line. I didn't consider having multiple >> bytea or text columns filled with small amount of data. > > It'd be pretty unwise to mark a column MAIN if it's likely to contain > wide values ("wide" meaning more than 1K or so). As you say, it'll > still get toasted --- but not until after everything else in the row has > been toasted, even quite narrow values that happen to be of toastable > types. Additionally, EXTENDED means that the toaster tries to get the tuple down to a 1/4 blocksize. With MAIN, it won't do so. MAIN storage strategy would be for wide columns that you *always* touch in *every* select *and* update and where the access pattern is always resulting in an index scan. Only in that case, you save from having the value right in the main tuple and don't need to pull it from the toast table and also don't lose the optimization of reusing external toast values if they aren't touched on update. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #