Replication

Replication is technique for copying the data from one database to another. This is useful for:

Spreading out the "load". For example, if your database has a lot of reads, you might want to split it between two databases.
Reducing latency. For example, you may want one database in London to serve your European customers, and one in New York to serve the US.
In Supabase's case, we use the built-in replication functionality to provide a real-time API.

Publications#

Publications are a way of choosing which changes should be sent to other systems (usually another Postgres database).

Managing Publications#

Supabase provides an interface for managing your publications, or you can use SQL.

Go to the "Database" section.
Click on "Replication" in the sidebar.
Control which database events are sent by toggling the Insert/Update/Delete toggles.
Control which tables broadcast changes by clicking into the "Source" and toggling the tables.

Create a Publication#

This publication will contain all changes to all tables.

create publication publication_name 
for all tables;

Create a Publication which listens to individual tables#

create publication publication_name 
for table table_one, table_two;

Add tables to an existing publication#

alter publication publication_name 
add table table_name;

Listen to `insert`#

create publication publication_name 
for all tables
with (publish = 'insert');

Listen to `update`#

create publication publication_name 
for all tables
with (publish = 'update');

Listen to `delete`#

create publication publication_name 
for all tables
with (publish = 'delete');

Remove a Publication#

drop publication if exists publication_name;

Recreate a Publication#

If you are planning to re-create a publication, it's best to do it in a transaction to ensure the operation succeeds.

begin; 
  -- remove the realtime publication
  drop publication if exists publication_name; 

  -- re-create the publication but don't enable it for any tables
  create publication publication_name;  
commit;