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.
The documentation under https://www.postgresql.org/docs/16/indexes-types.html implies that it can be used to access the sorted values - but does that feed through the query planner?
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
(edit: simplified sql)
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.