3.3. Queue

The built-in queue subsystem supports PostgreSQL, and the URI is specified as a regular connection string as environment.database.uri in smtpd.yaml and queued.yaml. The installation instructions describes how the tables and indexes should be setup. Many of the examples below contain partitioning for scalability and manageability. In PostgreSQL 11, indexes are automatically created on partitons.

3.3.1. Hold queue

Messages can be put on hold in queue using status of HOLD. A quarantine can be implemented by setting a metadata as ID, and run a retention cleanup script like the example below:

#!/usr/bin/python3

import psycopg2

conn = psycopg2.connect("dbname=postgres user=postgres")
cur = conn.cursor()
cur.execute("UPDATE queue SET status = 'DELETE' WHERE status = 'HOLD' AND metadata->'_quarantineid' = 'mailquarantine:release1' AND ts < NOW() - '1 days'::INTERVAL;")
print("Deleted %d quarantined messages from release1" % cur.rowcount)
conn.commit()

In many cases it’s beneficial to separate the active and hold queue by database partitioning. You can do this by adding to the CREATE TABLE queue ... query:

CREATE TABLE queue ... PARTITION BY LIST (status);
CREATE TABLE queue_hold PARTITION OF queue FOR VALUES IN ('HOLD');
CREATE TABLE queue_def PARTITION OF queue DEFAULT;

3.3.2. Multiple queue workers

It’s possible to have multiple queued workers running on the same database by separating them using the metadata JSONB field. In queued.yaml you specify the selection condition as environment.database.where; for example metadata->>'_partid' IN ('2'). It’s recommended that you partition based on this condition, possibly in combination with the active/hold status, as in the example below:

CREATE TABLE queue ... PARTITION BY LIST (status);
CREATE TABLE queue_hold PARTITION OF queue FOR VALUES IN ('HOLD');
CREATE TABLE queue_def PARTITION OF queue DEFAULT PARTITION BY LIST ((metadata->>'_partid'));
CREATE TABLE queue_def_123 PARTITION OF queue_def FOR VALUES IN ('1', '2', '3');
CREATE TABLE queue_def_456 PARTITION OF queue_def FOR VALUES IN ('4', '5', '6');
CREATE TABLE queue_def_def PARTITION OF queue_def DEFAULT;

You then need to make sure that the necessary indexes and trigger exists on the partitions. In PostgreSQL 11 all indexes are automatically inherited, and the trigger is inherited from the primary table to the first level of partitions. You only need to add the trigger to sub-partitions:

CREATE TRIGGER messages_refcount AFTER INSERT OR DELETE ON queue_def_123 FOR ROW EXECUTE PROCEDURE messages_refcount_func();
CREATE TRIGGER messages_refcount AFTER INSERT OR DELETE ON queue_def_456 FOR ROW EXECUTE PROCEDURE messages_refcount_func();
CREATE TRIGGER messages_refcount AFTER INSERT OR DELETE ON queue_def_def FOR ROW EXECUTE PROCEDURE messages_refcount_func();

If the metadata field isn’t set, it’ll be null and can be matched using ...OR metadata->>'_partid' IS NULL on one of the workers.