Transactions across databases

conceptThe concept

Two-phase commit: a coordinator runs PREPARE TRANSACTION 'id' on every participant, which durably saves the transaction without committing it, then COMMIT PREPARED 'id' on all of them. If the coordinator dies between the phases, prepared transactions stay open, holding locks and blocking VACUUM until someone resolves them. PostgreSQL disables the feature by default (max_prepared_transactions = 0).

Transactional outbox: write the business change and an event row into an outbox table in the same local transaction. A relay process reads unpublished events and publishes them to a queue or another service. The event is published if and only if the change committed.

Idempotency: every message may be delivered more than once, so consumers record processed message ids (an inbox) or use idempotency keys with a unique constraint, making a repeat a no-op.

Sagas: a sequence of local transactions, each with a compensating action (refund the payment, release the stock) that runs if a later step fails.

whyWhy it matters

Dual writes ("commit to the database, then publish to Kafka") lose events whenever the process crashes in between. The outbox and idempotency patterns make each step locally atomic and safely repeatable, which is the practical foundation of consistent microservices.

Use caseReal-world scenario

Checkout writes the order and an OrderPaid outbox event in one transaction. The relay publishes it; the loyalty service consumes it, records the event id in its inbox and adds points in the same transaction. A redelivered event finds its id already in the inbox and does nothing.

Try itExamples

Run and edit these in the interactive lesson.

SHOW max_prepared_transactions;
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 7;
PREPARE TRANSACTION 'order-981-inventory';

-- postgresql.conf on every participant
max_prepared_transactions = 100

-- phase 1, on each participant
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 7;
PREPARE TRANSACTION 'order-981-inventory';

-- phase 2, once every participant prepared successfully
COMMIT PREPARED 'order-981-inventory';
-- or, if any participant failed
ROLLBACK PREPARED 'order-981-inventory';

-- the check you must monitor
SELECT gid, prepared, owner FROM pg_prepared_xacts WHERE prepared < now() - interval '5 minutes';

BEGIN;
CREATE TABLE outbox (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  aggregate     text NOT NULL,
  aggregate_id  bigint NOT NULL,
  event_type    text NOT NULL,
  payload       jsonb NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  published_at  timestamptz
);

WITH o AS (
  INSERT INTO orders (customer_id, status, created_at, total)
  VALUES (42, 'paid', now(), 59.90)
  RETURNING id, customer_id, total
)
INSERT INTO outbox (aggregate, aggregate_id, event_type, payload)
SELECT 'order', id, 'OrderPaid', jsonb_build_object('order_id', id, 'customer_id', customer_id, 'total', total)
FROM o;

-- relay: claim a batch without blocking other relay workers
WITH batch AS (
  SELECT id FROM outbox
  WHERE published_at IS NULL
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE outbox SET published_at = now()
FROM batch WHERE outbox.id = batch.id
RETURNING outbox.event_type, outbox.payload ->> 'customer_id' AS customer;
ROLLBACK;

BEGIN;
CREATE TABLE payments (
  idempotency_key uuid PRIMARY KEY,
  order_id        integer NOT NULL REFERENCES orders (id),
  amount          numeric(12,2) NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);
INSERT INTO payments (idempotency_key, order_id, amount)
VALUES ('6f1c2a7e-6c1b-4d47-9a43-1d3c4e5f6a7b', 1, 25.00)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING 'charged' AS result;
INSERT INTO payments (idempotency_key, order_id, amount)
VALUES ('6f1c2a7e-6c1b-4d47-9a43-1d3c4e5f6a7b', 1, 25.00)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING 'charged' AS result;
SELECT count(*) AS payments_recorded FROM payments;
ROLLBACK;

internalsUnder the hood

A prepared transaction keeps its XID, locks and snapshot horizon until resolved, survives restarts and appears in pg_prepared_xacts. An orphaned one silently holds back the xmin horizon and can lead to bloat and, in the extreme, wraparound protection. Monitoring pg_prepared_xacts is mandatory wherever 2PC is enabled.

Avoid / preferDo this, not that

-- application code
-- db.commit(order)
-- kafka.publish(OrderPaid)   -- crash here and the event is lost

-- one local transaction
-- INSERT INTO orders ...;
-- INSERT INTO outbox ...;
-- COMMIT;  -- a relay publishes from outbox

-- enable 2PC everywhere and never look at it again
max_prepared_transactions = 1000

-- enable only where needed, with an external transaction manager,
-- and alert on pg_prepared_xacts older than a few minutes

PitfallsCommon mistakes

• Assuming exactly-once delivery from a message broker; consumers must be idempotent.
• Relaying outbox events without SKIP LOCKED, so parallel relay workers block each other.
• Writing sagas without compensating actions for every step that can be followed by a failure.

LimitsWhen not to use it

Avoid 2PC across services owned by different teams or across the internet: availability drops to that of the weakest participant and failures need manual cleanup. Avoid sagas when all the data can live in one database; a local transaction is simpler and stronger.

SecuritySecurity notes

Outbox payloads often end up in queues and logs with weaker access control than the database. Put identifiers in events rather than personal data, and let consumers fetch details they are allowed to see.

ChallengeTry it yourself

BEGIN;
CREATE TABLE inbox (event_id bigint PRIMARY KEY, received_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE loyalty_points (customer_id integer PRIMARY KEY, points integer NOT NULL);

WITH claim AS (
  INSERT INTO inbox (event_id) VALUES (1001) ON CONFLICT DO NOTHING RETURNING event_id
)
INSERT INTO loyalty_points SELECT 42, 60 FROM claim
ON CONFLICT (customer_id) DO UPDATE SET points = loyalty_points.points + EXCLUDED.points;

WITH claim AS (
  INSERT INTO inbox (event_id) VALUES (1001) ON CONFLICT DO NOTHING RETURNING event_id
)
INSERT INTO loyalty_points SELECT 42, 60 FROM claim
ON CONFLICT (customer_id) DO UPDATE SET points = loyalty_points.points + EXCLUDED.points;

SELECT points FROM loyalty_points WHERE customer_id = 42;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• postgres_fdw
• PREPARE TRANSACTION
• Citus documentation