The Spock Extension

The Spock extension provides multi-master (active-active) replication for PostgreSQL versions 15, 16, and 17. The extension leverages the pgLogical and Open Source project as a solid foundation to build upon for this enterprise-class extension.

You build, install, and create the extension like any other PostgreSQL extension. For more information about using PostgreSQL extensions, visit the website (opens in a new tab).

For more information about the Spock extension, visit the pgEdge Github repo (opens in a new tab).

Conflict-Free Delta-Apply Columns (Conflict Avoidance)

Logical multi-master replication can get into trouble when running sums (such as a YTD balance). Unlike other solutions, we do NOT have a special data type for this; any numeric data type works (including numeric, float, double precision, int4, int8, etc).

This feature is powerful and simple in its implementation:

• When a conflicting update occurs on a log_old_value column:
  • First, the old value for that column is captured to the WAL files.
  • Second, the new value comes in the transaction to be applied to a subscriber.
  • Before the new value overwrites the old value, a delta value is created from the above two steps and it is correctly applied.

Note that on a conflicting transaction, the delta column will be correctly applied. This conflict resolution strategy applies to non-delta columns (normally last-update-wins). As a special safety-valve feature, if you ever need to re-set a log_old_value column, you can temporarily alter the column to log_old_value is false.

Conflict Configuration options

Some Spock behaviors can be configured using configuration options that can be either set in postgresql.conf or via ALTER SYSTEM SET.

• spock.conflict_resolution Sets the resolution method for any detected conflicts between local data and incoming changes. Accepted values are:

  • last_update_wins - the version of data with newest commit timestamp is kept (this can be either local or remote version).

• spock.conflict_log_level Sets the log level for reporting detected conflicts when spock.conflict_resolution is set to anything other than error. The default is LOG. If the parameter is set to a value lower than log_min_messages, resolved conflicts are not written to the server log.

  The primary use for this setting is to suppress logging of conflicts. The possible values (opens in a new tab) are the same as for the log_min_messages PostgreSQL setting.

• spock.batch_inserts Tells Spock to use a batch insert mechanism if possible. The batch mechanism uses PostgreSQL internal batch insert mode which is also used by the COPY command. The default is on.