Handle upstream schema changes

Materialize allows you to handle certain types of upstream table schema changes seamlessly, specifically:

• Adding a column in the upstream database.
• Dropping a column in the upstream database.
• Dropping a PRIMARY KEY, UNIQUE, or NOT NULL constraint in the upstream database.

This guide walks you through how to handle these changes without any downtime in Materialize.

Prerequisites

Some familiarity with Materialize. If you’ve never used Materialize before, start with our guide to getting started.

Set up a PostgreSQL database

For this guide, setup a PostgreSQL 11+ database. In your PostgreSQL, create a table T and populate:

CREATE TABLE T (
    A INT,
    CONSTRAINT t_pkey PRIMARY KEY (A)
);

INSERT INTO T (A) VALUES
    (10);

Connect your source database to Materialize

To create a source from PostgreSQL 11+, you must first:

• Configure upstream PostgreSQL instance
  • Set up logical replication.
  • Create a publication.
  • Create a replication user and password for Materialize to use to connect.
• Configure network security
  • Ensure Materialize can connect to your PostgreSQL instance.
• Create a connection to PostgreSQL in Materialize
  • The connection setup depends on the network security configuration.

For details, see the PostgreSQL integration guides.

Create a source using the new syntax

In Materialize, create a source using the updated CREATE SOURCE syntax.

CREATE SOURCE IF NOT EXISTS my_source
    FROM POSTGRES CONNECTION my_connection (PUBLICATION 'mz_source');

Unlike the legacy syntax, the new syntax does not include the FOR [[ALL] TABLES|SCHEMAS] clause; i.e., the new syntax does not create corresponding subsources in Materialize automatically. Instead, the new syntax requires a separate CREATE TABLE ... FROM SOURCE, which will create the corresponding tables and start the snapshotting process. See Create a table from the source.

Create a table from the source

To start ingesting specific tables from your source database, you can create a table in Materialize. We’ll add it into the v1 schema in Materialize.

CREATE SCHEMA v1;

CREATE TABLE v1.T
    FROM SOURCE my_source(REFERENCE public.T);

Once you’ve created a table from source, the initial snapshot of table v1.T will begin.

Create a view on top of the table.

For this guide, add a materialized view matview (also in schema v1) that sums column A from table T.

CREATE MATERIALIZED VIEW v1.matview AS
    SELECT SUM(A) from v1.T;

Handle upstream column addition

A. Add a column in your upstream PostgreSQL database

In your upstream PostgreSQL database, add a new column B to the table T:

ALTER TABLE T
    ADD COLUMN B BOOLEAN DEFAULT false;

INSERT INTO T (A, B) VALUES
    (20, true);

This operation will have no immediate effect in Materialize. In Materialize, v1.T will continue to ingest only column A. The materialized view v1.matview will continue to have access to column A as well.

B. Incorporate the new column in Materialize

To incorporate the new column into Materialize, create a new v2 schema and recreate the table in the new schema:

CREATE SCHEMA v2;

CREATE TABLE v2.T
    FROM SOURCE my_source(REFERENCE public.T);

The snapshotting of table v2.T will begin. v2.T will include columns A and B.

When the new v2.T table has finished snapshotting, create a new materialized view matview in the new schema. Since the new v2.matview is referencing the new v2.T, it can reference column B:

CREATE MATERIALIZED VIEW v2.matview AS
    SELECT SUM(A)
    FROM v2.T
    WHERE B = true;

Handle upstream column drop

A. Exclude the column in Materialize

To drop a column safely, in Materialize, first, create a new v3 schema, and recreate table T in the new schema but exclude the column to drop. In this example, we’ll drop the column B.

CREATE SCHEMA v3;
CREATE TABLE v3.T
    FROM SOURCE my_source(REFERENCE public.T) WITH (EXCLUDE COLUMNS (B));

B. Drop a column in your upstream PostgreSQL database

In your upstream PostgreSQL database, drop the column B from the table T:

ALTER TABLE T DROP COLUMN B;

Dropping the column B will have no effect on v3.T. However, the drop affects v2.T and v2.matview from our earlier examples. When the user attempts to read from either, Materialize will report an error that the source table schema has been altered.

Handle upstream constraint drop

Materialize ignores the following constraints: foreign key, CHECK, and EXCLUSION. As such, you can add or drop them without affecting ingestion. To handle changes in PRIMARY KEY, UNIQUE, and NOT NULL constraints, follow the steps below.

A. Exclude the constraint in Materialize

To drop a PRIMARY KEY, UNIQUE, or NOT NULL constraint, in Materialize, first, create a new v4 schema, and recreate table T in the new schema but exclude the constraint to drop. In this example, we’ll drop the primary key t_pkey. The constraint name is a string literal and must match the upstream name exactly, including case.

CREATE SCHEMA v4;
CREATE TABLE v4.T
    FROM SOURCE my_source(REFERENCE public.T) WITH (EXCLUDE CONSTRAINTS ('t_pkey'));

Materialize does not record the excluded constraint as a key of v4.T.

Excluding nullability constraints

EXCLUDE CONSTRAINTS only accepts PRIMARY KEY and UNIQUE constraint names. Individual NOT NULL constraints cannot be excluded by name. To drop NOT NULL constraints safely, use EXCLUDE ALL NOT NULL CONSTRAINTS:

CREATE SCHEMA v4;
CREATE TABLE v4.T
    FROM SOURCE my_source(REFERENCE public.T) WITH (EXCLUDE ALL NOT NULL CONSTRAINTS);

Note that when excluding NOT NULL constraints:

• Primary key columns remain NOT NULL, unless the primary key is also excluded with EXCLUDE CONSTRAINTS.

• Columns in a UNIQUE constraint become nullable. This means Materialize no longer uses that constraint as a key when optimizing queries, which might reduce query performance. UNIQUE NULLS NOT DISTINCT constraints (PostgreSQL 15+) are still used as keys.

• PRIMARY KEY and UNIQUE constraints are still recorded. Dropping one upstream still puts the table into an error state unless it is also excluded:

  CREATE TABLE v4.T
      FROM SOURCE my_source(REFERENCE public.T)
      WITH (EXCLUDE ALL NOT NULL CONSTRAINTS, EXCLUDE CONSTRAINTS ('t_pkey'));
  

Excluding all constraints

To record no constraints at all, use EXCLUDE ALL CONSTRAINTS: the table has no keys and every column is nullable, so any later constraint drop is safe. Queries that relied on those keys for optimization may run slower.

CREATE TABLE v4.T
    FROM SOURCE my_source(REFERENCE public.T) WITH (EXCLUDE ALL CONSTRAINTS);

B. Drop the constraint in your upstream PostgreSQL database

In your upstream PostgreSQL database, drop the constraint t_pkey from the table T:

ALTER TABLE T DROP CONSTRAINT t_pkey;

Dropping the constraint will have no effect on v4.T. However, the drop affects v3.T from our earlier example, which still records t_pkey as a key. When the user attempts to read from it, Materialize will report an error that the constraint was dropped upstream.