1

Is the standard postgresql way also possible on a timescaledb hypertable?

CLUSTER [VERBOSE] tablename [ USING indexname ]

Example:

  1. Creating table & hypertyble
CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data VARCHAR(8) NOT NULL,

SELECT create_hypertable('l1', 'timestamp', chunk_time_interval => interval '1 day');
  1. Produces an index like this (can be viewed using this command):
l1        | l1_timestamp_idx                                               | CREATE INDEX l1_timestamp_idx ON public.l1 USING btree ("timestamp" DESC)
  1. And than CLUSTER the data like this
CLUSTER l1 USING l1_timestamp_idx;
stdout: CLUSTER

It looks like it works.. But is this recommended? And is it possible to reverse the order of the CLUSTER command? Meaning, sorting it with the lowest timestamp first.

1 Answer 1

1

We recommend instead to use TimescaleDB's reorder_chunk command (or a reorder policy).

CLUSTER will take an ACCESS EXCLUSIVE lock on the entire hypertable, which prevents any operations (reads or writes) from occurring during the entire cluster process (https://www.postgresql.org/docs/current/sql-cluster.html).

We re-implemented similar functionality in reorder_chunk to not acquire require such a heavy weight lock: It operates chunk-by-chunk, and you can still read from the table/chunk while the reordering/clustering is occurring.

But to answer your other questions:

  • You can order in either timestamp order. Define your index to be clustered/reordered as having either timestamps DESC (descending) or ASC (ascending) order.

  • Reordering chunks can be really great for performance. You don't typically need just for timestamp if you are inserting data is loose timestamp order. But we often see them in composites, so if you have a device & timestamp, and you often do a query like:

SELECT foo from table where device_id = X and timestamp > now() - interval '1 week'

then its very useful to reorder based on a composite index like (device_id, timestamp).

https://docs.timescale.com/latest/api#reorder_chunk https://docs.timescale.com/latest/api#add_reorder_policy

4
  • Thank you very much for this comprehensive answer! All my questions have been answered.
    – gies0r
    Sep 12, 2019 at 23:43
  • 1
    Show chunks: \dt *.* Resort example: SELECT reorder_chunk('_timescaledb_internal._hyper_1_1_chunk', 'l1_timestamp_idx');
    – gies0r
    Sep 17, 2019 at 21:33
  • 1
    @Mike Freedman reorder_chunk only works on a single chunk of a hypertable? What do you recommend if I have a hypertable with thousands of chunks and I want to have an ordering like (device, timestamp)?
    – Szabolcs
    Mar 18, 2020 at 12:40
  • @Szabolcs My answer is quite delayed, but you can create a reordering policy to automate the reordering of individual chunks. docs.timescale.com/latest/api#add_reorder_policy Dec 11, 2020 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.