Understand database schemas

A database schema defines how tables and indexes are organized. Learn how to design efficient schemas for real-time analytics in TimescaleDB

A database schema defines how the tables and indexes in your database are organized. Using a schema that is appropriate for your workload can result in significant performance improvements. Conversely, using a poorly suited schema can result in significant performance degradation.

If you are working with semi-structured data, such as readings from IoT sensors that collect varying measurements, you might need a flexible schema. In this case, you can use JSON and JSONB data types.

supports all table objects supported within , including data types, indexes, and triggers. However, when you create a , set the datatype for the time column as timestamptz and not timestamp. For more information, see timestamp best practices.

This section explains how to design your schema, how indexing and tablespaces work, and how to use constraint types. It also includes examples to help you create your own schema, and learn how to use JSON and JSONB for semi-structured data.

In this section:

• Accelerate queries using indexes: learn how indexing works in , including default indexes on s, composite indexes, and best practices for query optimization
• Manage tablespaces: use tablespaces to control where stores data on disk and move older chunks to different storage tiers
• Ensure data integrity with constraints: implement constraints including CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE to maintain data quality
• Automate tasks with triggers: use triggers to automatically execute functions when data changes occur, including data validation, audit logging, and notifications
• Handle semi-structured data with JSON: work with flexible, semi-structured data using JSON and JSONB data types for IoT sensor readings, logs, and varying structures
• Query external data sources with FDW: query other Tiger Cloud services or external databases using foreign data wrappers
• Alter and update table schemas: modify existing schemas using ALTER TABLE and handle schema changes on s with columnstore enabled