> Is there a simple way to get a very rough kind of fairness between tenants in this case?
Yes, since the SKIP LOCKED query is just a special sort of SELECT that skips over locked rows, you can use all the normal SQL tools such as ORDER BY to accomplish what you want.
One way is to make the top row fair by randomizing over the tenant ID, for example
SELECT itemid
FROM queue
ORDER BY
md5(tenant_id || current_timestamp::TEXT),
item_id
FOR UPDATE SKIP LOCKED
LIMIT 1;
Here we append the ID to the timestamp and then hash it, so each time the SELECT is run there is effectively a different ordering between tenant IDs, hence fairness.
Note that this will eventually have performance implications for a deep queue since it forces a scan on the whole table. Adding an index on tenant_id may mitigate that, but as always do your own profiling.
Having a query like this that gets slower as the queue gets bigger is a really bad idea. It'll work just fine in the steady state when your queue is short. And then when you get overloaded and start queueing, you will be adding more load into the equation just to drain the queue. It's a recipe for a metastable system that causes a major outage. Adding an index on tenant_id will do nothing because you are always appending the current timestamp before hashing.
The query running in O(queue size) is very likely avoidable if you add an index on the tenant_id.
Ideally the query planner figures this out (see the sibling comment), but if not, the query can likely be written to "encourage" the planner, for example by first selecting the top few values of md5(tenant_id, current_timestamp) within a subquery before lateral joining into the SKIP LOCKED table.
If the concern is only that the queue falls over once it gets large enough, you can also LIMIT to a fixed number of rows before doing the outer SELECT. This gives a weaker fairness bound (tenants that have fewer than O(queue size / selected items) rows are at risk of being ignored) but you get a better guarantee of progress under contention.
Out of curiosity, is Postgres usually able to use a b-tree index's representation of the columnar data for its (set of) columns to calculate a derived value for those column(s), then scan through that in-memory derived data? Certainly not as fast as the index lookup itself, but avoids needing to have the entire table in memory, I would hope?
I'm not addressing your point on fairness but I've been working on a similar program but instead of taking locks out on the queue table I maintain a consumers position for locked and acknowldeged messages on the consumers table. It makes lots of queries simpler. Here's a simplified schema using SQL Server:
CREATE TABLE [dbo].[Nodes] (
[Id] UNIQUEIDENTIFIER NOT NULL,
[NetworkId] UNIQUEIDENTIFIER NOT NULL,
[Locked] INT NOT NULL,
[Acknowledged] INT NOT NULL,
);
CREATE TABLE [dbo].[Messages] (
[Id] UNIQUEIDENTIFIER NOT NULL,
[NetworkId] UNIQUEIDENTIFIER NOT NULL,
[NodeId] UNIQUEIDENTIFIER NOT NULL,
[Payload] NVARCHAR (MAX) NULL,
[InsertedOrder] INT DEFAULT (NEXT VALUE FOR dbo.MessageSequence) NOT NULL,
[TimestampUtc] DATETIME2 (7) DEFAULT (sysutcdatetime()) NOT NULL,
);
Select *
From Nodes With (Rowlock, Holdlock)
Where NetworkId = @networkId
And Id = @nodeId
Yes, since the SKIP LOCKED query is just a special sort of SELECT that skips over locked rows, you can use all the normal SQL tools such as ORDER BY to accomplish what you want.
One way is to make the top row fair by randomizing over the tenant ID, for example
Here we append the ID to the timestamp and then hash it, so each time the SELECT is run there is effectively a different ordering between tenant IDs, hence fairness.Note that this will eventually have performance implications for a deep queue since it forces a scan on the whole table. Adding an index on tenant_id may mitigate that, but as always do your own profiling.