From: | Alexis Zapata <alexise23(at)hotmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | RE: Table : Bloat grow high |
Date: | 2022-11-13 14:50:40 |
Message-ID: | BLAPR19MB46269F4CF29710CDFADB94C7A1029@BLAPR19MB4626.namprd19.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Laurenz ,
I found that xmin does not change when running the vacuum.
________________________________
De: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Enviado: sábado, 12 de noviembre de 2022 9:05 a. m.
Para: Alexis Zapata <alexise23(at)hotmail(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Asunto: Re: Table : Bloat grow high
On Fri, 2022-11-11 at 17:09 +0000, Alexis Zapata wrote:
> In postgresql 13.5 I have a table (size 3.1 GB) and in this table occurs near to
> 200 updates per second, after 2 days the size table is 7 GB and bloat grow to 45% and
> the query operations are degraded. vacuum runs every 5 seconds over this. but the
> bloat growth continues, to solve the
> problem quickly, we have made a replica of the table with a trigger, then a copy
> of the data and in a
> transaction we rename the table, but it would not be the best solution.
> Some suggestion about stop this size increase or parameter to setting up?
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/
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | 徐志宇徐 | 2022-11-13 16:09:36 | How to check stream replication latest history status |
Previous Message | Tom Lane | 2022-11-13 06:07:25 | Re: Upgrading to v12 |