Back to Articles

Building Real-Time Apps Over Postgres: Why Many Developers Struggle (and How to Do It Right)

• Vela Team • 9 min read RealtimePostgresSupabaseCDCWebSocketsVela

Real‑time features over PostgreSQL are deceptively hard. Developers start with triggers and NOTIFY, then hit limits around delivery guarantees, fan‑out, ordering, and permissioning. Managed offerings can work well for simple broadcast, but once you need durability, replay, and precise access rules, complexity spikes. This guide explains the trade‑offs and how to build it right — and how Vela simplifies the stack.

TL;DR

  • LISTEN/NOTIFY is great for lightweight signals, not a durable event bus.
  • Polling is simple but wasteful; it misses sub‑second latency targets at scale.
  • Logical decoding/CDC offers durability and replay, but you still need fan‑out, backpressure, and auth.
  • Doing it right means: an outbox pattern, idempotency, ordered streams, and channel‑level authorization.
  • Vela provides a unified real‑time layer on Postgres with durable channels, replay, and RBAC integrated.

Common Approaches & Trade‑offs

  • Polling: trivial to implement; high load, poor latency, risk of duplicate reads.
  • LISTEN/NOTIFY: low overhead signals; no persistence, payload size limits, can drop messages during restarts.
  • Triggers + NOTIFY: convenient but ties business logic to the DB, hard to test, no delivery guarantees.
  • Logical decoding/CDC: durable and replayable; requires managing replication slots, fan‑out workers, and consumer offsets.
  • External brokers (Redis/Kafka): powerful but adds another system to operate, plus data duplication and consistency concerns.

Why Many Implementations Break Down

  • Delivery semantics: lack of idempotency leads to duplicates on reconnect or failover.
  • Ordering: cross‑table events arrive out of order; clients need sequence numbers or cursors.
  • Fan‑out: N clients → N DB connections; backpressure is rarely handled.
  • Authorization: mapping RLS/roles to channels and presence is non‑trivial.
  • Recovery: after deploys or outages you need replay/catch‑up without data loss.

How to Do It Right

  • Outbox pattern: write business events to an events table in the same transaction as state changes.
  • CDC stream: consume via logical decoding, persist offsets, and publish to channels.
  • Idempotency keys: include monotonic IDs; clients de‑duplicate on receipt.
  • Channel authorization: derive channel access from DB roles/RLS; enforce on server side.
  • Replay & catch‑up: allow clients to resume from last acknowledged cursor.

Where Supabase Realtime Fits

Supabase Realtime is excellent for many apps, especially broadcast and simple row‑level subscriptions. Pain points arise with strict delivery guarantees, large fan‑out, enterprise auth requirements, or when you need durable replay. Teams often bridge gaps with additional queues or custom gateway services.

How Vela Helps

  • Durable channels on Postgres: CDC‑backed event streams with replay and ordered cursors.
  • Integrated RBAC: organization/project roles map to channel permissions; works with RLS.
  • Efficient fan‑out: WebSocket gateways handle backpressure and batching; no N+1 DB connections.
  • Idempotent delivery: message IDs and client cursors prevent duplicates after reconnects.
  • Observability: per‑channel metrics, slow subscriber detection, and dead‑letter queues.

What to Do Next

Explore patterns and try it yourself: