PostgreSQL (Edge)
On This Page
Hevo Edge supports the following variations of PostgreSQL as a Source:
Handling Source Partitioned Tables
PostgreSQL allows partitioning tables into smaller segments, with each segment stored as a separate table in the database. For PostgreSQL versions 10 through 17, Hevo Edge supports loading data ingested from partitioned tables. For versions 10 to 12, the ingested data is always loaded into individual Destination tables. However, for versions 13 through 17, the loading behavior is determined by the value of the publish_via_partition_root parameter in the specified publication key.
The following table explains Edge’s behavior for loading data ingested from partitioned tables based on the value of the publish_via_partition_root parameter:
| PostgreSQL Version | Value of publish_via_partition_root |
Hevo Behavior |
|---|---|---|
| 10 - 12 | Not applicable | The Source table’s partitions are treated as separate entities, and data from them is loaded into individual tables at the Destination. |
| 13 - 17 | TRUE | The partitioned table is treated as a single entity, and its data is loaded into a single Destination table. |
| FALSE | The Source table’s partitions are treated as separate entities, and data from them is loaded into individual tables at the Destination. |
Note: For versions 13 and above, by default, a publication is created with publish_via_partition_root set to FALSE. Refer to the Create a publication for your database tables section in the respective Source documentation to create one with the value set to TRUE.
Handling Toast Data
PostgreSQL can efficiently store large amounts of data in columns using The Oversized-Attribute Storage Technique (TOAST). When a column’s value exceeds approximately 8 KB (the default page size), PostgreSQL compresses the data and may store it in a separate TOAST table. Columns managed in this way are referred to as TOASTed columns.
Hevo Edge identifies TOASTed columns in the ingested data and replicates data from these columns into your Destination tables using a merge operation. This operation updates existing records and adds new ones.
Note: In Edge, Hevo does not replicate data from TOASTed columns if your Pipeline loads data in Append mode. In this mode, existing records are not updated; only new records are added.
Resolving Data Loss in Paused Pipelines
For log-based Edge Pipelines created with any variant of the PostgreSQL Source, the data to be replicated is identified from the write-ahead logs (WAL) by the publications created on the database tables. Hence, disabling the log-based Pipeline may lead to data loss, as the corresponding WAL segment may have been deleted. The log can get deleted due to the expiry of its retention period or limited storage space in the case of large log files.
If you notice a data loss in your Edge Pipeline after enabling it, resync the Pipeline. The Resync Pipeline action restarts the historical load for all the active objects in your Pipeline, thus recovering any lost data.
Note: The re-ingested data does not count towards your quota consumption and is not billed.