Get maximum number of job slaves per instance that can be created for the execution in PostgreSQL

Question

I am migrating oracle database to postgresql.

While migration came to know with the following query in the oracle side.

Oracle Query:

SELECT
TRIM(value) AS val
FROM v$parameter
WHERE name = 'job_queue_processes';

I just want to know how can we get the maximum number of job slaves per instance that can be created for the execution at the postgresql side.

I have created pg_cron extension and created required jobs till now. But one of the function is using above query in oracle, so I just want to convert it into the postgresql.

Answer 1

The documentation is usually a good source of information.

Important: By default, pg_cron uses libpq to open a new connection to the local database.

In this case, there is no specific limit. It would be limited in the same way other user connections are limited. Mostly by max_connections, but possibly lowered from that for particular users or particular databases by the ALTER command. You could create a user specifically for cron if you wanted to limit its connections separately, then grant that user privileges of other roles it will operate on behalf of. I don't know what pg_cron does if the limit is reached, does it deal with it gracefully or not?

Alternatively, pg_cron can be configured to use background workers. In that case, the number of concurrent jobs is limited by the max_worker_processes setting, so you may need to raise that.

Note that the max number of workers may have to be shared with parallel execution workers and maybe with other extensions.