High QPS, random index writes and vacuum

From: peter plachta <pplachta(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: High QPS, random index writes and vacuum
Date: 2023-04-18 00:35:33
Message-ID: CAGTqnmbqhQWSDVOX+1ehQW5en=YCaXghnoRUh6tnnPeQex_OwQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all

The company I work for has a large (50+ instances, 2-4 TB each) Postgres
install. One of the key problems we are facing in vanilla Postgres is
vacuum behavior on high QPS (20K writes/s), random index access on UUIDs.

In one case the table is 50Gb and has 3 indexes which are also 50Gb each.
It takes 20 hours to vacuum the entire thing, where bulk of the time is
spent doing 'index vacuuming'. The table is then instantly vacuumed again.
I increased work_mem to 2Gb, decreased sleep threshold to 2ms and increased
the IO limit to 2000. I also changed the autovacuum thresholds for this
table.

I understand that doing random index writes is not a good strategy, but, 20
hours to vacuum 200Gb is excessive.

My question is: what is the recommended strategy to deal with such cases in
Postgres?

Thanks very much!!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter Geoghegan 2023-04-18 00:38:40 Re: High QPS, random index writes and vacuum
Previous Message peter plachta 2023-04-18 00:25:06 Re: time sorted UUIDs