“At Most Once” vs. “At Least Once”

Well as noted in the post, you can’t do anything exactly once. You can run something at most once or you can run something at least once, and which of those you want depends on the situation.

The simplest extension of this to achieve at most once would be to do something non-repeatable to indicate that the job has started (like set a value in a row in your database, within a transaction) before you do the step that you don’t want to repeat (e.g. sending an email).

Delayed Jobs

SQS lets you enqueue messages with a delay up to 15 minutes. That would be a good way to do a thing slightly farther into the near future.

In the event that you want to do a thing farther into the future than 15 minutes from now, then the simplest way would be to think about it slightly differently. Instead of saying “do this at a specific time” write a job that asks the question, “is there any work to be done right now?” Then schedule that job to run periodically and arrange for that answer to be yes at the appointed time.

An example

Sticking to the sending-an-email example: maybe we want to send a welcome email after six hours. And we’re ok with occasionally not sending it, but we want to avoid ever sending it twice.

We can have a users table with fields:

create table user (
  user_id bigserial not null primary key,
  email text not null,
  created timestamp not null default current_timestamp,
  sent_welcome boolean not null default false
);

So then the job to send the mails has to do this to fetch a batch of work:

select email 
from users 
where created < current_timestamp - '6 hours'::interval
  and not sent_welcome
limit 100;

Then before sending an email, to achieve at most once delivery you’d want to commit a query like this:

update users 
set sent_welcome = true
where user_id = :user-id and not sent_welcome
returning user_id;

That tries to flip the sent_welcome bit on one row to true, as long as it isn’t already true.

So we’d write the job to bail out if that query doesn’t return the user_id back to us, as that would imply that another copy of the job has already sent the mail.

Hope that helps.