As an architectural limitation, PostgreSQL has a transaction wraparound problem that can cause the system to stop, and in order to avoid this, it is necessary to collect transaction IDs by freezing tuples.

Tuple freezing works by autovacuum or aggressive freeze for tuples (VACUUM FREEZE). However, with autovacuum alone, in a system where transactions are consumed at a high speed, you may encounter transaction ID wraparound problems. This is because autovacuum is slow to prevent collisions with applications and increases in system load. In such a system, it is necessary to perform aggressive freeze for tuples at an appropriate time.

To avoid such problems, it provides the ability to monitor transaction ID usage and to periodically perform an aggressive freeze for tuples. When an FEPCluster is built with monitoring enabled, monitoring of transaction ID usage is enabled by default.

If AlertManager reports Warning "PostgresqlTooManyTxidUsage", it is possible that autovacuum alone is not sufficient to freeze transaction IDs in time. In such cases, consider enabling the periodic feature of aggressive freeze for tuples.

To enable the periodic execution of aggressive freeze for tuples, set spec.fep.freezingTuples.enable in the FEPClsuter custom resource to true. The default for spec.fep.freezingTuples.enable is false.

The following items can be set.

• Schedule

• Execution period

The following example defines an aggressive freeze for tuples for two schedules: Sunday at 1:00 AM for three hours and Wednesday at 2:00 AM for one hour.

spec:
  fep:
    freezingTuples:
      enable: true
      schedule1:
        start: 0 1 * * 0
        executionTime: 10800
      schedule2:
        start: 0 2 * * 3
        executionTime: 3600

When scheduling an aggressive freeze for tuples, specify a time period with low business load when tuple freezing will have minimal impact.

Even after aggressive freeze for tuples is scheduled, continue to monitor the trend in transaction ID usage. If you determine that the freezing process is not keeping up with the schedule, you will need to reconsider the schedule. To collect the statistical information required for the review, enable the extended functionality for aggressive freeze for tuples using the following procedure.

1. Set the shared_preload_libraries parameter in postgresql.conf

   shared_preload_libraries = 'pgx_stat_vacuum_freeze'

2. Execute the following SQL

   SELECT datname FROM pg_database WHERE datallowconn = true;

   Execute the following CREATE EXTENSION for all databases to be output and define the extension.

   CREATE EXTENSION pgx_stat_vacuum_freeze;

For information on tuning the allocation time for aggressive freeze for tuples, refer to the Fujitsu Enterprise Postgres Operation Guide.