Re: Table : Bloat grow high

On 2022-11-12 08:24:23 -0600, Ron wrote:
> On 11/12/22 08:05, Laurenz Albe wrote:
> You'd be most happy with HOT updates. Make sure that there is no index on any of
> the columns you update, and change the table to have a "fillfactor" less than
> 100. Then you can get HOT updates which don't require VACUUM for cleaning up.
>
> https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/
>
>
> To clarify: do HOT updates automatically happen if there's enough space on the
> page AND you don't update an indexed field (which should be minimized anyway)?

Yes.

> If that is true, what happens if someone then updates an indexed field?

Then that update won't be a HOT update.

>   Does PG keep doing HOT updates on the other tuples,

Yes.

> or does it stop HOT updates altogether until you recluster or full
> vacuum it?

No. The decision on whether an update is a done per tuple update. Within
a single update query, some tuples may be HOT updated and some not. And
for the next update query, each affected tuple is again considered for
HOT updates, so again some may be HOT updated and some not. If you are
updating the same tupel several times, you may get a few HOT updates
first, then a non-HOT update, then HOT updates again.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"