pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table : Bloat grow high
7+ messages / 4 participants
[nested] [flat]

* Table : Bloat grow high
@ 2022-11-11 17:09  Alexis Zapata <alexise23@hotmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Alexis Zapata @ 2022-11-11 17:09 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>


Hi all,

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?

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Table : Bloat grow high
@ 2022-11-11 18:46  Ron <ronljohnsonjr@gmail.com>
  parent: Alexis Zapata <alexise23@hotmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Ron @ 2022-11-11 18:46 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/11/22 11:09, Alexis Zapata wrote:
>
> Hi all,
> In postgresql 13.5

Upgrade to at least 13.8.  (13.9 was released yesterday.)

> 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?

Manually vacuum it on a regular basis.

Think about partitioning it on PK boundaries; that way you can vacuum each 
child at the same time.

-- 
Angular momentum makes the world go 'round.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Table : Bloat grow high
@ 2022-11-12 14:05  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Alexis Zapata <alexise23@hotmail.com>
  1 sibling, 2 replies; 7+ messages in thread

From: Laurenz Albe @ 2022-11-12 14:05 UTC (permalink / raw)
  To: Alexis Zapata <alexise23@hotmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Table : Bloat grow high
@ 2022-11-12 14:24  Ron <ronljohnsonjr@gmail.com>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 1 reply; 7+ messages in thread

From: Ron @ 2022-11-12 14:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/12/22 08:05, Laurenz Albe wrote:
> 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/

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)?

If that is true, what happens if someone then updates an indexed field?  
Does PG keep doing HOT updates on the /other/ tuples, or does it stop HOT 
updates altogether until you recluster or full vacuum it?

-- 
Angular momentum makes the world go 'round.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Table : Bloat grow high
@ 2022-11-12 17:54  Peter J. Holzer <hjp-pgsql@hjp.at>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Peter J. Holzer @ 2022-11-12 17:54 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: Table : Bloat grow high
@ 2022-11-13 14:50  Alexis Zapata <alexise23@hotmail.com>
  parent: Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 1 reply; 7+ messages in thread

From: Alexis Zapata @ 2022-11-13 14:50 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi Laurenz ,
I found that xmin does not change when running the vacuum.



________________________________
De: Laurenz Albe <laurenz.albe@cybertec.at>
Enviado: sábado, 12 de noviembre de 2022 9:05 a. m.
Para: Alexis Zapata <alexise23@hotmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Table : Bloat grow high
@ 2022-11-13 20:15  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Alexis Zapata <alexise23@hotmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Laurenz Albe @ 2022-11-13 20:15 UTC (permalink / raw)
  To: Alexis Zapata <alexise23@hotmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Sun, 2022-11-13 at 14:50 +0000, Alexis Zapata wrote:
> I found that xmin does not change when running the vacuum.

Which xmin?

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2022-11-13 20:15 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-11 17:09 Table : Bloat grow high Alexis Zapata <alexise23@hotmail.com>
2022-11-11 18:46 ` Ron <ronljohnsonjr@gmail.com>
2022-11-12 14:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-11-12 14:24   ` Ron <ronljohnsonjr@gmail.com>
2022-11-12 17:54     ` Peter J. Holzer <hjp-pgsql@hjp.at>
2022-11-13 14:50   ` Alexis Zapata <alexise23@hotmail.com>
2022-11-13 20:15     ` Laurenz Albe <laurenz.albe@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox