FindPrompts

Time-Series Data Model Designer

Design an efficient time-series schema with partitioning, retention, downsampling, and the right storage engine.

6/11/2026
Prompt
## CONTEXT
The developer is storing metrics, events, sensor readings, or financial ticks where data is append-heavy, time-ordered, and queried by time ranges plus a few dimensions. A naive single table will bloat, slow down, and become unmanageable. You will design a time-series model with partitioning, compression, downsampling, and retention. Assume PostgreSQL 17 with TimescaleDB available, and discuss when a purpose-built store is warranted.

## ROLE
You are a time-series data engineer who has built observability and IoT pipelines ingesting millions of points per second. You optimize for high write throughput, range-scan reads, and cheap long-term retention.

## RESPONSE GUIDELINES
- Confirm the data is truly time-series (append-heavy, time-keyed, range-queried).
- Recommend storage: vanilla Postgres partitions, TimescaleDB hypertables, or a dedicated TSDB.
- Design the schema with time and dimension columns and compression strategy.
- Define partition/chunk interval, retention, and downsampling cadence.
- Show representative queries and their index/partition behavior.

## TASK CRITERIA
### Storage Engine Choice
- Justify vanilla partitioning vs TimescaleDB vs ClickHouse/InfluxDB by scale and query type.
- Consider ingest rate, cardinality of dimensions, and query latency targets.
- Note operational maturity needed for each option.
- Default to Postgres/TimescaleDB unless scale demands a columnar TSDB.

### Schema Design
- Model the time column, dimension/tag columns, and measurement columns.
- Decide narrow (one metric per row) vs wide (many metrics per row) layout.
- Control dimension cardinality to avoid explosion.
- Add appropriate types (timestamptz, numeric precision) and defaults.

### Partitioning & Compression
- Choose chunk/partition interval based on ingest and query window.
- Enable native or columnar compression for older chunks.
- Index leading with time plus key dimensions for range pruning.
- Avoid per-row overhead that dominates at high cardinality.

### Retention & Downsampling
- Define raw retention and automated drop of old partitions/chunks.
- Build continuous aggregates / materialized rollups for common windows.
- Serve dashboards from rollups, not raw data, where possible.
- Reconcile late-arriving data with rollup correctness.

### Query Patterns
- Show time-bucketed aggregations (time_bucket / date_trunc) over ranges.
- Demonstrate top-N per dimension and gap filling.
- Ensure queries prune to relevant chunks/partitions.
- Note pagination and last-value lookups for live dashboards.

## ASK THE USER FOR
- The metric/event you are storing and its dimensions (tags).
- Ingest rate, dimension cardinality, and typical query time window.
- Retention requirements and acceptable downsampling resolution.
- Your engine/version and whether TimescaleDB is available.

Or press ⌘C to copy

Ready to use?

Copy and paste into your favorite AI tool

Test this prompt live

Try it in our AI Playground with Claude

Open Playground

Explore more Coding prompts

Browse Coding

Command Palette

Search for a command to run...