On Wed, 13 Mar 2024 at 14:27, Laurenz Albe wrote:
>
> On Thu, 2023-09-21 at 16:18 -0700, Andres Freund wrote:
> > I think a minimal working approach could be to have the configuration be
> > based
> > on the relation size vs space known to the FSM. If the target block of an
> > update is higher t
On Thu, 2023-09-21 at 16:18 -0700, Andres Freund wrote:
> I think a minimal working approach could be to have the configuration be based
> on the relation size vs space known to the FSM. If the target block of an
> update is higher than ((relation_size - fsm_free_space) *
> new_reloption_or_guc), t
Hi,
On 2023-09-19 20:20:06 +0200, Matthias van de Meent wrote:
> Mostly agreed, but I think there's a pitfall here. You seem to assume
> we have a perfect oracle that knows the optimal data size, but we
> already know that our estimates can be significantly off. I don't
> quite trust the statistic
Hi,
On 2023-09-19 14:50:13 -0400, Robert Haas wrote:
> On Tue, Sep 19, 2023 at 12:56 PM Andres Freund wrote:
> > Yea, a setting like what's discussed here seems, uh, not particularly useful
> > for achieving the goal of compacting tables. I don't think guiding this
> > through SQL makes a lot of
Greetings,
* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote:
> > On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera
> > wrote:
> > > I was thinking something vaguely like "a table size that's roughly what
> > > an optimal autovacuuming schedule
On Tue, 2023-09-19 at 12:52 -0400, Robert Haas wrote:
> On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera
> wrote:
> > I was thinking something vaguely like "a table size that's roughly what
> > an optimal autovacuuming schedule would leave the table at" assuming 0.2
> > vacuum_scale_factor. You w
On Tue, 2023-09-19 at 14:50 -0400, Robert Haas wrote:
> But I know people will try to use it for instant compaction too, and
> there it's worth remembering why we removed old-style VACUUM FULL. The
> main problem is that it was mind-bogglingly slow. The other really bad
> problem is that it caused
On Tue, Sep 19, 2023 at 2:20 PM Matthias van de Meent
wrote:
> Mostly agreed, but I think there's a pitfall here. You seem to assume
> we have a perfect oracle that knows the optimal data size, but we
> already know that our estimates can be significantly off. I don't
> quite trust the statistics
Hi,
On 2023-09-19 19:33:22 +0200, Matthias van de Meent wrote:
> On Tue, 19 Sept 2023 at 18:56, Andres Freund wrote:
> >
> > Hi,
> >
> > On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> > > This makes me think that maybe the logic needs to be a little more
> > > complex to avoid the problem
On Tue, Sep 19, 2023 at 12:56 PM Andres Freund wrote:
> Yea, a setting like what's discussed here seems, uh, not particularly useful
> for achieving the goal of compacting tables. I don't think guiding this
> through SQL makes a lot of sense. For decent compaction you'd want to scan the
> table b
On Tue, 19 Sept 2023 at 18:52, Robert Haas wrote:
>
> On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera
> wrote:
> > I was thinking something vaguely like "a table size that's roughly what
> > an optimal autovacuuming schedule would leave the table at" assuming 0.2
> > vacuum_scale_factor. You wo
On Tue, 19 Sept 2023 at 18:56, Andres Freund wrote:
>
> Hi,
>
> On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> > This makes me think that maybe the logic needs to be a little more
> > complex to avoid the problem you describe: if an UPDATE is prevented
> > from being HOT because of this set
Hi,
On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> This makes me think that maybe the logic needs to be a little more
> complex to avoid the problem you describe: if an UPDATE is prevented
> from being HOT because of this setting, but then it goes and consults
> FSM and it gives the update
On Tue, Sep 19, 2023 at 12:30 PM Alvaro Herrera wrote:
> I was thinking something vaguely like "a table size that's roughly what
> an optimal autovacuuming schedule would leave the table at" assuming 0.2
> vacuum_scale_factor. You would determine the absolute minimum size for
> the table given th
On 2023-Sep-19, Robert Haas wrote:
> On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera
> wrote:
> > Second, I think we should make it auto-reset. That is, have the user
> > set some value; later, when some condition triggers (say, the table size
> > is 1.2x the limit value you configured), then th
On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera wrote:
> Second, I think we should make it auto-reset. That is, have the user
> set some value; later, when some condition triggers (say, the table size
> is 1.2x the limit value you configured), then the local_update_limit is
> automatically removed
On 2023-Sep-18, Robert Haas wrote:
> On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe wrote:
> > I don't think that is a good comparison. While most people probably
> > never need to touch "local_update_limit", "work_mem" is something everybody
> > has to consider.
> >
> > And it is not so hard to t
On Mon, 2023-09-18 at 12:22 -0400, Robert Haas wrote:
> On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe wrote:
> > I don't think that is a good comparison. While most people probably
> > never need to touch "local_update_limit", "work_mem" is something everybody
> > has to consider.
> >
> > And it
On Tue, Sep 5, 2023 at 11:15 PM Laurenz Albe wrote:
> I don't think that is a good comparison. While most people probably
> never need to touch "local_update_limit", "work_mem" is something everybody
> has to consider.
>
> And it is not so hard to tune: the setting would be the desired table
> si
On Wed, 2023-08-30 at 09:31 -0400, Robert Haas wrote:
> On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
> wrote:
> > I've reworked the patch a bit to remove the "excessive bloat with low
> > fillfactors when local space is available" issue that this parameter
> > could cause - local updates
On Wed, 30 Aug 2023 at 15:31, Robert Haas wrote:
>
> On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
> wrote:
> > I've reworked the patch a bit to remove the "excessive bloat with low
> > fillfactors when local space is available" issue that this parameter
> > could cause - local updates ar
On Wed, Aug 30, 2023 at 9:31 AM Robert Haas wrote:
> That seems like a good chance, but
*change
--
Robert Haas
EDB: http://www.enterprisedb.com
On Wed, Aug 30, 2023 at 9:01 AM Matthias van de Meent
wrote:
> I've reworked the patch a bit to remove the "excessive bloat with low
> fillfactors when local space is available" issue that this parameter
> could cause - local updates are now done if the selected page we would
> be inserting into i
On Mon, 28 Aug 2023 at 17:57, Robert Haas wrote:
>
> On Mon, Aug 28, 2023 at 11:50 AM Matthias van de Meent
> wrote:
> > Agreed on all points. But isn't that true for most most tools on bloat
> > prevention and/or detection? E.g. fillfactor, autovacuum_*, ...
>
> Not nearly to the same extent, IM
On Mon, Aug 28, 2023 at 11:50 AM Matthias van de Meent
wrote:
> Agreed on all points. But isn't that true for most most tools on bloat
> prevention and/or detection? E.g. fillfactor, autovacuum_*, ...
Not nearly to the same extent, IMHO. A lot of those parameters can be
left alone forever and you
On Mon, 28 Aug 2023 at 17:14, Robert Haas wrote:
>
> On Mon, Aug 28, 2023 at 10:52 AM Matthias van de Meent
> wrote:
> > In this new patch, I've updated a few comments to get mostly within
> > line length limits; the name of the storage parameter is now
> > "local_update_limit", as per discussion
On Mon, Aug 28, 2023 at 10:52 AM Matthias van de Meent
wrote:
> In this new patch, I've updated a few comments to get mostly within
> line length limits; the name of the storage parameter is now
> "local_update_limit", as per discussion on naming.
> I've also added local_update_limit to psql's aut
On Wed, 19 Jul 2023 at 14:58, Laurenz Albe wrote:
>
> On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > wrote:
> > > > So what were you thinking of? A session GUC
On Thu, 2023-08-24 at 18:23 +0200, Matthias van de Meent wrote:
> On Wed, 19 Jul 2023 at 15:13, Thom Brown wrote:
> >
> > On Wed, 19 Jul 2023, 13:58 Laurenz Albe, wrote:
> > > I agree that the name "max_local_update" could be improved.
> > > Perhaps "avoid_hot_above_size_mb".
> >
> > Or "hot_ta
On Wed, 19 Jul 2023 at 15:13, Thom Brown wrote:
>
> On Wed, 19 Jul 2023, 13:58 Laurenz Albe, wrote:
>> I agree that the name "max_local_update" could be improved.
>> Perhaps "avoid_hot_above_size_mb".
>
> Or "hot_table_size_threshold" or "hot_update_limit"?
Although I like these names, it doesn'
On Fri, 7 Jul 2023 at 12:18, Tomas Vondra wrote:
>
> On 7/7/23 11:55, Matthias van de Meent wrote:
>> On Fri, 7 Jul 2023 at 06:53, Dilip Kumar wrote:
>>>
>>>
>>> So IIUC, this parameter we can control that instead of putting the new
>>> version of the tuple on the same page, it should choose usin
On Wed, 19 Jul 2023, 13:58 Laurenz Albe, wrote:
> On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > wrote:
> > > > So what were you thinking of? A session GUC? A
On Thu, 2023-07-06 at 22:18 +0200, Matthias van de Meent wrote:
> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> >
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > wrote:
> > > So what were you thinking of? A session GUC? A table option?
> >
> > Both.
>
> Here's a small patch imple
On Fri, 7 Jul 2023 at 13:18, Tomas Vondra wrote:
> On 7/7/23 11:55, Matthias van de Meent wrote:
> > On Fri, 7 Jul 2023 at 06:53, Dilip Kumar wrote:
> >>
> >> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> >> wrote:
> >>>
> >>> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
>
> >>>
On Thu, 6 Jul 2023 at 21:18, Matthias van de Meent
wrote:
>
> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> >
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > wrote:
> > > So what were you thinking of? A session GUC? A table option?
> >
> > Both.
>
> Here's a small patch implementin
On Fri, 2023-07-07 at 16:27 +0530, Dilip Kumar wrote:
> On Fri, Jul 7, 2023 at 3:48 PM Tomas Vondra
> wrote:
> > I'm imagining either a table option with a couple possible values
> > (default, non-hot, first-page, ...) or maybe something even more
> > elaborate (perhaps even a callback?).
> >
>
On Fri, Jul 7, 2023 at 3:48 PM Tomas Vondra
wrote:
>
> On 7/7/23 11:55, Matthias van de Meent wrote:
> > On Fri, 7 Jul 2023 at 06:53, Dilip Kumar wrote:
> >>
> >> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> >> wrote:
> >>>
> >>> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
>
On 7/7/23 11:55, Matthias van de Meent wrote:
> On Fri, 7 Jul 2023 at 06:53, Dilip Kumar wrote:
>>
>> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
>> wrote:
>>>
>>> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
wrote:
On Fri, 7 Jul 2023 at 06:53, Dilip Kumar wrote:
>
> On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
> wrote:
> >
> > On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> > >
> > > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > > wrote:
> > > > So what were you thinking of? A session GU
On Fri, Jul 7, 2023 at 1:48 AM Matthias van de Meent
wrote:
>
> On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
> >
> > On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> > wrote:
> > > So what were you thinking of? A session GUC? A table option?
> >
> > Both.
>
> Here's a small patch implement
On Wed, 5 Jul 2023 at 19:55, Thom Brown wrote:
>
> On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
> wrote:
> > So what were you thinking of? A session GUC? A table option?
>
> Both.
Here's a small patch implementing a new table option max_local_update
(name very much bikesheddable). Value is
On Wed, 2023-07-05 at 12:02 +0100, Thom Brown wrote:
> On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> wrote:
> > On Wed, 5 Jul 2023 at 12:45, Thom Brown wrote:
> > > Heap-Only Tuple (HOT) updates are a significant performance
> > > enhancement, as they prevent unnecessary page writes. Howe
On Wed, 5 Jul 2023 at 18:05, Matthias van de Meent
wrote:
>
> On Wed, 5 Jul 2023 at 14:39, Thom Brown wrote:
> >
> > On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
> > wrote:
> > >
> > > On Wed, 5 Jul 2023 at 13:03, Thom Brown wrote:
> > > >
> > > > On Wed, 5 Jul 2023 at 11:57, Matthias van
On Wed, 5 Jul 2023 at 14:39, Thom Brown wrote:
>
> On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
> wrote:
> >
> > On Wed, 5 Jul 2023 at 13:03, Thom Brown wrote:
> > >
> > > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> > > wrote:
> > > >
> > > > On Wed, 5 Jul 2023 at 12:45, Thom Bro
On Wed, 5 Jul 2023 at 13:12, Matthias van de Meent
wrote:
>
> On Wed, 5 Jul 2023 at 13:03, Thom Brown wrote:
> >
> > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> > wrote:
> > >
> > > On Wed, 5 Jul 2023 at 12:45, Thom Brown wrote:
> > > > Heap-Only Tuple (HOT) updates are a significant p
On Wed, 5 Jul 2023 at 13:03, Thom Brown wrote:
>
> On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
> wrote:
> >
> > On Wed, 5 Jul 2023 at 12:45, Thom Brown wrote:
> > > Heap-Only Tuple (HOT) updates are a significant performance
> > > enhancement, as they prevent unnecessary page writes. Howe
On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
wrote:
>
> On Wed, 5 Jul 2023 at 12:45, Thom Brown wrote:
> > Heap-Only Tuple (HOT) updates are a significant performance
> > enhancement, as they prevent unnecessary page writes. However, HOT
> > comes with a caveat: it means that if we have lot
On Wed, 5 Jul 2023 at 12:45, Thom Brown wrote:
> Heap-Only Tuple (HOT) updates are a significant performance
> enhancement, as they prevent unnecessary page writes. However, HOT
> comes with a caveat: it means that if we have lots of available space
> earlier on in the relation, it can only be use
Hi,
Heap-Only Tuple (HOT) updates are a significant performance
enhancement, as they prevent unnecessary page writes. However, HOT
comes with a caveat: it means that if we have lots of available space
earlier on in the relation, it can only be used for new tuples or in
cases where there's insuffic
49 matches
Mail list logo
