Hi,
I'm working on a small Django/Celery CRM backed by PostgreSQL. It
receives lead registrations and dispatches conversion events to
several external APIs.
At the moment, each lead/project row keeps small idempotency ledgers
in JSON fields, approximately like this:
lead_project
------------
id
capi_events_sent jsonb
tiktok_events_sent jsonb
google_events_sent jsonb
A Celery task currently does roughly this:
lp = SELECT lead_project WHERE id = ...
if event_name in lp.capi_events_sent:
return
result = send_event_to_external_api(...)
if result is successful:
lp.capi_events_sent[event_name] = current_timestamp
UPDATE lead_project ...
I realized there are at least two concurrency problems with this design.
First, if two workers receive the same task at nearly the same time,
both can read the row before either one updates it. Both see that the
event is absent and both can perform the external side effect.
Second, two workers processing different events can both read the same
JSON value, modify their local copy, and then write it back,
potentially causing a lost update.
The external HTTP call makes this slightly more complicated because I
obviously cannot make the PostgreSQL transaction and the remote API
call atomic.
I'm considering replacing the JSON ledgers with a separate table such as:
event_delivery
--------------
id
lead_project_id
provider
event_name
state
created_at
sent_at
with:
UNIQUE (lead_project_id, provider, event_name)
A worker could first reserve an event using INSERT ... ON CONFLICT DO
NOTHING, then perform the HTTP request and finally mark the row as
sent.
However, there is still the failure case where the remote API accepts
the event, but the worker crashes before PostgreSQL records the
successful state.
For this type of workload, what PostgreSQL pattern would you normally recommend?
Would you prefer:
a unique event-delivery row plus state transitions,
SELECT ... FOR UPDATE,
advisory locks,
an outbox/queue table processed with SKIP LOCKED,
or some other approach?
My main goal is to avoid duplicate external events while also making
retries after worker crashes reasonably safe.
Thanks,
Long
https://nguyenthuland.vn/
