add_reorder_policy()

Add a policy to reorder rows in hypertable chunks

Since 1.2.0

Create a policy to reorder the rows of a hypertable‘s chunks on a specific index. The policy reorders the rows for all chunks except the two most recent ones, because these are still getting writes. By default, the policy runs every 24 hours. To change the schedule, call alter_job and adjust schedule_interval.

You can have only one reorder policy on each hypertable.

For manual reordering of individual chunks, see reorder_chunk.

When a chunk‘s rows have been reordered by a policy, they are not reordered by subsequent runs of the same policy. If you write significant amounts of data into older chunks that have already been reordered, re-run reorder_chunk on them. If you have changed a lot of older chunks, it is better to drop and recreate the policy.

Samples

SELECT add_reorder_policy('conditions', 'conditions_device_id_time_idx');

Creates a policy to reorder chunks by the existing (device_id, time) index every 24 hours. This applies to all chunks except the two most recent ones.

Arguments

The syntax is:

SELECT add_reorder_policy(
    hypertable = '<hypertable_name>',
    index_name = '<index_name>',
    if_not_exists = true | false,
    initial_start = <timestamptz>,
    timezone = '<timezone>'
);

Name	Type	Default	Required	Description
hypertable	REGCLASS	-	✔	Hypertable to create the policy for
index_name	TEXT	-	✔	Existing hypertable index by which to order the rows on disk
if_not_exists	BOOLEAN	false	✖	Set to true to avoid an error if the reorder_policy already exists. A notice is issued instead. Defaults to false.
initial_start	TIMESTAMPTZ	NULL	✖	Controls when the policy first runs and how its future run schedule is calculated. If omitted or set to NULL (default): The first run is scheduled at now() + schedule_interval (defaults to 24 hours). The next run is scheduled at one full schedule_interval after the end of the previous run. If set: The first run is at the specified time. The next run is scheduled as initial_start + schedule_interval regardless of when the previous run ends.
timezone	TEXT	NULL	✖	A valid time zone. If initial_start is also specified, subsequent runs of the reorder policy are aligned on its initial start. However, daylight savings time (DST) changes might shift this alignment. Set to a valid time zone if this is an issue you want to mitigate. If omitted, UTC bucketing is performed. Defaults to NULL.

Returns

Column	Type	Description
job_id	INTEGER	TimescaleDB background job ID created to implement this policy