add_reorder_policy()

CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.

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.

Note

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.

Required arguments

Name	Type	Description
`hypertable`	REGCLASS	Hypertable to create the policy for
`index_name`	TEXT	Existing hypertable index by which to order the rows on disk

Optional arguments

Name	Type	Description
`if_not_exists`	BOOLEAN	Set to `true` to avoid an error if the `reorder_policy` already exists. A notice is issued instead. Defaults to `false`.
`initial_start`	TIMESTAMPTZ	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	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

Sample usage


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.