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.

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
4
  • Thank you very much for this comprehensive answer! All my questions have been answered. Commented Sep 12, 2019 at 23:43
  • 1
    Show chunks: \dt *.* Resort example: SELECT reorder_chunk('_timescaledb_internal._hyper_1_1_chunk', 'l1_timestamp_idx'); Commented 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)? Commented 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 Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.