Postgres Changes use cases
Postgres Changes allows listening to database changes in real-time.
Supabase · Realtime · all subjects
realtime/postgres-changes
45 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Postgres Changes authorization overhead at scale
For Postgres Changes, every change event must be checked to verify the subscribed user has access via RLS. This means if 100 users are subscribed to a table and a single insert occurs, it triggers 100 authorization reads. This can create a database bottleneck that limits message throughput and delays changes until timeout occurs.
Postgres Changes single-thread processing
Database changes are processed on a single thread to maintain change order. This means compute upgrades have little effect on Postgres change subscription performance.
Postgres Changes scaling recommendations
To use Postgres Changes at scale, consider using a separate public table without RLS and filters, or use Realtime server-side only and re-stream changes to clients using Realtime Broadcast.
Postgres Changes respects RLS policies for access control
When using Postgres Changes on tables with RLS, database records are sent only to clients who are allowed to read them based on your RLS policies. Both private and public channels can subscribe to Postgres Changes.
Realtime limits by plan - postgres change payload size
Postgres change payload size limits are: Free 1,024 KB, Pro 1,024 KB, Pro (no spend cap) 1,024 KB, Team 1,024 KB, Enterprise 1,024+ KB.
Postgres changes payload limit behavior
When the postgres changes payload limit is reached, the new and old record payloads only include fields with a value size of less than or equal to 64 bytes.
Postgres Changes extension listens for database changes
The Postgres Changes extension listens for database changes and sends them to clients, enabling you to receive database changes in real-time.
Postgres Changes setup with publication
To enable Postgres Changes, create a supabase_realtime publication and add tables to it. Example SQL: begin; drop publication if exists supabase_realtime; create publication supabase_realtime; commit; alter publication supabase_realtime add table messages;
Postgres Changes limitations
Postgres Changes has limitations as applications scale and should not be used for most use cases. Broadcast is recommended instead.
Subscribe to UPDATE events with Postgres Changes
Example code to stream all updated rows using the UPDATE event with Postgres Changes: import { createClient } from '@supabase/supabase-js'; const supabase = createClient('your_project_url', 'your_supabase_api_key'); const channel = supabase.channel('schema-db-changes').on('postgres_changes', { event: 'UPDATE', schema: 'public' }, (payload) => console.log(payload)).subscribe();
Postgres Changes Events report
The Postgres Changes Events report monitors the volume of database change events (INSERT, UPDATE, DELETE) sent to Realtime clients over time. Postgres Changes use logical replication to stream database changes from the Write-Ahead Log (WAL) to subscribed clients. Each event represents a database change broadcast to clients subscribed to the relevant schema and table. Note that Postgres Changes process changes on a single thread to maintain order, which can create bottlenecks at scale compared to Broadcast. Essential for understanding database change processing and identifying performance bottlenecks or scaling issues. Available for all plans.
Postgres Changes payload size limit
Postgres Changes payload size limit is 1,024 KB for all plans.
System event structure
System events contain: `message` (human-readable description), `status` (ok, error, or timeout), `extension` (postgres_changes or system), and `channel` (channel name). When replication_ready is set to true in phx_join, the server sends a system message with extension 'system' once the Postgres replication connection is ready (status 'ok' with message 'Replication connection established', or status 'error' if not established in time, which closes the channel).
Postgres changes subscription configuration
Postgres changes is an array in phx_join config where each subscription object contains: `event` (INSERT|UPDATE|DELETE|* to listen to all events), `schema` (schema name, accepts * wildcard), `table` (table name, accepts * wildcard), optional `filter` (PostgREST-style column=operator.value filter expression with AND combining and not. negation), and optional `select` (array of column names to restrict payload to subset, not supported for wildcard schema/table subscriptions). The `select` parameter reduces payload size and transferred data per event.
Postgres changes server message structure
postgres_changes messages contain: `ids` (array of unique identifiers matching subscription), `data` object with `schema` (table schema), `table` (table name), `commit_timestamp` (ISO format), `type` (INSERT|UPDATE|DELETE|*), `columns` (array of {name, type} objects), `record` (new values), `old_record` (previous values), and `errors` (null or error string). When subscription used `select` array, columns/record/old_record are restricted to selected columns only.
Postgres Changes subscription errors
Postgres changes errors with extension 'postgres_changes' do NOT close the channel. Status 'ok' with message 'Subscribed to PostgreSQL' confirms subscription is live. Error scenarios: Invalid filter operator (no retry, fix params), Missing schema/table (no retry, fix params), Subscription insert failed (retries every 5-10s, surface as degraded), Database error (retries every 5-10s, surface as degraded), 'Too many database timeouts' (no retry, reduce load). Supported filter operators: eq, neq, lt, lte, gt, gte, in, like, ilike, is, match, imatch, isdistinct, can be negated with not. prefix.
phx_reply example with postgres_changes subscription
Example phx_reply response to phx_join in protocol 2.0.0: ['1', '1', 'realtime:chat-room', 'phx_reply', { 'status': 'ok', 'response': { 'postgres_changes': [{ 'id': 106243155, 'event': '*', 'schema': 'public', 'table': 'test' }] } }]
Postgres changes server message example
Example postgres_changes message: [null, null, 'realtime:chat-room', 'postgres_changes', { 'ids': [104868189], 'data': { 'schema': 'public', 'table': 'test', 'commit_timestamp': '2025-11-19T00:22:40.877Z', 'type': 'UPDATE', 'columns': [{ 'name': 'id', 'type': 'int8' }, { 'name': 'created_at', 'type': 'timestamptz' }, { 'name': 'text', 'type': 'text' }], 'record': { 'id': 46, 'text': 'content', 'created_at': '2025-11-03T09:32:55+00:00' }, 'old_record': { 'id': 46 }, 'errors': null } }]
System message for replication readiness
When channel joined with config.broadcast.replication_ready set to true, server sends system message: [join_ref, null, 'realtime:chat-room', 'system', { 'message': 'Replication connection established', 'status': 'ok', 'extension': 'system', 'channel': 'main' }] on success. On failure (connection not established in time), status is 'error' and channel is closed.
Listen to changes by schema
Use the schema parameter to subscribe to all changes in a specific schema. Example: .on('postgres_changes', {event: '*', schema: 'public'}, callback).subscribe() listens to all events in the public schema.
Listen to changes on specific table
Use the table parameter in addition to schema to filter postgres_changes to a specific table. Example: .on('postgres_changes', {event: '*', schema: 'public', table: 'todos'}, callback).subscribe()
Multiple postgres_changes handlers on single channel
Multiple .on('postgres_changes', {...}, callback) handlers can be chained on the same channel to listen to different event and schema/table/filter combinations. Example: channel.on('postgres_changes', {event: '*', schema: 'public', table: 'messages'}, callback1).on('postgres_changes', {event: 'INSERT', schema: 'public', table: 'users'}, callback2).subscribe()
Subscribe to INSERT, UPDATE, and DELETE events on specific table
To subscribe to INSERT, UPDATE, and DELETE events on a specific table, use the postgres_changes event type with the event parameter set to the desired event type (INSERT, UPDATE, DELETE, or * for all), and specify the schema and table parameters. Example in JavaScript: supabase.channel('table-db-changes').on('postgres_changes', {event: 'INSERT', schema: 'public', table: 'todos'}, (payload) => console.log(payload)).subscribe()
Enable postgres replication in Publications settings
To listen to database changes, tables must be added to the supabase_realtime publication. This can be done via the Publications settings in the Supabase dashboard under supabase_realtime, or by running: alter publication supabase_realtime add table your_table_name;
Channel name restrictions
The channel name can be any string except 'realtime'. This restriction applies to all postgres_changes subscriptions.
Postgres changes event types
The event parameter in postgres_changes subscriptions can be set to: INSERT (for new rows), UPDATE (for modified rows), DELETE (for removed rows), or * (for all changes).
Postgres changes filter syntax
Filters use the format column=operator.value (e.g., id=eq.1 or title=like.%foo%). Filters are evaluated on the server, so filtered-out events never leave the database. Use the postgresChangesFilter() helper in JavaScript for type-safe filter building.
Equal to (eq) filter operator
The eq filter matches when a column equals the specified value. Uses Postgres's = operator. Example: id=eq.1
Not equal to (neq) filter operator
The neq filter matches when a column does not equal the specified value. Uses Postgres's != operator. Example: status=neq.done
Less than (lt) and less than or equal (lte) filter operators
The lt filter uses Postgres's < operator for columns less than a value. The lte filter uses <= for columns less than or equal to a value. Both work for non-numeric types. Examples: age=lt.65 and age=lte.65
Greater than (gt) and greater than or equal (gte) filter operators
The gt filter uses Postgres's > operator for columns greater than a value. The gte filter uses >= for columns greater than or equal to a value. Both work for non-numeric types. Examples: quantity=gt.10 and quantity=gte.10
In (in) filter operator - maximum 100 values
The in filter matches when a column equals any value in a specified list. Uses Postgres's = ANY operator. Realtime allows a maximum of 100 values for this filter. Example: name=in.(red,blue,yellow)
Pattern matching filters (like, ilike)
The like filter is case-sensitive pattern matching using Postgres's LIKE operator. The ilike filter is case-insensitive using ILIKE. Use % to match any sequence of characters and _ to match a single character. Both require text-compatible columns. Examples: title=like.%foo% and title=ilike.%breaking%
Regular expression filters (match, imatch)
The match filter is case-sensitive POSIX regex matching using Postgres's ~ operator. The imatch filter is case-insensitive using ~*. Both require text-compatible columns and the pattern is validated when subscribing. Examples: slug=match.^post- and slug=imatch.pattern
NULL and boolean checks filter (is)
The is filter checks if a column IS null, true, false, or unknown. is.null works on any column type. is.true, is.false, and is.unknown require a boolean column. Uses Postgres's IS operator. Examples: deleted_at=is.null and active=is.true
Distinct from filter (isdistinct)
The isdistinct filter is a NULL-safe inequality using Postgres's IS DISTINCT FROM operator. Unlike neq, it treats null as a comparable value, so a null column is considered distinct from a non-null value. Example: state=isdistinct.active
Negate filters with not operator
Any filter operator can be negated by prefixing with not. (e.g., not.eq, not.like). This allows you to invert filter logic.
Combine multiple filters with AND
Multiple filter conditions can be combined using commas, which are applied as AND logic. Example: quantity=gte.10,status=eq.open
JavaScript postgresChangesFilter() helper
The @supabase/supabase-js library provides a postgresChangesFilter() helper that builds type-safe filters, handling operator names, negation, AND composition, and escaping automatically. Example: postgresChangesFilter().gte('quantity', 10).eq('status', 'open') generates 'quantity=gte.10,status=eq.open'
Install Supabase JavaScript client
To use Realtime with JavaScript, install the client library with: npm install @supabase/supabase-js
Create Supabase client for Realtime
Create a client with: const supabase = createClient('https://<project>.supabase.co', '<sb_publishable_... key>'). This client is used to listen to Postgres changes.
Postgres Changes authorization and throughput scaling
Postgres Changes authorizes every event against each subscriber. When you make a single change to a table with 100 subscribed users, Realtime performs 100 authorization checks — one per user — so throughput scales with the number of subscribers, not the write rate. Changes are also processed on a single thread to preserve their order, which means larger compute add-ons do not meaningfully increase Postgres Changes throughput.
Best practices for Postgres Changes performance
To get the best performance with Postgres Changes: (1) Use filters and column selection to send each client only the events and columns it needs. (2) Keep authorization cheap by writing indexed RLS policies.
Use Broadcast instead of Postgres Changes for high subscriber counts
If you expect more than approximately 3,000 concurrent subscribers on the same changes, use Broadcast to stream database changes instead. Broadcast sends each change once and fans it out to all subscribers, so it scales to far higher connection counts than per-subscriber authorization allows.