When you call supabase.channel('room-1').on('postgres_changes', ...) in your client application, it feels simple. A row updates in your database, and your frontend state updates a few milliseconds later.
Most developers assume Supabase runs a basic WebSocket server that connects to PostgreSQL, sets up an event listener, and forwards JSON payloads. Some assume it relies on PostgreSQL's built-in LISTEN and NOTIFY commands.
Neither assumption is correct.
Underneath that client method is an infrastructure pipeline involving PostgreSQL Write-Ahead Logs (WAL), binary logical decoding plugins, an Elixir clustering engine, and an in-database row evaluation system called WALRUS.
If you run this in production without understanding how the pipeline works, you will eventually run into three classic issues: unexpected WAL disk accumulation that forces your database into read-only mode, write amplification that turns tiny column updates into massive disk I/O, and Row Level Security (RLS) query storms that saturate CPU cores.
Here is what actually happens under the hood.
Why Postgres NOTIFY Was Rejected
PostgreSQL already has a native pub/sub mechanism: LISTEN and NOTIFY. Many early real-time database tools used it. You add a trigger to a table, and whenever a row changes, the trigger executes pg_notify('channel', payload).
Supabase avoided building their real-time engine on NOTIFY for three critical reasons:
- The 8,000-byte hard limit: PostgreSQL enforces a strict 8,000-byte payload limit on
NOTIFY. If an updated row exceeds this limit, the trigger fails or truncates the notification. The classic workaround is to notify only the row ID and make the client query the database for the new row, but that turns one database write into hundreds of subsequent read queries under heavy subscriber load. - Trigger sprawl: Setting up
NOTIFYrequires authoring, managing, and migrating custom PL/pgSQL triggers across every single table you want to monitor. - Connection saturation:
LISTENrequires persistent, dedicated database connections. If you have 5,000 clients listening for updates, managing those connections directly against Postgres quickly exhausts connection pools.
Instead of intercepting writes inside the SQL engine via triggers, Supabase stepped down into the database storage engine.
The Core Pipeline: Logical Replication and WAL
Every relational database needs a mechanism to survive sudden crashes without losing committed transactions. In PostgreSQL, that mechanism is the Write-Ahead Log (WAL).
Before PostgreSQL writes changes to table data files on disk, it appends a binary record of the change to an append-only log file in pg_wal. The WAL contains an exact byte-level history of database mutations.
Client Write (SQL) │ ▼ [ PostgreSQL Engine ] ──(Append)──► [ Write-Ahead Log (WAL) ] │ ▼ (Logical Decoding) [ Replication Slot: pgoutput ] │ ▼ (Binary Stream) [ Supabase Realtime (Elixir) ] │ ▼ (WebSockets) [ Connected Clients ] Logical Decoding
Physical replication streams raw disk block alterations to standby replicas. Logical replication, introduced in PostgreSQL 10, works differently: it decodes those raw binary log records into higher-level logical events (INSERT, UPDATE, DELETE) using an output plugin such as pgoutput or wal2json.
To keep track of what has been read, PostgreSQL uses a Replication Slot.
A replication slot tracks a Log Sequence Number (LSN), which acts as a bookmark pointing to the consumer's current position in the WAL. As long as the slot exists, Postgres guarantees that no WAL file containing unread LSNs will be recycled or deleted from disk.
Supabase Realtime connects to Postgres as a replication consumer. It opens a logical replication slot on your publication and receives a continuous stream of decoded row mutations.
The Bridge: Supabase Realtime and the BEAM
The component that bridges Postgres replication to client WebSockets is Supabase Realtime, an open-source server written in Elixir on top of the Phoenix Framework.
The choice of Elixir is deliberate. Elixir runs on the Erlang BEAM virtual machine, which uses the actor model. In Realtime, every single connected WebSocket client is represented by an isolated, lightweight Erlang process that requires only a few kilobytes of memory.
This solves the connection scaling problem:
- The PostgreSQL database maintains only one or two long-lived replication connections to the Realtime cluster.
- The Realtime cluster distributes incoming database mutations across thousands of Erlang processes, routing messages to connected browsers over WebSockets.
If one client connection hangs, experiences network latency, or crashes, its Erlang process terminates without impacting other subscribers or the database replication connection.
The RLS Dilemma and WALRUS
Streaming raw database changes over WebSockets is straightforward if all data is public. But modern applications rely on Row Level Security (RLS) to restrict data access based on user roles and JWT claims.
Here is the dilemma: PostgreSQL Write-Ahead Logs have zero awareness of application authorization.
The WAL records that row id: 104 in table orders was updated. It does not know that User A is allowed to view that order, while User B is not.
If Realtime simply broadcasted decoded changes to everyone listening to orders, it would leak private data to unauthorized clients.
To enforce security, Supabase had to evaluate RLS policies against every single change for every single connected subscriber.
Doing this in the external Elixir service would require replicating PostgreSQL's entire query and policy evaluation engine in Elixir, which is practically impossible. Polling the database over standard SQL connections for every subscriber would introduce massive network latency.
Supabase solved this by moving the authorization check directly into the database engine through WALRUS (Write Ahead Log Realtime Unified Security).
[ Decoded Change ] ──► [ Realtime Server ] │ ▼ Invokes SQL Function [ WALRUS in PostgreSQL ] │ ┌─────────────────────────┴─────────────────────────┐ ▼ ▼ Read realtime.subscription Loop Through Subscribers: (Finds users listening to table) - SET LOCAL ROLE authenticated - SET LOCAL "request.jwt.claims" - SELECT EXISTS (SELECT 1 WHERE id = :id) │ ┌───────────────────────────────────────────────────┘ ▼ Return authorized subscriber IDs ──► Realtime filters & broadcasts over WebSockets How WALRUS Evaluates Visibility
- Realtime maintains a dedicated
realtime.subscriptiontable inside Postgres that tracks active WebSocket connections, their target tables, and their decoded JWT claims. - When Realtime receives a WAL mutation, it passes the change payload to the WALRUS SQL function in Postgres.
- WALRUS queries the subscription table to identify all connected users listening to that table.
- For each subscriber, WALRUS impersonates that specific user by setting transaction-local parameters:
SET LOCAL ROLE authenticated; SET LOCAL "request.jwt.claims" = '{"sub": "user_uuid_here", "role": "authenticated"}'; - It runs a lightweight probe query against the modified table:
SELECT EXISTS (SELECT 1 FROM orders WHERE id = $1); - Because the probe query executes within the impersonated role's security context, PostgreSQL evaluates the table's RLS policies. If the query returns
true, the subscriber is authorized to view the row. Iffalse, the row is hidden. - To minimize overhead, WALRUS wraps this check in a prepared statement. This eliminates query planning overhead on subsequent subscriber checks, cutting execution time significantly.
- WALRUS returns the list of authorized subscriber IDs back to Realtime, which delivers the payload exclusively to those clients.
The Performance Cost of Subscriber Checks
While prepared statements make each check fast, processing time still grows with the number of subscribers listening to an active table:
| Active Subscribers on Table | Average Processing Latency per Change |
|---|---|
| 1 subscriber | ~11.2 ms |
| 10 subscribers | ~14.2 ms |
| 100 subscribers | ~24.5 ms |
| 1,000 subscribers | ~64.7 ms |
| 10,000 subscribers | ~303.8 ms |
If your table has complex RLS policies involving multi-table joins or subqueries, every single row mutation triggers those joins for every active subscriber. A burst of database writes on a table with many listeners can quickly spike database CPU utilization.
The REPLICA IDENTITY FULL Trap
When an application receives an UPDATE event, the client often needs to know what the row looked like before the update.
In standard PostgreSQL configuration, updates in the WAL only log the new row values along with the primary key. If you inspect payload.old on the client, you will find it is empty or contains only the ID.
To receive the previous row state, developers run:
ALTER TABLE orders REPLICA IDENTITY FULL; This tells PostgreSQL to write the entire previous row into the WAL whenever an update occurs.
The Write Amplification Problem
Setting REPLICA IDENTITY FULL fixes payload.old, but it introduces significant overhead on write-heavy tables:
- Double WAL size: If you update a single column in a 30-column table with text and JSON fields, PostgreSQL must write the entire old row and the entire new row into the log.
- Index lookups on replicas: When applying updates on replica systems, full replica identity can force full table scans if unique identifiers are not indexed properly.
- Checkpoint pressure: Larger WAL payloads cause PostgreSQL to reach its checkpoint threshold (
max_wal_size) faster, increasing disk I/O and flush frequency.
Unless your frontend logic strictly requires the complete pre-update state of every column, avoid blanket REPLICA IDENTITY FULL declarations on high-throughput tables.
The Ghost Replication Slot
The most dangerous operational failure mode in logical replication is the abandoned or lagging replication slot.
A replication slot guarantees that PostgreSQL will retain all WAL segments needed by the consumer. If Realtime disconnects, crashes, or falls behind during high write activity, PostgreSQL freezes its WAL cleanup process at the slot's restart_lsn.
Disk Storage Timeline: Hour 1: [WAL: 1GB] ── Slot LSN active, older logs recycled cleanly Hour 2: [WAL: 8GB] ── Realtime disconnects, LSN stops advancing Hour 3: [WAL: 32GB] ── Postgres retains all segments since last confirmed LSN Hour 4: [WAL: 100%] ── Disk fills completely -> PostgreSQL PANIC shutdown If the replication consumer stops advancing its LSN, 16MB WAL segment files accumulate in pg_wal without being recycled. Eventually, the disk fills to 100%.
When PostgreSQL runs out of disk space, it halts immediately with a PANIC error and enters recovery mode to protect transaction integrity. Your entire database goes offline.
The Protection Mechanism
To prevent an unresponsive replication slot from taking down the entire database, configure max_slot_wal_keep_size:
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB'; SELECT pg_reload_conf(); When set, PostgreSQL allows replication slots to retain WAL up to the specified limit (e.g. 10 GB). If Realtime falls behind past that limit, PostgreSQL invalidates the slot and resumes recycling WAL segments, saving your disk and keeping your primary database online.
Architecture Comparison: NOTIFY vs Logical Replication
| Feature | PostgreSQL LISTEN / NOTIFY | Supabase Realtime (Logical Replication) |
|---|---|---|
| Data Source | SQL Triggers calling pg_notify | Write-Ahead Log via pgoutput / wal2json |
| Payload Limit | 8,000 bytes hard ceiling | Entire row data (limited only by WAL record size) |
| Database Connections | 1 dedicated connection per listener | 1-2 connections total for the entire cluster |
| RLS Enforcement | Manual filtering inside trigger functions | Automated via WALRUS in-database evaluation |
| Disk Risk | Zero WAL accumulation risk | Requires max_slot_wal_keep_size monitoring |
| Write Overhead | Synchronous trigger execution on write | Asynchronous log decoding decoupled from write path |
Practical Rules for Production Realtime
- Keep RLS policies simple on listened tables: Because WALRUS runs your RLS policy for every active subscriber on every write, avoid joins to unrelated tables in your policies. If a policy requires checking permissions across three tables, consider storing a denormalized authorization flag directly on the listened table.
- Index foreign keys and user IDs: If your RLS policy evaluates
auth.uid() = user_id, ensureuser_idhas an index. Unindexed policy checks turn WALRUS evaluations into sequential table scans. - Set
max_slot_wal_keep_size: Never run production Postgres with unconstrained replication slots. Set a reasonable upper bound so a disconnected real-time consumer cannot exhaust disk storage. - Broadcast only what you need: If clients only need notifications of specific events rather than full database row synchronization, consider using Supabase Broadcast functions (
realtime.broadcast_changes()) or client-to-client broadcast rather than subscribing to full table mutations. - Monitor replication slot lag: Set up alerts on
pg_replication_slotsmonitoringpg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)to catch lagging consumers before they accumulate gigabytes of WAL.


