For this kind of workload, I’d design it as a time-series analytics system with separate raw and aggregated storage, optimized for:

• high-ingest writes
• time-based retention
• rollups/downsampling
• fast dashboard queries
• cheap long-term storage

My recommendation

Best general-purpose architecture

Use:

1. PostgreSQL + TimescaleDB if you want the simplest strong default
2. ClickHouse if query speed and analytics scale matter more than transactional features

If you expect:

• lots of dashboard queries,
• wide scans over large time ranges,
• high-cardinality dimensions,
• and millions/billions of rows,

then ClickHouse is usually the best fit.

If you want:

• easier operational model,
• SQL familiarity,
• built-in hypertables, compression, retention, continuous aggregates,

then TimescaleDB is excellent.

---

Recommended design pattern

1) Split data into three logical layers

A. Raw event/sensor/metric tables

Store immutable, append-only, time-partitioned data.

Examples:

• metrics_raw
• events_raw
• sensor_readings_raw

Keep these optimized for ingest.

B. Rollup tables/materialized views

Precompute:

• 1 minute
• 5 minute
• 1 hour
• 1 day

Store:

• avg
• min
• max
• p50/p95/p99
• count
• sum
• maybe last, first

These are what dashboards should query most of the time.

C. Long-term archive / cold storage

Move old raw data to cheaper storage if needed:

• object storage
• parquet files
• data lake
• or keep only coarse rollups

---

Core design decisions

2) Use event-time, not ingestion-time

Every row should have:

• timestamp = when it actually happened
• ingested_at = when it arrived

Why:

• late-arriving data is common
• dashboards should reflect actual event time
• retention and rollups should be based on event time

---

3) Model data by type, but share the same pattern

You mentioned:

• metrics
• events
• sensor data

These are similar, but not identical.

Metrics

Usually numeric, high-volume, time series.

Schema idea:

• time
• metric_name
• value
• tags/dimensions
• optional source_id, host, service

Events

Discrete occurrences with payloads.

Schema idea:

• time
• event_type
• severity
• entity_id
• payload
• tags

Sensor data

Often numeric and frequent, possibly multiple channels.

Schema idea:

• time
• sensor_id
• reading_type
• value
• unit
• tags

You can keep them in separate tables, or unify them with a data_type column if the schema is similar enough.
I usually prefer separate tables if query patterns differ.

---

Database recommendation in more detail

Option A: TimescaleDB

Best if you want SQL + operational simplicity.

Why it works well

• hypertables automatically partition by time
• compression for old data
• retention policies
• continuous aggregates for rollups
• good SQL support
• easy joins to dimension tables

Good for

• moderate to high volume
• dashboards and monitoring
• teams already using PostgreSQL

Trade-offs

• not as fast as ClickHouse for huge analytical scans
• storage and compression are good, but not as specialized for OLAP as ClickHouse

---

Option B: ClickHouse

Best if analytics speed is the top priority.

Why it works well

• extremely fast aggregation over time ranges
• columnar storage
• compression is excellent
• materialized views are strong for rollups
• very good for high-cardinality tags/dimensions
• handles huge datasets well

Good for

• observability systems
• dashboards
• ad hoc analytics
• very large event volumes

Trade-offs

• not a traditional OLTP database
• joins/updates/deletes are more constrained
• requires more careful schema design

---

My practical recommendation

If you’re building a product from scratch:

Choose ClickHouse for analytics + a small Postgres for metadata

This is the most scalable pattern.

• Postgres for users, configs, devices, schemas, permissions
• ClickHouse for metrics/events/sensor data

This gives you:

• fast writes
• fast dashboard queries
• flexible retention
• easy rollups
• clean separation of concerns

---

Schema design guidance

4) Use a narrow fact table for raw data

For raw metrics/sensor readings, avoid overly wide rows unless necessary.

Example conceptual schema:

time
source_id
series_key
metric_name
value
tag1
tag2
tag3
ingested_at
payload_json

But don’t shove everything into JSON if you need filtering.
Put common filters into typed columns.

Rule of thumb

• frequently filtered/grouped fields: separate columns
• rarely used or variable fields: JSON payload

---

5) Control cardinality

Dashboard performance often dies because of too many distinct tag combinations.

Examples of high-cardinality fields:

• request_id
• trace_id
• user_id
• device_serial
• session_id

These are fine for event lookup, but bad as primary grouping dimensions in rollups.

Keep rollups on low-to-medium cardinality dimensions:

• service
• host
• region
• sensor type
• metric name
• device class

---

Partitioning and clustering

6) Partition by time, cluster by common filters

You want queries like:

• “last 24 hours by service”
• “7-day trend by region”
• “sensor readings for device X”

So organize storage around:

• time
• series/device/metric key
• common tag dimensions

In ClickHouse, this means choosing:

• ORDER BY (series_key, time) or (metric_name, time)
• partition by month/day depending on volume

In TimescaleDB:

• hypertable partitioning by time
• optional space partitioning by series/device

---

Retention strategy

7) Apply tiered retention

Do not keep all raw data forever unless absolutely necessary.

Typical policy:

• raw data: 7–30 days
• 1-minute rollups: 30–90 days
• 5-minute rollups: 6–12 months
• 1-hour/day rollups: years

This gives you:

• detailed recent debugging
• cheap historical trends
• predictable storage growth

---

Rollup strategy

8) Pre-aggregate at the query grain

Think about what dashboards need.

Common dashboard queries:

• last 15 minutes
• last 24 hours
• compare this week vs last week
• top N hosts/services
• percentiles by time bucket

Precompute buckets like:

• 1m
• 5m
• 1h

Store aggregates such as:

• count
• sum
• avg
• min
• max
• stddev
• percentile states if supported

Key point

Do not compute percentiles from raw every time if you can avoid it.

---

Query pattern

9) Route queries to the best layer automatically

A smart API layer should decide:

• last 1–24h? → maybe raw or 1m rollup
• 2 days–30 days? → 5m or 1h rollup
• months/years? → 1h/day rollup
• drilldown to exact event? → raw event table

This makes dashboards fast and cost-efficient.

---

Data ingestion

10) Use append-only ingestion with batching

For performance:

• batch inserts
• avoid row-at-a-time writes
• use idempotency keys if duplicates are possible
• use a queue/stream if ingestion volume is high

Good ingestion pipeline:

• app/agents → Kafka/Redpanda/PubSub optional → ingestion service → DB
• or direct batch writes if volume is smaller

---

Recommended concrete stack

If you want the strongest recommendation:

ClickHouse + Postgres

• ClickHouse: metrics/events/sensor facts, raw + rollups
• Postgres: metadata, users, device registry, dashboards, alert configs
• optional queue: Kafka/Redpanda
• optional cache: Redis for hot dashboard results

This is the best balance of performance and flexibility for fast analytics.

---

Simple decision guide

Choose TimescaleDB if:

• you want one database
• team knows PostgreSQL well
• scale is moderate
• you want easiest ops and SQL

Choose ClickHouse if:

• dashboards must be very fast
• data volume is large
• you need strong rollups and long retention
• analytics is the main workload

---

What I would do

If I were designing this today:

1. Store raw telemetry in ClickHouse
2. Use materialized views for 1m/5m/1h rollups
3. Apply retention on raw data
4. Keep only long-term aggregates
5. Store metadata/config in Postgres
6. Cache expensive dashboard queries in Redis
7. Use event-time and append-only writes
8. Avoid high-cardinality rollup dimensions

---

Final recommendation

If your goal is fast dashboards + retention + rollups + mixed metrics/events/sensor data, I’d recommend:

ClickHouse for telemetry analytics, Postgres for metadata, and precomputed rollups for dashboard queries.

It’s the most future-proof design for this workload.

If you want, I can also give you:

1. a sample schema
2. a partitioning/retention plan
3. a dashboard query routing strategy
4. a TimescaleDB vs ClickHouse comparison table