From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Chris Browne <cbbrowne(at)acm(dot)org>, Jan Wieck <JanWieck(at)Yahoo(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Modifying TOAST thresholds |
Date: | 2007-04-04 20:14:42 |
Message-ID: | 200704042014.l34KEgO01540@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Simon Riggs wrote:
> On Mon, 2007-04-02 at 22:23 -0400, Tom Lane wrote:
> > Chris Browne <cbbrowne(at)acm(dot)org> writes:
> > > tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> > >> ... tuning the TOAST parameters seems like
> > >> something we understand well enough already, we just need to put some
> > >> cycles into testing different alternatives. I would have no objection
> > >> to someone working on that during April and delivering a final patch
> > >> sometime before beta.
> >
> > > Here's a "drafty" patch that *tries* to do this using a GUC variable;
> > > it passes some interactive testing.
>
> Having both default GUC and individual table-level WITH parameters seems
> like the best way to me.
Agreed.
> > I came across a couple of issues while fooling with decoupling
> > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE:
> >
> > * Should TOAST_TUPLE_TARGET be configurable separately from
> > TOAST_TUPLE_THRESHOLD? It certainly doesn't make sense for the target
> > to be larger, but perhaps it is sane to want it to be smaller.
>
> I can't see I'd ever set them differently in practice. Sounds like too
> many people would get confused and set them wrong anyhow.
OK.
> > * There's a hardwired assumption in the system that
> > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at
> > all when we can prove that the maximum tuple width is less than
> > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c).
> > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed.
> > Should we abandon the notion altogether, and create a toast table
> > anytime the table contains any toastable types?
>
> That will create many more catalog entries than we have now, which seems
> not that great a side-effect.
OK, but we need to throw a clear message when the TOAST table needs to
be created by the administrator.
> > Or should we revel
> > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary
> > depending on the current threshold setting? We'd have to fix the
> > toaster routines to not try to push stuff out-of-line when there is no
> > out-of-line to push to ... but I think we probably had better do that
> > anyway for robustness, if we're allowing any variability at all in these
> > numbers.
>
> Sounds like the best plan.
Agreed. If you add a GUC without pg_class storage of the value at
CREATE TABLE/ALTER TABLE time, the GUC has to be checked at INSERT time,
meaning if the GUC changes, you might need a TOAST table during an
INSERT, which is going to fail.
The big question is whether this is for 8.3 or 8.4. I think adding a
GUC just for 8.3, without pg_class storage, will be a problem because
the GUC behavior will change once pg_class storage exists, i.e. GUC will
control at CREATE TABLE/ALTER TABLE rather than at INSERT time.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-04-04 20:26:02 | Re: Modifying TOAST thresholds |
Previous Message | Andrew Dunstan | 2007-04-04 20:05:55 | Re: Auto Partitioning |