Why Does HVR Activate Take So Much Time on PostgreSQL Sources?

Question

During HVR activate, validating whether the correct supplemental logging is in place can take a significant amount of time. Why does HVR activate take a long time on a PostgreSQL source, and how do I resolve it?

Environment

• HVR 6
• Source: PostgreSQL

Answer

This answer focuses on tables without a primary key, where long activation is most common. In PostgreSQL, the table-level setting REPLICA IDENTITY is required for replication, and you are responsible for configuring it correctly. For more information, see Configuring REPLICA IDENTITY.

The HVR activate step must set certain configuration options on source tables in order to properly replicate the data. This includes the Supplemental Logging component, which sets REPLICA IDENTITY to FULL on source tables when replication actions require more logging for a particular table. This change is normally quick, but it scales with the number of tables. For each table, it requires an ACCESS EXCLUSIVE lock. This is the strictest lock type in PostgreSQL, and no other operation can be performed on that table while the lock is held. In short, if any long-running transaction of any kind (SELECT, UPDATE, DELETE, and so on) involves that table, HVR activate stalls and waits for it to finish.

This can also affect other queries. PostgreSQL executes queries in the order they were issued. Queries that need only an ACCESS SHARE lock (for example, SELECT) can run in parallel. HVR's request for an ACCESS EXCLUSIVE lock creates a query execution barrier, so queries that you issue after activation starts also wait.

To speed up activation, do one of the following:

• Schedule activation during your quiet hours.
• Arrange a window of time during which source tables in the channel are not involved in any long-running transaction.
• Set REPLICA IDENTITY to FULL yourself, directly in PostgreSQL, at a time of your choosing. For the ALTER TABLE statement, see Configuring REPLICA IDENTITY. HVR detects this setting and skips the corresponding steps in the activation process.
• Unselect the Supplemental Logging component in the Activate Replication dialog. You can only do this if the channel was successfully activated previously and no source tables were added to the channel or dropped and recreated since the last activation.

These steps can significantly reduce the time that activation takes.

For a related issue, see Tables Become Read-Only During HVR Activation.