# Handle upstream schema changes
How to add a column, drop a column, or drop a constraint in your source PostgreSQL database, without any downtime in Materialize

> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).


> **Note:** - Changing column types is currently unsupported.


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](https://preview.materialize.com/materialize/40269/get-started/quickstart/).

### Set up a PostgreSQL database

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

```sql
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](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/postgres/index.md#integration-guides).


## Create a source using the new syntax

In Materialize, create a source using the updated [`CREATE SOURCE`
syntax](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/postgres-v2/index.md).

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

Unlike the [legacy syntax](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/postgres/index.md), 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`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/index.md), which will create the corresponding tables and
start the snapshotting process. See [Create a table from the
source](#create-a-table-from-the-source).

> **Note:** The [legacy syntax](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/postgres/index.md) is still supported. However,
> the legacy syntax doesn't support upstream schema changes.


## 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.

```sql
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](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/index.md#snapshotting) of table `v1.T` will begin.

> **Note:** In private preview starting in v26.47, Materialize can snapshot a new table
> without blocking ingestion for existing tables. This uses more memory and CPU.
> Single-worker replica sizes (`25cc` or `50cc`) might not have enough capacity
> to keep both the new table and existing tables fresh. Use a source cluster size
> of `100cc` or larger, or temporarily resize it up for the snapshot. See the
> [release notes](https://preview.materialize.com/materialize/40269/markdown-docs/releases/index.md#v26.47-postgres-add-table-freshness).
> Without this feature, snapshotting a new table blocks ingestion for existing
> tables. To speed up the snapshot, consider sizing up the cluster. You can
> monitor snapshot progress on the source's overview page in the Materialize
> Console.


## 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`.

```sql
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`:

```sql
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:

```sql
CREATE SCHEMA v2;

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

The [snapshotting](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/index.md#snapshotting) of table `v2.T` will begin.
`v2.T` will include columns `A` and `B`.

> **Note:** In private preview starting in v26.47, Materialize can snapshot a new table
> without blocking ingestion for existing tables. This uses more memory and CPU.
> Single-worker replica sizes (`25cc` or `50cc`) might not have enough capacity
> to keep both the new table and existing tables fresh. Use a source cluster size
> of `100cc` or larger, or temporarily resize it up for the snapshot. See the
> [release notes](https://preview.materialize.com/materialize/40269/markdown-docs/releases/index.md#v26.47-postgres-add-table-freshness).
> Without this feature, snapshotting a new table blocks ingestion for existing
> tables. To speed up the snapshot, consider sizing up the cluster. You can
> monitor snapshot progress on the source's overview page in the Materialize
> Console.



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`:

```sql {hl_lines="4"}
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.

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

> **Note:** In private preview starting in v26.47, Materialize can snapshot a new table
> without blocking ingestion for existing tables. This uses more memory and CPU.
> Single-worker replica sizes (`25cc` or `50cc`) might not have enough capacity
> to keep both the new table and existing tables fresh. Use a source cluster size
> of `100cc` or larger, or temporarily resize it up for the snapshot. See the
> [release notes](https://preview.materialize.com/materialize/40269/markdown-docs/releases/index.md#v26.47-postgres-add-table-freshness).
> Without this feature, snapshotting a new table blocks ingestion for existing
> tables. To speed up the snapshot, consider sizing up the cluster. You can
> monitor snapshot progress on the source's overview page in the Materialize
> Console.


### B. Drop a column in your upstream PostgreSQL database

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

```sql
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

> **Public Preview:** This feature is in public preview.


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.

```sql
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`:

```sql
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:

  ```sql
  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.

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

> **Note:** In private preview starting in v26.47, Materialize can snapshot a new table
> without blocking ingestion for existing tables. This uses more memory and CPU.
> Single-worker replica sizes (`25cc` or `50cc`) might not have enough capacity
> to keep both the new table and existing tables fresh. Use a source cluster size
> of `100cc` or larger, or temporarily resize it up for the snapshot. See the
> [release notes](https://preview.materialize.com/materialize/40269/markdown-docs/releases/index.md#v26.47-postgres-add-table-freshness).
> Without this feature, snapshotting a new table blocks ingestion for existing
> tables. To speed up the snapshot, consider sizing up the cluster. You can
> monitor snapshot progress on the source's overview page in the Materialize
> Console.


### B. Drop the constraint in your upstream PostgreSQL database

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

```sql
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.