Understand schema optimization

TimescaleDB inherits the full schema toolbox from PostgreSQL — indexes, constraints, triggers, tablespaces, foreign data wrappers, and JSON/JSONB types — and applies them to hypertables and chunks. A well-designed schema keeps hot paths fast; a poor fit shows up as wasted I/O, bloated indexes, or painful migrations later.

Indexes

Indexes are the primary tool for accelerating queries. TimescaleDB automatically creates a time-based index on every hypertable. You can add secondary indexes (B-tree, hash, GiST, GIN) to speed up filters on non-time columns such as device IDs, tags, or status codes.

For hypertables with hypercore enabled, secondary indexes also support chunk skipping, which lets the query planner skip entire chunks that don’t match a filter.

Constraints

Use PostgreSQL constraints — NOT NULL, CHECK, UNIQUE, and foreign keys — to enforce data integrity at the database level. Hypertables support all standard constraint types, with the requirement that UNIQUE and PRIMARY KEY constraints must include the partitioning column.

Triggers

Triggers let you run custom logic automatically when rows are inserted, updated, or deleted. Common use cases include auditing changes, enforcing business rules, and transforming data on write.

Tablespaces

Tablespaces let you control where PostgreSQL stores data on disk. You can place hot chunks on fast SSDs and move older chunks to slower, cheaper storage. This is primarily useful for self-hosted deployments — Tiger Cloud manages storage tiers automatically.

JSON and JSONB

When your data has varying or unpredictable fields — for example, IoT sensors that report different measurements — use PostgreSQL JSONB columns for the flexible parts and regular columns for the fields you always filter or aggregate on. This gives you schema flexibility without sacrificing query performance on known fields.

Foreign data wrappers (FDW)

Foreign data wrappers let you query external PostgreSQL data sources as if they were local tables. This is useful for joining time-series data with reference data stored elsewhere.

Schema changes

You can alter hypertable schemas after creation: add or drop columns, change data types, rename columns, and modify constraints. Schema changes propagate automatically across all chunks.

Best practices

• Set the time column datatype to timestamptz, not timestamp. See PostgreSQL timestamp best practices.
• Add secondary indexes only on columns you actively filter or join on — unused indexes slow down writes.
• Use JSONB over JSON for semi-structured data — JSONB supports indexing and is more efficient to query.
• Include the partitioning column in any UNIQUE or PRIMARY KEY constraint.

Learn more

• Primary keys, time columns, and uniqueness: Partition column and constraint rules for hypertables.
• Wide, narrow, and medium tables: Choose a table layout for your time-series data.
• Accelerate queries using indexes: How-to guide for creating and tuning indexes.
• Alter and update table schemas: Modify schemas on existing hypertables.
• Handle semi-structured data with JSON: Using JSONB columns effectively.