Time-Series vs. Relational Data Models

Tiger Data 101 → TimescaleDB | Section: Working with Time-Series Data | ⏱ Time: ~2 min

The Fundamental Difference

Dimension	Relational Model	Time-Series Model
Organizing principle	Entity identity (ID)	Timestamp
Primary query	"Get me entity X" (lookup)	"Get me data from time T1 to T2" (range scan)
Data mutability	Frequent updates, deletes	Append-only
Storage optimization	Row-based (fast for random access)	Column-based (fast for range queries)
Example use case	Customer, Order, Product records	Sensor readings, stock prices, events

---

Side-by-Side Example

Relational: User Profiles

-- A typical relational table
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name TEXT,
  email TEXT,
  account_tier VARCHAR(50),
  updated_at TIMESTAMP
);

-- Relational query: Find a specific user
SELECT * FROM users WHERE id = 12345;

Characteristics:

• Queries are lookups by ID or small filters
• Data updates frequently (email, tier, preferences)
• Each row is independent
• Storage is row-based (all columns for one user together)

Time-Series: Sensor Readings

-- A time-series table (hypertable in TimescaleDB)
CREATE TABLE sensor_readings (
  time TIMESTAMP NOT NULL,
  device_id TEXT NOT NULL,
  temperature FLOAT,
  humidity FLOAT,
  pressure FLOAT
);

-- Time-series query: Get data over a time range
SELECT * FROM sensor_readings
WHERE time >= '2025-01-01'::timestamp
  AND time < '2025-01-02'::timestamp
  AND device_id = 'sensor_042';

Characteristics:

• Queries are range scans (fetch data between two timestamps)
• Data never updates (readings are immutable)
• Each row is part of a larger time-ordered sequence
• Storage is column-based (all temperatures, all humidities, etc.)

---

Why the Differences Matter

Relational Optimization

Relational databases optimize for:

• Random access: Fast row lookups by ID
• In-place updates: Modify values efficiently
• Complex relationships: JOINs between entities

When you apply relational optimization to time-series data, you get slow queries and expensive storage.

Time-Series Optimization

Time-series databases optimize for:

• Sequential writes: Bulk appends are cheap
• Range queries: Scanning a time window is fast
• Compression: Encode columns efficiently
• Lifecycle: Automatically manage old data

When you apply time-series optimization to relational data, you get slow random lookups.

Note

The same database can hold both! TimescaleDB is a PostgreSQL extension, so you can store relational data (users, orders, metadata) and time-series data (metrics, events, sensor readings) in the same instance, with each optimized appropriately.

---

NEXT PAGE→

Common Time-Series Patterns

Explore the most common patterns for time-series data: aggregations, downsampling, windowing, and anomaly detection.