Re: Disable parallel query by default

On Tue, 15 Jul 2025 12:04:12 +0200
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote:

> On Mon, 14 Jul 2025 17:25:22 -0400
> Greg Sabino Mullane <htamfids@gmail.com> wrote:
> […]
> > > Other than picking an arbitrary value (i.e. 5000), any thoughts about how
> > > to build a case around a specific value ?
> >
> >
> > Do you have actual examples of queries / situations that are harmed by the
> > current settings? Let's start there.
>
> I did, mid 2024. The customer environment was PostgreSQL 13 and later
> PostgreSQL 16 after a major upgrade.
>
> The application was Nextcloud, ~4000 users per day, 500 per minutes, around
> 4000 queries per second on a database of only 40GB. Typical OLTP workload.

NB: 500 **active** users per minute.

I went back in time to gather as many stats/info as I could from our report,
various sysstat numbers and one pgbadger report. I only extracted the top 10
time consuming queries from the report. You'll see it's a basic OLTP workload.

The "max_parallel_worker_per_gather=0" as been set the 16th at 10am.

Note that I asked our customer to double check the stats from the 22nd were
collected from the primary as they are surprisingly low compared to the 15th
and 16th. He assures me the production was on this node and the activity was
usual (600 concurrent users per minute).

See attachments.

> Despite connection poolers in the architecture (one per application node), the
> number of procs/s on the (dedicated) server was between 60-150+ depending on
> the activity. This was hammering the server (a small VM of 8 cores 32GB of
> memory).

NB: As you can see on the graph, VM has grown from 8 to 16 cores before
we turned "max_parallel_worker_per_gather=0", I forgot about that.

I'm not stating the PQ should be off by default, but there's definitely
something to improve in the costing model, or parallel background worker
management I suppose.

Regards,