What is the best way to get the current number of worker processes?

Hey, all,

As part of configuring the max_worker_processes parameter I would like to know
how many current worker processes are active, but I'm unsure of the best way
to do this.

pg_stat_activity has a column backend_type which normally takes one of the
following values:

autovacuum launcher
autovacuum worker
logical replication launcher
logical replication worker
parallel worker
background writer
client backend
checkpointer
startup
walreceiver
walsender
walwriter

But extensions can launch additional processes and set this to a custom value
as well. (pglogical, as an example, does this.)

To the best of my understanding, only processes with a backend type set to
"logical replication worker" or "parallel worker" count towards
max_worker_processes, as well as any processes started by an extension that
have backend_type set to any value other than the above values. I believe that,
despite having "worker" in the name, an "autovacuum worker" process does not
count towards max_worker_processes, correct?

So I believe the following query should return the number of processes that
count towards max_worker_processes.

SELECT COUNT(*)
FROM pg_catalog.pg_stat_activity
WHERE backend_type NOT IN (
  'autovacuum launcher',
  'autovacuum worker',
  'logical replication launcher',
  -- 'logical replication worker', -- This is a worker process!
  -- 'parallel worker',            -- This is a worker process!
  'background writer',
  'client backend',
  'checkpointer',
  'startup',
  'walreceiver',
  'walsender',
  'walwriter'
);

Is there a better way to do this? Also, in 9.6 and earlier, pg_stat_activity
doesn't have a backend_type column, so I'm really unsure how to calculate
this in those older versions. I think pglogical is pretty much the only
extension that creates background workers on a regular basis, so I think I
can have a rough approximation by checking the application_name instead
for "pglogical", but I'd prefer a less hacky approach.



On a related note, can anyone verify that all processes that count towards
max_wal_senders will have backend_type set to "walsender", and all processes
that count towards max_connections will have backend_type set to "client
backend"?

I think it might be useful to have additional columns added to pg_stat_activity
that indicate which resource limits each process contributes to.

max_worker_processes           -> isworker
max_wal_senders                -> iswalsender
max_connections                -> isconn
superuser_reserved_connections -> issuconn

(You could then get the non-superuser connections via
  COUNT(*) FILTER (WHERE isconn) - COUNT(*) FILTER (WHERE issuconn)
.)

These two might also be useful, but I imagine using backend_type is pretty fool
proof for these:

max_logical_replication_workers -> islogrepworker
max_parallel_workers -> isparallel


Thanks,
Paul