F.49. pg_ilm — Information Lifecycle Management#

F.49. pg_ilm — Information Lifecycle Management
F.49. pg_ilm — Information Lifecycle Management
Prev UpAppendix F. Additional Supplied Modules and ExtensionsHome Next

F.49. pg_ilm — Information Lifecycle Management #

F.49.1. Overview
F.49.2. Rationale
F.49.3. Installation
F.49.4. Quick start
F.49.5. Configuration
F.49.6. Usage
F.49.7. Constraints
F.49.8. Compatibility
F.49.9. Notes

Version: pg_ilm--1.0 (beta)

F.49.1. Overview #

pg_ilm is a data lifecycle management extension for Tantor SE intended to compute archiving recommendations for regular tables and leaf partitions.

Recommendations are formed on the basis of:

  • Physical storage condition

  • Activity level (write load)

  • Archiving Rule Configurations

The extension divides the process into three independent levels:

  1. Life cycle policy (rules).

  2. Computation the next permissible transition (recommendation).

  3. Physical execution via pg_archive.

This separation provides deterministic, explainable, and step-by-step data state changes without performing implicit or complex operations.

F.49.2. Rationale #

Transitions between storage states depend not only on logical rules, but also on the physical table characteristics:

  • Actual tablespace.

  • Current access method (heap or columnar).

  • relation type (regular_table, partitioned_parent, partition_leaf).

  • Constraints pg_archive and pg_columnar.

  • The possibility of direct movement or the need to recreate the table.

Because of this, pg_ilm implements the transition model as a constrained state machine rather than a binary hot/cold data model.

F.49.3. Installation #

The pg_ilm extension is available starting from Tantor SE 18.3. Before installation, you must prepare a Tantor SE instance, server configuration, and supporting extensions.

F.49.3.1. Prerequisites #

Before installation the following must be available:

  • pg_archive — as an executive layer

  • pg_columnar — for transitions to columnar storage

  • pg_cron — for scheduling background tasks

  • pg_partman — for servicing partitioning

F.49.3.2. Setting shared_preload_libraries #

Before starting the server, you need to include background libraries in shared_preload_libraries in postgresql.conf: 

shared_preload_libraries = 'pg_archive_bgw,pg_cron,pg_partman_bgw'

The shared_preload_libraries parameter is applied only at server start. After changing postgresql.conf, an instance restart is required. In Tantor SE, parameters of this class relate to server startup parameters and are not applied through a simple configuration reload.

F.49.3.3. Preparing directories for tablespace #

If you plan to use separate table spaces to host data (for example, to separate hot and cold storage), you must first create directories on the file system to house them.

Typical example: 

mkdir -p /path/to/ilm_hot_ts
mkdir -p /path/to/ilm_cold_ts
chown postgres:postgres /path/to/ilm_hot_ts /path/to/ilm_cold_ts
chmod 700 /path/to/ilm_hot_ts /path/to/ilm_cold_ts

Usernames and paths depend on the specific installation. The directories must be accessible by the system user under whom the Tantor SE server is running.

After preparing the directories, table spaces are created using SQL commands: 

CREATE TABLESPACE ilm_hot_ts  LOCATION '/path/to/ilm_hot_ts';
CREATE TABLESPACE ilm_cold_ts LOCATION '/path/to/ilm_cold_ts';

F.49.3.4. Preparing schemas #

Before creating extensions, it is recommended to create schemas in advance in which the pg_partman and pg_archive service objects will be located: 

CREATE SCHEMA IF NOT EXISTS partman;
CREATE SCHEMA IF NOT EXISTS archive;

Using separate schemas allows you to isolate service objects from user data and simplifies maintenance.

F.49.3.5. Extension creation order #

Extensions must be created in the following order: 

CREATE EXTENSION IF NOT EXISTS pg_cron CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
CREATE EXTENSION IF NOT EXISTS pg_archive SCHEMA archive CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_ilm CASCADE;

The order is important for the following reasons:

  1. pg_cron must be available before setting up runtime tasks.

  2. pg_partman must be set before enabling partitioned parent servicing logic.

  3. pg_archive must be set before creating executor-path.

  4. pg_ilm is installed last because it relies on dependent components that are already available.

When installing pg_archive, the required extension pg_columnar is automatically installed if it is not already installed in the database.

F.49.3.6. Installation verification #

After creating extensions, it is recommended to verify:

  • Availability of necessary extensions: 

    SELECT extname
FROM pg_extension
WHERE extname IN ('pg_archive', 'pg_columnar', 'pg_cron', 'pg_ilm', 'pg_partman')
ORDER BY extname;

    Result: 

       extname   
-------------
 pg_archive
 pg_columnar
 pg_cron
 pg_ilm
 pg_partman

  • Availability of partman and archive schemes: 

    SELECT nspname
FROM pg_namespace
WHERE nspname IN ('archive', 'partman')
ORDER BY nspname;

    Result: 

     nspname 
---------
 archive
 partman

  • Availability of required tablespace, for example: 

    SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace
WHERE spcname IN ('ilm_hot_ts', 'ilm_cold_ts')
ORDER BY spcname;

    Result: 

       spcname   |                     pg_tablespace_location                      
-------------+-----------------------------------------------------------------
 ilm_cold_ts | /path/to/ilm_cold_ts
 ilm_hot_ts  | /path/to/ilm_hot_ts
(2 rows)

  • Correctness of the shared_preload_libraries value: 

    SELECT DISTINCT lib
FROM pg_settings,
    unnest(string_to_array(setting, ',')) AS lib
WHERE name = 'shared_preload_libraries'
AND trim(lib) IN ('pg_archive_bgw', 'pg_cron', 'pg_partman_bgw')
ORDER BY lib;

    Result: 

          lib       
----------------
 pg_archive_bgw
 pg_cron
 pg_partman_bgw
(3 rows)

  • Successful start of the instance after a configuration change. 

    SELECT pg_postmaster_start_time();

    The result is a successful output of the instance start date and time.

F.49.3.7. Runtime initialization #

After installing extensions and preparing tablespace, you need to initialize the runtime configuration pg_ilm.

Typical example (configuration with recommendation computation 4 times per day): 

SELECT ilm.init(
  p_partman_interval             => '1 day',
  p_partman_retention            => '180 days',
  p_stats_schedule               => '0 */6 * * *',
  p_cleanup_schedule             => '0 3 * * *',
  p_partman_maintenance_schedule => '0 */6 * * *',
  p_archive_rules_schedule       => '0 */6 * * *'
);

In this example:

  • p_partman_interval — partitioning interval (partition size) is 1 day.

  • p_partman_retention — statistics history is stored for 180 days.

  • p_stats_schedule — statistics are collected every 6 hours.

  • p_cleanup_schedule — service data is cleared daily at 03:00.

  • p_partman_maintenance_schedule — partitioning maintenance is performed every 6 hours.

  • p_archive_rules_schedule—archiving rules are calculated and applied every 6 hours (4 times a day).

The values ​​of p_partman_interval and p_partman_retention are not fixed requirements. They determine how the partitioning will be done and how long the pg_ilm service statistics will be stored.

For a typical production scenario, a daily partitioning interval and storing several months of history is usually more realistic than the short intervals used in test and demo scenarios.

The frequency of task execution and statistics storage period can be changed depending on the load profile, data volume and requirements for the depth of historical analysis.

During initialization, the pg_ilm configuration is saved, background jobs are created or redefined, and the runtime environment is prepared for collecting statistics and calculating recommendations.

F.49.3.8. Result #

After installation is complete, the system should be ready for:

  • Creating archiving rules.

  • Collecting activity statistics.

  • Recommendation computation.

  • dry-run and performing transitions through pg_archive.

F.49.4. Quick start #

This section provides a typical example of initially enabling pg_ilm in a production configuration.

The example is based on the following assumptions:

  • Activity statistics are collected 4 times per day.

  • Recommendations are formed based on already accumulated data.

  • Data are considered cooling candidates after 30 days without significant write activity.

  • The recommendation execution is performed explicitly, by administrator decision.

The section is divided into two independent scenarios:

Each scenario can be used independently of the other.

F.49.4.1. Scenario for a regular table #

Below is a minimal self-contained example for a regular table.

  1. Preparing a test schema: 

    CREATE SCHEMA IF NOT EXISTS t;

    The schema is used for demonstration purposes only. In a real system, a rule can be created for a table in any application schema.

  2. Creating a table: 

    CREATE TABLE t.r1 (
    id bigint,
    ts timestamptz,
    payload text
);

    The table is created in the standard row-oriented format (heap) and, unless explicitly stated otherwise, is placed in the effective tablespace by default.

  3. Data filling: 

    INSERT INTO t.r1
SELECT
    g,
    now() - (g || ' seconds')::interval,
    repeat('x', 100)
FROM generate_series(1, 100000) g;

    This step is for example purposes only. In the production system, pg_ilm analyzes existing tables and statistics on their activity.

  4. Creating an archiving rule: 

    SELECT ilm.archive_rule_upsert(
    p_target_table       => 't.r1',
    p_target_kind        => 'regular_table',
    p_target_state       => 'cold_columnar',
    p_cold_access_method => 'columnar',
    p_cold_tablespace    => 'ilm_cold_ts',
    p_cold_after         => '30 days',
    p_keep_indexes       => true
);

    In this example, the rule means the following:

    • The rule object is the table t.r1.

    • The rule applies as to regular_table, without type autodetermination.

    • The target state is cold_columnar.

    • The cold step requires a transition to columnar.

    • The final placement must be done in ilm_cold_ts.

    • A table is considered a candidate for cooling after 30 days of no significant write activity.

    • Indexes must be stored within the supported backend-path.

    Example of execution result: 

    NOTICE:  Archive rule applied: target=t.r1, target_kind=regular_table, target_state=cold_columnar, cold_access_method=columnar, warm_tablespace=NULL, cold_tablespace=ilm_cold_ts
 archive_rule_upsert 
---------------------
                   1
(1 row)

    The value in the resulting string is the identifier of the created or updated rule. The NOTICE message shows the resulting normalized rule configuration that will be used when computing recommendations.

  5. Collecting statistics: 

    SELECT ilm.collect_stats_snapshot();

    Collecting statistics records the current state of relation activity, including write load. This allows you to calculate recommendations not based on assumptions, but on the current accumulated data at the time of calculation.

    In a production scenario, this step is usually not performed manually, since statistics collection starts automatically according to the schedule specified via ilm.init(...).

  6. Getting recommendations: 

    SELECT
    rule_id,
    target_table,
    current_state,
    recommended_next_state,
    recommendation_status,
    prepared_call_sql
FROM ilm.recommend_archive_actions(NULL, now(), FALSE)
WHERE target_table = 't.r1';

    The request includes:

    • rule_id — identifier of the rule within which the recommendation was built.

    • target_table — relation to which the recommendation applies.

    • current_state is a specific current physical state.

    • recommended_next_state is the next valid transition.

    • recommendation_status — recommendation status.

    • prepared_call_sql — prepared SQL call for execution.

    Typical result: 

     rule_id | target_table | current_state | recommended_next_state | recommendation_status | prepared_call_sql
---------+--------------+---------------+------------------------+-----------------------+-------------------------------------------------------------
       1 | t.r1         | warm_row      | cold_columnar          | recommend             | SELECT * FROM ilm.execute_recommendations(...)
(1 row)

    Interpretation of the result:

    • recommend— a valid next step was found for the object.

    • skip — transition is not required or is currently impossible.

    • prepared_call_sql — can be used as a ready-made call form for manual execution.

  7. Executing recommendation: 

    SELECT *
FROM ilm.execute_recommendations(
    p_target_table := 't.r1'::regclass,
    p_dry_run := FALSE
);

    When executed, pg_ilm selects the appropriate executor-path and calls the low-level procedure pg_archive. Depending on the current state, this could be:

    • Transfer to another tablespace.

    • Change access method.

    • recreate-path preserving the relation name.

    • Combined transition with preservation of table structure.

  8. Verifying result: 

    SELECT
    't.r1'::regclass AS relation_name,
    ilm._relation_access_method('t.r1'::regclass) AS access_method,
    ilm._relation_tablespace_name('t.r1'::regclass) AS tablespace_name;

    Expected result:

    • access_method = columnar

    • tablespace_name = ilm_cold_ts

    This means that the table is brought to the target cold state in terms of physical location and access method.

F.49.4.2. Scenario for partitioned table #

Below is a complete self-contained example for partitioned_parent and its leaf partitions using pg_partman.

  1. Creating a partitioned parent: 

    CREATE TABLE t.p1 (
    id bigint,
    ts timestamptz NOT NULL,
    payload text
) PARTITION BY RANGE (ts);

    The Parent table specifies the overall data structure and partitioning key. The pg_ilm rule is created specifically on the parent, and recommendations are then calculated for leaf partitions.

  2. Registration in pg_partman: 

    SELECT partman.create_parent(
    p_parent_table => 't.p1',
    p_control      => 'ts',
    p_interval     => '1 day',
    p_premake      => 1
);

    This step is required to hand over partitioning maintenance to the pg_partman extension.

    The example parameters mean:

    • p_parent_table — parent relation that will be serviced.

    • p_control — partitioning column.

    • p_interval = '1 day' — daily interval for range partitions.

    • p_premake = 1 — one future section is supported in advance.

    After registration, pg_partman gets the ability to automatically create subsequent sections and perform their maintenance on a schedule.

  3. Creating a leaf partition (if required manually): 

    CREATE TABLE t.p1_p20260101
PARTITION OF t.p1
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

    This step is for illustration purposes only. In typical operation, leaf partitions are usually created and maintained automatically via pg_partman.

  4. Data filling: 

    INSERT INTO t.p1_p20260101
SELECT g, now() - interval '40 days', repeat('x', 100)
FROM generate_series(1, 10000) g;

    The example uses data that is already “old” enough to generate a cooling recommendation at the 30 days threshold.

  5. Creating a rule for parent: 

    SELECT ilm.archive_rule_upsert(
    p_target_table       => 't.p1',
    p_target_kind        => 'partitioned_parent',
    p_target_state       => 'cold_columnar',
    p_cold_access_method => 'columnar',
    p_cold_tablespace    => 'ilm_cold_ts',
    p_cold_after         => '30 days'
);

    The rule sets:

    • parent relation t.p1 as a lifecycle control point.

    • Application to partitioned_parent.

    • Target state of leaf partitions: cold_columnar.

    • Final access method: columnar.

    • Final tablespace: ilm_cold_ts.

    • Transition threshold: 30 days without significant activity.

    Example of execution result: 

    NOTICE:  Archive rule applied: target=t.p1, target_kind=partitioned_parent, target_state=cold_columnar, cold_access_method=columnar, warm_tablespace=NULL, cold_tablespace=ilm_cold_ts
 archive_rule_upsert 
---------------------
                   1
(1 row)

    As with a regular table, NOTICE shows the normalized total of the rule, and the return value is the ID of the record in archive_rules.

  6. Collecting statistics: 

    SELECT ilm.collect_stats_snapshot();

    Collecting statistics allows you to record the activity state of parent and leaf partitions at recommendation computation time. For partitioned tables, this is especially important, since the decision is made not for the parent as a container, but for individual leaf-partitions.

  7. Getting recommendations: 

    SELECT
    rule_id,
    target_table,
    current_state,
    recommended_next_state,
    recommendation_status,
    prepared_call_sql
FROM ilm.recommend_archive_actions(NULL, now(), FALSE)
WHERE target_table LIKE 't.p1%';

    The query returns recommendations already for specific leaf partitions that match the parent rule.

    Example result: 

     rule_id |     target_table      | current_state | recommended_next_state | recommendation_status | prepared_call_sql
---------+------------------------+---------------+------------------------+-----------------------+-------------------------------------------------------------
       1 | t.p1_p20260101        | warm_row      | cold_columnar          | recommend             | SELECT * FROM ilm.execute_recommendations(...)
(1 row)

    This means that:

    • Rule rule_id = 1 was applied to leaf partition t.p1_p20260101.

    • The current state of leaf is defined as warm_row.

    • The next valid transition is cold_columnar.

    • The recommendation is feasible.

    • An SQL call has been prepared for it.

  8. Executing recommendation: 

    SELECT * FROM ilm.execute_recommendations(
    p_target_table := 't.p1_p20260101'::regclass,
    p_dry_run := FALSE
);

    In this case, exactly the transition will be performed for the leaf partition, not for the parent relation as a whole. The low-level path is selected depending on its current state and the target cold-state.

  9. Verifying result: 

    SELECT
    't.p1_p20260101'::regclass AS relation_name,
    ilm._relation_access_method('t.p1_p20260101'::regclass) AS access_method,
    ilm._relation_tablespace_name('t.p1_p20260101'::regclass) AS tablespace_name;

    Expected result:

    • access_method = columnar

    • tablespace_name = ilm_cold_ts

    This confirms that the leaf partition has been transferred to the final cold state.

F.49.4.3. Note #

Both scenarios demonstrate the full cycle of pg_ilm:

  1. Accumulation and recording of statistics.

  2. Recommendation computation.

  3. Explicitly perform a valid transition.

  4. Checking the physical result.

In real operation, all stages except execution are usually performed automatically according to a schedule. It is recommended to monitor the implementation of recommendations manually or through external orchestration.

Future versions plan to support a fully automatic execution mode with customizable policies.

F.49.5. Configuration #

This section describes the model of states and transitions, runtime configuration parameters, methods for managing archiving rules, and restrictions that affect the admissibility of recommendations.

F.49.5.1. State model #

pg_ilm uses a finite set of physical data storage states defined by a combination of two characteristics:

  • access method (heap or columnar)

  • data placement (effective tablespace)

Main states:

  • hot_row — row-oriented storage (heap) in the base or target (hot) tablespace.

  • warm_row — row-oriented storage (heap) with possible movement to the target (hot) tablespace.

  • warm_columnar — columnar storage in the target (hot) tablespace.

  • cold_columnar — columnar storage in the target (archive) tablespace.

The state of the relation is determined by the actual values ​​of the access method and tablespace, not just the rule configuration.

F.49.5.2. Transition model #

pg_ilm does not try to immediately transfer the relation to the final state, but only calculates the next valid step. This is due to the fact that the admissibility of a transition is determined not only by the target policy, but also by the current physical state of the relation.

The following valid cooling transitions are used for the current model:

  • hot_row → warm_row — transfer of row-oriented relation to a cold tablespace without changing the access method.

  • hot_row → warm_columnar — conversion to columnar without final cold-placement.

  • warm_row → cold_columnar — conversion of a relation already located in a cold tablespace into columnar.

  • warm_columnar → cold_columnar — final translation of columnar relation into cold tablespace.

For regular table and partition leaf, the set of valid states is common, but the executor-path is different. In particular, some columnar transitions for partition leaf are implemented through recreate-path, and not through direct movement.

pg_ilm always returns only the closest valid transition. Therefore, a rule with a target state of cold_columnar can lead to a recommendation of warm_row or warm_columnar, if such a step is correct at this stage.

F.49.5.3. Configuration Methods #

System configuration is performed at two levels:

  1. Global runtime configuration (ilm.init, ilm.apply_config_changes, ilm.save_config).

  2. Archiving rules (ilm.archive_rule_upsert and related rule management functions).

F.49.5.4. Runtime configuration #

The runtime configuration specifies:

  • Statistics collection schedule.

  • Service data cleaning schedule.

  • Maintenance schedule pg_partman.

  • Schedule for background recalculation of recommendations.

  • Partitioning and storage intervals are ilm.stats_history.

F.49.5.4.1. ilm.init(...) #

The ilm.init(...) function is the main initialization point. It saves the configuration, configures pg_partman for ilm.stats_history, and overrides pg_cron background jobs.

Parameters:

  • p_partman_interval — partitioning interval ilm.stats_history.

  • p_partman_retention — statistics storage period.

  • p_stats_schedule — cron schedule for collecting statistics.

  • p_cleanup_schedule — cron cleaning schedule.

  • p_partman_maintenance_schedule — cron schedule for pg_partman maintenance.

  • p_archive_rules_schedule — cron schedule for the background launch of ilm.run_archive_rules().

Example: 

SELECT ilm.init(
  p_partman_interval             => '1 day',
  p_partman_retention            => '180 days',
  p_stats_schedule               => '0 */6 * * *',
  p_cleanup_schedule             => '0 3 * * *',
  p_partman_maintenance_schedule => '0 */6 * * *',
  p_archive_rules_schedule       => '0 */6 * * *'
);
F.49.5.4.2. ilm.apply_config_changes(...) #

The ilm.apply_config_changes(...) function is used to change an existing runtime configuration without re-describing the initialization logic. She:

  1. Stores new values ​​in ilm.config.

  2. Reinitializes the pg_partman configuration for ilm.stats_history.

  3. Redefines cron jobs according to new schedules.

The function is convenient when changing schedules in the working system.

F.49.5.4.3. ilm.save_config(...) #

The ilm.save_config(...) function updates the values ​​in ilm.config, but does not itself recreate cron tasks and does not reinitialize pg_partman. It is useful as a low-level API if you want to store a configuration separately without immediately using it.

F.49.5.4.4. Runtime configuration constraints #

  • Schedules must be specified in the format cron.

  • Collecting statistics too frequently increases the load on the system.

  • Too rare — reduces the relevance of recommendations.

  • The storage period for statistics should be selected taking into account the required depth of historical analysis.

  • The runtime parameters only affect the accumulation of data and recommendation computation, but do not include automatic execution of archive transitions.

F.49.5.5. Managing archiving rules #

The following API objects are used to manage rules:

  • ilm.archive_rule_upsert(...) — create or update a rule.

  • ilm.archive_rule_delete(...) — delete the rule.

  • ilm.archive_rule_set_enabled(...) — enable or disable the rule.

  • ilm.archive_rules_list — view the current rules configuration in a normalized form.

F.49.5.6. Rule configuration (ilm.archive_rule_upsert) #

The ilm.archive_rule_upsert(...) function creates a new rule or updates an existing rule for target_table.

F.49.5.6.1. Main parameters #

  • p_target_table — target object of type regclass, parameter required

  • p_target_kind — object type:

    • regular_table

    • partitioned_parent

    • auto — determined dynamically during recommendation computation

  • p_target_state — target state:

    • hot_row

    • warm_row

    • warm_columnar

    • cold_columnar

  • p_control_column — partitioning column for partitioned_parent

  • p_max_age — maximum section age, used for parent rules

  • p_cold_after — inactivity interval after which the relation can be considered a candidate for cooling

  • p_min_age — minimum age of relation/partition for consideration

  • p_idle_threshold — acceptable write-inactivity threshold

  • p_min_table_size_bytes — the minimum size of a relation, starting from which the recommendation generally makes sense

  • p_pg_archive_schema — scheme in which pg_archive is installed, default archive

  • p_target_schema — destination scheme, if the scheme needs to be changed during archiving

  • p_warm_tablespace — tablespace for target hot allocation

  • p_cold_access_method — access method for cold-path, valid values ​​are logically limited by policy

  • p_cold_tablespace — tablespace of final cold placement

  • p_keep_indexes — whether to save indexes when recreate-path

  • p_keep_publication — whether to keep relation participation in publications

  • p_enabled — whether the rule is enabled

F.49.5.6.2. Full example #
SELECT ilm.archive_rule_upsert(
    p_target_table           => 't.r1',
    p_target_kind            => 'regular_table',
    p_target_state           => 'cold_columnar',
    p_control_column         => NULL,
    p_max_age                => NULL,
    p_cold_after             => '30 days',
    p_min_age                => '0 days',
    p_idle_threshold         => '30 days',
    p_min_table_size_bytes   => 0,
    p_pg_archive_schema      => 'archive',
    p_target_schema          => NULL,
    p_warm_tablespace        => 'ilm_hot_ts',
    p_cold_access_method     => 'columnar',
    p_cold_tablespace        => 'ilm_cold_ts',
    p_keep_indexes           => true,
    p_keep_publication       => false,
    p_enabled                => true
);
F.49.5.6.3. Built-in archive_rule_upsert constraints #

The function validates some combinations of parameters at the rule creation stage.

In particular:

  • The states warm_columnar and cold_columnar require p_cold_access_method = 'columnar'.

  • With p_target_kind = 'auto' the relation type will be determined during recommendation computation.

  • If warm_row or cold_columnar is not set to p_cold_tablespace, NOTICE will be returned, which means cooling-transition will save the relation in the current tablespace.

  • If warm_columnar is not set to p_warm_tablespace, NOTICE will be returned, which means the relation will remain in the current tablespace.

Thus, some errors are prevented already at the rule declaration level, and some are recorded as acceptable, but non-ideal behavior through NOTICE.

F.49.5.7. Viewing rules (ilm.archive_rules_list) #

The ilm.archive_rules_list view returns a list of rules in an easy-to-read form.

It additionally shows:

  • resolved_target_kind — actually resolved relation type.

  • enabled — whether the rule is enabled.

  • Cold and hot placement parameters.

  • Time thresholds and structure preservation flags.

Example: 

SELECT *
FROM ilm.archive_rules_list
ORDER BY target_table;

This view is recommended as the operator's primary way to view current rules.

F.49.5.8. Enabling and disabling a rule (ilm.archive_rule_set_enabled) #

The ilm.archive_rule_set_enabled(...) function only changes the enabled flag and updates updated_at.

Disable example: 

SELECT ilm.archive_rule_set_enabled('t.r1'::regclass, false);

Re-enable example: 

SELECT ilm.archive_rule_set_enabled('t.r1'::regclass, true);

This function is convenient when you need to temporarily remove a rule from recommendation computation without deleting it.

F.49.5.9. Deleting a rule (ilm.archive_rule_delete) #

The ilm.archive_rule_delete(...) function deletes the rule by target_table.

Example: 

SELECT ilm.archive_rule_delete('t.r1'::regclass);

Removing a rule stops further recommendation computation for the specified object.

F.49.5.10. Rule configuration constraints #

  • To go to columnar, the pg_columnar extension must be installed.

  • The rule for partitioned_parent should be set to the parent table, recommendations are then built for leaf partitions.

  • For partitioned_parent, the schema constraint requires p_control_column and p_max_age.

  • The presence of foreign keys may block recreate-path.

  • identity columns (GENERATED AS IDENTITY) are not supported during conversion.

  • Not all indexes can be recovered for columnar.

  • Not every combination of target_state, access method and tablespace results in an immediately executable transition: in this case, pg_ilm will offer an intermediate step or return skip or blocked.

F.49.5.11. Default behavior #

If the parameter is not explicitly specified, the default value defined in the API is used:

  • p_target_kind = 'auto'

  • p_target_state = 'cold_columnar'

  • p_cold_after = '30 days'

  • p_min_age = '0 minutes'

  • p_idle_threshold = '30 days'

  • p_min_table_size_bytes = 0

  • p_pg_archive_schema = 'archive'

  • p_cold_access_method = 'columnar'

  • p_keep_indexes = true

  • p_keep_publication = false

  • p_enabled = true

These values ​​are convenient for test and demo scenarios, but in a production configuration they usually need to be explicitly adjusted.

The pg_ilm configuration must take into account the actual limitations of the physical storage layer and the available executor-paths, since these are the ones that determine the permissibility of transitions and the sequence of recommendations.

F.49.6. Usage #

This section discusses the use of pg_ilm after the runtime has already been initialized and the archiving rules have been created.

F.49.6.1. Flamegraph activity #

Before analyzing pg_ilm recommendations, it is advisable to evaluate the actual activity of physical relations for the period of interest. To do this, the extension provides a set of flamegraph functions built on the basis of the accumulated history of ilm.stats_history.

The flamegraph tool is intended for:

  • Identification of the “hottest” relations based on write activity.

  • Preliminary assessment of which relations should not be considered as candidates for cooling.

  • Comparisons of heap- and columnar-relation activity in one report.

  • Visual analysis of the accumulated load for a fixed period.

Flamegraph reports are built only on physical relations. Parent partition tables (relkind = 'p') are excluded from the reports, and only real stored relations, including leaf partitions, are included in the output.

F.49.6.2. Flamegraph operating principle #

Flamegraph relies on periodic snapshot entries in ilm.stats_history. For each relation, deltas between successive snapshots are calculated, after which activity for the selected period is aggregated.

For visualization, a normalized scale of 20 characters is used. The higher the accumulated write activity of a relation relative to other relations in the report, the longer the value in the flame column.

It is important to consider that flamegraph:

  • This is not a direct recommendation for archiving.

  • Does not replace the result of recommend_archive_actions(...).

  • Used as an auxiliary operator tool before recommendation analysis.

F.49.6.3. Main flamegraph functions #

F.49.6.3.1. ilm.flame(p_interval) #

Basic function for generating a report for an arbitrary interval. 

SELECT schemaname, relname, access_method, flame, relation_size_bytes, total_blocks_dirtied
FROM ilm.flame('30 days'::interval)
ORDER BY flame DESC, relation_size_bytes DESC;

The function returns:

  • schemaname, relname — relation name.

  • access_method — current access method (heap or columnar).

  • flame — normalized visual activity bar up to 20 characters long.

  • relation_size_bytes is the current physical size of the relation.

  • total_blocks_dirtied — total number of modified blocks for the period.

  • dirty_ratio — relative intensity of changes taking into account the size of the relation.

  • snapshot_count, period_start, period_end — computation context.

F.49.6.3.2. Brief functions for typical periods #

Ready-made shells are available for typical periods:

  • ilm.flame_d() — for 1 day.

  • ilm.flame_m() — for 1 month.

  • ilm.flame_k() — for 3 months.

  • ilm.flame_h() — for 6 months.

  • ilm.flame_y() — for 1 year.

  • ilm.flame_p(p_days) — for an arbitrary number of days.

Examples: 

SELECT * FROM ilm.flame_d();
SELECT * FROM ilm.flame_m();
SELECT * FROM ilm.flame_p(30);

F.49.6.4. Auxiliary statistics functions #

For more low-level analysis also available:

  • ilm.get_stats_for_period(...) — aggregated deltas by relation for period.

  • ilm.get_table_activity(...) — extended report with access method, relation size and dirty ratio.

Example: 

SELECT schemaname, relname, access_method, relation_size_bytes, total_blocks_dirtied, dirty_ratio
FROM ilm.get_table_activity('30 days'::interval)
ORDER BY dirty_ratio DESC, relation_size_bytes DESC;

This report is convenient if you do not need a visual activity bar, but a numerical interpretation of the intensity of changes.

F.49.6.5. Practical flamegraph use #

Typical operator scenario for flamegraph use:

  1. Select an analysis period that matches the expected life cycle of the data.

  2. Create a flamegraph report.

  3. Exclude relations with high write activity from candidates for cooling.

  4. Proceed to recommendation computation via recommend_archive_actions(...). 

SELECT schemaname, relname, access_method, flame, total_blocks_dirtied
FROM ilm.flame_p(30)
ORDER BY flame DESC, relation_size_bytes DESC;

Example output: 

 schemaname |        relname         | access_method |        flame         | total_blocks_dirtied
------------+------------------------+---------------+----------------------+----------------------
 sales      | orders_2026_01         | heap          | ████████████████████ |                18420
 sales      | orders_2026_02         | heap          | ████████████████     |                13280
 billing    | invoices_current       | columnar      | ████████████         |                 7420
 events     | event_log_p202601      | heap          | █████████            |                 4180
 archive    | invoices_2025_q4       | columnar      | ██████               |                 1330
 crm        | customers              | heap          | ████                 |                  420
 archive    | event_log_p202511      | columnar      | █                    |                   35
 public     | reference_countries    | heap          |                      |                    0
(8 rows)

This example shows relations with different activity levels:

  • Actively used heap tables.

  • Actively used and moderately active columnar tables.

  • leaf partitions with different rates of changes.

  • Almost unused and empty relation.

This output is convenient for the initial visual sorting of objects before analyzing recommendations.

F.49.6.6. Flamegraph constraints #

  • Data — the report depends on the quality and regularity of the snapshot in ilm.stats_history.

  • Interpretation — reflects only physical write activity and is a relative metric.

  • Coverage area — only physical relations are considered, without parent tables.

After preliminary analysis of flamegraph, you can proceed to recommendation computation.

F.49.6.7. General workflow #

In general, the work includes four stages:

  1. Accumulation of statistics.

  2. Recommendation computation.

  3. Transition analysis.

  4. Execution.

pg_ilm does not hide intermediate steps.

F.49.6.8. Typical work cycle #

  1. Checking the rules: 

    SELECT *
FROM ilm.archive_rules_list
ORDER BY target_table;

  2. Statistics update: 

    SELECT ilm.collect_stats_snapshot();

  3. Getting recommendations.

    The main operator request for analyzing the state of objects is ilm.recommend_archive_actions(...).

    Function signature: 

    ilm.recommend_archive_actions(
    p_target_table REGCLASS DEFAULT NULL,
    p_now TIMESTAMPTZ DEFAULT now(),
    p_persist_history BOOLEAN DEFAULT TRUE
)

    Parameters:

    • p_target_table — limits the recommendation computation for a specific relation. If the parameter is not specified, all enabled rules are analyzed.

    • p_now is the point in time relative to which the state is calculated and time thresholds are applied.

    • p_persist_history — determines whether the calculation result should be saved in ilm.archive_recommendation_history.

    Return fields:

    • rule_id — identifier of the rule by which the recommendation was built.

    • target_table — relation to which the result belongs.

    • resolved_target_kind is the actual type of the object (regular_table, partition_leaf, etc.).

    • current_state — current physical state of the relation.

    • recommended_next_state is the next valid transition.

    • recommendation_status — calculation result (recommend, skip, blocked).

    • recommendation_reason — explanation of why the recommendation was issued or not issued.

    • prepared_call_sql — prepared SQL call for execution.

    • blocking_constraints — restrictions that prevent automatic execution.

    • query_compatibility_risk — request compatibility risk.

    • access_risk — risk associated with access to data after the change.

    • restore_risk — risk of recovery or return.

    Basic example of a complete calculation: 

    SELECT
    rule_id,
    target_table,
    resolved_target_kind,
    current_state,
    recommended_next_state,
    recommendation_status,
    recommendation_reason,
    prepared_call_sql,
    blocking_constraints,
    query_compatibility_risk,
    access_risk,
    restore_risk
FROM ilm.recommend_archive_actions(NULL, now(), FALSE)
ORDER BY target_table;

    It is recommended to use this query as the main operator report, since it shows not only the recommendation itself, but also its context.

    Example output: 

     rule_id |     target_table      | resolved_target_kind | current_state | recommended_next_state | recommendation_status |               recommendation_reason                |                    prepared_call_sql                     | blocking_constraints | query_compatibility_risk | access_risk | restore_risk
---------+-----------------------+----------------------+---------------+------------------------+-----------------------+----------------------------------------------------+----------------------------------------------------------+----------------------+--------------------------+-------------+-------------
       7 | t.r1                  | regular_table        | warm_row      | cold_columnar          | recommend             | columnar cold placement is recommended             | SELECT * FROM ilm.execute_recommendations(...);          |                      | low                      | low         | high
      12 | t.p1_p20260101        | partition_leaf       | hot_row       | warm_row               | recommend             | cold tablespace move is recommended before columnar conversion | SELECT * FROM ilm.execute_recommendations(...); |                      | low                      | low         | medium
      13 | t.p2_p20260101        | partition_leaf       | warm_columnar | cold_columnar          | blocked               | final cold placement requires cold_tablespace to be configured |                                                          |                      | low                      | low         | medium
      14 | t.r2                  | regular_table        | hot_row       |                        | skip                  | table does not satisfy idle/size thresholds yet    |                                                          |                      | low                      | low         | low
(4 rows)

    Typical usage scenarios:

    To compute recommendations using only one table: 

    SELECT *
FROM ilm.recommend_archive_actions('t.r1'::regclass, now(), FALSE);

    To force a recalculation with recording in history: 

    SELECT *
FROM ilm.recommend_archive_actions(NULL, now(), TRUE)
ORDER BY target_table;

    To analyze only the current state without saving history: 

    SELECT target_table, current_state, recommended_next_state, recommendation_status
FROM ilm.recommend_archive_actions(NULL, now(), FALSE)
ORDER BY target_table;

    It is practically recommended to use the following approach:

    1. First run the query with p_persist_history := FALSE for operational analysis.

    2. If necessary, repeat the calculation with p_persist_history := TRUE if you want to record the state in history.

    3. After this, move on to selecting executable recommendations or dry-run execution.

  4. Interpretation

    • recommend — can be executed.

    • skip — no action required.

    • blocked — constraints exist.

F.49.6.9. Actionable recommendation selection #

The ilm.list_actionable_recommendations(...) function returns only those recommendations that:

  • They have the status recommend.

  • Contains a prepared SQL call (prepared_call_sql).

  • Can be performed without additional constraints.

Function signature: 

ilm.list_actionable_recommendations(
    p_target_table REGCLASS DEFAULT NULL,
    p_target_kind TEXT DEFAULT NULL,
    p_target_state TEXT DEFAULT NULL
)

Parameters:

  • p_target_table — limits selection to a specific relation or leaf partition.

  • p_target_kind — type filter (regular_table, partition_leaf).

  • p_target_state — filter by target next state (warm_row, warm_columnar, cold_columnar).

Returned fields:

  • target_table — relation for which action is prepared.

  • resolved_target_kind — actual object type.

  • recommended_next_state — next lifecycle step.

  • prepared_call_sql — ready SQL call for execution.

Basic example: 

SELECT *
FROM ilm.list_actionable_recommendations();

Filtering examples:

Only for a specific table: 

SELECT *
FROM ilm.list_actionable_recommendations('t.r1'::regclass);

Only for leaf partitions: 

SELECT *
FROM ilm.list_actionable_recommendations(
    p_target_kind => 'partition_leaf'
);

Only transitions to cold layer: 

SELECT *
FROM ilm.list_actionable_recommendations(
    p_target_state => 'cold_columnar'
);

F.49.6.10. Dry-run #

Before actual execution, it is recommended to use dry-run mode: 

SELECT *
FROM ilm.execute_recommendations(
    p_target_table := 't.r1'::regclass,
    p_dry_run := TRUE
);

In dry-run mode:

  • The execution scope is determined.

  • Candidates are displayed.

  • Physical changes are not performed.

F.49.6.11. Working with recommendation history #

The history is stored in ilm.archive_recommendation_history and is used to analyze dynamics.

F.49.6.11.1. Latest recommendations #
SELECT *
FROM ilm.archive_recommendation_history
ORDER BY evaluated_at DESC
LIMIT 50;
F.49.6.11.2. History #

This section is intended to analyze previously computed recommendations and their evolution over time.

History stores a complete snapshot of the result recommend_archive_actions(...) at the time of calculation. Unlike short operator selections, the ilm.archive_recommendation_history table contains all the main context fields:

  • recommendation_id is a unique identifier for the history record.

  • rule_id — the rule by which the calculation was performed.

  • evaluated_at — moment of calculation.

  • target_table — relation to which the recommendation applies.

  • resolved_target_kind is the actual type of the object (regular_table, partition_leaf).

  • current_state — specific current state of the relation.

  • recommended_next_state is the next valid transition.

  • recommendation_status — calculation result (recommend, skip, blocked).

  • recommendation_reason — explanation of the reason.

  • prepared_call_sql — prepared SQL call for execution.

  • blocking_constraints — restrictions that prevent execution.

  • query_compatibility_risk, access_risk, restore_risk — accompanying risk assessment.

Typical history usage scenarios are shown below.

F.49.6.11.3. Basic latest records view #
SELECT *
FROM ilm.archive_recommendation_history
ORDER BY evaluated_at DESC, recommendation_id DESC
LIMIT 50;

This query is used to view the latest computed recommendations in full, without losing context.

Example output: 

 recommendation_id | rule_id |         evaluated_at          |     target_table      | resolved_target_kind | current_state | recommended_next_state | recommendation_status |                     recommendation_reason                      |                                                  prepared_call_sql                                                  | blocking_constraints | query_compatibility_risk | access_risk | restore_risk
-------------------+---------+-------------------------------+-----------------------+----------------------+---------------+------------------------+-----------------------+------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------------------+--------------------------+-------------+-------------
                17 |       3 | 2026-03-29 23:50:00.009702+03 | t.p3_p20260329_234000 | partition_leaf       | hot_row       | warm_row               | recommend             | row-oriented cold placement is recommended                             | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.p3_p20260329_234000'::regclass, p_dry_run := FALSE); |                      | low                      | low         | low
                16 |       7 | 2026-03-29 23:49:00.008033+03 | t.r2                  | regular_table        | warm_row      |                        | skip                  | table already appears to be in target state warm_row                   |                                                                                                                     |                      | low                      | low         | low
                15 |       6 | 2026-03-29 23:49:00.008033+03 | t.r1                  | regular_table        | hot_row       |                        | skip                  | table does not satisfy idle/size thresholds yet                        |                                                                                                                     |                      | low                      | low         | low
                14 |       7 | 2026-03-29 23:46:00.016892+03 | t.r2                  | regular_table        | hot_row       | warm_row               | recommend             | row-oriented cold placement is recommended                             | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.r2'::regclass, p_dry_run := FALSE);                  |                      | low                      | low         | low
(4 rows)
F.49.6.11.4. History for a specific table #
SELECT *
FROM ilm.archive_recommendation_history
WHERE target_table = 't.r1'
ORDER BY evaluated_at DESC, recommendation_id DESC;

This variant allows you to track how the state, status, and recommended next step changed for one relation.

Example output: 

 recommendation_id | rule_id |         evaluated_at          | target_table | resolved_target_kind | current_state | recommended_next_state | recommendation_status |                         recommendation_reason                          |                                         prepared_call_sql                                          | blocking_constraints | query_compatibility_risk | access_risk | restore_risk
-------------------+---------+-------------------------------+--------------+----------------------+---------------+------------------------+-----------------------+------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+----------------------+--------------------------+-------------+-------------
                12 |       6 | 2026-03-29 23:43:00.018068+03 | t.r1         | regular_table        | warm_row      | cold_columnar          | recommend             | columnar cold placement is recommended; execution may recreate directly in cold tablespace | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.r1'::regclass, p_dry_run := FALSE); |                      | low                      | low         | high
                 8 |       6 | 2026-03-29 23:42:00.023564+03 | t.r1         | regular_table        | hot_row       | warm_row               | recommend             | row-oriented cold placement is recommended                             | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.r1'::regclass, p_dry_run := FALSE); |                      | low                      | low         | low
                 1 |       6 | 2026-03-29 23:40:00.027896+03 | t.r1         | regular_table        | hot_row       |                        | skip                  | table does not satisfy idle/size thresholds yet                        |                                                                                                    |                      | low                      | low         | low
(3 rows)
F.49.6.11.5. State change analysis #
SELECT
    target_table,
    current_state,
    recommended_next_state,
    recommendation_status,
    recommendation_reason,
    evaluated_at
FROM ilm.archive_recommendation_history
ORDER BY target_table, evaluated_at DESC, recommendation_id DESC;

This query is convenient for relation lifecycle analysis without overloading with secondary fields.

Example output: 

     target_table      | current_state | recommended_next_state | recommendation_status |                     recommendation_reason                      |         evaluated_at
-----------------------+---------------+------------------------+-----------------------+------------------------------------------------------------------------+-------------------------------
 t.p1_p20260329_233000 | warm_row      | cold_columnar          | recommend             | columnar conversion of an already cold leaf is recommended             | 2026-03-29 23:43:00.018068+03
 t.p1_p20260329_233000 | hot_row       | warm_row               | recommend             | cold tablespace move is recommended before columnar conversion         | 2026-03-29 23:42:00.023564+03
 t.p4_p20260329_233000 | warm_columnar |                        | skip                  | partition t.p4_p20260329_233000 already appears to be in target state warm_columnar | 2026-03-29 23:45:00.009105+03
 t.r2                  | warm_row      |                        | skip                  | table already appears to be in target state warm_row                  | 2026-03-29 23:49:00.008033+03
(4 rows)
F.49.6.11.6. Selecting only executable recommendations from history #
SELECT *
FROM ilm.archive_recommendation_history
WHERE recommendation_status = 'recommend'
ORDER BY evaluated_at DESC, recommendation_id DESC;

This option is used when you need to determine which objects in previous calculations were already ready for execution.

Example output: 

 recommendation_id | rule_id |         evaluated_at          |     target_table      | resolved_target_kind | current_state | recommended_next_state | recommendation_status |                     recommendation_reason                      |                                                  prepared_call_sql                                                  | blocking_constraints | query_compatibility_risk | access_risk | restore_risk
-------------------+---------+-------------------------------+-----------------------+----------------------+---------------+------------------------+-----------------------+------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+----------------------+--------------------------+-------------+-------------
                17 |       3 | 2026-03-29 23:50:00.009702+03 | t.p3_p20260329_234000 | partition_leaf       | hot_row       | warm_row               | recommend             | row-oriented cold placement is recommended                             | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.p3_p20260329_234000'::regclass, p_dry_run := FALSE); |                      | low                      | low         | low
                14 |       7 | 2026-03-29 23:46:00.016892+03 | t.r2                  | regular_table        | hot_row       | warm_row               | recommend             | row-oriented cold placement is recommended                             | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.r2'::regclass, p_dry_run := FALSE);                  |                      | low                      | low         | low
                12 |       6 | 2026-03-29 23:43:00.018068+03 | t.r1                  | regular_table        | warm_row      | cold_columnar          | recommend             | columnar cold placement is recommended; execution may recreate directly in cold tablespace | SELECT * FROM ilm.execute_recommendations(p_target_table := 't.r1'::regclass, p_dry_run := FALSE); |                      | low                      | low         | high
(3 rows)
F.49.6.11.7. Blocking analysis #
SELECT
    target_table,
    recommendation_reason,
    blocking_constraints,
    evaluated_at
FROM ilm.archive_recommendation_history
WHERE recommendation_status = 'blocked'
ORDER BY evaluated_at DESC, recommendation_id DESC;

This query allows you to highlight only those cases where the transition was logically desirable, but could not be performed automatically.

If there are no locks, the selection may be empty. In systems where locks have already accumulated, the typical output looks like this: 

     target_table      |                  recommendation_reason                   |          blocking_constraints           |         evaluated_at
-----------------------+----------------------------------------------------------+-----------------------------------------+-------------------------------
 t.p4_p20260329_233000 | final cold placement requires cold_tablespace to be configured | cold_tablespace is not configured | 2026-03-29 23:45:00.009105+03
(1 row)

History is recommended for use in the following scenarios:

  • Audit relation state changes.

  • Analysis of when an object went from skip to recommend.

  • Diagnostics of blocking causes.

  • Comparison of completed actions with recommendations that preceded execution.

F.49.7. Constraints #

The constraints relate to the architecture of the current version of the extension and the features of the backend components used.

F.49.7.1. Data and storage model #

  • Transitions between states are performed in stages and do not guarantee a direct transition to the target state.

  • Some transitions require intermediate steps (for example, hot_row → warm_row → cold_columnar).

  • The recommendation correctness depends on the completeness of accumulated statistics.

  • If there is insufficient data volume (stats_history), recommendations may be preliminary.

F.49.7.2. Execution constraints #

  • Executing transitions is not completely automatic and requires an explicit call to ilm.execute_recommendations(...).

  • Some transitions may be blocked by pg_archive restrictions (for example, foreign keys, identity columns).

  • Transitions that require changing the access method (heap → columnar) may require recreate operations.

  • In the absence of cold_tablespace, some target states are unattainable.

F.49.7.3. Coverage area #

  • Recommendations are generated only for regular_table and leaf partitions.

  • parent tables are used only as a policy setting point.

  • The logic does not take into account the business semantics of the data, only physical activity and size.

  • flamegraph and statistics reflect only write activity.

F.49.8. Compatibility #

The pg_ilm extension runs as part of Tantor SE and uses a number of dependencies.

F.49.8.1. Supported Versions #

  • Tantor SE 18.3 and higher.

  • Tantor SE compatible statistics mechanisms (pg_stat_*).

F.49.8.2. Dependencies #

For correct operation, the following extensions are required:

  • pg_cron — background job scheduling.

  • pg_partman — partitioning control ilm.stats_history.

  • pg_archive — execution of movement and storage format change operations.

The absence of pg_archive does not block recommendation computation, but makes transition execution impossible.

F.49.8.3. Compatibility constraints #

  • The behavior depends on the implementation of the access method (heap/columnar).

  • Columnar format support is determined by the capabilities of the installed backend.

  • Differences in statistical functions (pg_stat_get_blocks_*) are taken into account dynamically, but may affect the accuracy of the calculations.

  • When upgrading the Tantor SE version, it is recommended to re-check the configuration and schedules.

F.49.9. Notes #

F.49.9.1. General notes #

  • pg_ilm implements the “recommendation-first” model: the system suggests actions, but does not perform them automatically.

  • The administrator controls transitions and can execute them selectively.

  • Prepared SQL calls (prepared_call_sql) should be considered the recommended execution method, but not the only one.

F.49.9.2. Operation #

  • It is recommended to use dry-run before making changes.

  • Transitions should be planned taking into account the load on the system.

  • It is recommended to periodically check the relevance of the rules (archive_rules_list).

F.49.9.3. Functionality evolution #

  • In the current version, automatic execution is not enabled by default.

  • Further development involves the emergence of a customizable fully automatic mode.

  • The state and transition model can be expanded in future versions.

F.49.9.4. Practical recommendations #

  • Use flamegraph as a pre-filter before analyzing recommendations.

  • Do not perform transitions for actively used relations.

  • Consider risks (query_compatibility_risk, restore_risk) when making decisions.

  • Analyze history (archive_recommendation_history) when diagnosing system behavior.

Prev Up Next
F.48. pg_hint_plan — configuring plans of SQL queries in SQL comments Home F.50. pg_ivm — Incremental View Maintenance (IVM) feature for Tantor SE