Hello Hackers, (CC people involved in the earlier discussion) While considering the implementation of timestamp-based conflict resolution (last_update_wins) in logical replication (see [1]), there was a feedback at [2] and the discussion on whether or not to manage clock-skew at database level. We tried to research the history of clock-skew related discussions in Postgres itself and summarized that at [3].
We also analyzed how other databases deal with it. Based on our research, the other classic RDBMS like Oracle and IBM, using similar timestamp-based resolution methods, do not address clock-skew at the database level. Instead, they recommend using external time synchronization solutions, such as NTP. - Oracle while handling conflicts[2] assumes clocks are synchronized and relies on external tools like NTP for time synchronization between nodes[4]. - IBM Informix, similarly, recommends using their network commands to ensure clock synchronization across nodes[5]. Other postgres dependent databases like EDB-BDR and YugabyteDB provide GUC parameters to manage clock-skew within the database: - EDB-BDR allows configuration of parameters like bdr.maximum_clock_skew and bdr.maximum_clock_skew_action to define acceptable skew and actions when it exceeds[6]. - YugabyteDB offers a GUC max_clock_skew_usec setting, which causes the node to crash if the clock-skew exceeds the specified value[7]. There are, of course, other approaches to managing clock-skew used by distributed systems, such as NTP daemons, centralized logical clocks, atomic clocks (as in Google Spanner), and time sync services like AWS[4]. Implementing any of these time-sync services for CDR seems quite a bit of deviation and a big project in itself, which we are not sure is really needed. At best, for users' aid, we should provide some GUCs based implementation to handle clock-skew in logical replication. The idea is that users should be able to handle clock-skew outside of the database. But in worst case scenarios, users can rely on these GUCs. We have attempted to implement a patch which manages clock-skew in logical replication. It works based on these new GUCs: (see [10] for detailed discussion) - max_logical_rep_clock_skew: Defines the tolerable limit for clock-skew. - max_logical_rep_clock_skew_action: Configures the action when clock-skew exceeds the limit. - max_logical_rep_clock_skew_wait: Limits the maximum wait time if the action is configured as "wait." The proposed idea is implemented in attached patch v1. Thank you Shveta for implementing it. Thanks Kuroda-san for assisting in the research. Thoughts? Looking forward to hearing others' opinions! [1]: https://www.postgresql.org/message-id/CAJpy0uD0-DpYVMtsxK5R%3DzszXauZBayQMAYET9sWr_w0CNWXxQ%40mail.gmail.com [2]: https://www.postgresql.org/message-id/CAFiTN-uTycjZWdp1kEpN9w7b7SQpoGL5zyg_qZzjpY_vr2%2BKsg%40mail.gmail.com [3]: https://www.postgresql.org/message-id/CAA4eK1Jn4r-y%2BbkW%3DJaKCbxEz%3DjawzQAS1Z4wAd8jT%2B1B0RL2w%40mail.gmail.com [4]: https://www.oracle.com/cn/a/tech/docs/technical-resources/wp-oracle-goldengate-activeactive-final2-1.pdf [5]: https://docs.oracle.com/en/operating-systems/oracle-linux/8/network/network-ConfiguringNetworkTime.html [6]: https://www.ibm.com/docs/en/informix-servers/14.10?topic=environment-time-synchronization [7]: https://www.enterprisedb.com/docs/pgd/latest/reference/pgd-settings/#bdrmaximum_clock_skew [8]: https://support.yugabyte.com/hc/en-us/articles/4403707404173-Too-big-clock-skew-leading-to-error-messages-or-tserver-crashes [9]: https://aws.amazon.com/about-aws/whats-new/2023/11/amazon-time-sync-service-microsecond-accurate-time/ [10]: https://www.postgresql.org/message-id/CAJpy0uDCW%2BvrBoUZWrBWPjsM%3D9wwpwbpZuZa8Raj3VqeVYs3PQ%40mail.gmail.com -- Thanks, Nisha
v1-0001-Implements-Clock-skew-management-between-nodes.patch
Description: Binary data
