Partitioning & archiving

​

Overview

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.

​

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

​

Oracle partitioning

​

Oracle archiving

​

Daily operations

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

​

Configuring partitioning and archiving

This section contains environment variables that control the settings for data partitioning and archiving and also for the archiving scheduler. These settings determine how data is partitioned, retained, and managed, including compression and batch processing.

Environment Variable	Description	Default Value	Possible Values
FLOWX_DATA_PARTITIONING_ENABLED	Activates data partitioning.	false	true, false
FLOWX_DATA_PARTITIONING_INTERVAL	Interval for partitioning (the time interval contained in a partition).	MONTH	DAY, WEEK, MONTH
FLOWX_DATA_PARTITIONING_RETENTION_INTERVALS	Number of intervals retained in the FlowX database (for partitioned tables).	3	Integer values (e.g., 1, 2, 3)
FLOWX_DATA_PARTITIONING_DETACHED_PARTITION_COMPRESSION	Enables compression for archived (detached) partitions.	OFF	OFF, BASIC, ADVANCED
SCHEDULER_DATA_PARTITIONING_ENABLED	Activates the cron job for detaching and archiving partitions.	true	true, false
SCHEDULER_DATA_PARTITIONING_CRON_EXPRESSION	Cron expression for the data partitioning scheduler.	0 0 1 * * ? -> every day at 1:00AM	Cron expression (e.g., 0 0 1 * * ?)

​

Logging information

Partitioning and archiving actions are logged in two tables:

• DATA_PARTITIONING_LOG: For tracking detached partitions.
• DATA_PARTITIONING_LOG_ENTRY: For logging SQL commands executed for detachment.

​

Enabling Partitioning and Elasticsearch indexing strategy

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

​

Elasticsearch indexing configuration

Check the Elasticsearch indexing setup here:

Elasticsearch indexing setup

The partitioning configuration must be aligned with the configuration extractred 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:

Kafka Elasticsearch 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_idwould 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