Re: Proposed adjustments in MaxTupleSize andtoastthresholds
От | Bruce Momjian |
---|---|
Тема | Re: Proposed adjustments in MaxTupleSize andtoastthresholds |
Дата | |
Msg-id | 200702192108.l1JL8Gi14704@momjian.us обсуждение исходный текст |
Ответ на | Re: Proposed adjustments in MaxTupleSize andtoastthresholds ("Simon Riggs" <simon@2ndquadrant.com>) |
Список | pgsql-hackers |
Added to TODO: * Consider allowing configuration of TOAST thresholds http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php --------------------------------------------------------------------------- Simon Riggs wrote: > On Mon, 2007-02-05 at 19:18 -0500, Jan Wieck wrote: > > On 2/5/2007 11:52 AM, Tom Lane wrote: > > > "Simon Riggs" <simon@2ndquadrant.com> writes: > > >> Sounds like a good time to suggest making these values configurable, > > >> within certain reasonable bounds to avoid bad behaviour. > > > > > > Actually, given what we've just learned --- namely that choosing these > > > values at random is a bad idea --- I'd want to see a whole lot of > > > positive evidence before adding such a configuration knob. > > > > Some of the evidence is TOAST itself. Every time you do not SET a column > > that has been toasted into external storage during an UPDATE, you win > > because the columns data isn't read during the scan for the row to > > update, it isn't read during heap_update(), it isn't actually updated at > > all (the toast reference is copied as is and the external value reused), > > and not a single byte of the external data is bloating WAL. If someone > > knows that 99% of their updates will not hit certain text columns in > > their tables, actually forcing them to be compressed no matter what and > > to be stored external if they exceed 100 bytes will be a win. > > Yes, thats the main use case. > > > Of course, this is a bit different from Simon's approach. What I > > describe here is a per pg_attribute configuration to enforce a certain > > new toaster behavior. Since we already have something that gives the > > toaster a per column cluestick (like not to bother trying to compress), > > it might be much easier to implement then Simon's proposal. It would > > require that the toaster goes over the initial heap tuple for those > > specially configured columns even if the tuple is below the toast > > threshold, which suggests that a pg_class.relhasspecialtoastneeds could > > be useful. But I think as for fine tuning capabilities, a column > > insensitive maximum tuple size is insufficient anyway. > > Well, sounds like we both want the same thing. The only discussion seems > to be about user interface. > > Setting it per column is much better for very fine tuning, but setting > them in isolation doesn't help decide what to do when you have lots of > medium length strings where the sum exceeds the toast target. > > IMHO it would be better to have an col-level "storage priority" (default > 0) and then an table-level settable toast target. So we start applying > the storage handling mechanisms on the highest priority columns and keep > going in descending order until we are under the limit for the table. > > ALTER TABLE foo > ALTER COLUMN foocol > SET STORAGE EXTERNAL PRIORITY 5 > WITH > (toast_target = 400); /* must be MAXALIGNed value */ > > Equal priorities are allowed, in which case lowest attribute id wins, > i.e. current behaviour remains the default. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
В списке pgsql-hackers по дате отправления: