From: | Luís Roberto Weck <luisroberto(at)siscobra(dot)com(dot)br> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slow query on a one-tuple table |
Date: | 2019-09-19 22:46:46 |
Message-ID: | f1468794-1681-3f1e-2f9c-b491cb87ed5d@siscobra.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Em 19/09/2019 19:32, Michael Lewis escreveu:
>
> I have about 6 bigint fields in this table that are very frequently
> updated, but none of these are indexed. I thought that by not
> having an
> index on them, would make all updates HOT, therefore not bloating the
> primary key index. Seems I was wrong?
>
>
> HOT update is only possible if there is room in the page. How wide is
> your single tuple?
>
> Have you tuned autovacuum or are you running defaults? Not sure of
> your perception of "very frequently" updated values, but if you have
> bloat issue, vacuum early and often. Not sure how the math works out
> on a table with single tuple in terms of calculating when it is time
> to vacuum, but it certainly needs to be tuned differently than a table
> with millions of rows which is what I would be more used to.
I'm not sure how to measure how wide the tuple is, can you point me in
the right direction?
As fas as autovacuum options, this is what I'm using:
autovacuum_enabled=true,
fillfactor=50,
autovacuum_vacuum_threshold=25,
autovacuum_vacuum_scale_factor=0,
autovacuum_analyze_threshold=10,
autovacuum_analyze_scale_factor=0.05,
autovacuum_vacuum_cost_delay=10,
autovacuum_vacuum_cost_limit=1000,
toast.autovacuum_enabled=true
By "very frequently" I mean I can update it up to 800000 times a day.
Usually this number is closer to 100000.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-09-19 22:57:11 | Re: Slow query on a one-tuple table |
Previous Message | Michael Lewis | 2019-09-19 22:32:19 | Re: Slow query on a one-tuple table |