Setting Up Streams with PostgreSQL

In the following section, we'll show you how to set up a Stream and configure your destination settings to use PostgreSQL.

Streams Settings

On your QuickNode dashboard, navigate to the Streams page by clicking the Streams tab on the left side-panel. After, click the Create Stream button in the top-right corner. You'll be prompted to first configure your Stream settings:

Chain and Network: Begin by selecting a target Chain and Network from the list of supported chains and networks. You can check the supported chains and network here
Dataset: Once you've chosen the network and chain, proceed to select a Dataset that defines the type of data you intend to stream. See the full list of supported data schemas here.
Stream name: A stream name will be populated by default but you can modify this if needed.
Region: By default, the region most closest to you will be populated, however, you can modify this if needed.
Batch size: Configure the batch size for your Stream. By default this will be set to 1 (for minimal latency), however you can update this if needed.
Stream start: By default, your stream will start at the tip of the chain (e.g., latest block number), however if you are backfilling (retrieving historical data), you can update this value to start from a specific block number.
Stream end: If you want your Stream to end a specific block number, update this value. Otherise, by default the Stream won't end.

Reorg Handling

Streams can be configured to handle reorgs. You can enable the Latest block delay and Restream on reorg settings to properly manage reorgs to your preferences. Check out the Reorg handling page to learn more about Reorgs and how to properly manage them.

PostgreSQL Data Schema

When setting up PostgreSQL as your destination, you'll need to define a data schema in PostgreSQL. Below is an example of what the PostgreSQL table schema might look like:

CREATE TABLE "blast-mainnet" (
    block_number bigint,
    network character varying,
    stream_id uuid NOT NULL,
    data jsonb NOT NULL,
    CONSTRAINT "blast-mainnet_pkey" PRIMARY KEY (block_number, network)
);

In this schema, each message is inserted as a separate row into the data column. The primary key is structured to accommodate updates in the event of a reorganization (reorg) of the data.

Some considerations:

The primary key includes both block_number and network to ensure that each entry remains unique and updates during reorgs can be efficiently managed.
The batch size configuration determines the number of messages inserted at once. Note that the data column does not store arrays of messages; each message is inserted as a standalone row.

Destination Settings

To initiate the stream creation process through PostgreSQL, follow these steps:

Destination: Select the destination type as PostgreSQL. It will open a new sections for adding destination settings.
Host: Input the PostgreSQL host URL (e.g., postgresql-instance.example.com)
Port: Input the port number for the host URL (e.g., 5432)
Username & Password: Input your PostgreSQL username and Password
Database & Table: Input the PostgreSQL database and table you want to Streams to deliver too. This database and table should already be created before Streaming too.
Retry wait period - Determine the time for which it should wait to retry to stream the data again.
Pause stream after - Determine the number of tries after which the stream should should pause. When we exhaust retries, we would terminate your stream that you’d be able to resume anytime the delivery setbacks are resolved.
Use SSL: Check mark the Use SSL checkbox if your service requires this setting (If not selected, you will see an error while trying to create a stream if PostgreSQL service requires you to use SSL).

After configuring all the settings according to your preferences, click on the Test destination button located in the bottom. This action will test the destination and confirm it can be delivered to. Then, click Create a Stream in the bottom-right corner.

Setting Up Streams with PostgreSQL