Pain points of schema changes
Schema changes in PostgreSQL tables, such as adding or deleting columns or modifying data types, are often a source of frustration for developers, data engineers, and other stakeholders. These updates can disrupt workflows and introduce risks, including compatibility issues, broken processes, and new bugs. This can lead to cascading delays in development timelines, hindering the delivery of new features. Without effective tools or automation, manual schema changes can be highly disruptive and inefficient.
Auto schema change in RisingWave
To mitigate these issues, RisingWave now supports auto schema change as a Premium Edition feature when you ingest CDC data from PostgreSQL. This feature enables RisingWave’s table schemas to automatically sync with schema changes made to the source PostgreSQL tables. With auto schema change in RisingWave, you can spend less time manually maintaining and managing data pipelines and more time on other operations.
Availability and licensing
Note that this feature is only available with the premium edition of RisingWave. To learn more about RisingWave Premium, see Everything You Want to Know About RisingWave Premium and RisingWave Premium Edition.
How to enable auto schema change for Postgres CDC sources
Ensure you have a license key for Premium Edition
After launching RisingWave, check that your RisingWave instance has a valid license key by running the following SQL query. If you license key properly set up and valid, it will return t. To learn how to set up your license key, see RisingWave Premium Edition.
SELECT rw_test_paid_tier();
Configure PostgreSQL CDC source
To enable the auto schema change feature, set the auto.schema.change parameter to true when creating your PostgreSQL CDC source. This is an optional parameter when creating a source and only available in Premium Edition.
To learn more about how to ingest data from a PostgreSQL table, see the official documentation.
CREATE SOURCE pg_mydb WITH (
connector = 'postgres-cdc',
hostname = 'localhost',
port = '5432',
username = 'myuser',
password = '123456',
database.name = 'mydb',
auto.schema.change = 'true'
);
Demo: PostgreSQL auto schema change
Set up a table in PostgreSQL
For this example, assume the following table, pg_names, is set up in your PostgreSQL database. You will ingest this table into RisingWave.
id | name
----+---------
1 | Alice
3 | Charlie
2 | Bob
Create a PostgreSQL CDC source
Start a RisingWave instance and configure the Premium Edition license key. To learn about the different methods of quickly deploying RisingWave, see our Quick start guide.
To read data from a PostgreSQL table in RisingWave, you first need to create a source and then create a table. The source will connect to a PostgreSQL database and the table will ingest data from a specific table. This process allows you to easily ingest CDC data from multiple tables.
Step 1: Create a source
Use the CREATE SOURCE command to connect to a PostgreSQL database. Enable the auto schema change feature by setting the auto.schema.change parameter to true.
CREATE SOURCE pg_mydb WITH (
connector = 'postgres-cdc',
hostname = 'localhost',
port = '5432',
username = 'myuser',
password = '123456',
database.name = 'mydb',
auto.schema.change = 'true'
);
Step 2: Create a table
Ingest data from the pg_names PostgreSQL table by using the CREATE TABLE command. Instead of defining the data schema, use * to automatically map all columns of the PostgreSQL table.
CREATE TABLE names (*)
FROM pg_mydb TABLE 'public.pg_names';
If changes are made to the schema of the table in PostgreSQL, this RisingWave table’s schema will change accordingly.
See the schema change
Test this feature by making a change to the data schema of the PostgreSQL table. For instance, you can add a new column to the table in PostgreSQL.
ALTER TABLE simple_table
ADD COLUMN age INT DEFAULT 0;
Then, query from the RisingWave table. The new column will automatically reflected.
SELECT * FROM names;
---
id | name | age
----+---------+-----
2 | Bob | 0
1 | Alice | 0
3 | Charlie | 0
(3 rows)
Likewise, if you delete a column in PostgreSQL, the RisingWave table will reflect those same changes.
Conclusion
With the auto schema change feature, RisingWave eliminates the manual effort traditionally needed for schema updates. You don’t need to worry about modifying dependent workflows or dealing with pipeline disruptions. This feature ensures that it’s easier for you and your data team to manage your streaming data in RisingWave.
