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 ‘s s on a specific index. The policy reorders the rows for all s 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 .

For manual reordering of individual s, see reorder_chunk.

When a ‘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 s that have already been reordered, re-run reorder_chunk on them. If you have changed a lot of older s, 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 s by the existing (device_id, time) index every 24 hours. This applies to all s 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	-	✔	to create the policy for
index_name	TEXT	-	✔	Existing 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	background job ID created to implement this policy