How Sequin works
Stream tables with Sequin in real-time
With Sequin, you’ll create Streams. Streams present a strictly ordered view of rows from one or more tables. Then, you’ll consume rows from the Stream:
- Using Consumer Groups
- Receiving webhooks
- Using the Sync API (coming soon)
Whether you’re pulling rows or having them pushed to you, Sequin always delivers the latest version of rows in order. When new rows are inserted or when rows are updated, they’re re-delivered to you.
If you want to capture discrete change events to rows, you can use a Change Capture Pipeline. With a Change Capture Pipeline, Sequin will capture all inserts, updates, and deletes along with new
and old
values, and store them in an event log table in your database. You can then create a Stream for the event log table and consume each change.
You can also filter and transform rows before they’re delivered to your application:
Streams
Sequin produces a Stream for every table you connect. Due to Postgres’ multiversion concurrency control, tables do not provide strict ordering guarantees ¹. This makes them difficult to stream properly without skipping rows ².
To produce a stricly ordered Stream, Sequin uses a combination of the source table and a replication slot to produce a Stream. This makes it possible to consume rows from a table. When rows are inserted or updated, Sequin adds the rows to the end of the Stream so consumers will process them. Sequin stores Stream information in its internal catalog tables.
When you setup a Stream for a table in Sequin, you must specify its sort column. Sequin will use this sort column to produce the initial Stream. You can also use the sort column when rewinding a consumer, as it approximates the Stream’s order.
The sort column orders historical rows in the Stream. For new and updated rows, their order in the Stream will depend on when they are committed. This is because auto-increment and datetime columns will not necessarily commit in order in Postgres.
After the Stream starts, the order of rows in the Stream will be the order they appear in Sequin’s replication slot. This corresponds to the latest entry of the row in the WAL (i.e. the most recent LSN corresponding to a given row).
It’s important you choose the right sort column. The right sort column is either:
- A timestamp like
updated_at
that your system updates whenever a row is inserted or updated. - A Postgres stream like
idx
orindex
that your system increments whenever a row is inserted or updated.
If your table does not have a timestamp or integer column that is updated on insert/update, you can add one:
inserted_at
or an auto-incrementing id
column as the sort column. uuid
columns will not work as they are not sequential.Rows
Sequin converts Postgres rows into JSON objects that are then delivered to your consumers.
A row has this shape:
For example:
Consuming rows
After creating a Stream, you have a few options for consuming rows with Sequin:
Consumer Groups
With Consumer Groups, you can setup an HTTP endpoint that your apps and services pull rows from. The endpoint follows the popular consumer group pattern of other queues and streams:
- You can have many consumers (processes or workers) all pulling from the consume endpoint at the same time.
- After a batch of rows is delivered to a consumer in the group, that consumer has a certain period of time to process the rows. This visibility timeout is configurable at the group level.
- After processing a batch of rows, the consumer acknowledges them by calling the acknowledge endpoint.
- If the consumer fails to process the batch, rows in the batch will be made available again for delivery to other consumers in the group.
- When a row is updated in the database, it is re-delivered to the group.
You can setup many consumer groups for a single table.
Read more about the Consumer Group API endpoints.
Webhooks
Sequin’s Webhook Subscriptions are powered by Consumer Groups. Each webhook payload contains one row. Rows are processed exactly once per subscription.
If your webhook endpoint returns a non-200 status code or fails to process the webhook within a configurable timeout, the row will be re-delivered.
Sequin will backoff and retry webhook delivery indefinitely.
You can monitor webhook status on the Sequin dashboard, including failing and outstanding messages.
Sync API
The sync API is still in development.
The Sync API is a simple alternative to Consumer Groups. With the Sync API, Sequin presents your table as a paginateable endpoint. You can start paginating from any point in the table. When you reach the end, you can long-poll to receive new and updated rows in real-time.
The Sync API is great for situations where you want to manage the state of the cursor and don’t need multiple consumers to process a stream in parallel.
Filtering
In all of Sequin’s consuming paradigms, you can specify one or more filters to process a subset of a table’s rows or changes. The filters support SQL operators:
Grouping
In all of Sequin’s consuming paradigms, you can specify one or more grouping columns to group messages during delivery.
Grouping ensures that messages with the same values in the specified grouping columns are processed in a strict order relative to each other. This means that for a given group, messages are always processed one by one, in the order they were received.
However, messages belonging to different groups may be processed concurrently or out of order relative to each other.
Grouping is particularly useful when you need to maintain consistency for related messages while still allowing parallel processing across different groups.
Transforming
Transforms are still under development and coming soon.
You can transform messages before they’re delivered to your consumers using a Lua function. This is useful for transforming data into a format more suitable for your application.
Change Capture Pipelines
Sequin streams rows directly from your tables. But sometimes, you want to log every discrete change to rows, and capture the old
and new
values of that change. Or, you want to retain a log of deleted rows.
With Change Capture Pipelines, Sequin captures changes from tables you specify. Sequin will write each change as it happens to a table in your database. Then, you can setup a Stream for your event table to stream its rows.
Change Capture Pipelines can indicate which operations to capture (i.e. insert
, update
, delete
) as well as apply SQL filtering on changes.
If you don’t want to keep every change indefinitely, you can setup your target event table with a retention policy using extensions such as pg_cron
or pg_partman
. By retaining recent changes in a Postgres table, you get to use Sequin features like replay and rewind.
Event tables look like this:
Learn more about Change Capture Pipelines.
Replication slots
To maintain Streams, Sequin needs to detect changes in your database. Core to that process is a replication slot and a corresponding publication. The publication determines which tables are captured by the replication slot.
Replication slots ensure Sequin never misses a change, even if Sequin is disconnected from your database for a period of time.
Guarantees
Strict ordering
Sequin guarantees that messages with the same primary key are processed in order for a given consumer.
Sync endpoints are strictly ordered. For the Consumer Group API and webhooks, Sequin will not deliver messages to a consumer until the previous messages for that primary key has been acknowledged.
This is useful for applications that need to process changes to a single record in order. For example, if you’re processing changes to a user’s profile, you want to make sure that the changes are applied in order.
Exactly-one processing
Both the Consume Groups and Webhook Subscriptions have exactly-once processing guarantees.
Was this page helpful?