Re: High QPS, random index writes and vacuum

On Mon, Apr 17, 2023 at 7:43 PM peter plachta 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/do

Re: High QPS, random index writes and vacuum

On Tue, Apr 18, 2023 at 2:43 PM peter plachta wrote: > I was trying to understand whether there are any known workarounds for random > access + index vacuums. Are my vacuum times 'normal' ? Ah, it's not going to help on the old versions you mentioned, but for what it's worth: I remember noticing

Re: High QPS, random index writes and vacuum

Thank you David -- I increased this to 1GB as well (seeing as that was the max). We are doing mostly single passes now. On Mon, Apr 17, 2023 at 7:40 PM David Rowley wrote: > On Tue, 18 Apr 2023 at 12:35, peter plachta wrote: > > I increased work_mem to 2Gb > > maintenance_work_mem is the config

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 ar

Re: High QPS, random index writes and vacuum

On Tue, 18 Apr 2023 at 12:35, peter plachta wrote: > I increased work_mem to 2Gb maintenance_work_mem is the configuration option that vacuum uses to control how much memory it'll make available for storage of dead tuples. I believe 1GB would allow 178,956,970 tuples to be stored before multiple

Re: High QPS, random index writes and vacuum

peter plachta 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

Re: High QPS, random index writes and vacuum

On Mon, Apr 17, 2023 at 5:35 PM peter plachta wrote: > My question is: what is the recommended strategy to deal with such cases in > Postgres? You didn't say what version of Postgres you're using... -- Peter Geoghegan

High QPS, random index writes and vacuum

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.