pgmq.purge(queue_name text) performance

[olirice]

In pgmq.purge(queue_name text) it uses delete from with no where clause and then collect the row count with GET DIAGNOSTICS

pgmq/pgmq-extension/sql/pgmq--1.4.0--1.4.1.sql

Lines 310 to 311 in 78a766b

	EXECUTE format('DELETE FROM pgmq.%I', qtable);
	GET DIAGNOSTICS deleted_count = ROW_COUNT;

If the queue is large that will create a lot of IO and WAL with 1 record per deleted tuple. At scale that could contribute to exhausting IO on the primary and cause replication lag on any replicas

What do you think about swapping it out for something like

SELECT count(*) INTO deleted_count FROM pgmq.my_tab;
TRUNCATE pgmq.my_tab;

since truncate produces a single WAL record a little IO on the primary?

[ChuckHend]

Yea I think this is a good enhancement. We've also seen poor perf when queue grows very large and have used TRUNCATE directly on the queue table as a workaround. I think makes sense to put that in the API.