From: Tom Lane <tgl@sss.pgh.pa.us>
To: peter plachta <pplachta@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: High QPS, random index writes and vacuum
In-reply-to: 
 <CAGTqnmbqhQWSDVOX+1ehQW5en=YCaXghnoRUh6tnnPeQex_OwQ@mail.gmail.com>
References: 
 <CAGTqnmbqhQWSDVOX+1ehQW5en=YCaXghnoRUh6tnnPeQex_OwQ@mail.gmail.com>
Comments: In-reply-to peter plachta <pplachta@gmail.com>
	message dated "Mon, 17 Apr 2023 17:35:33 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2548532.1681783265.1@sss.pgh.pa.us>
Date: Mon, 17 Apr 2023 22:01:05 -0400
Message-ID: <2548533.1681783265@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2548533.1681783265%40sss.pgh.pa.us>
Precedence: bulk

peter plachta <pplachta@gmail.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