Designing a Time-Series Data Platform: Ingestion, Storage, and Query Patterns at Scale

Time-series data is everywhere. IoT sensors, application metrics, financial ticks, user event streams, infrastructure telemetry. The volume is high, the write rate is continuous, and the queries are almost always range-based and aggregation-heavy. Relational databases can handle time-series workloads at low scale. They fall apart well before you think they will.

The failure mode is predictable: you start with a Postgres table, add a timestamp index, and it works fine for a few months. Then cardinality grows, retention grows, and query p99 climbs. You add partitioning. You add read replicas. Eventually you realize you are spending engineering time maintaining a relational database against a workload it was not designed for.

This article covers the architecture of a production time-series platform from the ground up: what makes time-series data structurally different, how to design the ingestion layer, what storage engines actually do, the query patterns that matter, and where the major databases fit.

Why Time-Series Data Is Different

Relational databases optimize for random reads and writes across arbitrary rows. Time-series data has a fundamentally different access pattern:

Writes are append-only and sequential. Data arrives in time order. You almost never update or delete individual rows mid-stream. This is the polar opposite of OLTP workloads where any row is a write candidate.

Reads are bulk and range-bound. A query asks for “all sensor readings between T-1h and now, grouped by 5-minute buckets.” You never ask for one row by primary key. You read ranges, compute aggregates, and discard the raw data.

Cardinality is a first-class problem. In time-series, a “series” is the unique combination of metric name and labels (e.g., cpu_usage{host="web-01", region="us-east-1"}). The number of unique series in a system is its cardinality. High cardinality means more indexes, more index lookups, and more memory pressure. This is why naive relational schemas degrade.

Retention is structural. Time-series data has a natural lifecycle. Raw data at 1-second resolution matters for the last hour. At week scale, you only need 1-minute rollups. At year scale, hourly rollups are enough. Any platform that does not model retention as a first-class concern will eventually drown in storage.

Ingestion Layer

The ingestion layer is where most teams underinvest. They expose a direct write endpoint to the database and call it done. That works until it does not.

Batched Writes

Individual point inserts are expensive in any time-series database. The overhead of one write is almost the same as the overhead of a thousand writes (network round trip, flush, WAL write). Batching amortizes that cost.

interface DataPoint {
  metric: string;
  timestamp: number; // Unix ms
  value: number;
  labels: Record<string, string>;
}

class IngestionBuffer {
  private buffer: DataPoint[] = [];
  private flushTimer: ReturnType<typeof setTimeout> | null = null;

  constructor(
    private readonly flushInterval: number = 1000,
    private readonly maxBufferSize: number = 5000,
    private readonly flush: (points: DataPoint[]) => Promise<void>
  ) {}

  async push(point: DataPoint): Promise<void> {
    this.buffer.push(point);

    if (this.buffer.length >= this.maxBufferSize) {
      await this.drainBuffer();
      return;
    }

    if (!this.flushTimer) {
      this.flushTimer = setTimeout(() => this.drainBuffer(), this.flushInterval);
    }
  }

  private async drainBuffer(): Promise<void> {
    if (this.flushTimer) {
      clearTimeout(this.flushTimer);
      this.flushTimer = null;
    }

    const batch = this.buffer.splice(0, this.buffer.length);
    if (batch.length === 0) return;

    await this.flush(batch);
  }
}

The two flush triggers here are important and serve different failure modes. The maxBufferSize trigger prevents unbounded memory growth under sustained load. The flushInterval trigger prevents data going stale when write rates are low.

Backpressure

A buffer without backpressure is a queue with no bound. Under sustained overload, you will exhaust memory and crash the ingestion process. Backpressure signals the producer to slow down when the consumer cannot keep up.

class BackpressureBuffer {
  private inFlight = 0;

  constructor(
    private readonly maxConcurrentFlushes: number = 3,
    private readonly maxBufferSize: number = 50_000,
    private readonly flush: (points: DataPoint[]) => Promise<void>
  ) {}

  async push(points: DataPoint[]): Promise<void> {
    if (points.length > this.maxBufferSize) {
      throw new Error(`Batch size ${points.length} exceeds limit ${this.maxBufferSize}`);
    }

    while (this.inFlight >= this.maxConcurrentFlushes) {
      // Yield to the event loop; retry on next tick
      await new Promise((resolve) => setTimeout(resolve, 10));
    }

    this.inFlight++;
    try {
      await this.flush(points);
    } finally {
      this.inFlight--;
    }
  }
}

In a real system, backpressure should propagate upstream: to the HTTP handler that rejects with 429, to the Kafka consumer that pauses consumption, or to the gRPC stream that applies flow control. The buffer here is the mechanism; the upstream signal is the enforcement point.

Handling Out-of-Order Data

Sensors go offline. Networks partition. IoT devices batch locally and upload late. Your ingestion layer will receive data with timestamps in the past. How far in the past is your “late arrival tolerance.”

The standard pattern is a watermark: any data arriving with a timestamp older than now - lateArrivalWindow is either rejected or written to a cold path for manual reconciliation. Data within the window is accepted normally.

function classifyPoint(
  point: DataPoint,
  lateArrivalWindowMs: number = 5 * 60 * 1000 // 5 minutes
): "accept" | "late" | "reject" {
  const age = Date.now() - point.timestamp;

  if (age < 0) {
    // Future timestamps: reject
    return "reject";
  }

  if (age <= lateArrivalWindowMs) {
    return "accept";
  }

  return "late";
}

Late data going to a cold path needs a separate reconciliation process. Most teams skip this until a customer asks why their data has gaps.

Storage Engines

The storage engine choice determines everything downstream: write throughput, compression ratios, query latency, and operational complexity.

LSM Trees

Log-structured merge trees are the dominant storage engine for write-heavy time-series workloads. The key insight is that they convert random writes into sequential writes by accumulating mutations in memory (a memtable), then flushing sorted segments to disk (SSTables). Background compaction merges and sorts the segments.

Write path:

1. Write to WAL for durability
2. Write to in-memory memtable
3. When memtable hits size threshold, flush to immutable SSTable on disk
4. Background compaction merges SSTables, dropping expired data and tombstones

The tradeoff is read amplification: a read must check the memtable plus potentially many SSTable levels. Time-series mitigates this naturally because reads are range scans over recent data, not point lookups across the full dataset. The hot data lives in the top SSTable levels.

InfluxDB’s TSM (Time-Structured Merge Tree) is an LSM variant optimized specifically for time-series. It organizes data by series key first, timestamp second, which collapses the cardinality problem by ensuring all data for a series is co-located on disk.

Columnar Storage

Columnar engines store data column-by-column rather than row-by-row. For aggregation queries over a single field (e.g., AVG(cpu_usage) WHERE ...), columnar reads are dramatically more efficient because you only read the columns you need, not entire rows.

Columnar storage also enables superior compression. Values in the same column tend to have similar magnitude and distribution. Run-length encoding handles repeated values. Delta encoding handles monotonically increasing timestamps. Dictionary encoding handles low-cardinality label strings. Combined, time-series data in a columnar engine typically compresses at 10-20x compared to raw.

ClickHouse is a columnar OLAP engine that handles time-series exceptionally well. Its MergeTree family of table engines supports time-based partitioning, TTL expressions, and primary key ordering that maps naturally to time-series query patterns.

CREATE TABLE metrics (
  timestamp DateTime64(3),
  metric_name LowCardinality(String),
  host LowCardinality(String),
  region LowCardinality(String),
  value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (metric_name, host, timestamp)
TTL timestamp + INTERVAL 90 DAY DELETE;

The ORDER BY here is doing significant work. ClickHouse physically sorts data on disk by (metric_name, host, timestamp). Range queries on metric_name and host touch minimal disk blocks. The TTL expression handles retention automatically at the storage layer.

Retention Policies

Retention is not a cleanup job you run on a cron. It is a storage-layer concern that needs to be expressed in the schema.

The pattern that works at scale is tiered retention with automatic downsampling:

• Raw data: retained for 7-30 days
• 1-minute rollups: retained for 6 months
• 1-hour rollups: retained for 2 years

Each tier is a separate table (or chunk group in TimescaleDB’s terminology). A background job or a continuous aggregate materializes the rollups as raw data ages. The query layer knows to hit the appropriate tier based on the requested time range.

Query Patterns

Downsampling and Aggregation Windows

The most common time-series query: “give me the average CPU usage per 5-minute bucket over the last 24 hours.” This requires aligning timestamps to a bucket boundary and aggregating.

function buildWindowedQuery(params: {
  metric: string;
  host: string;
  startMs: number;
  endMs: number;
  windowMs: number;
}): string {
  const { metric, host, startMs, endMs, windowMs } = params;

  // Align to window boundary
  const windowSec = Math.floor(windowMs / 1000);

  return `
    SELECT
      toStartOfInterval(timestamp, INTERVAL ${windowSec} SECOND) AS bucket,
      avg(value) AS avg_value,
      min(value) AS min_value,
      max(value) AS max_value,
      count() AS sample_count
    FROM metrics
    WHERE
      metric_name = '${metric}'
      AND host = '${host}'
      AND timestamp BETWEEN
        fromUnixTimestamp64Milli(${startMs})
        AND fromUnixTimestamp64Milli(${endMs})
    GROUP BY bucket
    ORDER BY bucket ASC
  `;
}

The toStartOfInterval function (ClickHouse) and equivalents in other engines do the bucket alignment. Getting this wrong by computing bucket boundaries in application code is a common mistake that produces misaligned buckets and confusing dashboards.

Latest-Value Queries

Dashboards that show current state (not historical trends) need the latest value per series. This is deceptively hard in systems designed for range queries.

Naive approach: SELECT value FROM metrics WHERE metric_name = ? ORDER BY timestamp DESC LIMIT 1. At scale this is slow. You are scanning from the end of a large dataset.

The right approach is a separate last-value cache. Write the latest value to Redis on every ingestion. The time-series database handles historical queries; Redis handles the “current state” queries. They are different access patterns and deserve different storage.

interface LastValueCache {
  set(seriesKey: string, value: number, timestamp: number): Promise<void>;
  get(seriesKey: string): Promise<{ value: number; timestamp: number } | null>;
  getMany(seriesKeys: string[]): Promise<Map<string, { value: number; timestamp: number }>>;
}

class RedisLastValueCache implements LastValueCache {
  constructor(private readonly redis: RedisClient) {}

  private toKey(seriesKey: string): string {
    return `tsdb:last:${seriesKey}`;
  }

  async set(seriesKey: string, value: number, timestamp: number): Promise<void> {
    await this.redis.hset(this.toKey(seriesKey), {
      value: value.toString(),
      timestamp: timestamp.toString(),
    });
  }

  async get(seriesKey: string): Promise<{ value: number; timestamp: number } | null> {
    const data = await this.redis.hgetall(this.toKey(seriesKey));
    if (!data?.value) return null;
    return { value: parseFloat(data.value), timestamp: parseInt(data.timestamp, 10) };
  }

  async getMany(seriesKeys: string[]): Promise<Map<string, { value: number; timestamp: number }>> {
    const pipeline = this.redis.pipeline();
    seriesKeys.forEach((key) => pipeline.hgetall(this.toKey(key)));
    const results = await pipeline.exec();

    const map = new Map<string, { value: number; timestamp: number }>();
    results?.forEach((result, index) => {
      const data = result[1] as Record<string, string> | null;
      if (data?.value) {
        map.set(seriesKeys[index], {
          value: parseFloat(data.value),
          timestamp: parseInt(data.timestamp, 10),
        });
      }
    });
    return map;
  }
}

Cardinality-Aware Query Planning

Before executing a query, validate that the cardinality of the series selector is within bounds. A query that accidentally matches 50,000 series will bring down most time-series databases.

interface SeriesSelector {
  metric: string;
  labels: Record<string, string | string[]>;
}

async function validateCardinality(
  selector: SeriesSelector,
  maxSeries: number = 1000
): Promise<{ valid: boolean; estimatedSeries: number }> {
  // Call your TSDB's cardinality estimation endpoint
  const estimate = await estimateSeriesCount(selector);

  return {
    valid: estimate <= maxSeries,
    estimatedSeries: estimate,
  };
}

InfluxDB exposes /api/v2/query with SHOW SERIES CARDINALITY. TimescaleDB can approximate via pg_stats. ClickHouse supports EXPLAIN estimate. The specific API varies, but the validation step belongs in your query layer before the query hits the database.

Database Comparison

Dimension	TimescaleDB	ClickHouse	InfluxDB OSS
Data model	Relational (PostgreSQL extension)	Columnar OLAP	Custom TSM engine
Write throughput	High (up to ~1M points/s with tuning)	Very high (10M+ points/s in cluster)	High (~500K points/s)
Query language	SQL (full PostgreSQL)	SQL dialect	Flux / InfluxQL
Compression	Good (columnar chunks, ~8-10x)	Excellent (10-30x typical)	Good (TSM, ~8x)
Cardinality handling	Good (PostgreSQL indexes)	Excellent (sparse indexes, skip indexes)	Poor (degrades above ~10M series)
Retention automation	Continuous aggregates + data retention policies	TTL expressions per column	Retention policies per bucket
Operational model	Manage PostgreSQL + extension	Separate cluster, ZooKeeper for replication	Embedded (OSS), separate cluster (Cloud)
Best fit	Teams already on PostgreSQL, SQL-first analytics	Extreme write volume, complex OLAP queries	Simple sensor/metrics use cases
Avoid when	Cardinality exceeds ~100M series	Simple queries that do not need OLAP power	Cardinality above 10M series

TimescaleDB is the pragmatic choice if your team already runs PostgreSQL. You get time-series optimizations (automatic time partitioning, continuous aggregates, compression) without operating a separate database cluster. The full SQL surface area matters more than most people expect: joins between time-series and relational data are common, and SQL handles them naturally.

ClickHouse is the right call when write volume is the binding constraint or when queries require heavy OLAP-style computation across many columns. Its compression is genuinely superior, and its columnar execution engine handles aggregations over billions of rows at sub-second latency. The operational overhead is real: ClickHouse clusters need careful tuning of merge settings, replication topology, and query resource limits.

InfluxDB’s sweet spot is simplicity. If you are ingesting telemetry from a bounded set of sensors with low cardinality, InfluxDB’s operational model (one binary, one config file) is hard to beat. It degrades predictably as cardinality grows, so know your cardinality ceiling before committing.

Production Considerations

Schema design is irreversible. Getting the label set wrong in InfluxDB or the ORDER BY columns wrong in ClickHouse requires a full data migration. Design the schema against real query patterns, not hypothetical ones.

Monitor ingestion lag, not just throughput. The metric that matters is the difference between the event timestamp and the write timestamp. Rising lag means your ingestion layer is falling behind. Throughput can look fine while lag climbs.

Compaction is a background tax. LSM engines run compaction continuously. Under sustained write load, compaction cannot keep up with incoming data, and read performance degrades. Monitor your compaction backlog and set resource limits that do not starve compaction.

Continuous aggregates have consistency windows. In TimescaleDB, continuous aggregates are refreshed on a schedule, not in real-time. Queries that hit recent data will see the raw values; queries that hit older data will see the aggregate. This is correct behavior, but it surprises teams who expect aggregates to be always-current.

Separate the hot and cold query paths. Operational dashboards querying the last 15 minutes need single-digit millisecond response. Historical trend queries scanning 90 days of data can tolerate seconds. Route them to different replicas with different resource limits. Running them against the same node will cause hot-path latency to degrade whenever a long-range query runs.

Closing

Time-series at scale is a storage and query design problem before it is a database selection problem. The database you choose matters, but it matters less than getting the ingestion buffer, the retention tiers, and the query patterns right first. A well-designed schema in TimescaleDB will outperform a poorly designed schema in ClickHouse, and the operational overhead of ClickHouse will hurt you if you do not actually need its write throughput ceiling. Understand your cardinality, design your retention policy, validate your query patterns against real data volumes, then select the storage engine that fits those constraints.