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' ?
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@xxxxxxxxxxxxx> wrote:
peter plachta <pplachta@xxxxxxxxx> 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
