drop_chunks()

Delete chunks by time range

Since 0.1.0

Removes data s whose time range falls completely before (or after) a specified time. Shows a list of the s that were dropped, in the same style as the show_chunks function.

s are constrained by a start and end time and the start time is always before the end time. A is dropped if its end time is older than the older_than timestamp or, if newer_than is given, its start time is newer than the newer_than timestamp.

Note that, because s are removed if and only if their time range falls fully before (or after) the specified timestamp, the remaining data may still contain timestamps that are before (or after) the specified one.

s can only be dropped based on their time intervals. They cannot be dropped based on a hash partition.

Samples

• Drop all s from conditions older than 3 months:

  SELECT drop_chunks('conditions', INTERVAL '3 months');
  

  Example output:

                drop_chunks
  ----------------------------------------
   _timescaledb_internal._hyper_3_5_chunk
   _timescaledb_internal._hyper_3_6_chunk
   _timescaledb_internal._hyper_3_7_chunk
   _timescaledb_internal._hyper_3_8_chunk
   _timescaledb_internal._hyper_3_9_chunk
  (5 rows)
  

• Drop all s from conditions created before 3 months:

  SELECT drop_chunks('conditions', created_before => now() -  INTERVAL '3 months');
  

• Drop all s more than 3 months in the future from conditions:

  This is useful for correcting data ingested with incorrect clocks:

  SELECT drop_chunks('conditions', newer_than => now() + interval '3 months');
  

• Drop all s from conditions before 2017:

  SELECT drop_chunks('conditions', '2017-01-01'::date);
  

• Drop all s from conditions before 2017, where time column is given in milliseconds from the UNIX epoch:

  SELECT drop_chunks('conditions', 1483228800000);
  

• Drop all s older than 3 months ago and newer than 4 months ago from conditions:

  SELECT drop_chunks('conditions', older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months')
  

• Drop all s created 3 months ago and created 4 months before from conditions:

  SELECT drop_chunks('conditions', created_before => INTERVAL '3 months', created_after => INTERVAL '4 months')
  

• Drop all s older than 3 months ago across all s:

  SELECT drop_chunks(format('%I.%I', hypertable_schema, hypertable_name)::regclass, INTERVAL '3 months')
    FROM timescaledb_information.hypertables;
  

Arguments

The syntax is:

SELECT drop_chunks(
    relation = '<hypertable_or_cagg_name>',
    older_than = <interval>,
    newer_than = <interval>,
    verbose = true | false,
    created_before = <interval>,
    created_after = <interval>
);

Name	Type	Default	Required	Description
relation	REGCLASS	-	✔	or from which to drop s.
older_than	ANY	-	✖	Specification of cut-off point where any s older than this timestamp should be removed.
newer_than	ANY	-	✖	Specification of cut-off point where any s newer than this timestamp should be removed.
verbose	BOOLEAN	FALSE	✖	Setting to true displays messages about the progress of the drop_chunks command.
created_before	ANY	-	✖	Specification of cut-off point where any s created before this timestamp should be removed.
created_after	ANY	-	✖	Specification of cut-off point where any s created after this timestamp should be removed.

The older_than and newer_than parameters can be specified in two ways:

• interval type: The cut-off point is computed as now() - older_than and similarly now() - newer_than. An error is returned if an INTERVAL is supplied and the time column is not one of a TIMESTAMP, TIMESTAMPTZ, or DATE.

• timestamp, date, or integer type: The cut-off point is explicitly given as a TIMESTAMP / TIMESTAMPTZ / DATE or as a SMALLINT / INT / BIGINT. The choice of timestamp or integer must follow the type of the ‘s time column.

The created_before and created_after parameters can be specified in two ways:

• interval type: The cut-off point is computed as now() - created_before and similarly now() - created_after. This uses the creation time relative to the current time for the filtering.

• timestamp, date, or integer type: The cut-off point is explicitly given as a TIMESTAMP / TIMESTAMPTZ / DATE or as a SMALLINT / INT / BIGINT. The choice of integer value must follow the type of the ‘s partitioning column. Otherwise the creation time is used for the filtering.

When using just an interval type, the function assumes that you are removing things in the past. If you want to remove data in the future, for example to delete erroneous entries, use a timestamp.

When both older_than and newer_than arguments are used, the function returns the intersection of the resulting two ranges. For example, specifying newer_than => 4 months and older_than => 3 months drops all s between 3 and 4 months old. Similarly, specifying newer_than => '2017-01-01' and older_than => '2017-02-01' drops all s between ‘2017-01-01’ and ‘2017-02-01’. Specifying parameters that do not result in an overlapping intersection between two ranges results in an error.

When both created_before and created_after arguments are used, the function returns the intersection of the resulting two ranges. For example, specifying created_after => 4 monthsandcreated_before=> 3 months drops all s created between 3 and 4 months from now. Similarly, specifying created_after=> ‘2017-01-01’andcreated_before => '2017-02-01' drops all s created between ‘2017-01-01’ and ‘2017-02-01’. Specifying parameters that do not result in an overlapping intersection between two ranges results in an error.

The created_before/created_after parameters cannot be used together with older_than/newer_than.

Returns

Column	Type	Description
drop_chunks	TEXT	The name of each that was dropped. Returns one row per dropped .

The function returns a set of names in the format _timescaledb_internal._hyper_X_Y_chunk, where each row represents a that was successfully dropped. If no s match the specified criteria, the function returns an empty result set.