Why should you care?

PostgresVerse order service publishes OrderPlaced without dual-write to Kafka and DB getting out of sync.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE outbox_events (
  event_id bigserial PRIMARY KEY,
  aggregate_type text NOT NULL,
  aggregate_id text NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz DEFAULT now(),
  published_at timestamptz
);

INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', '9001', 'OrderPlaced', '{"total":1299}'::jsonb);

What happened?

• Same transaction as order insert writes outbox row.
• Relay process polls WHERE published_at IS NULL, publishes to queue, marks published.
• Crash-safe pattern.

Practice next

1. Create outbox_events.
2. Wrap order INSERT + outbox INSERT in BEGIN...COMMIT.
3. Poll unpublished events SELECT ... FOR UPDATE SKIP LOCKED.
4. Add partial index WHERE published_at IS NULL.
5. Partition outbox by created_at monthly.

Remember

Transactional outbox avoids dual-write bug. FOR UPDATE SKIP LOCKED for worker pools. Logical decoding alternative for CDC.

PostgresVerse order events

Warehouse service consumes OrderPlaced from outbox relay; inventory updates async.

Outcome: Order API stays fast; downstream scales independently.