RisingWave as a PostgreSQL foreign data wrapper
A foreign data wrapper in PostgreSQL allows you to directly virtualize data stored in an external database as a local external table, also known as a foreign table. This tutorial will demonstrate how to interact between PostgreSQL and RisingWave. In this example, RisingWave will use CDC (Change Data Capture) to extract data from PostgreSQL, and analyze it using a materialized view. Then PostgreSQL will directly retrieve the computation results stored in RisingWave.
PUBLIC PREVIEW
This feature is in the public preview stage, meaning it’s nearing the final product but is not yet fully stable. If you encounter any issues or have feedback, please contact us through our Slack channel. Your input is valuable in helping us improve the feature. For more information, see our Public preview feature list.
Prerequisites
The following demo needs to be completed under two psql
connections. One connection links to PostgreSQL, providing the source data for analysis and obtaining the analysis results from RisingWave. In our demo, the command to connect to PostgreSQL is psql -h localhost -p 5432 -d myd -U postgresuser
, with the password being postgrespw
. The other connection links to RisingWave to establish a job for analyzing the data. In our demo, the command to connect to RisingWave is psql -h localhost -p 4566 -d dev -U root
, with no password required. You can choose to exit from psql
and log in to the other database when you need to operate in another database. Alternatively, you can use tmux
to open two terminals simultaneously, connecting to the respective databases with psql
.
- The PostgreSQL used supports the
postgres_fdw
extension. - Both PostgreSQL and RisingWave are accessible from each other.
- Both of the users in PostgreSQL (
postgresuser
in this demo) and in RisingWave (root
in this demo) have the necessary permissions to create tables and materialized views.
Prepare data in PostgreSQL
The following commands create a table in PostgreSQL and insert data into it.
Analyze data in RisingWave
The following command creates a table in RisingWave. This table will use the native CDC connector to synchronize the data of the Person table from PostgreSQL, and then create a materialized view to analyze the ingested data.
Query result in PostgreSQL using FDW
The following command creates a foreign table in PostgreSQL to connect to RisingWave and query the materialized view. The first four commands prepare the remote access of postgres_fdw
. You can check the PostgreSQL’s doc here for more details.
NOTE
Currently, write operations to RisingWave through a foreign data wrapper are not supported. The data in the foreign table is read-only.
Differences between sinking to Postgres and using FDW in Postgres
There are two main methods to interact between RisingWave and PostgreSQL: sinking data to PostgreSQL and utilizing a foreign data wrapper of PostgreSQL to access data in RisingWave. The table below provides a summary of the differences between these two methods. Your choice between these methods will depend on your specific requirements, data architecture, and performance considerations.
Aspect | Sinking to PostgreSQL | Using PostgreSQL FDW to access data |
---|---|---|
Data Access | Data is physically stored in PostgreSQL | Data is physically stored in RisingWave |
Performance | Potential latency for RisingWave to write to PostgreSQL | Potential latency when reading data from RisingWave |
Message Delivery Guarantee | At-least-once while sinking into PostgreSQL tables | Exactly-once for MVs and the data is not moved |
Extra Requirement | None | Requires the postgres_fdw extension and involves more setup steps |
Was this page helpful?