replication

13 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Replication methods in Supabase

Supabase supports three replication methods: Read replicas (additional Supabase Postgres databases kept in sync with your primary database), Supabase Pipelines (a managed CDC product for moving data to supported destination systems), and Manual replication (using Postgres logical replication features with tools like Airbyte, Estuary, Fivetran, Materialize, Stitch, or AWS DMS).

Read replicas use case and purpose

Read replicas are additional Supabase Postgres databases kept in sync with your primary database. Use them when you want read-only query capacity, lower latency in another region, or to isolate analytical reads from application writes while staying inside Supabase Postgres.

Pipelines supported destinations and status

BigQuery is currently available as a managed destination. ClickHouse, DuckLake, and Snowflake are in Early Access. Managed Pipelines run in AWS eu-central-1 (Frankfurt). Destination resources should be chosen as close as possible to Frankfurt to reduce network latency and replication lag.

Pipelines destination capabilities table

| Destination | Insert | Update | Delete | Truncate | Schema change | Data model | |---|---|---|---|---|---|---| | BigQuery | ✅ Supported | ✅ Supported | ✅ Supported | ✅ Supported | Beta (limited) | Current-state tables | | ClickHouse | ✅ Supported | REPLICA IDENTITY FULL | Primary-key or full identity | ✅ Supported | Early Access (limited) | Current-state tables (default) or append-only CDC history | | DuckLake | ✅ Supported | Row identity required | Row identity required | ✅ Supported | Early Access (limited) | Current-state lakehouse tables backed by a SQL catalog and object storage | | Snowflake | ✅ Supported | REPLICA IDENTITY FULL | Row identity required | ✅ Supported | Early Access (limited) | Append-only CDC history. Source TRUNCATE operations and table resets erase it |

Manual replication with logical replication

Manual replication uses the same underlying Postgres logical replication features as Pipelines, but you configure and operate the pieces yourself. Use this when you want to connect tools such as Airbyte, Estuary, Fivetran, Materialize, Stitch, AWS DMS, or another system that supports Postgres logical replication.

Write-Ahead Log (WAL) in Postgres

Postgres uses the Write-Ahead Log (WAL) to manage changes to the database. As changes are made, they are appended to the WAL, which is a series of files (segments) with a specifiable file size. Once one segment is full, Postgres starts appending to a new segment. After a period of time, a checkpoint occurs and Postgres synchronizes the WAL with the database. Once the checkpoint is complete, WAL files can be removed from disk and free up space.

Logical replication definition

Logical replication is a method of replication where Postgres uses WAL files to transmit changes to another Postgres database, or to a system that supports reading WAL files.

LSN (Log Sequence Number)

LSN is a Log Sequence Number that identifies a position in the WAL. It is often used to determine the progress of replication in subscribers and calculate the lag of a replication slot.

Logical replication architecture components

When setting up logical replication, three key components are involved: publication (a set of tables on your primary database that will be published), replication slot (a slot used for replicating data from a single publication that specifies the output format of changes), and subscription (created from an external system such as another Postgres database and must specify the name of the publication; if you do not specify a replication slot, one is automatically created).

Logical replication output formats

Logical replication is typically output in two forms: pgoutput and wal2json. The output method is how Postgres sends changes to any active replication slot.

Logical replication WAL configuration concerns

When using logical replication, Postgres keeps WAL files around for longer than it otherwise needs them. If the files are removed too soon, then your replication slot can become inactive or lost if the database receives a large number of changes in a short time. Postgres has many options and settings that can be tweaked to manage WAL usage effectively, but not all are user-configurable as they can impact database stability.

Postgres logical replication configuration settings

| Setting | Description | User-facing | Default | |---|---|---|---| | max_replication_slots | Max count of replication slots allowed | No | | | wal_keep_size | Minimum size of WAL files to keep for replication | No | | | max_slot_wal_keep_size | Max WAL size that can be reserved by replication slots | No | | | checkpoint_timeout | Max time between WAL checkpoints | No | |

Replication use cases

Database replication can be used for: analytics and data warehousing (replicate your operational database to analytics platforms for complex analysis without impacting your application's performance), data integration (keep your data synchronized across different systems and services in your tech stack), and operational reporting (maintain a copy of selected application data that you can query in another system).

Give your agent this brain