Spanner queues deliver messages at least once and are designed for parallel processing across multiple receivers. When multiple messages for the same entity (such as a customer account, an order, or a device) are sent to a queue, different workers can pull and process them at the same time. Even if one message is sent first, a network delay or retry can slow down the first worker while a second worker finishes a later message.
If every message across the entire queue were processed in strict global order, one slow worker could stall the entire queue (a phenomenon known as head-of-line blocking). However, most applications don't need every message across the entire database to wait in a single global line. Instead, you only need tasks for the same entity to run in order, while tasks for different entities run in parallel. You can use the following patterns to process messages in order per entity based on your workload.
Chain messages for sequential workflows
When a background job for an entity consists of dependent sequential steps (for
example, step-1 followed by step-2 and step-3), and external requests
don't add competing tasks to that workflow in parallel, you want each step to
start only after the previous step completes.
To enforce step-by-step ordering, enqueue only the first step initially. When a
worker finishes processing the current step, it acknowledges the current message
(DELETE ...
ASSERT_ROWS_MODIFIED
1) and inserts the message for the next step in the same
read-write transaction.
Because the message for the next step isn't created until the current step is completed and acknowledged in the same transaction, two steps for the same workflow never exist in the queue at the same time and can't be processed out of order by concurrent workers.
For example, when a worker finishes step-1 of an onboarding workflow and
schedules step-2:
GoogleSQL
-- Inside the worker transaction after finishing step-1:
-- 1. Acknowledge the current step
DELETE FROM OnboardingQueue
WHERE CustomerId = 456 AND StepName = 'step-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Enqueue the next step in the sequence
INSERT INTO OnboardingQueue (CustomerId, StepName, DeliverTime, Payload)
VALUES (456, 'step-2', CURRENT_TIMESTAMP(), b'{"stage": "provision_resources"}');
PostgreSQL
-- Inside the worker transaction after finishing step-1:
-- 1. Acknowledge the current step
DELETE FROM onboardingqueue
WHERE customerid = 456 AND stepname = 'step-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Enqueue the next step in the sequence
INSERT INTO onboardingqueue (customerid, stepname, deliver_time, payload)
VALUES (456, 'step-2', CURRENT_TIMESTAMP, CAST('{"stage": "provision_resources"}' AS bytea));
Use an ordered work table and a wake-up queue
However, when you need all tasks for a particular entity to run in commit order, even while external requests, webhooks, or user actions add new tasks as earlier tasks are still processing, this pattern supports workloads that fit within the write capacity of a single split.
To process concurrent tasks in order per entity, extend the dirty flag pattern by storing ordered tasks in an interleaved table and using the queue only for lightweight wake-up notifications.
First, interleave an ordered work table (using commit timestamps) and a wake-up queue in a parent entity table that tracks the active claim. To avoid hotspots across splits, choose a primary key (such as a UUID or a bit-reversed sequence) that distributes entities evenly:
GoogleSQL
CREATE SEQUENCE CustomerIdSeq OPTIONS (sequence_kind = 'bit_reversed_positive');
CREATE TABLE Customers (
CustomerId INT64 NOT NULL DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE CustomerIdSeq)),
CustomerName STRING(1024),
IsProcessing BOOL NOT NULL DEFAULT (FALSE),
ActiveClaimId STRING(36),
ProcessingExpiresAt TIMESTAMP,
) PRIMARY KEY (CustomerId);
CREATE TABLE CustomerWorkItems (
CustomerId INT64 NOT NULL,
CreatedAt TIMESTAMP NOT NULL DEFAULT (PENDING_COMMIT_TIMESTAMP())
OPTIONS (allow_commit_timestamp = true),
BatchIndex INT64 NOT NULL DEFAULT (0),
WorkType STRING(128) NOT NULL,
WorkPayload JSON NOT NULL,
) PRIMARY KEY (CustomerId, CreatedAt, BatchIndex),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
CREATE QUEUE CustomerDirtyQueue (
CustomerId INT64 NOT NULL,
MessageId STRING(36) NOT NULL,
Payload BYTES(MAX) NOT NULL,
) PRIMARY KEY (CustomerId, MessageId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
PostgreSQL
CREATE SEQUENCE customeridseq BIT_REVERSED_POSITIVE;
CREATE TABLE customers (
customerid bigint NOT NULL DEFAULT nextval('customeridseq'),
customername varchar(1024),
isprocessing boolean NOT NULL DEFAULT false,
activeclaimid varchar(36),
processingexpiresat timestamptz,
PRIMARY KEY (customerid)
);
CREATE TABLE customerworkitems (
customerid bigint NOT NULL,
createdat spanner.commit_timestamp NOT NULL DEFAULT spanner.pending_commit_timestamp(),
batchindex bigint NOT NULL DEFAULT 0,
worktype varchar(128) NOT NULL,
workpayload jsonb NOT NULL,
PRIMARY KEY (customerid, createdat, batchindex)
) INTERLEAVE IN PARENT customers ON DELETE CASCADE;
CREATE QUEUE customerdirtyqueue (
customerid bigint NOT NULL,
messageid varchar(36) NOT NULL,
payload bytea NOT NULL,
PRIMARY KEY (customerid, messageid)
) INTERLEAVE IN PARENT customers ON DELETE CASCADE;
With this schema in place, senders and workers coordinate using the following workflow:
- Insert ordered tasks and a wake-up message: In the sender transaction,
insert the tasks into
CustomerWorkItems(usingPENDING_COMMIT_TIMESTAMP()and a zero-basedBatchIndexto order multiple tasks within the same transaction) and insert a single wake-up message with an empty payload (b'') intoCustomerDirtyQueue. - Claim, process, and release the entity in three steps:
- Step 1 (Claim the entity in Transaction 1): When a worker receives a
wake-up message from
RECEIVE_CustomerDirtyQueue, it runs a short read-write transaction:- Check
Customersand acknowledge the incoming wake-up message (DELETE ... ASSERT_ROWS_MODIFIED 1). If another worker already holds an active claim (IsProcessing = TRUEandProcessingExpiresAt > CURRENT_TIMESTAMP()), commit Transaction 1 and exit. The active worker checks for remaining tasks before releasing its claim. - Otherwise, read the oldest batch of tasks (for example,
LIMIT 25) fromCustomerWorkItemsordered byCreatedAt ASC, BatchIndex ASC, and delete any remaining expired fallback message (whoseMessageIdis the priorActiveClaimId). - If tasks are returned, set
IsProcessing = TRUE,ActiveClaimId, andProcessingExpiresAt(such as 10 seconds in the future) onCustomers, and insert a fallback message (MessageId = ActiveClaimId) intoCustomerDirtyQueuescheduled forProcessingExpiresAt. If no tasks remain, resetIsProcessing = FALSE,ActiveClaimId = NULL, andProcessingExpiresAt = NULL.
- Check
- Step 2 (Process tasks outside a transaction): After Transaction 1 commits, the worker runs the tasks sequentially in application code. If a task fails, the worker stops the batch early and still runs Step 3 so completed tasks are deleted before retrying the failed task.
- Step 3 (Delete completed tasks and release the entity in
Transaction 2): The worker opens a second short read-write
transaction, deletes its fallback message (
ActiveClaimId) fromCustomerDirtyQueuewithASSERT_ROWS_MODIFIED 1, deletes the completed tasks fromCustomerWorkItems, inserts a new wake-up message if more tasks remain inCustomerWorkItems, and resetsIsProcessing = FALSE,ActiveClaimId = NULL, andProcessingExpiresAt = NULLonCustomers.
- Step 1 (Claim the entity in Transaction 1): When a worker receives a
wake-up message from
This pattern preserves strict per-entity ordering and high throughput for the following reasons:
- Commit-timestamp ordering: Storing tasks in
CustomerWorkItemskeyed by(CustomerId, CreatedAt, BatchIndex)orders every task by its transaction commit timestamp, even when multiple senders write concurrently. - Single-split locality:
Interleaving
CustomerWorkItemsandCustomerDirtyQueueinCustomerskeeps an entity's claim state, tasks, and wake-up messages in the same split. KeepingCustomerDirtyQueue.Payloadempty (b'') also minimizes queue storage and in-memory delivery overhead. - Short-lived database locks: Holding a read-write transaction open during
Step 2 locks the
Customersrow and blocks concurrent senders. Splitting the claim (Transaction 1) and completion (Transaction 2) into two short transactions ensures that only one worker processes an entity at a time without holding database locks while application code runs. - Crash recovery and fencing: Immediately acknowledging the initial
wake-up message in Transaction 1 and scheduling a fallback message
(
ActiveClaimId) atProcessingExpiresAtreplaces the fixed 10-secondRECEIVEmessage lease with a configurable entity claim. If the worker crashes during Step 2, the fallback message matures and wakes up another worker. If a slow worker finishes after its claim expired and another worker already took over, deletingActiveClaimIdwithASSERT_ROWS_MODIFIED 1in Transaction 2 fails withOUT_OF_RANGE. Letting that error propagate (or explicitly rolling back) aborts Transaction 2 before those writes can commit.
The following statements insert two ordered tasks and a wake-up message in a single sender transaction:
GoogleSQL
-- 1. Insert ordered tasks for the entity using a 0-based BatchIndex
INSERT INTO CustomerWorkItems (CustomerId, CreatedAt, BatchIndex, WorkType, WorkPayload)
VALUES
(456, PENDING_COMMIT_TIMESTAMP(), 0, 'ORDER_CREATED', JSON '{"order_id": 1001}'),
(456, PENDING_COMMIT_TIMESTAMP(), 1, 'PAYMENT_CAPTURED', JSON '{"order_id": 1001}');
-- 2. Send a single lightweight wake-up message to the interleaved queue
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'msg-uuid-1', CURRENT_TIMESTAMP(), b'');
PostgreSQL
-- 1. Insert ordered tasks for the entity using a 0-based batchindex
INSERT INTO customerworkitems (customerid, createdat, batchindex, worktype, workpayload)
VALUES
(456, spanner.pending_commit_timestamp(), 0, 'ORDER_CREATED', '{"order_id": 1001}'::jsonb),
(456, spanner.pending_commit_timestamp(), 1, 'PAYMENT_CAPTURED', '{"order_id": 1001}'::jsonb);
-- 2. Send a single lightweight wake-up message to the interleaved queue
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'msg-uuid-1', CURRENT_TIMESTAMP, CAST('' AS bytea));
The following SQL statements show how a worker receives wake-up messages, claims the entity in Transaction 1, and releases the entity in Transaction 2:
GoogleSQL
-- Stream wake-up messages from the queue:
SELECT CustomerId, MessageId, Payload, DeliverTime, SpannerLeaseToken
FROM RECEIVE_CustomerDirtyQueue(max_duration=>'20m', max_batch_size=>10);
-- Transaction 1 (claim the entity):
-- 1. Check whether another worker holds an active claim on the entity
SELECT
IsProcessing,
ActiveClaimId,
IFNULL(ProcessingExpiresAt > CURRENT_TIMESTAMP(), FALSE) AS HasActiveClaim
FROM Customers
WHERE CustomerId = 456;
-- Always acknowledge the incoming wake-up message in Transaction 1.
-- Let OUT_OF_RANGE propagate to roll back the transaction so duplicate workers exit.
-- If IsProcessing = TRUE and HasActiveClaim = TRUE, commit after this DELETE and exit.
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'msg-uuid-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Only if no active claim exists, read the next ordered batch of work items
SELECT CreatedAt, BatchIndex, WorkType, WorkPayload
FROM CustomerWorkItems
WHERE CustomerId = 456
ORDER BY CreatedAt ASC, BatchIndex ASC
LIMIT 25;
-- 3. Always clean up any remaining expired fallback message
-- (when ActiveClaimId IS NOT NULL AND ActiveClaimId != 'msg-uuid-1'):
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'expired-claim-uuid';
-- 4. If step 2 returns work items, schedule a 10-second fallback message
-- and mark the entity as busy. (If step 2 returns no rows, clear any
-- expired claim fields on Customers instead.)
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'claim-uuid-2', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND), b'');
UPDATE Customers
SET IsProcessing = TRUE,
ActiveClaimId = 'claim-uuid-2',
ProcessingExpiresAt = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND)
WHERE CustomerId = 456;
-- Transaction 2 (after processing the batch in order outside a transaction):
-- 1. Verify the claim is still active by deleting the fallback message.
-- Let OUT_OF_RANGE propagate to roll back the transaction if another worker took over.
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
-- 2. Delete the completed work items
DELETE FROM CustomerWorkItems
WHERE CustomerId = 456
AND CreatedAt = TIMESTAMP '2026-05-01T12:00:00.123456Z'
AND BatchIndex IN (0, 1);
-- 3. Check whether any unprocessed work items remain:
SELECT 1 FROM CustomerWorkItems
WHERE CustomerId = 456
LIMIT 1;
-- Run this INSERT only if the preceding SELECT returned a row:
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'continuation-uuid-3', CURRENT_TIMESTAMP(), b'');
-- 4. Release the entity claim
UPDATE Customers
SET IsProcessing = FALSE, ActiveClaimId = NULL, ProcessingExpiresAt = NULL
WHERE CustomerId = 456 AND ActiveClaimId = 'claim-uuid-2';
PostgreSQL
-- Stream wake-up messages from the queue:
SELECT customerid, messageid, payload, deliver_time, spanner_lease_token
FROM spanner.receive_customerdirtyqueue(
max_batch_size=>10, priority=>NULL, max_duration=>'20m');
-- Transaction 1 (claim the entity):
-- 1. Check whether another worker holds an active claim on the entity
SELECT
isprocessing,
activeclaimid,
COALESCE(processingexpiresat > CURRENT_TIMESTAMP, false) AS hasactiveclaim
FROM customers
WHERE customerid = 456;
-- Always acknowledge the incoming wake-up message in Transaction 1.
-- Let OUT_OF_RANGE propagate to roll back the transaction so duplicate workers exit.
-- If isprocessing = true and hasactiveclaim = true, commit after this DELETE and exit.
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'msg-uuid-1'
ASSERT_ROWS_MODIFIED 1;
-- 2. Only if no active claim exists, read the next ordered batch of work items
SELECT createdat, batchindex, worktype, workpayload
FROM customerworkitems
WHERE customerid = 456
ORDER BY createdat ASC, batchindex ASC
LIMIT 25;
-- 3. Always clean up any remaining expired fallback message
-- (when activeclaimid IS NOT NULL AND activeclaimid != 'msg-uuid-1'):
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'expired-claim-uuid';
-- 4. If step 2 returns work items, schedule a 10-second fallback message
-- and mark the entity as busy. (If step 2 returns no rows, clear any
-- expired claim fields on customers instead.)
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'claim-uuid-2', CURRENT_TIMESTAMP + INTERVAL '10 SECOND', CAST('' AS bytea));
UPDATE customers
SET isprocessing = true,
activeclaimid = 'claim-uuid-2',
processingexpiresat = CURRENT_TIMESTAMP + INTERVAL '10 SECOND'
WHERE customerid = 456;
-- Transaction 2 (after processing the batch in order outside a transaction):
-- 1. Verify the claim is still active by deleting the fallback message.
-- Let OUT_OF_RANGE propagate to roll back the transaction if another worker took over.
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
-- 2. Delete the completed work items
DELETE FROM customerworkitems
WHERE customerid = 456
AND createdat = '2026-05-01T12:00:00.123456Z'::timestamptz
AND batchindex IN (0, 1);
-- 3. Check whether any unprocessed work items remain:
SELECT 1 FROM customerworkitems
WHERE customerid = 456
LIMIT 1;
-- Run this INSERT only if the preceding SELECT returned a row:
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'continuation-uuid-3', CURRENT_TIMESTAMP, CAST('' AS bytea));
-- 4. Release the entity claim
UPDATE customers
SET isprocessing = false, activeclaimid = NULL, processingexpiresat = NULL
WHERE customerid = 456 AND activeclaimid = 'claim-uuid-2';
Deduplicate bursty wake-up messages with temporal batching
When an entity receives frequent bursts of updates within a few seconds, sending a separate wake-up message on every transaction creates unnecessary queue writes and redundant wake-up deliveries.
To coalesce wake-up messages at write time, combine
temporal batching
with a deterministic MessageId:
- Set
MessageIdto the time window's bucket ID (for example, a 5-second bucket). - Round
DeliverTimeup to the next time window plus a per-entity offset computed withFARM_FINGERPRINT(orspanner.farm_fingerprint). - Insert the wake-up message using
INSERT OR IGNOREin GoogleSQL orINSERT ... SELECT ... FROM ... WHERE NOT EXISTSin PostgreSQL.
Every transaction for the same entity within that 5-second window computes the
same MessageId and DeliverTime. All transactions insert their tasks into
CustomerWorkItems, but only the first transaction in the window writes a
wake-up message to CustomerDirtyQueue. Adding a per-entity offset spreads
delivery times across the 5-second interval so different entities don't all
trigger at the exact same second.
For example, the following sender transaction coalesces wake-up messages into 5-second buckets:
GoogleSQL
-- 1. Insert the ordered work item
INSERT INTO CustomerWorkItems (CustomerId, CreatedAt, BatchIndex, WorkType, WorkPayload)
VALUES (456, PENDING_COMMIT_TIMESTAMP(), 0, 'METRIC_RECORDED', JSON '{"v": 42}');
-- 2. Coalesce wake-up messages into a 5-second window with a per-customer offset
INSERT OR IGNORE INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (
456,
CONCAT('bucket-', CAST(DIV(UNIX_SECONDS(CURRENT_TIMESTAMP()), 5) * 5 AS STRING)),
TIMESTAMP_SECONDS(
DIV(UNIX_SECONDS(CURRENT_TIMESTAMP()), 5) * 5 + 5
+ MOD(ABS(FARM_FINGERPRINT(CAST(456 AS STRING))), 5)
),
b''
);
PostgreSQL
-- 1. Insert the ordered work item
INSERT INTO customerworkitems (customerid, createdat, batchindex, worktype, workpayload)
VALUES (456, spanner.pending_commit_timestamp(), 0, 'METRIC_RECORDED', '{"v": 42}'::jsonb);
-- 2. Coalesce wake-up messages into a 5-second window with a per-customer offset
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
SELECT
456,
'bucket-' || bucket.start_sec::text,
to_timestamp(
bucket.start_sec + 5
+ (abs(spanner.farm_fingerprint(456::text)) % 5)
),
CAST('' AS bytea)
FROM (
SELECT (floor(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP))::bigint / 5) * 5 AS start_sec
) AS bucket
WHERE NOT EXISTS (
SELECT 1 FROM customerdirtyqueue
WHERE customerid = 456
AND messageid = 'bucket-' || bucket.start_sec::text
);
Reduce lock contention from concurrent wake-up messages
When multiple wake-up messages for the same busy entity mature at the same time,
several workers can start Transaction 1 simultaneously and contend for
read-write locks on the same Customers row.
To reduce lock contention on hot entities, perform a lock-free
single read (client.Single())
before starting Transaction 1 to check whether IsProcessing = TRUE and
ProcessingExpiresAt > CURRENT_TIMESTAMP(). If the entity is busy, sleep for a
brief random delay (such as 10 to 50 milliseconds) before running Transaction 1
to re-check Customers and acknowledge the duplicate wake-up message.
A single read checks the entity's claim status without acquiring read-write
locks, and the random delay staggers the subsequent read-write transactions so
workers don't contend on the Customers row at the same millisecond. Always
re-check IsProcessing inside Transaction 1 before deleting the wake-up message
so that if the active worker finishes right after the single read, the wake-up
message isn't deleted while new tasks remain unprocessed.
For example, the following Go function applies a random delay when an entity is already busy:
func maybeJitterBeforeClaim(ctx context.Context, client *spanner.Client, customerID int64) error {
iter := client.Single().Query(ctx, spanner.Statement{
SQL: `SELECT IsProcessing AND IFNULL(ProcessingExpiresAt > CURRENT_TIMESTAMP(), FALSE) AS IsBusy
FROM Customers WHERE CustomerId = @cid`,
Params: map[string]interface{}{"cid": customerID},
})
defer iter.Stop()
row, err := iter.Next()
if errors.Is(err, iterator.Done) {
return nil
}
if err != nil {
return err
}
var isBusy bool
if err := row.Columns(&isBusy); err != nil {
return err
}
if isBusy {
time.Sleep(time.Duration(10+rand.Intn(40)) * time.Millisecond)
}
return nil
}
Handle long-running batches and external calls
When a batch of tasks takes longer to process than the initial 10-second
ProcessingExpiresAt window, or calls external APIs (such as payment gateways
or webhooks) that aren't rolled back when a database transaction aborts, you
need to prevent duplicate external actions and premature claim expiration.
To protect external calls and extend entity claims for slow batches:
- Pass a deterministic idempotency key to external APIs: Combine
CustomerId,CreatedAt, andBatchIndex(for example,456:2026-05-01T12:00:00.123456Z:0) and pass that key to downstream services during Step 2. - Extend the entity claim with a periodic heartbeat: Either set a longer
initial
ProcessingExpiresAtand fallbackDeliverTime(such as 30 or 60 seconds) in Transaction 1, or run a background heartbeat transaction before the claim expires (for example, every 3 to 4 seconds on a 10-second claim window). Each heartbeat transaction deletes the current fallback message (ActiveClaimId) withASSERT_ROWS_MODIFIED 1, inserts a new fallback message with a refreshed futureDeliverTime, and updatesActiveClaimIdandProcessingExpiresAtonCustomers. Stop the heartbeat before starting Transaction 2.
Because Step 2 runs outside a database transaction and queue delivery is
at-least-once, a worker crash after an external API call causes the next worker
to retry the task. Passing a deterministic idempotency key lets the external
service ignore duplicate requests. For more information, see
Transactional outbox.
Periodically replacing the fallback message and extending ProcessingExpiresAt
keeps the entity claim active while the worker is healthy, while still allowing
another worker to take over if the worker crashes.
For example, the following heartbeat transaction extends an active entity claim by 10 seconds:
GoogleSQL
-- Run every 3 to 4 seconds on a 10-second claim window before starting Transaction 2:
DELETE FROM CustomerDirtyQueue
WHERE CustomerId = 456 AND MessageId = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
INSERT INTO CustomerDirtyQueue (CustomerId, MessageId, DeliverTime, Payload)
VALUES (456, 'claim-uuid-3', TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND), b'');
UPDATE Customers
SET ActiveClaimId = 'claim-uuid-3',
ProcessingExpiresAt = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND)
WHERE CustomerId = 456 AND ActiveClaimId = 'claim-uuid-2';
PostgreSQL
-- Run every 3 to 4 seconds on a 10-second claim window before starting Transaction 2:
DELETE FROM customerdirtyqueue
WHERE customerid = 456 AND messageid = 'claim-uuid-2'
ASSERT_ROWS_MODIFIED 1;
INSERT INTO customerdirtyqueue (customerid, messageid, deliver_time, payload)
VALUES (456, 'claim-uuid-3', CURRENT_TIMESTAMP + INTERVAL '10 SECOND', CAST('' AS bytea));
UPDATE customers
SET activeclaimid = 'claim-uuid-3',
processingexpiresat = CURRENT_TIMESTAMP + INTERVAL '10 SECOND'
WHERE customerid = 456 AND activeclaimid = 'claim-uuid-2';
Scale high-volume entities with sharding and stale reads
When a single entity receives more writes than one Spanner split
can handle, writing every task into a single Customers split creates a
write hotspot, even if you
still need to process tasks for that entity in commit order.
To scale write throughput for a high-volume entity while preserving global
ordering, distribute writes across N shards and merge them asynchronously with
a periodic sweeper worker using
stale reads:
- Shard the work and deduplication tables: Create a
CustomerShardsparent table withPRIMARY KEY (ShardId, CustomerId)(ShardIdfrom0toN - 1) and interleave a sharded work table keyed by(ShardId, CustomerId, CreatedAt, BatchIndex). MakingShardIdthe leading key column spreads writes acrossNsplits. To prevent duplicate task rows when a retried transaction receives a newPENDING_COMMIT_TIMESTAMP(), also interleave a batch deduplication table keyed by(ShardId, CustomerId, BatchId)with a TTL policy. - Route each producer batch to one shard without queue writes: In each
producer transaction, compute
ShardIdfrom a hash of a uniqueBatchId(such asFARM_FINGERPRINT) and insert into both the deduplication table and the sharded work table without inserting into a queue. HashingBatchIdkeeps each producer transaction within a single split and eliminates per-write queue overhead. - Sweep shards in order using a stale read and a progress cursor: Maintain
an active claim and a
(LastSweptAt, LastBatchIndex, LastShardId)cursor in a separateCustomerSweeperStatetable with an interleaved wake-up queue containing one recurring sweeper message per entity. When triggered, the worker claims the entity (using the same claim and fallback-message pattern as Use an ordered work table and a wake-up queue), queries across allNshards after the cursor using a 10-second exact staleness read ordered byCreatedAt ASC, BatchIndex ASC, ShardId ASC, processes the batch in order, and commits a completion transaction that deletes the processed rows, advances the cursor, and schedules the next sweep. Using a stale read avoids blocking on in-flight producer writes across splits, while the cursor skips rows that were processed and deleted within the staleness window.
What's next
- Learn how to use Spanner queues, including best practices and monitoring.
- Explore more Spanner queues scenarios and examples.
- Learn about exactly-once processing and at-most-once acknowledgment.
- Configure access control with fine-grained access control for queues.