Change retention

​

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()
);

​

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:

​

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               |