FAQ: PostgreSQL sources
This page addresses common questions and challenges when working with PostgreSQL sources in Materialize. For general ingestion questions/troubleshooting, see:
For my trial/POC, what if I cannot use REPLICA IDENTITY FULL
?
Materialize requires REPLICA IDENTITY FULL
on PostgreSQL tables to capture all
column values in change events. If for your trial/POC (Proof-of-concept) you cannot modify your existing tables, here are two common alternatives:
-
Outbox Pattern (shadow tables)
NOTE: With the Outbox pattern, you will need to implement dual writes so that all changes apply to both the original and shadow tables.With the Outbox pattern, you create duplicate “shadow” tables for the ones you want to replicate and set the shadow tables to
REPLICA IDENTITY FULL
. You can then use these shadow tables for Materialize instead of the originals. -
Sidecar Pattern
NOTE: With the Sidecar pattern, you will need to keep the sidecar in sync with the source database (e.g., via logical replication or ETL processes).With the Sidecar pattern, you create a separate PostgreSQL instance as an integration layer. That is, in the sidecar instance, you recreate the tables you want to replicate, setting these tableswith
REPLICA IDENTITY FULL
. You can then use the sidecar for Materialiez instead of your primary database.
What if my table contains data types that are unsupported in Materialize?
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 astext
, the implicit ordering of the original PostgreSQLenum
type is not preserved; instead, Materialize will sort values astext
. -
money
: When decoded astext
, resultingtext
value cannot be cast back tonumeric
, since PostgreSQL adds typical currency formatting to the output.
See also: PostgreSQL considerations.