Optimizing Query Modes for Objects

Last updated on Jan 09, 2024
On This Page

Starting Release 2.19, Hevo has stopped supporting XMIN as a query mode for all variants of the PostgreSQL Source. As a result, you will not be able to create new Pipelines using this query mode. This change does not affect existing Pipelines. However, you will not be able to change the query mode to XMIN for any objects currently ingesting data using other query modes.

Note: This feature is currently available for relational database Sources, such as SQL Server, MySQL, and PostgreSQL.

If the objects ingested in Full Load for a Pipeline are higher than 10% of the billable events, an OPTIMIZE QUERY MODE button is provided in the Smart Assist widget at the top of the Pipeline Overview page to configure the query mode of the objects in your Pipeline, to avoid higher ingestion and load costs. In the image below, we are selecting Generic PostgreSQL Source database as an example.

Read Query modes to understand how Hevo fetches your data.

Note: You can click the Close (close icon) to remove the Smart Assist widget from the Pipeline Overview page. Once the banner is closed, it will not be displayed for the next 30 days. However, you can change the query mode at any point in time.

Pipeline Overview Page

Perform the following steps to optimize the query modes for the objects in your Pipelines:

  1. Do one of the following:

    • In the Smart Assist widget, click OPTIMIZE QUERY MODE, to configure the query mode for the Full Load objects in your database.


    • Select the objects using the Pipeline Objects section of the Pipeline Overview page:

      1. Select the check boxes for the required objects individually or select the check box above the objects list to select all the objects.


      2. Click MORE, and then click Update Query Mode from the drop-down.


  2. In the Configure Objects dialog, select the query mode for the objects and provide the associated parameters. You can also update only some of the objects at this time.

    Query Modes

    Note: The UPDATE TO CHANGE TRACKING option is specific to SQL Server variants.

    Depending on the query mode, specify the following parameters:

    Query Mode Parameter
    Full Load None.
    Delta-Timestamp - Timestamp Column Name
    - Milliseconds to wait before ingesting a row. (Optional.
    Change Data Capture - Incrementing Column Name
    - Timestamp Column Name
    - Milliseconds to wait before ingesting a row. (Optional)
    Unique Incrementing Append Only - One or more Unique Column Name(s). The selected columns must not contain any null values.
  3. Click SAVE CHANGES.

Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Jan-10-2024 NA Removed mentions of XMIN as a query mode.
Apr-07-2023 NA Enhanced and reorganized the content for clarity.
Feb-10-2023 NA Created as a new document from the FAQ, How do I change the query mode for my Pipeline? Added details of changing the query mode for multiple objects at once.

Tell us what went wrong