About

In Sequin, change messages are ephemeral. Once they’re sent to a sink, they’re deleted.

For some sinks and use cases, it can be desirable to retain change messages for some period of time. Then, you can take advantage of Sequin’s backfill feature to replay the changes to the sink.

For example, if your sink is a webhook endpoint, you might deploy a bug that erroneously processes messages. With change retention, you can play back change messages from the last few days to the sink to reprocess the affected messages.

With change retention, you store change messages in a Postgres table. You can then use Sequin’s standard consumption methods to stream these messages to your sink.

Configuration

You can configure change retention for tables in the Sequin web console, under the Change Retention tab.

Source configuration

Select the table you want to retain changes for. You can specify which operations to capture (i.e. inserts, updates, and/or deletes). And you can filter the changes to capture with one or more column filters.

Destination configuration

Sequin writes events to an event table with a Sequin-specified schema. If you already have an event table ready to go, you can select it. Otherwise, click “Create new event table” which will open a modal that will provide instructions for creating an event table in your database.

Event table

Schema

Sequin retains changes in a change table in your database. This table has a schema specified by Sequin.

Change tables look like this:

create table sequin_changes (
  id serial primary key,
  seq bigint not null,
  source_database_id uuid not null,
  source_table_oid bigint not null,
  source_table_schema text not null,
  source_table_name text not null,
  record_pk text not null,
  record jsonb not null,
  changes jsonb,
  action text not null,
  committed_at timestamp with time zone not null,
  inserted_at timestamp with time zone not null default now()
);

The schema of this table varies slightly if you’re using pg_partman to manage retention. In particular, id and committed_at are a composite primary key, and the create table DDL includes a partition by range clause.

id
integer
required

Auto-generated, auto-incrementing ID for the event entry.

seq
bigint
required

(internal) A Sequin stream for the event entry.

source_database_id
uuid
required

(internal) The Sequin ID for your source database.

source_table_oid
bigint
required

The OID for the source table.

source_table_schema
text
required

The schema of the source table.

source_table_name
text
required

The name of the source table.

record_pk
text
required

The primary key for the source row. It’s text, regardless of the type of the source’s primary key type.

record
jsonb
required

For inserts and updates, this contains all the latest field values for the row (i.e. new). For deletes, this contains all the field values prior to deletion (i.e. old).

changes
jsonb

For updates, this is a JSON of all the old fields that changed in this update. changes does not include unchanged values. So, to get the entire old row, just merge changes on top of record. (null for insert and delete operations.)

action
text
required

One of insert, update, or delete.

committed_at
timestamp with time zone
required

The time the change was committed.

inserted_at
timestamp with time zone
required

The time this event was inserted into the event table.

Creating a change table

The Sequin web console will provide you with the SQL commands you need to create an event table in your database.

Retention policies

You can configure retention policies for your event tables using extensions like pg_cron or pg_partman. This means old events will be automatically deleted from your event table.

We generally recommend using pg_cron, especially if you’re just starting out. It’s simple to setup and maintain and can handle high volume. We’ve benched a pg_cron retention solution on a 16 CPU/64GB machine at 100M writes/day (≈1K writes/sec) and it’s worked just fine.

For very high volumes, you might consider pg_partman.

pg_cron

pg_cron is an extension that allows you to run SQL commands on a schedule. It is supported by all major Postgres providers.

When creating an event table, the Sequin web console will provide you with the SQL commands you need to create a pg_cron job to delete old events.

The commands look like this:

-- create required extension
-- you need to run this in the database that pg_cron is installed in
-- (usually in `postgres`)
create extension if not exists pg_cron;

-- setup cron job to run every 10 minutes and delete old data
select cron.schedule('retention_policy_10min', '*/10 * * * *', $$
  delete from public.sequin_events
  where committed_at < now() - interval '30 days';
$$);

pg_partman

pg_partman is a Postgres extension that allows you to manage retention policies for your event tables using Postgres partitions. pg_partman creates a new partition for each “bucket” of time you specify (e.g. one partition per day). To evict old data, pg_partman drops old partitions that fall outside of your retention window. This operation is very fast and efficient.

Like pg_cron, pg_partman is supported by all major Postgres providers. While it’s more efficient, it’s also more complicated, so we generally recommend using pg_cron unless you have a very high write volume (greater than 1M writes per day).

When creating an event table, the Sequin web console will provide you with the SQL commands you need to create a pg_partman retention policy.

It looks like this:

-- You'll need to setup pg_partman in your database, including adding this to postgresql.conf:
-- shared_preload_libraries = 'pg_partman_bgw'     # (change requires restart)

-- create required extensions
create schema partman;
create extension pg_partman schema partman;

-- set up pg_partman for time-based partitioning
select partman.create_parent(
  p_parent_table => 'public.sequin_events',
  p_template_table => 'public.sequin_events',
  p_control => 'committed_at',
  p_interval => '1 day',
  p_automatic_maintenance => 'on',
  p_default_table := false
);

-- set up retention policy
update partman.part_config
set
  retention = '30 days',
  retention_keep_table = false,
  infinite_time_partitions = true
where parent_table = 'public.sequin_events';

create_parent sets up the partitions and inserts a record into partman.part_config. The record in partman.part_config is used by the pg_partman background worker to determine which tables to manage. The update ... part_config command is used to set the retention policy. (You need to run the update after the create_parent command, as create_parent does not accept configuration options such as retention.)

A brief explanation of the options:

p_parent_table
text
required

The fully qualified name of the table to partition (e.g. ‘public.sequin_events’).

p_template_table
text
required

The table to use as a template for new partitions. When set to the parent table, ensures all partitions have the same schema as the parent.

p_control
text
required

The column to partition by. For time-based partitioning, this should be a timestamp column (e.g. ‘committed_at’).

p_interval
text
required

A Postgres interval value specifying the size of each partition (e.g. ‘1 day’). Each partition will contain data for this time period.

p_automatic_maintenance
boolean
required

When ‘on’, uses the pg_partman background worker to automatically create new partitions and manage retention. Requires proper setup in postgresql.conf. (See pg_partman’s README)

p_default_table
boolean
required

When false, disables the default partition table. Generally recommended to set to false for time-based partitioning.

retention
text

How long to retain partitions before dropping them (e.g. ‘30 days’). Set via update to partman.part_config after creation.

retention_keep_table
boolean

When false, physically drops old partitions. When true, only detaches them from the parent table. Default is true.

infinite_time_partitions
boolean

When true, ensures pg_partman continues creating future partitions indefinitely. Recommended to set to true for time-based partitioning.

Message examples

Below are some examples of messages that Sequin captures:

Insert message

record contains the new values for the row. changes is null for insert messages:

action                | insert
record_pk             | 9
record                | {
                      |   "id": 9,
                      |   "name": "us-gov-west-1",
                      |   "timezone": "pst",
                      |   "updated_at": "2024-10-28T21:37:53",
                      |   "inserted_at": "2024-10-28T21:37:53"
                      | }
changes               |

Update message

record contains the new values for the row. changes contains the old values that changed:

action                | update
record_pk             | 9
record                | {
                      |   "id": 9,
                      |   "name": "us-gov-west-1",
                      |   "timezone": "mst",
                      |   "updated_at": "2024-10-28T21:39:21",
                      |   "inserted_at": "2024-10-28T21:37:53"
                      | }
changes               | {
                      |   "timezone": "pst",
                      |   "updated_at": "2024-10-28T21:37:53"
                      | }

So, if you want the entire new row, just use record. If you want the entire old row, merge changes on top of record.

Delete message

record contains the old values for the row. changes is null for delete messages:

action                | delete
record_pk             | 9
record                | {
                      |   "id": 9,
                      |   "name": "us-gov-west-1",
                      |   "timezone": "mst",
                      |   "updated_at": "2024-10-28T21:39:21",
                      |   "inserted_at": "2024-10-28T21:37:53"
                      | }
changes               |

Was this page helpful?