Runbook

Health at a glance

-- Current backlog and dead letters
SELECT * FROM synctable.v_health;

-- Drift: 0 = consistent
SELECT * FROM synctable.reconcile_all();

-- Per-view perf (last hour)
SELECT * FROM synctable.v_perf ORDER BY max_ms DESC;

Remediation

Drift detected

Run SELECT synctable.reconcile_diff('view_id') to see which rows differ. If non-zero, rebuild:

SELECT synctable.bootstrap('view_id');
-- then verify:  SELECT synctable.reconcile_diff('view_id');  -- 0

Dead-lettered keys

-- Inspect the dead letter
SELECT * FROM synctable.dead_letter WHERE view_id = 'view_id';

-- After fixing the issue, replay:
SELECT synctable.replay_dead_letter(id);
SELECT synctable.process_queue();

Backlog / stalled worker

SELECT * FROM synctable.v_backlog;

-- Reset stale claims (claimed_by not null, no progress)
UPDATE synctable.refresh_queue SET claimed_by = NULL, next_attempt_at = clock_timestamp()
WHERE claimed_by IS NOT NULL AND next_attempt_at < now() - interval '5 minutes';

-- Manual drain
SELECT synctable.process_queue(1000);

Aurora failover

1. Recreate the logical replication slot: SELECT pg_create_logical_replication_slot('synctable_slot', 'pgoutput');
2. Outbox cursor drainer survives failover (cursor state is in subscriber_cursor).
3. Verify: SELECT synctable.reconcile_all();

Outbox growth

-- Check subscriber cursors (lowest = bottleneck)
SELECT * FROM synctable.subscriber_cursor ORDER BY last_seq;

-- Force prune acknowledged rows
SELECT synctable.prune_outbox();