_create-hypertable-blockchain.md

import CreateHypertablePolicyNote from "versionContent/_partials/_create-hypertable-columnstore-policy-note.mdx"; import HypertableIntro from "versionContent/_partials/_tutorials_hypertable_intro.mdx";

Optimize time-series data using hypertables

1. Connect to your $SERVICE_LONG

   In $CONSOLE open an SQL editor. The in-Console editors display the query speed. You can also connect to your service using psql.

2. Create a $HYPERTABLE for your time-series data using CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data:

   CREATE TABLE transactions (
      time TIMESTAMPTZ NOT NULL,
      block_id INT,
      hash TEXT,
      size INT,
      weight INT,
      is_coinbase BOOLEAN,
      output_total BIGINT,
      output_total_usd DOUBLE PRECISION,
      fee BIGINT,
      fee_usd DOUBLE PRECISION,
      details JSONB
   ) WITH (
      tsdb.hypertable,
      tsdb.segmentby='block_id', 
      tsdb.orderby='time DESC'
   );

3. Create an index on the hash column to make queries for individual transactions faster:

   CREATE INDEX hash_idx ON public.transactions USING HASH (hash);

4. Create an index on the block_id column to make block-level queries faster:

When you create a $HYPERTABLE, it is partitioned on the time column. $TIMESCALE_DB automatically creates an index on the time column. However, you'll often filter your time-series data on other columns as well. You use indexes to improve query performance.

```sql
CREATE INDEX block_idx ON public.transactions (block_id);
```

1. Create a unique index on the time and hash columns to make sure you don't accidentally insert duplicate records:

   CREATE UNIQUE INDEX time_hash_idx ON public.transactions (time, hash);