Your first hypertable

You will create a hypertable from scratch, see why time-based partitioning matters, and finish with queries you can reuse. Expect the same SQL surface you already know, with chunk-aware planning underneath.

What is a hypertable?

A hypertable is the main abstraction TimescaleDB uses for time-series data. To your application it looks and behaves like a regular PostgreSQL table: you INSERT, SELECT, UPDATE, and DELETE using standard SQL. Under the hood, the database automatically partitions your rows by time into storage units called chunks. Each chunk holds a time range of data (for example, one day or one week). When you run a query, TimescaleDB figures out which chunks are relevant and only touches those, instead of scanning the whole table. That is why hypertables can scale to very large volumes while keeping queries fast.

For a deeper look at structure, partitioning, and indexes, see Understand hypertables.

Why use hypertables?

Time-series data (sensors, metrics, events, logs) usually grows quickly and is queried by time range. A normal table can become slow and hard to manage as it gets large. Hypertables are built for that workload: they give you automatic time-based partitioning, efficient time-range queries, and scalable data management (for example, compression and retention) without changing how you write SQL. You get the familiarity of a PostgreSQL table with the performance and scalability of a purpose-built time-series store.

Advantages of hypertables

• Efficient data management: Data is split into chunks by time. You can tune chunk intervals to match how you ingest and query (for example, one chunk per day or per week).
• Better performance: A time-descending index is created by default; you can add more indexes (including unique indexes) as needed. Queries use these indexes and only open relevant chunks.
• Chunk skipping: The planner skips chunks that don’t match your query (for example, a time filter), which reduces work and speeds up queries.
• Advanced analysis: Hyperfunctions and related features let you aggregate and analyze large amounts of time-series data efficiently. See About TimescaleDB hyperfunctions.
• Familiar interface: You interact with a hypertable exactly like a normal table; partitioning and optimization are handled for you.

How are hypertables used?

You use a hypertable like any other table: connect with your usual client, run INSERT and SELECT (and UPDATE/DELETE where needed). Typical use cases include:

• IoT and sensors: Device readings with a timestamp (temperature, humidity, location).
• Metrics and monitoring: Server or app metrics (CPU, latency, errors) over time.
• Events and logs: User or system events with a time component.

For creating, altering, and managing hypertables in real projects, see Understand hypertables. For issues such as unique indexes, compression, or dropping chunks, see Troubleshoot hypertables.

Make your first hypertable

You will create a simple hypertable for sensor-style data: a conditions table with time, location, device, temperature, and humidity. When you run the command, the table is created and automatically set up as a hypertable partitioned by the time column.

Prerequisites for this tutorial

To follow the procedure on this page, you'll need:

• A Tiger Cloud service with Real-time analytics or a running instance of self-hosted TimescaleDB
• Your connection details
• Any client that can run SQL (Tiger Console, psql, or your app’s SQL driver)

1. Create the hypertable

   Run the following SQL. The important part is the WITH (tsdb.hypertable, ...) clause: it tells the database to treat this table as a hypertable. The first column with a timestamp type (time) is used as the partitioning column by default. We use tsdb.segmentby = 'device' so that rows are grouped by device inside chunks, which helps both query and compression when you filter by device. We use tsdb.orderby = 'time DESC' so that recent data is stored in an order that matches typical time-range queries.

   CREATE TABLE conditions (
      time        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      device      TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
   ) WITH (
      tsdb.hypertable,
      tsdb.segmentby = 'device',
      tsdb.orderby = 'time DESC'
   );

   You should see a confirmation that the table was created (exact message depends on your client). The table is now a hypertable: new chunks will be created automatically as you insert data into new time ranges.

2. Insert a few rows

   Insert some sample rows so we can query the hypertable:

   INSERT INTO conditions (time, location, device, temperature, humidity)
   VALUES
      (NOW() - INTERVAL '2 hours', 'office', 'sensor-1', 22.1, 45.0),
      (NOW() - INTERVAL '1 hour',  'office', 'sensor-1', 22.3, 44.8),
      (NOW(),                      'office', 'sensor-1', 22.2, 45.1);

3. Query the hypertable

   Query it like a normal table:

   SELECT * FROM conditions ORDER BY time DESC;

   You should see your three rows, with the most recent first. Notice that you didn’t have to mention chunks or partitioning, you just use standard SQL. The database routes the query to the right chunk(s) for you.

You have built your first hypertable and used it for inserts and a time-ordered query. From here you can add more data, add indexes, or enable features like the columnstore. The Understand hypertables guide and Hypertable operations cover those next steps.

Next steps

• Understand chunks: What chunks are, why they matter, and how to use them effectively.
• Basic compression with hypercore: Compress data into the columnstore for up to 98% storage savings and faster analytical queries.
• Hypertable operations: Create, alter, and drop hypertables, and speed up data ingest.