Postgres Changes

Use Realtime's Postgres Changes to listen to database events.

Quick start#

In this example we'll set up a database table, secure it with Row Level Security, and subscribe to all changes using the Supabase client libraries.

1
-- Create a table called "todos"
2
-- with a column to store tasks.
3
create table todos (
4
  id serial primary key,
5
  task text
6
);

1
-- Grant the privileges roles need
2
GRANT SELECT ON public.todos TO anon;
3
4
-- Turn on security
5
alter table "todos"
6
enable row level security;
7
8
-- Allow anonymous access
9
create policy "Allow anonymous access"
10
on todos
11
for select
12
to anon
13
using (true);

1
alter publication supabase_realtime
2
add table your_table_name;

1
npm install @supabase/supabase-js

1
import { createClient } from '@supabase/supabase-js'
2
3
const supabase = createClient(
4
  'https://<project>.supabase.co',
5
  '<sb_publishable_... key>'
6
)

1
import { createClient } from '@supabase/supabase-js'
2
const supabase = createClient('your_project_url', 'your_supabase_api_key')
3
4
// ---cut---
5
const channelA = supabase
6
  .channel('schema-db-changes')
7
  .on(
8
    'postgres_changes',
9
    {
10
      event: '*',
11
      schema: 'public',
12
    },
13
    (payload) => console.log(payload)
14
  )
15
  .subscribe()

1
insert into todos (task)
2
values
3
  ('Change!');

Usage#

You can use the Supabase client libraries to subscribe to database changes.

Listening to specific schemas#

Subscribe to specific schema events using the schema parameter:

1
const changes = supabase
2
  .channel('schema-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      schema: 'public', // Subscribes to the "public" schema in Postgres
7
      event: '*',       // Listen to all changes
8
    },
9
    (payload) => console.log(payload)
10
  )
11
  .subscribe()

The channel name can be any string except 'realtime'.

Listening to specific events#

Use the event parameter to listen only to a specific database event. event can be INSERT, UPDATE, DELETE, or * to listen to all changes.

1
const changes = supabase
2
  .channel('schema-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
    },
9
    (payload) => console.log(payload)
10
  )
11
  .subscribe()

The channel name can be any string except 'realtime'.

Listening to specific tables#

Subscribe to specific table events using the table parameter:

1
const changes = supabase
2
  .channel('table-db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'todos',
9
    },
10
    (payload) => console.log(payload)
11
  )
12
  .subscribe()

The channel name can be any string except 'realtime'.

Listening to multiple changes#

To listen to different events and schema/tables/filters combinations with the same channel:

1
const channel = supabase
2
  .channel('db-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'messages',
9
    },
10
    (payload) => console.log(payload)
11
  )
12
  .on(
13
    'postgres_changes',
14
    {
15
      event: 'INSERT',
16
      schema: 'public',
17
      table: 'users',
18
    },
19
    (payload) => console.log(payload)
20
  )
21
  .subscribe()

Filtering for specific changes#

Use the filter parameter for granular changes:

1
const changes = supabase
2
  .channel('table-filter-changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'todos',
9
      filter: 'id=eq.1',
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

Available filters#

Realtime offers filters so you can specify the data your client receives at a more granular level. A filter is a column=operator.value expression (for example id=eq.1 or title=like.%foo%) that Realtime evaluates on the server, so filtered-out events never leave the database.

The following operators are available:

You can also negate any operator with not. and combine multiple conditions with commas (applied as an AND).

1
import { postgresChangesFilter } from '@supabase/supabase-js'
2
3
// → 'quantity=gte.10,status=eq.open'
4
const filter = postgresChangesFilter().gte('quantity', 10).eq('status', 'open')

Equal to (eq)#

To listen to changes when a column's value in a table equals a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'messages',
9
      filter: postgresChangesFilter().eq('body', 'hey'),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's = filter.

Not equal to (neq)#

To listen to changes when a column's value in a table does not equal a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'messages',
9
      filter: postgresChangesFilter().neq('body', 'bye'),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's != filter.

Less than (lt)#

To listen to changes when a column's value in a table is less than a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'profiles',
9
      filter: postgresChangesFilter().lt('age', 65),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's < filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

Less than or equal to (lte)#

To listen to changes when a column's value in a table is less than or equal to a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'profiles',
9
      filter: postgresChangesFilter().lte('age', 65),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres' <= filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

Greater than (gt)#

To listen to changes when a column's value in a table is greater than a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'products',
9
      filter: postgresChangesFilter().gt('quantity', 10),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's > filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

Greater than or equal to (gte)#

To listen to changes when a column's value in a table is greater than or equal to a client-specified value:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'products',
9
      filter: postgresChangesFilter().gte('quantity', 10),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's >= filter, so it works for non-numeric types. Make sure to check the expected behavior of the compared data's type.

Contained in list (in)#

To listen to changes when a column's value in a table equals any client-specified values:

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'colors',
9
      filter: postgresChangesFilter().in('name', ['red', 'blue', 'yellow']),
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

This filter uses Postgres's = ANY. Realtime allows a maximum of 100 values for this filter.

Pattern matching (like, ilike)#

To listen to changes when a text column matches a pattern, use like (case-sensitive) or ilike (case-insensitive). Use % to match any sequence of characters and _ to match a single character.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'articles',
9
      // matches "Breaking News", "BREAKING", ...
10
      filter: postgresChangesFilter().ilike('title', '%breaking%'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

like uses Postgres's LIKE and ilike uses ILIKE. Both require a text-compatible column. The examples above use ilike; swap in like for case-sensitive matching—usage is otherwise identical.

Regular expression matching (match, imatch)#

To listen to changes when a text column matches a POSIX regular expression, use match (case-sensitive) or imatch (case-insensitive).

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'posts',
9
      // matches "post-1", "post-42", ...
10
      filter: postgresChangesFilter().match('slug', '^post-\\d+$'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

match uses Postgres's ~ operator and imatch uses ~*. Both require a text-compatible column, and the pattern is validated when you subscribe. The examples above use match; swap in imatch for case-insensitive matching—usage is otherwise identical.

Null and boolean checks (is)#

To listen to changes when a column IS null, true, false, or unknown, use is. is.null works on any column type; is.true, is.false, and is.unknown require a boolean column.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'todos',
9
      // only rows that are not yet completed
10
      filter: postgresChangesFilter().is('completed_at', null),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

This filter uses Postgres's IS operator.

Distinct from (isdistinct)#

isdistinct is a NULL-safe inequality (IS DISTINCT FROM). Unlike neq, it treats null as a comparable value, so a null column is considered distinct from a non-null value.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'UPDATE',
7
      schema: 'public',
8
      table: 'orders',
9
      // includes rows where status is null
10
      filter: postgresChangesFilter().isDistinct('status', 'shipped'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

Negating a filter (not)#

Prefix any operator with not. to invert it — for example not.in, not.is, or not.like.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'posts',
9
      // anything except drafts and archived
10
      filter: postgresChangesFilter().not('status', 'in', ['draft', 'archived']),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

Combining filters with AND#

Combine multiple conditions by separating them with commas. All conditions must match (logical AND). You can only combine conditions with AND — OR is not supported.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: 'INSERT',
7
      schema: 'public',
8
      table: 'orders',
9
      // amount > 100 AND status = "open"
10
      filter: postgresChangesFilter().gt('amount', 100).eq('status', 'open'),
11
    },
12
    (payload) => console.log(payload)
13
  )
14
  .subscribe()

Selecting specific columns#

By default each change event contains the full row. Use select to receive only a subset of columns instead. This reduces payload size and the data transferred per event, which is especially 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's not supported on wildcard subscriptions.

1
const channel = supabase
2
  .channel('changes')
3
  .on(
4
    'postgres_changes',
5
    {
6
      event: '*',
7
      schema: 'public',
8
      table: 'profiles',
9
      select: ['id', 'username'], // payload.new only contains { id, username }
10
    },
11
    (payload) => console.log(payload)
12
  )
13
  .subscribe()

Receiving old records#

By default, only new record changes are sent but if you want to receive the old record (previous values) whenever you UPDATE or DELETE a record, you can set the replica identity of your table to full:

1
alter table
2
  messages replica identity full;

Private schemas#

Postgres Changes works out of the box for tables in the public schema. You can listen to tables in your private schemas by granting table SELECT permissions to the database role found in your access token. You can run a query similar to the following:

1
grant select on "non_private_schema"."some_table" to authenticated;

Custom tokens#

You may choose to sign your own tokens to customize claims that can be checked in your RLS policies.

Your project JWT secret is found in the Settings > API keys section of the Dashboard.

To use your own JWT with Realtime make sure to set the token after instantiating the Supabase client and before connecting to a Channel.

1
const { createClient } = require('@supabase/supabase-js')
2
3
const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_KEY, {})
4
5
// Set your custom JWT here
6
supabase.realtime.setAuth('your-custom-jwt')
7
8
const channel = supabase
9
  .channel('db-changes')
10
  .on(
11
    'postgres_changes',
12
    {
13
      event: '*',
14
      schema: 'public',
15
      table: 'messages',
16
      filter: 'body=eq.bye',
17
    },
18
    (payload) => console.log(payload)
19
  )
20
  .subscribe()

Limitations#

Delete events are not filterable#

You can't filter Delete events when tracking Postgres Changes. This limitation is due to the way changes are pulled from Postgres.

Scaling Postgres Changes#

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 don't meaningfully increase Postgres Changes throughput.

For most applications this is plenty. To get the best performance:

• Use filters and column selection to send each client only the events and columns it needs.
• Keep authorization cheap by writing , indexed RLS policies.

Use the estimator below to gauge the maximum throughput for your instance, and run your own benchmarks to confirm it fits your use case:

If you're unsure which approach fits your use case, reach out through the Support Form — our engineers are happy to help you find the best solution.