Re: High QPS, random index writes and vacuum

Thank you Tom.
Version: I sheepishly admit it's 9.6, 10 and 11 (it's Azure Single Server,
that's another story).

I am definitely looking at redoing the way we do UUIDs... but that' s not a
trivial change given the volume of data we have + 24/7 workload.

I was trying to understand whether there are any known workarounds for
random access + index vacuums. Are my vacuum times 'normal' ?

On Mon, Apr 17, 2023 at 7:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> peter plachta <pplachta(at)gmail(dot)com> writes:
> > 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.
>
> Indexing on a UUID column is an antipattern, because you're pretty much
> guaranteed the worst-case random access patterns for both lookups and
> insert/delete/maintenance cases. Can you switch to timestamps or
> the like?
>
> There are proposals out there for more database-friendly ways of
> generating UUIDs than the traditional ones, but nobody's gotten
> around to implementing that in Postgres AFAIK.
>
> regards, tom lane
>