Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On Fri, 2007-02-02 at 15:11 -0500, Tom Lane wrote: 2. Fix TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET to be correctly calculated (properly allowing for line pointers) and to be MAXALIGN multiples. The threshold value should be exactly the size of the largest tuple that you can put four of onto one page. Fix TOAST_MAX_CHUNK_SIZE so that it is *not* necessarily a MAXALIGN multiple, but rather causes the total length of a toast tuple to come out that way. This guarantees minimum space wastage on toast pages. Jan suggested to me a while back that having a configurable toast threshold would be a useful thing, when that table is also updated reasonably frequently. ISTM like a good idea, so a prototype has been written - nothing to do with Pavan's comments though. As you might expect, it does help in cases where we would otherwise produce lots of UPDATEd versions of a 1000 byte row, as well as on MIS queries that often don't pay much attention to text strings. This then allows the user some control over how much data gets toasted out of the main row. Many applications have long text fields of 100s of characters, for example a customer's stated, cleaned and previous addresses might together be VARCHAR(750), yet we may also want to UPDATE them regularly to store their current_balance. TOAST_MAX_CHUNK_SIZE can be fixed, though TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET could be settable for a table using a WITH parameter. It would seem like overkill to allow the threshold and target to differ when setting the parameter. If configurable, only MAXALIGNed values would be allowed. Sounds like a good time to suggest making these values configurable, within certain reasonable bounds to avoid bad behaviour. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
Simon Riggs [EMAIL PROTECTED] 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. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On 2/5/2007 11:52 AM, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] 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. 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. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed adjustments in MaxTupleSize and toastthresholds
On Feb 5, 2007, at 10:45 AM, Simon Riggs wrote: Jan suggested to me a while back that having a configurable toast threshold would be a useful thing, when that table is also updated reasonably frequently. While we're in there it probably makes sense to allow a configurable value for when to compress as well. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
