postgres changes subscriptions

34 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Subscription IDs are Erlang processes representing sockets

Subscription IDs are Erlang processes representing underlying sockets on the cluster. These IDs are globally unique and messages to processes are routed automatically by the Erlang virtual machine. After receiving results from the polling query with subscription IDs appended, Realtime delivers records to those clients.

Postgres logical replication slot used for streaming changes

A Postgres logical replication slot is acquired when connecting to your database. Realtime delivers changes by polling the replication slot and appending channel subscription IDs to each WAL record.

Realtime connects to the closest database region

Realtime allows you to listen to changes from your Postgres database. When a new client connects to Realtime and initializes the postgres_changes Realtime Extension, the cluster will connect to your Postgres database and start streaming changes from a replication slot. Realtime knows the region your database is in and connects to it from the closest region possible.

realtime.broadcast_changes function

The realtime.broadcast_changes function uses realtime.send to broadcast changes with a format that is compatible with Postgres Changes.

When to use Postgres Changes

Use Postgres Changes for quick testing and development with a low amount of connected users. Consider migrating to Broadcast for production.

How to subscribe to real-time changes on a table

There are two methods to subscribe to real-time changes on a table in Supabase: (1) Broadcast method - attach a Postgres trigger to the table that calls realtime.broadcast_changes(), set up RLS policies for broadcast authorization, then on the client call supabase.realtime.setAuth() followed by supabase.channel(topic, { config: { private: true } }).on('broadcast', ...).subscribe(); (2) Postgres Changes method - create a supabase_realtime publication and add the table to it using ALTER PUBLICATION, then on the client call supabase.channel(name).on('postgres_changes', { event: 'INSERT'|'UPDATE'|'DELETE', schema: 'public' }, callback).subscribe();

Negate filters with not prefix

Prefix any operator with 'not.' to invert it — for example 'not.in', 'not.is', or 'not.like'. In Dart, set negate: true on PostgresChangeFilter. In Swift, wrap the filter in .not(...). In JavaScript filter strings, use 'status=not.in.(draft,archived)'.

Negate filter example (JavaScript string)

const channel = supabase.channel('changes').on('postgres_changes', {event: '*', schema: 'public', table: 'posts', filter: 'status=not.in.(draft,archived)'}, (payload) => console.log(payload)).subscribe()

Negate filter example (Dart)

supabase.channel('changes').onPostgresChanges(event: PostgresChangeEvent.all, schema: 'public', table: 'posts', filter: PostgresChangeFilter(type: PostgresChangeFilterType.inFilter, column: 'status', value: ['draft', 'archived'], negate: true), callback: (payload) => print(payload)).subscribe()

Negate filter example (Swift)

let myChannel = await supabase.channel('db-changes'); let changes = await myChannel.postgresChange(AnyAction.self, schema: 'public', table: 'posts', filter: .not(.in('status', values: ['draft', 'archived']))); await myChannel.subscribe()

Negate filter example (Kotlin)

val myChannel = supabase.channel('db-changes'); val changes = myChannel.postgresChangeFlow<PostgresAction>(schema = 'public') { table = 'posts'; filter = 'status=not.in.(draft,archived)' }

Negate filter example (Python)

changes = supabase.channel('db-changes').on_postgres_changes('*', schema='public', table='posts', filter='status=not.in.(draft,archived)', callback=lambda payload: print(payload)).subscribe()

Negate filter example (C#)

var channel = supabase.Realtime.Channel('changes'); channel.Register(new PostgresChangesOptions('public', 'posts', ListenType.All, 'status=not.in.(draft,archived)')); channel.AddPostgresChangeHandler(ListenType.All, (sender, change) => { Console.WriteLine(change.Payload); }); await channel.Subscribe()

Combine multiple filters with AND

Combine multiple filter conditions by separating them with commas. All conditions must match (logical AND). OR is not supported. In JavaScript, use the builder like .gt('amount', 100).eq('status', 'open') or the string 'amount=gt.100,status=eq.open'. In Dart, pass a list of filters to the filters parameter. In Swift, use .and([...]).

Combine filters example (JavaScript string)

const channel = supabase.channel('changes').on('postgres_changes', {event: 'INSERT', schema: 'public', table: 'orders', filter: 'amount=gt.100,status=eq.open'}, (payload) => console.log(payload)).subscribe()

Combine filters example (Dart)

supabase.channel('changes').onPostgresChanges(event: PostgresChangeEvent.insert, schema: 'public', table: 'orders', filters: [PostgresChangeFilter(type: PostgresChangeFilterType.gt, column: 'amount', value: 100), PostgresChangeFilter(type: PostgresChangeFilterType.eq, column: 'status', value: 'open')], callback: (payload) => print(payload)).subscribe()

Combine filters example (Swift)

let myChannel = await supabase.channel('db-changes'); let changes = await myChannel.postgresChange(InsertAction.self, schema: 'public', table: 'orders', filter: .and([.gt('amount', value: 100), .eq('status', value: 'open')])); await myChannel.subscribe()

Combine filters example (Kotlin)

val myChannel = supabase.channel('db-changes'); val changes = myChannel.postgresChangeFlow<PostgresAction.Insert>(schema = 'public') { table = 'orders'; filter = 'amount=gt.100,status=eq.open' }

Combine filters example (Python)

changes = supabase.channel('db-changes').on_postgres_changes('INSERT', schema='public', table='orders', filter='amount=gt.100,status=eq.open', callback=lambda payload: print(payload)).subscribe()

Combine filters example (C#)

var channel = supabase.Realtime.Channel('changes'); channel.Register(new PostgresChangesOptions('public', 'orders', ListenType.Inserts, 'amount=gt.100,status=eq.open')); channel.AddPostgresChangeHandler(ListenType.Inserts, (sender, change) => { Console.WriteLine(change.Payload); }); await channel.Subscribe()

Filter values with reserved characters must be double-quoted

Values that contain reserved characters (comma, parenthesis, quote, or backslash) must be double-quoted PostgREST-style so the server does not read them as condition or list boundaries. For example: name=eq."Doe, Jane". The postgresChangesFilter() builder (JavaScript), PostgresChangeFilter (Dart), and RealtimePostgresFilter (Swift) apply this quoting automatically.

Select specific columns in postgres changes

Use the select parameter to receive only a subset of columns instead of the full row. This reduces payload size and data transferred per event, which is useful for tables with large bytea, jsonb, or text columns. The listed columns must be selectable by the subscribing role, and the table's primary key is always included so you can identify the row. select requires an explicit schema and table — it is not supported on wildcard subscriptions.

Select specific columns example (JavaScript)

const channel = supabase.channel('changes').on('postgres_changes', {event: '*', schema: 'public', table: 'profiles', select: ['id', 'username']}, (payload) => console.log(payload)).subscribe()

Select specific columns example (Dart)

supabase.channel('changes').onPostgresChanges(event: PostgresChangeEvent.all, schema: 'public', table: 'profiles', select: ['id', 'username'], callback: (payload) => print(payload)).subscribe()

Select specific columns example (Swift)

let myChannel = await supabase.channel('db-changes'); let changes = await myChannel.postgresChange(AnyAction.self, schema: 'public', table: 'profiles', select: ['id', 'username']); await myChannel.subscribe()

Select specific columns example (Python)

changes = supabase.channel('changes').on_postgres_changes('*', schema='public', table='profiles', select=['id', 'username'], callback=lambda payload: print(payload)).subscribe()

Receive old records on UPDATE and DELETE

By default, only new record changes are sent. To receive the old record (previous values) whenever you UPDATE or DELETE a record, set the replica identity of your table to full by running: alter table messages replica identity full;

Listen to tables in private schemas

Postgres Changes works out of the box for tables in the public schema. To listen to tables in private schemas, grant table SELECT permissions to the database role found in your access token. For example: grant select on "non_private_schema"."some_table" to authenticated;

Delete events can only be filtered with replica identity full

You can only filter DELETE events when tracking Postgres Changes if the table has the replica identity set to full.

Subscribe to INSERT, UPDATE, DELETE with isDistinct filter (Dart example)

In Dart, use onPostgresChanges with PostgresChangeEvent.update, schema 'public', table 'orders', and a PostgresChangeFilter with type isDistinct, column 'status', value 'shipped'. The filter checks if the column value is distinct (changed).

Subscribe to postgres changes with filter (Swift example)

In Swift, call myChannel.postgresChange() with UpdateAction.self, schema 'public', table 'orders', and filter .isDistinct('status', value: 'shipped'). Iterate over the async changes stream to receive updates.

Subscribe to postgres changes with filter (Kotlin example)

In Kotlin, create a postgresChangeFlow with schema 'public', table 'orders', and filter 'status=isdistinct.shipped'. Use onEach to handle each change and launchIn to start the flow.

Subscribe to postgres changes with filter (Python example)

In Python, use on_postgres_changes with event 'UPDATE', schema 'public', table 'orders', filter 'status=isdistinct.shipped', and a callback function to handle the payload.

Subscribe to postgres changes with filter (C# example)

In C#, create a channel, register PostgresChangesOptions with schema 'public', table 'orders', ListenType.Updates, and filter 'status=isdistinct.shipped'. Add a handler and subscribe.

Give your agent this brain