Home  /  Ingest data

PostgreSQL

View as Markdown

Change Data Capture (CDC)

Materialize supports PostgreSQL as a real-time data source. The PostgreSQL source uses PostgreSQL’s replication protocol to continually ingest changes resulting from CRUD operations in the upstream database. The native support for PostgreSQL Change Data Capture (CDC) in Materialize gives you the following benefits:

  • No additional infrastructure: Ingest PostgreSQL change data into Materialize in real-time with no architectural changes or additional operational overhead. In particular, you do not need to deploy Kafka and Debezium for PostgreSQL CDC.

  • Transactional consistency: The PostgreSQL source ensures that transactions in the upstream PostgreSQL database are respected downstream. Materialize will never show partial results based on partially replicated transactions.

  • Incrementally updated materialized views: Materialized views in PostgreSQL are computationally expensive and require manual refreshes. You can use Materialize as a read-replica to build views on top of your PostgreSQL data that are efficiently maintained and always up-to-date.

Supported versions and services

The PostgreSQL source requires PostgreSQL 11+ and is compatible with most common PostgreSQL hosted services.

Integration guides

To help you get started, the following integration guides are available:

Considerations

Publication membership

PostgreSQL’s logical replication API does not provide a signal when users remove tables from publications. Because of this, Materialize relies on periodic checks to determine if a table has been removed from a publication, at which time it generates an irrevocable error, preventing any values from being read from the table.

However, it is possible to remove a table from a publication and then re-add it before Materialize notices that the table was removed. In this case, Materialize can no longer provide any consistency guarantees about the data we present from the table and, unfortunately, is wholly unaware that this occurred.

To mitigate this issue, if you need to drop and re-add a table to a publication, ensure that you remove the table/subsource from the source before re-adding it using the DROP SOURCE command.

Supported types

Materialize natively supports the following PostgreSQL types (including the array type for each of the types):

  • bool
  • bpchar
  • bytea
  • char
  • date
  • daterange
  • float4
  • float8
  • int2
  • int2vector
  • int4
  • int4range
  • int8
  • int8range
  • interval
  • json
  • jsonb
  • numeric
  • numrange
  • oid
  • text
  • time
  • timestamp
  • timestamptz
  • tsrange
  • tstzrange
  • uuid
  • varchar

Replicating tables that contain unsupported data types is possible via the TEXT COLUMNS option. The specified columns will be treated as text; i.e., will not have the expected PostgreSQL type features. For example:

  • enum: When decoded as text, the implicit ordering of the original PostgreSQL enum type is not preserved; instead, Materialize will sort values as text.

  • money: When decoded as text, resulting text value cannot be cast back to numeric, since PostgreSQL adds typical currency formatting to the output.

Inherited tables

When using PostgreSQL table inheritance, PostgreSQL serves data from SELECTs as if the inheriting tables’ data is also present in the inherited table. However, both PostgreSQL’s logical replication and COPY only present data written to the tables themselves, i.e. the inheriting data is not treated as part of the inherited table.

PostgreSQL sources use logical replication and COPY to ingest table data, so inheriting tables’ data will only be ingested as part of the inheriting table, i.e. in Materialize, the data will not be returned when serving SELECTs from the inherited table.

  • If using legacy syntax CREATE SOURCE ... FOR ...:

    You can mimic PostgreSQL’s SELECT behavior with inherited tables by creating a materialized view that unions data from the inherited and inheriting tables (using UNION ALL). However, if new tables inherit from the table, data from the inheriting tables will not be available in the view. You will need to add the inheriting tables via ADD SUBSOURCE and create a new view (materialized or non-) that unions the new table.

  • If using new CREATE TABLE FROM SOURCE syntax:

    You can mimic PostgreSQL’s SELECT behavior with inherited tables by creating a materialized view that unions data from the inherited and inheriting tables (using UNION ALL). However, if new tables inherit from the table, data from the inheriting tables will not be available in the view. You will need to add the inheriting tables via CREATE TABLE .. FROM SOURCE and create a new view (materialized or non-) that unions the new table.

Replication slots

Each source ingests the raw replication stream data for all tables in the specified publication using a single replication slot. To manage replication slots:

  • For PostgreSQL 13+, set a reasonable value for max_slot_wal_keep_size to limit the amount of storage used by replication slots.

  • If you stop using Materialize, or if either the Materialize instance or the PostgreSQL instance crash, delete any replication slots. You can query the mz_internal.mz_postgres_sources table to look up the name of the replication slot created for each source.

  • If you delete all objects that depend on a source without also dropping the source, the upstream replication slot remains and will continue to accumulate data so that the source can resume in the future. To avoid unbounded disk space usage, make sure to use DROP SOURCE or manually delete the replication slot.

Modifying an existing source

When you add a new subsource to an existing source (ALTER SOURCE ... ADD SUBSOURCE ...), Materialize starts the snapshotting process for the new subsource. During this snapshotting, the data ingestion for the existing subsources for the same source is temporarily blocked. As such, if possible, you can resize the cluster to speed up the snapshotting process and once the process finishes, resize the cluster for steady-state.

Snapshotting

The PostgreSQL source performs parallel snapshotting of tables by distributing rows among workers using ranges of CTID. Materialize uses PostgreSQL statistics to estimate the amount of data and number of rows to read. Missing or stale statistics can result in uneven work distribution, reducing snapshot performance. They can also cause incorrect snapshot progress reporting in the Console.

To avoid this situation, before creating the source in Materialize, ensure statistics are up to date by running PostgreSQL ANALYZE command.

Handling upstream operations

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

Changing constraints

Materialize ignores the following constraint changes: foreign key, CHECK, and EXCLUSION. As such, you can add or drop them without affecting ingestion.

Materialize also ignores NOT NULL, UNIQUE, and PRIMARY KEY constraints that are added after the Materialize table is created (that is, the table was created without them). Adding such a constraint, and later dropping it, does not affect ingestion.

Dropping a NOT NULL, UNIQUE, or PRIMARY KEY constraint that existed when the table was created puts the affected table into an error state.

Changing a column’s data type

Changing an ingested column’s data type upstream puts the affected Materialize table into an error state unless the column was ingested as text via the TEXT COLUMNS option. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

  • Dropping a table (DROP TABLE), removing it from the publication (ALTER PUBLICATION ... DROP TABLE), or dropping the publication (DROP PUBLICATION).
  • Renaming a table or moving it to a different schema.
  • Setting a table’s replica identity to anything other than FULL (ALTER TABLE ... REPLICA IDENTITY).
  • Truncating a table (TRUNCATE). To clear a table without putting it into an error state, use an unqualified DELETE FROM t; instead.
Back to top ↑