Re: High QPS, random index writes and vacuum

On Mon, Apr 17, 2023 at 7:43 PM peter plachta <pplachta(at)gmail(dot)com> wrote:
> Version: I sheepishly admit it's 9.6, 10 and 11 (it's Azure Single Server, that's another story).

If you can upgrade to 14, you'll find that there is much improved
management of index updates on that version:

https://www.postgresql.org/docs/current/btree-implementation.html#BTREE-DELETION

But it's not clear what the problem really is here. If the problem is
that you're dependent on vacuum to get acceptable response times by
holding back index bloat, then an upgrade could easily help a lot. But
an upgrade might not make VACUUM take less time, given that you've
already tuned it fairly aggressively. It depends.

An upgrade might make VACUUM go faster if you set
vacuum_cost_page_miss to 2, which is the default on later versions
anyway -- looks like you didn't touch that. And, as Thomas said, later
versions do have parallel VACUUM, though that cannot be used by
autovacuum workers.

--
Peter Geoghegan