> ## Documentation Index
> Fetch the complete documentation index at: https://docs.flowx.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Partitioning & archiving

> Improving data management using data partitioning and the archival processes.

## Overview

> [!TIP]
>
> This page covers partitioning and archiving for the **FlowX.AI Engine**, which partitions **process instances** in Oracle or PostgreSQL. Integration Designer partitions **workflow instances** separately, in MongoDB, and is configured on its own. See [Integration Designer setup, Workflow partitioning](https://docs.flowx.ai/6.x/setup-guides/integration-designer-setup.md#workflow-partitioning-configuration).

Partitioning and archiving are data management strategies used to handle large volumes of data efficiently. They improve database performance, manageability, and storage optimization. By dividing data into partitions and archiving older or less frequently accessed data, organizations can ensure better data management, quicker query responses, and optimized storage use.

## Partitioning

Partitioning is the process of dividing a large database table into smaller, more manageable pieces, called partitions. Each partition can be managed and queried independently. The primary goal is to improve performance, enhance manageability, and simplify maintenance tasks such as backups and archiving.

Partitions can be created per day, week or month. This means that a partition ID is computed at insert time for each row of process\_instance and related tables.

Afterwards, a retention period can be setup (eg: 3 partitions). A flowx engine-driven cron job (with configurable start interval) will check if there is any partition which needs to be archived and will perform the necessary actions.

### Benefits of partitioning

* **Improved Query Performance**: By scanning only relevant partitions instead of the entire table.
* **Simplified Maintenance**: Easier to perform maintenance tasks like backups and index maintenance.
* **Data Management**: Better data organization and management by dividing data based on specific criteria such as date, range, or list.

> [!NOTE]
>
> Database Compatibility: Oracle Database and PostgreSQL.

## Archiving

Archiving involves moving old data from the primary tables to separate archive tables. This helps in reducing the load on the primary tables, thus improving performance and manageability.

### Benefits of archiving

* **Storage Optimization**: Archived data can be compressed, saving storage space.
* **Performance Improvement**: Reduces the volume of data in primary tables, leading to faster query responses.
* **Historical Data Management**: Maintains historical data in a separate, manageable form.

## Partitioning and archiving in Oracle Database

### Oracle Database partitioning

**1. Concept**

Oracle Database [**partitioned tables**](https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/partition-concepts.html#GUID-6D369646-16AF-487B-BF32-5F6569D27C8A) are utilized, allowing for efficient data management and query performance.

**2. Implementation**

Each partition can be managed and queried independently.

Example: The `process_instance` table can be partitioned by day, week, or month.

**3. Benefits**

Improved query performance by scanning only relevant partitions.

Simplified maintenance tasks like backups and archiving.

### Oracle Database archiving

**1. Process**

Detaching the partition from the main table.

Converting the detached partition into a new table named `archived_${table_name}_${interval_name}_${reference}` for example: `archived_process_instance_monthly_2024_03` .

> [!NOTE]
>
> The DATE is in the format `yyyy-MM-dd` if the interval is `DAY`, or in the format `yyyy-MM` if the interval is `MONTH`, or in the format `yyyy-weekOfYear` if the interval is `WEEK`.

**2. Steps**

Identify partitions eligible for archiving based on retention settings.

Detach the partition from the main table.

Create a new table with the data from the detached partition.

Optionally compress the new table to save space.

**3. Benefits**

Manages historical data by moving it to separate tables, reducing the load on the main table.

Compression options (OFF, BASIC, ADVANCED) further optimize storage.

> [!NOTE]
>
> Oracle offers several compression options—OFF, BASIC, and ADVANCED—that optimize storage. Each option provides varying levels of data compression, impacting storage savings and performance differently.
>
> * **OFF**: No compression is applied.
> * **BASIC**: Suitable for read-only or read-mostly environments, it compresses data without requiring additional licenses.
> * **ADVANCED**: Offers the highest level of compression, supporting a wide range of data types and operations. It requires an Advanced Compression license and provides significant storage savings and performance improvements by keeping data compressed in memory.
>
> For more details, you can refer to Oracle's [**Advanced Compression documentation**](https://www.oracle.com/database/advanced-compression/#rc30related).

## PostgreSQL archiving

**1. Process**

Creating a new table for the partition being archived.

Moving data from the main table to the new archive table (in batches).

> [!NOTE]
>
> Since here the DB has more work to do than just changing some labels (actual data insert and delete vs relabel a partition into a table, for Oracle Database) the data move is batched and the batch size is configurable.

**2. Steps**

Identify partitions eligible for archiving based on retention settings.

Create a new table following this naming convention: `archived__${table_name}__${interval_name}_${reference}`, example: *archived\_\_process\_instance\_\_weekly\_2024\_09* .

> [!TIP]
>
> The new table is created (same name format as for Oracle Database: `archived__${table_name}__${interval_name}_${reference}`) and data is moved from the primary table here.

Configure the batch size for data movement to control the load on the database.

**3. Benefits**

Efficiently manages historical data.

Batch processing allows for better control over the archiving process and system performance.

> [!NOTE]
>
> Differences from Oracle DBs:
>
> Archiving involves actual data movement (insert and delete operations), unlike Oracle Databases where it is mainly a relabeling of partitions.
>
> The batch size for data movement is configurable, allowing fine-tuning of the archiving process.

***

## Native PostgreSQL partitioning

> [!NOTE]
>
> **Available starting with FlowX.AI 5.9.5**

On PostgreSQL, the engine's runtime tables are converted to **native PostgreSQL partitioning**. The nine tables below become `RANGE(partition_id)` partitioned tables with a composite primary key of `(id, partition_id)`:

`process_instance` · `token_instance` · `incident` · `embedded_subprocess_instance` · `process_instance_debug` · `process_instance_initiator` · `process_instance_snapshot` · `process_inst_swimlane_owner` · `process_instance_param_values_backup`

The conversion is a database migration that runs automatically the first time the engine starts on the new version. Oracle deployments are unaffected.

### Who this affects

> [!NOTE]
>
> **Only deployments that already had partitioning enabled.** The migration checks whether this deployment has ever partitioned data, and skips the conversion entirely if it has not. If you never enabled `FLOWX_DATA_PARTITIONING_ENABLED`, there is no conversion, no lock and no extra disk use.

> [!WARNING]
>
> **The conversion runs only once.** If the migration skips because partitioning was never enabled, it is recorded as complete for that database and does not run again. Enabling partitioning after you upgrade does not convert the tables to native PostgreSQL partitioning.

### What the conversion costs

The conversion rewrites each table in full, which is why it is skipped where it would bring no benefit. While it runs, expect:

| | |
| - | - |
| **Table locking** | Each table takes an `ACCESS EXCLUSIVE` lock from the start of its rewrite until it finishes. |
| **Disk** | Roughly twice a table's size in additional space while that table is being rewritten. |
| **WAL** | A large volume of write-ahead log. |

Two things limit the impact. Each table is committed on its own, so its lock is released as soon as that table is done rather than being held across all nine. And the migration is resumable: if startup is interrupted, the next run skips the tables that already converted and continues from where it stopped.

> [!WARNING]
>
> **The conversion is one way.** Once a table is natively partitioned it stays that way. Nothing in the migration converts the tables back, and the reverse path is untested.

> [!NOTE]
>
> This is separate from steady-state storage, which does not grow. Once converted, the engine writes straight into a partitioned table instead of writing to one large table and copying rows into archive tables, so the ongoing footprint is no larger than before.

### How archiving changes

Once tables are natively partitioned, archiving detaches a partition rather than copying rows out of it, using `DETACH CONCURRENTLY`. That makes PostgreSQL archiving behave much like Oracle's, where archiving is mainly a relabelling rather than a data move, so the batched insert-and-delete described under [PostgreSQL archiving](#postgresql-archiving) no longer applies.

This is controlled by `flowx.data.partitioning.archiving.postgres-native`, which defaults to **`true`**. Setting it to `false` returns archiving to the previous batched behaviour, but it does not undo the conversion: the tables remain natively partitioned.

## Daily operations

Once set up, the partitioning and archiving process involves the following operations:

**1. Partition ID Calculation**

The `partition_id` is automatically calculated based on the configured interval (DAY, WEEK, MONTH).
Example for daily partitioning: `2024-03-01 13:00:00` results in `partition_id = 124061`. See the [**Partition ID calculation**](#partition-id-calculation) section.

**2. Retention Management**

Data older than the configured retention interval becomes eligible for archiving and compressing.

**3. Archiving process**

A cron job checks for eligible partitions.

Eligible partitions are archived and optionally compressed.

The process includes deactivating foreign keys, creating new archive tables, moving data references, reactivating foreign keys, and dropping the original partition.

**4. Space management**

Archived tables remain in the database but occupy less space if compression is enabled.

**Recommendation**: to free up space, consider moving archived tables to a different database or tablespace. Additionally, you have the option to move only process instances or copy definitions depending on your needs.

> [!WARNING]
>
> **Partitions that still contain unfinished process instances are not archived.** The archiving job detaches a partition only when every process instance in it has reached a finished state: `FINISHED`, `FINISHED_WITH_ERROR`, `FAILED`, `EXPIRED`, `ABORTED`, `DISMISSED`, or `TERMINATED`. A single instance still in `CREATED`, `STARTED`, or `ON_HOLD` blocks the entire partition from being archived; the partition is retried on the next archiving run.
>
> To keep partitions archivable:
>
> * **Set process expiry** so instances don't stay unfinished indefinitely. See [Timer Expressions](https://docs.flowx.ai/6.x/docs/building-blocks/node/timer-events/timer-expressions.md) for setting up process expiration in FlowX Designer.
> * **Turn on the stuck-instance termination sweep** (`FLOWX_DATA_PARTITIONING_ARCHIVING_STUCKTERMINATE_ENABLED`) to terminate instances older than the retention window that would otherwise block archiving. See [Configuring partitioning and archiving](#configuring-partitioning-and-archiving) below.
>
> Archiving removes process instance data from the working data set, making it no longer available in FlowX.

> [!NOTE]
>
> Future schema updates or migrations will not affect archived tables. They retain the schema from the moment of archiving.

## Configuring partitioning and archiving

> [!NOTE]
>
> The Partitioning and Archiving feature is optional and can be configured as needed.

> [!TIP]
>
> When starting a new version of the process-engine, we recommend manually executing the setup SQL commands from Liquibase, as they may take more time. After setup, all existing information will go into the initial partition.

Partitioning and archiving are configured under the `flowx.data.partitioning` block on the process-engine. Archiving settings — including the scheduler that detaches and archives old partitions — live in the nested `archiving` block:

```yaml theme={"dark"}
flowx:
  data:
    partitioning:
      enabled: false        # master switch for data partitioning
      interval: MONTH        # DAY, WEEK, MONTH
      premake-cron-expression: "0 0 23 * * ?"   # creates upcoming partitions ahead of the rollover
      premake-partitions-ahead: 2               # future partitions kept created ahead of the current one
      archiving:
        enabled: false       # runs the cron job that detaches and archives old partitions
        cronExpression: "0 0 1 * * ?"   # daily at 01:00
        retention-intervals: 3          # intervals kept in the FlowX database before archiving
        detached-partition-compression: OFF   # OFF, BASIC, ADVANCED (Oracle only)
        moved-data-batch-size: 5000           # PostgreSQL only
        postgres-native: true                 # PostgreSQL only, see Native PostgreSQL partitioning
        stuck-terminate:
          enabled: false                      # terminate stuck instances so archiving isn't blocked
          cron-expression: "0 0 3 * * ?"      # runs before the archiving sweep
          batch-size: 100
      backfill:
        enabled: false                        # one-shot backfill of partition_id on legacy rows
        batch-size: 250
        sleep-between-batches-ms: 500
      org-id-backfill:
        enabled: false                        # one-shot backfill of organization_id on legacy rows
```

The equivalent environment variables:

| Environment Variable | Description | Default Value | Possible Values |
| - | - | - | - |
| `FLOWX_DATA_PARTITIONING_ENABLED` | Master switch for data partitioning. | `false` | `true`, `false` |
| `FLOWX_DATA_PARTITIONING_INTERVAL` | Interval contained in a partition. | `MONTH` | `DAY`, `WEEK`, `MONTH` |
| `FLOWX_DATA_PARTITIONING_PREMAKECRONEXPRESSION` | Cron expression for the job that creates upcoming partitions in advance. | `0 0 23 * * ?` (daily at 23:00) | Cron expression |
| `FLOWX_DATA_PARTITIONING_PREMAKEPARTITIONSAHEAD` | Number of future partitions kept created ahead of the current one. | `2` | Integer values |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_ENABLED` | Runs the cron job that detaches and archives old partitions. | `false` | `true`, `false` |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_CRONEXPRESSION` | Cron expression for the archiving scheduler. | `0 0 1 * * ?` (daily at 01:00) | Cron expression |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_RETENTIONINTERVALS` | Number of intervals retained in the FlowX database before archiving. | `3` | Integer values (e.g., `1`, `2`, `3`) |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_DETACHEDPARTITIONCOMPRESSION` | Compression for archived (detached) partitions (Oracle only). | `OFF` | `OFF`, `BASIC`, `ADVANCED` |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_MOVEDDATABATCHSIZE` | Batch size for moving data (PostgreSQL only). | `5000` | Integer values (e.g., `1000`, `5000`) |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_POSTGRESNATIVE` | Archives natively partitioned tables with `DETACH CONCURRENTLY` (PostgreSQL only). See [Native PostgreSQL partitioning](#native-postgresql-partitioning). | `true` | `true`, `false` |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_STUCKTERMINATE_ENABLED` | Terminates stuck process instances so unfinished data doesn't block archiving. | `false` | `true`, `false` |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_STUCKTERMINATE_CRONEXPRESSION` | Cron for the stuck-instance termination sweep. Runs before the archiving cron. | `0 0 3 * * ?` (daily at 03:00) | Cron expression |
| `FLOWX_DATA_PARTITIONING_ARCHIVING_STUCKTERMINATE_BATCHSIZE` | Batch size for the stuck-instance termination sweep. | `100` | Integer values |
| `FLOWX_DATA_PARTITIONING_BACKFILL_ENABLED` | One-shot backfill of `partition_id` on legacy rows created before partitioning was turned on. Turn off once it drains. | `false` | `true`, `false` |
| `FLOWX_DATA_PARTITIONING_BACKFILL_BATCHSIZE` | Batch size for the `partition_id` backfill. | `250` | Integer values |
| `FLOWX_DATA_PARTITIONING_BACKFILL_SLEEPBETWEENBATCHESMS` | Pause between backfill batches, in milliseconds. | `500` | Integer values |
| `FLOWX_DATA_PARTITIONING_ORGIDBACKFILL_ENABLED` | One-shot backfill of `organization_id` on legacy rows. Optional on self-hosted; can remain `false`. | `false` | `true`, `false` |

> [!NOTE]
>
> Set `FLOWX_DATA_PARTITIONING_INTERVAL` to the same value on the process-engine and Integration Designer. A mismatch desynchronizes archive alignment between the two services. See [Integration Designer setup, Workflow partitioning](https://docs.flowx.ai/6.x/setup-guides/integration-designer-setup.md#workflow-partitioning-configuration) for the Integration Designer settings.

> [!WARNING]
>
> **Upgrading from 5.1.x?** The configuration changed. The standalone `SCHEDULER_DATA_PARTITIONING_ENABLED` and `SCHEDULER_DATA_PARTITIONING_CRON_EXPRESSION` variables no longer exist — the archiving scheduler now lives under `flowx.data.partitioning.archiving`. The archiving scheduler also defaults to **off** (`archiving.enabled: false`), whereas the old scheduler defaulted to on. See [FlowX Engine partitioning migration](https://docs.flowx.ai/5.9/migrating-from-5.1-lts/engine-partitioning.md) for the full mapping.

> [!NOTE]
>
> Compression for archived (detached) partitions is available only for Oracle DBs.

> [!NOTE]
>
> The batch size setting for archiving data is available only for PostgreSQL DBs.

## Logging information

Partitioning and archiving actions are logged in two tables:

* `DATA_PARTITIONING_LOG`: For tracking archived partitions.

![Data partitioning log table](https://s3.eu-west-1.amazonaws.com/docx.flowx.ai/post41/partitioning_log.png)

* `DATA_PARTITIONING_LOG_ENTRY`: For logging SQL commands executed for archiving.

![Data partitioning log entry table](https://s3.eu-west-1.amazonaws.com/docx.flowx.ai/post41/partitioning_log_entry.png)

## Enabling partitioning and Elasticsearch indexing strategy

When partitioning is enabled, the Elasticsearch indexing strategy must also be enabled and configured on FlowX Engine setup.

> [card]
>
> **Why?**
>
> * When archiving process instances, data from Elasticsearch must be deleted, not just the cache but also indexed keys (e.g., those indexed for data search on process instances).

### Elasticsearch indexing configuration

Check the Elasticsearch indexing setup here:

> [card] [Elasticsearch indexing setup](./configuring-elasticsearch-indexing/)

The partitioning configuration must be aligned with the configuration extracted from the Kafka Elasticsearch Connector, especially with the following environment variables, so the intervals are similar:

### Index partitioning

* `transforms.routeTS.topic.format: "process_instance-${timestamp}"`: This value must start with the index name defined in the process-engine config: flowx.indexing.processInstance.index-name. In this example, the index name is prefixed with "process\_instance-" and appended with a timestamp for dynamic index creation. For backward compatibility, the prefix must be "process\_instance-". However, backward compatibility is not strictly required here.
  yaml

* `transforms.routeTS.timestamp.format: "yyyyMMdd"`: This format ensures that timestamps are consistently represented and easily parsed when creating or searching for indices based on the process instance start date. You can adjust this value as needed (e.g., for monthly indexes, use "yyyyMM"). However, note that changing this format will cause existing indexed objects to remain unchanged, and update messages will be treated as new objects, indexed again in new indices. It is crucial to determine your index size and maintain consistency.

Check the following Kafka Elasticsearch Connector configuration example for more details:

> [card] [Kafka Elasticsearch Connector](https://docs.flowx.ai/6.x/setup-guides/flowx-engine-setup-guide/configuring-elasticsearch-indexing/elasticsearch-indexing.md#step-3-configure-the-elasticsearch-sink-connector)

## Technical details

### Partition ID calculation

* The `partition_id` format follows this structure: `<LEVEL || YEAR || BIN_ID_OF_YEAR>`. This ID is calculated based on the start date of the `process_instance`, the partition interval, and the partition level.
  * `LEVEL`: This represents the "Partitioning level," which increments with each change in the partitioning interval (for example, if it changes from `DAY` to `MONTH` or vice versa).
  * `YEAR`: The year extracted from the `process_instance` date.
  * `BIN_ID_OF_YEAR`: This is the ID of a bucket associated with the `YEAR`. It is created for all instances within the selected partitioning interval. The maximum number of buckets is determined by the partitioning frequency:
    * **Daily**: Up to 366 buckets per year
    * **Weekly**: Up to 53 buckets per year
    * **Monthly**: Up to 12 buckets per year

#### Calculation example

For a timestamp of `2024-03-01 13:00:00` with a daily partitioning interval, the `partition_id`would be `124061`:

* `1`: Partitioning Level (`LEVEL`)
* `24`:  Year - 2024 (`YEAR`)
* `061`: Bucket per configuration (61st day of the year)

### Archived tables

* Naming format: `archived__${table_name}__${interval_name}_${reference}`. Examples:
  * archived\_\_process\_instance\_\_monthly\_2024\_03
  * archived\_\_process\_instance\_\_weekly\_2024\_09
  * archived\_\_process\_instance\_\_daily\_2024\_03\_06


## Related topics

- [Update environment variables](https://docs.flowx.ai/release-notes/v5.x/v5.3.0-december-2025/migrating-from-v5.2-to-v5.3/update-environment-variables-5.3.md)
- [Overview](https://docs.flowx.ai/release-notes/v5.x/v5.3.0-december-2025/migrating-from-v5.2-to-v5.3/migration-overview.md)
- [FlowX.AI 5.2.0 Release Notes](https://docs.flowx.ai/release-notes/v5.x/v5.2.0-november-2025/v5.2.0-november-2025.md)
- [Integration Designer setup](https://docs.flowx.ai/6.x/setup-guides/integration-designer-setup.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.