How do I enable TimescaleDB features on TigerData Tiger Cloud and convert an existing table to a hypertable?

Most teams land on Tiger Cloud because they already know Postgres and want TimescaleDB’s time-series performance without rebuilding everything from scratch. The good news: every Tiger Cloud service is just Postgres with the TimescaleDB extension already installed and ready to use—you only need to enable the features in your database and convert the right tables into hypertables.

This guide walks through exactly how to:

Confirm TimescaleDB is available on Tiger Cloud
Enable the extension in your database
Convert an existing “plain” Postgres table into a hypertable
Add indexes and policies so it performs well under telemetry workloads

Quick Answer: On Tiger Cloud, TimescaleDB is preinstalled. Connect to your service, run CREATE EXTENSION IF NOT EXISTS timescaledb;, then convert an existing table with SELECT create_hypertable('<table_name>', '<time_column>');. From there, add indexes and compression/retention policies to get full time-series performance.

The Quick Overview

What It Is: TimescaleDB on Tiger Cloud is a Postgres extension that adds hypertables (automatic partitioning), hybrid row-columnar storage (Hypercore), and time-series functions—without changing SQL or breaking compatibility.
Who It Is For: Teams ingesting high-volume telemetry, events, metrics, or tick data who want to keep standard Postgres while scaling to billions of rows.
Core Problem Solved: Plain Postgres tables become slow and expensive under heavy ingest plus historical queries. Hypertables and compression keep writes fast, queries sub-second, and storage costs controlled.

How It Works

Tiger Cloud runs a managed Postgres service with the TimescaleDB extension available out of the box. You don’t install binaries or tinker with postgresql.conf—you just:

Enable the extension in each database where you want TimescaleDB features.
Convert existing time-based tables into hypertables.
Optionally layer on compression and continuous aggregates.

Under the hood, a hypertable:

Automatically partitions your data into time- and key-based chunks
Uses Hypercore to store recent data in row format (fast writes) and older data in compressed columnar format (fast analytics, up to ~95% compression)
Exposes everything as a normal Postgres table to your applications (no query changes beyond best-practice indexes)

High-level flow:

Extension Enablement: Run CREATE EXTENSION timescaledb; in your database.
Hypertable Conversion: Use create_hypertable() to turn an existing table into a hypertable based on its time column (e.g., time, created_at, timestamp).
Optimization: Add indexes, compression, and continuous aggregates to match your workload.

Step 1: Confirm TimescaleDB on Tiger Cloud

Once you’ve created a Tiger Cloud service, connect via:

psql
Your preferred SQL client
Tiger Console’s in-browser SQL editor

Then check the available extensions:

SELECT * FROM pg_available_extensions
WHERE name = 'timescaledb';

You should see a row with timescaledb and a version (e.g., 2.x). If you don’t:

Confirm you’re connected to a Tiger Cloud service (not a different Postgres instance).
Ensure you’re in the right database (extensions are enabled per-database).

Step 2: Enable TimescaleDB in Your Database

You only need to enable the extension once per database:

CREATE EXTENSION IF NOT EXISTS timescaledb;

To verify:

SELECT * FROM pg_extension
WHERE extname = 'timescaledb';

Important:
Run this as a role with privileges to create extensions (typically the database owner/admin for that database). On Tiger Cloud, the primary user created when you provision the service usually has this permission.

Once enabled, you can use:

create_hypertable
Compression functions
Continuous aggregates
Time-series helper functions

All while still writing standard SQL.

Step 3: Assess Your Existing Table Before Conversion

Before converting a table to a hypertable, validate:

Time column exists
You need a column that represents time. Common patterns:
- time TIMESTAMPTZ
- created_at TIMESTAMPTZ
- event_time TIMESTAMPTZ
Primary key and indexes
Understand your current access patterns. For telemetry, you often query by:
- Time range: WHERE time >= now() - interval '1 day'
- Device/tenant and time: WHERE device_id = ? AND time >= ?
- Status/metric and time
This will inform the hypertable’s partitioning and indexing strategy.
Existing data volume
Large tables (hundreds of millions/billions of rows) benefit greatly from hypertables, but you may want to:
- Schedule conversion during a lower-traffic window
- Ensure application write loads can tolerate a brief schema change window

Step 4: Convert an Existing Table to a Hypertable

At minimum, you need:

The table name
The time column name

Basic conversion

SELECT create_hypertable('metrics', 'time');

This:

Converts metrics into a hypertable.
Creates internal chunks partitioned by time (using an automatic chunk size).
Keeps the same table name and schema. Your application keeps using metrics as before.

Note: Existing data is preserved. TimescaleDB will move data into chunks as part of the conversion.

Adding a partitioning key for scale

If you have many devices, tenants, or streams, add a partitioning key to keep writes and queries balanced:

SELECT create_hypertable(
    'metrics',
    'time',
    'device_id',        -- partitioning (space) key
    number_partitions => 8
);

Use a space partition when:

You have high ingest from many distinct IDs (devices, users, tenants).
You query by that key plus a time range (WHERE device_id = ? AND time >= ?).

Custom chunk size (optional)

By default, TimescaleDB optimizes chunk size based on your workload. For fine-grained control:

SELECT create_hypertable(
    'metrics',
    'time',
    chunk_time_interval => interval '1 day'
);

Pick a time interval that roughly yields:

Chunks that fit in memory for your hot working set.
Not too many small chunks (avoid millions of tiny chunks).

If you’re unsure, let the default behavior handle this initially; you can adjust chunking later.

Step 5: Indexes for Typical Telemetry Workloads

Hypertables are still Postgres tables, so you use CREATE INDEX as usual—just be deliberate.

Typical pattern: device + time

CREATE INDEX CONCURRENTLY IF NOT EXISTS metrics_device_time_idx
    ON metrics (device_id, time DESC);

Good for:

Fetching recent data per device:

SELECT *
FROM metrics
WHERE device_id = 'device-123'
  AND time >= now() - interval '1 day'
ORDER BY time DESC;

Time-only queries

If you frequently query by time alone:

CREATE INDEX CONCURRENTLY IF NOT EXISTS metrics_time_idx
    ON metrics (time DESC);

Important:
Building indexes on large hypertables can be expensive. Use CONCURRENTLY to avoid blocking writes, and schedule index creation when your ingest volume is manageable.

Step 6: Enable Compression to Cut Storage (and Speed Up Analytics)

Compression uses Hypercore: data is stored row-based while hot, then converted into compressed columnar form automatically. TimescaleDB often achieves up to ~95% compression on columnar segments.

1. Enable compression on the hypertable

ALTER TABLE metrics
SET (
    timescaledb.compress = true
);

2. Define which columns to segment and order by

Segment keys define how data is grouped in compressed chunks. Typically:

Segment by a low-cardinality key like device_id or tenant_id.
Order by time inside each segment.

SELECT add_compression_policy(
    'metrics',
    INTERVAL '7 days'      -- start compressing rows older than 7 days
);

For more control (two-step pattern):

SELECT alter_table_set_compression(
    'metrics',
    segmentby => 'device_id',
    orderby   => 'time DESC'
);

Then add the policy:

SELECT add_compression_policy(
    'metrics',
    INTERVAL '7 days'
);

Result:

Hot data (last 7 days) stays row-oriented for fast writes.
Older data is compressed columnar, cutting storage and making analytics scans faster.

Step 7: Continuous Aggregates for Always-Fresh Rollups

Telemetry workloads often need “data downsampled over time,” e.g.:

Per-minute or per-hour metrics
Dashboard queries over 30–90 days of history

Continuous aggregates materialize rollups incrementally, so each query doesn’t rescan raw data.

Create a continuous aggregate

CREATE MATERIALIZED VIEW metrics_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    avg(value)                  AS avg_value,
    max(value)                  AS max_value,
    min(value)                  AS min_value
FROM metrics
GROUP BY bucket, device_id;

Add a refresh policy

SELECT add_continuous_aggregate_policy(
    'metrics_1h',
    start_offset   => INTERVAL '30 days',
    end_offset     => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 minutes'
);

This keeps aggregates:

Covering the last 30 days.
Refreshing every 15 minutes.
Up-to-date with fresh data and late-arriving events within the window.

Note:
Continuous aggregates are eventually consistent within the refresh window; they’re perfect for dashboards and historical analytics, less so for “read-after-write within milliseconds” use cases.

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Hypertables	Automatically partition data by time (and optional key) into chunks	Fast writes and time-range queries at massive scale
Hybrid row-columnar	Stores recent data row-based and older data compressed columnar (Hypercore)	Up to ~95% compression and fast analytics on history
Continuous aggregates	Maintains incremental materialized views over time-based data	Always-fresh rollups without heavy query overhead

Ideal Use Cases

Best for high-ingest telemetry/metrics tables: Because hypertables keep inserts fast and queries efficient as the table grows into billions of rows, without abandoning Postgres.
Best for event and tick data analytics: Because compression and continuous aggregates let you keep detailed history online at lower cost, while still supporting rollups for dashboards and reports.

Limitations & Considerations

Extension is per-database: You must run CREATE EXTENSION timescaledb; in each database where you want TimescaleDB features. It’s not a global server-wide toggle.
Hypertable design is hard to change later: Partitioning choices (time column, space key) are foundational. Design upfront based on access patterns; changing them later can require a migration.

Pricing & Plans

On Tiger Cloud, you’re billed for the underlying managed Postgres service resources (compute, storage, backups, and replicas) rather than per TimescaleDB feature.

No per-query or per-metric fees.
No separate line item for TimescaleDB use—the extension is part of the platform.
Automated backups and point-in-time recovery are included; you don’t pay extra for them.

Plan fit typically looks like:

Performance / Scale: Best for production telemetry workloads needing high ingest, sub-second queries, HA, read replicas, and PITR—with clear, usage-based billing.
Enterprise: Best for regulated or mission-critical environments needing advanced security controls (e.g., HIPAA support), SOC 2 reports, private networking, and 24/7 SLA-backed support.

For exact pricing details and regional coverage, contact TigerData directly.

Frequently Asked Questions

Do I have to change my application queries after converting a table to a hypertable?

Short Answer: Usually no. Hypertables look and behave like normal Postgres tables to your application.

Details:
You keep using the same table name and standard SQL:

INSERT INTO metrics (...) VALUES (...);
SELECT * FROM metrics WHERE time >= now() - interval '1 hour';

The major differences are internal:

Data is partitioned into chunks by time (and optional key).
Compression, continuous aggregates, and policies run behind the scenes.

You may adjust indexes to match new query patterns, and you might add new tables/views for rollups, but you don’t need to adopt a new query language or client.

Can I convert a large existing table to a hypertable without downtime?

Short Answer: You can convert in place, but plan carefully for large tables.

Details:
create_hypertable() works on existing tables with data. For very large tables:

The conversion itself is typically quick because TimescaleDB repoints metadata and manages chunks internally rather than rewriting every row.
The heavier operations are often index creation and backfilling continuous aggregates, not the hypertable conversion.
Use CREATE INDEX CONCURRENTLY to avoid blocking writes when adding or changing indexes.
Consider doing the conversion during a lower-traffic window and monitor performance in Tiger Console.

If your availability requirements are strict, a “dual-write and cutover” pattern (new hypertable + logical replication) is also possible, but that’s an advanced migration strategy you’d plan separately.

Summary

Enabling TimescaleDB features on Tiger Cloud is a straightforward progression:

Connect to your Tiger Cloud service.
Enable the extension in your database with CREATE EXTENSION timescaledb;.
Convert your existing time-based tables into hypertables via create_hypertable().
Add the right indexes, compression, and continuous aggregates for your workload.

You stay in familiar Postgres territory—SQL, tables, indexes—while gaining time-series primitives that keep ingest fast, queries predictable, and storage efficient, even at telemetry scale.

Next Step

Get Started