Performance

Updates Recording Overhead

Recording updates in E-Maj log tables necessarily has an impact on the duration of these updates. The global impact of this logging on a given process depends on numerous factors, including:

The proportion of update activity relative to the overall process.
The intrinsic performance characteristics of the storage subsystem that hosts the log tables.

However, the E-Maj updates recording overhead is generally limited to a few percent. This overhead should be compared to the duration of potential intermediate saves that E-Maj avoids.

E-Maj Rollback Duration

The duration of an E-Maj rollback depends on several factors, such as:

The number of updates to cancel.
The intrinsic characteristics of the server and its storage hardware, as well as the load generated by other activities hosted on the server.
Triggers or foreign keys on tables processed by the rollback operation.
Contention on tables at lock set time.

To estimate the duration of an E-Maj rollback, you can use the emaj_estimate_rollback_group() and emaj_estimate_rollback_groups() functions.

Optimizing E-Maj Operations

Here is some advice to optimize E-Maj operations.

Use Tablespaces

Creating tables in tablespaces located on dedicated disks or file systems is a way to more efficiently distribute access to these tables. To minimize the impact of log table access on application table access, the E-Maj administrator has two ways to use tablespaces for log tables and indexes:

By setting a specific default tablespace for the session before creating table groups, log tables and indexes are created by default in this tablespace without any additional action.

Alternatively, through parameters set when calling the emaj_assign_table(), emaj_assign_tables(), and emaj_modify_table() functions, it is also possible to specify a tablespace for any log table or log index.

Declare Foreign Keys as DEFERRABLE

Foreign keys can be explicitly declared as DEFERRABLE at creation time. If a foreign key links two tables belonging to different table groups, or if one of them does not belong to any table group, and if the foreign key has no ON DELETE or ON UPDATE clause, it is recommended to declare it as DEFERRABLE. This avoids the need to drop and recreate it during subsequent E-Maj rollbacks. The foreign key checks for updated rows are simply deferred to the end of the rollback function execution, once all log tables are processed. This generally significantly speeds up the rollback operation.

Modify Memory Parameters

Increasing the value of the work_mem parameter when performing an E-Maj rollback may improve performance.

If foreign keys need to be recreated by an E-Maj rollback operation, increasing the value of the maintenance_work_mem parameter may also help.

If the E-Maj rollback functions are called directly in SQL, these parameters can be set at the session level using statements such as:

SET work_mem = <value>;
SET maintenance_work_mem = <value>;

If the E-Maj rollback operations are executed by a web client, it is also possible to set these parameters at the function level, as a SUPERUSER:

ALTER FUNCTION emaj._rlbk_tbl(emaj.emaj_relation, BIGINT, BIGINT, INT, BOOLEAN, BOOLEAN)
      SET work_mem = <value>;
ALTER FUNCTION emaj._rlbk_session_exec(INT, INT)
      SET maintenance_work_mem = <value>;