Slow worker start for large jobs table

[frisia-mtz]

We noticed a slow start of procrastinate when the procrastinate_jobs table has a huge number of rows.

Procrastinate tries to prune stalled workers and deletes all entries in the procrastinate_workers table.

procrastinate/procrastinate/worker.py

Lines 373 to 381 in 0ece824

	async def run(self):
	"""
	Run the worker
	This will run forever until asked to stop/cancelled, or until no more job is available is configured not to wait
	"""
	logger.debug("Pruning stalled workers with old heartbeats")
	pruned_workers = await self.app.job_manager.prune_stalled_workers(
	self.stalled_worker_timeout
	)

The procrastinate_workers.id is referenced by a foreign key in the procrastinate_jobs table column worker_id which is set to NULL if the worker entry is deleted.

procrastinate/procrastinate/sql/schema.sql

Line 61 in 0ece824

worker_id bigint REFERENCES procrastinate_workers(id) ON DELETE SET NULL,

With ~13 million succeeded jobs which still have a worker_id, this delete-operation takes several minutes per worker.

My suggestion would be to set the procrastinate_jobs.worker_id to NULL if the state of the job changes from doing to anything else.

[ewjoachim]

My suggestion would be to set the procrastinate_jobs.worker_id to NULL if the state of the job changes from doing to anything else.

I think there may be some value in knowing where a job was executed, at least for a limited time. But yes, that could vary depending on how you use the tool.

Maybe we should just add an index on worker id ?

[TkTech]

There was some conversation about this when the feature was implemented, #1344 (comment)

In Chancy's case, we simply don't touch the taken_by (worker_id in Procrastinate) until it's being worked on or if the periodic recovery task has nabbed it. Just remove the foreign key and limit writes to worker_id, IMO. At cleanup or recovery time a worker_id missing from the workers table is all that maters and nothing is gained by having strong guarantees.

[frisia-mtz]

I agree with @TkTech on removing the FK. That would be the simplest and fastest solution, I guess.