Designing a Time-Series Database: Storage Engines, Downsampling, and Retention Policies at Scale

Time-series data is not general-purpose data with timestamps tacked on. It has a distinct shape: high write throughput, almost no updates, reads concentrated on recent windows, and query patterns centered on aggregations over time ranges. Every design decision in a TSDB flows from those four properties.

This article covers what changes at the storage layer when you optimize for this shape, how write paths differ from OLTP systems, how query patterns drive index design, and where the production failure modes live.

Why General-Purpose Databases Struggle

Before covering what TSDBs do, it helps to name what breaks first when you force time-series workloads into PostgreSQL or MySQL without extensions.

Write amplification from B-tree inserts. B-trees maintain sort order on every write. Inserting a timestamp slightly in the past requires a page split or rebalancing. At 100k writes per second across thousands of series, this creates constant random I/O against your storage device.

Index bloat. A composite index on (series_id, timestamp) in a B-tree will fragment heavily under append-heavy loads. Vacuuming and autovacuum lag becomes a real operational burden at scale.

Poor compression. Row-oriented storage interleaves all columns. Time-series data has high redundancy within a column (timestamps are monotonic, values are often similar) but low redundancy across columns. You need columnar storage to exploit that redundancy.

Aggregation cost. Computing avg(value) GROUP BY series_id over 30 days of raw data requires scanning millions of rows. Without pre-aggregated rollups baked into the retention system, dashboard queries get expensive fast.

Storage Engine Design: LSM Trees Win for Append-Heavy Workloads

The core insight behind LSM (Log-Structured Merge) trees is batching random writes into sequential I/O. This maps well to time-series ingestion.

An LSM tree has two stages:

1. MemTable: an in-memory sorted structure (typically a skip list or red-black tree) that absorbs incoming writes
2. SSTables: immutable sorted files on disk, produced by flushing the MemTable when it exceeds a size threshold

All writes go to the MemTable and a Write-Ahead Log (WAL) for durability. Reads merge results from the MemTable and any SSTables that overlap the requested key range. Background compaction merges SSTables, removes tombstones, and enforces retention policies.

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

interface SSTableBlock {
  minTimestamp: number;
  maxTimestamp: number;
  seriesId: string;
  timestamps: Int64Array;
  values: Float64Array;
  compressed: boolean;
}

class MemTable {
  private data = new Map<string, TimeSeriesPoint[]>();
  private sizeBytes = 0;
  private readonly flushThreshold: number;

  constructor(flushThresholdBytes = 64 * 1024 * 1024) {
    this.flushThreshold = flushThresholdBytes;
  }

  insert(point: TimeSeriesPoint): boolean {
    const series = this.data.get(point.seriesId) ?? [];
    series.push(point);
    this.data.set(point.seriesId, series);
    this.sizeBytes += 24; // approximate: 8 bytes ts + 8 bytes value + overhead
    return this.sizeBytes >= this.flushThreshold;
  }

  flush(): Map<string, TimeSeriesPoint[]> {
    const snapshot = this.data;
    this.data = new Map();
    this.sizeBytes = 0;
    return snapshot;
  }
}

Where B-trees break down for time-series: if your ingestion includes out-of-order data (a sensor reconnecting and replaying buffered measurements), B-trees need to insert into the middle of a sorted structure on disk. LSM trees handle this gracefully because the MemTable is in memory and SSTables are immutable.

Write Path Optimization

Batching and WAL Design

Naively writing each data point to the WAL individually is a bottleneck. At 100k points/second, you want to group-commit: accumulate points in a small buffer (5-10ms), write one fsync covering all of them, then acknowledge the batch.

class WALWriter {
  private buffer: TimeSeriesPoint[] = [];
  private flushPromise: Promise<void> | null = null;
  private readonly fd: number;
  private readonly maxBufferMs = 5;

  constructor(private readonly walPath: string) {
    this.fd = openSync(walPath, 'a');
    this.scheduledFlush();
  }

  async write(point: TimeSeriesPoint): Promise<void> {
    this.buffer.push(point);
    return this.flushPromise ?? this.scheduleFlush();
  }

  private scheduleFlush(): Promise<void> {
    this.flushPromise = new Promise((resolve) => {
      setTimeout(async () => {
        const batch = this.buffer.splice(0);
        const encoded = this.encodeBatch(batch);
        writeSync(this.fd, encoded);
        fdatasyncSync(this.fd);
        this.flushPromise = null;
        resolve();
      }, this.maxBufferMs);
    });
    return this.flushPromise;
  }

  private encodeBatch(points: TimeSeriesPoint[]): Buffer {
    // Delta-encode timestamps, use varint for small deltas
    // Encode values as float64 or use XOR compression (Gorilla codec)
    const entries = points.map(p => ({
      s: p.seriesId,
      t: p.timestamp,
      v: p.value,
    }));
    return Buffer.from(JSON.stringify(entries));
  }
}

Out-of-Order Handling

Real ingestion pipelines receive late data. A mobile client with intermittent connectivity may replay points with timestamps from 10 minutes ago. The write path needs a configurable “out-of-order window”: accept points up to N minutes late without special handling, reject or shunt points older than that to a backfill queue.

class IngestionPipeline {
  private readonly outOfOrderWindowMs: number;
  private readonly wal: WALWriter;
  private readonly memTable: MemTable;
  private watermark = 0; // highest timestamp seen

  constructor(config: { outOfOrderWindowMs: number }) {
    this.outOfOrderWindowMs = config.outOfOrderWindowMs;
    this.wal = new WALWriter('/data/wal/current');
    this.memTable = new MemTable();
  }

  async ingest(point: TimeSeriesPoint): Promise<'accepted' | 'backfill' | 'rejected'> {
    const lag = this.watermark - point.timestamp;

    if (lag > this.outOfOrderWindowMs) {
      // Too old to merge into the active MemTable without complicating reads
      return 'backfill';
    }

    this.watermark = Math.max(this.watermark, point.timestamp);
    await this.wal.write(point);
    const needsFlush = this.memTable.insert(point);

    if (needsFlush) {
      await this.flushMemTable();
    }

    return 'accepted';
  }

  private async flushMemTable(): Promise<void> {
    const snapshot = this.memTable.flush();
    await this.writeSSTable(snapshot);
  }

  private async writeSSTable(data: Map<string, TimeSeriesPoint[]>): Promise<void> {
    // Sort each series by timestamp, apply columnar compression, write block index
    for (const [seriesId, points] of data) {
      points.sort((a, b) => a.timestamp - b.timestamp);
      // Delta-encode timestamps and XOR-encode values (Gorilla-style)
    }
  }
}

Columnar Compression

Time-series data compresses dramatically in columnar format because each column has low entropy:

• Timestamps within a series are monotonically increasing with small deltas. Delta encoding (store the difference rather than the absolute value) reduces 8-byte timestamps to 1-2 byte varints in the common case.
• Float values from sensors or metrics often change slowly. XOR encoding (popularized by Facebook’s Gorilla paper) exploits the fact that consecutive floats share leading bits. A sequence like 1.001, 1.002, 1.003 XOR-encodes to near-zero after the first value.

The combination typically achieves 10-12x compression versus raw binary storage, and up to 50x versus row-oriented JSON.

Query Patterns: Range Scans and Aggregation Windows

TSDB query patterns cluster around three operations:

1. Range scan: retrieve all points for a series between timestamps T1 and T2
2. Aggregation window: compute avg, sum, max, min, or count over fixed intervals
3. Downsampled read: return pre-computed rollups instead of raw points

For a range scan, the SSTable block index is the critical data structure. Each block stores (seriesId, minTimestamp, maxTimestamp). A range query first filters blocks whose timestamp range overlaps the requested window, then decompresses only those blocks.

interface BlockIndex {
  seriesId: string;
  minTimestamp: number;
  maxTimestamp: number;
  fileOffset: number;
  compressedSize: number;
}

class SSTableReader {
  private blockIndex: BlockIndex[];

  constructor(private readonly filePath: string) {
    this.blockIndex = this.loadIndex();
  }

  async rangeQuery(
    seriesId: string,
    startMs: number,
    endMs: number
  ): Promise<TimeSeriesPoint[]> {
    const relevantBlocks = this.blockIndex.filter(
      (b) =>
        b.seriesId === seriesId &&
        b.maxTimestamp >= startMs &&
        b.minTimestamp <= endMs
    );

    const results: TimeSeriesPoint[] = [];
    for (const block of relevantBlocks) {
      const points = await this.readBlock(block);
      results.push(
        ...points.filter((p) => p.timestamp >= startMs && p.timestamp <= endMs)
      );
    }

    return results.sort((a, b) => a.timestamp - b.timestamp);
  }

  private async aggregateWindow(
    seriesId: string,
    startMs: number,
    endMs: number,
    intervalMs: number,
    fn: 'avg' | 'max' | 'min' | 'sum'
  ): Promise<{ windowStart: number; value: number }[]> {
    const raw = await this.rangeQuery(seriesId, startMs, endMs);
    const windows = new Map<number, number[]>();

    for (const point of raw) {
      const bucket = Math.floor((point.timestamp - startMs) / intervalMs) * intervalMs + startMs;
      const group = windows.get(bucket) ?? [];
      group.push(point.value);
      windows.set(bucket, group);
    }

    return Array.from(windows.entries()).map(([windowStart, values]) => ({
      windowStart,
      value: aggregate(values, fn),
    }));
  }

  private loadIndex(): BlockIndex[] {
    // Read trailing index section from SSTable file
    return [];
  }

  private async readBlock(block: BlockIndex): Promise<TimeSeriesPoint[]> {
    // Decompress and decode the block at the given file offset
    return [];
  }
}

function aggregate(values: number[], fn: 'avg' | 'max' | 'min' | 'sum'): number {
  if (fn === 'avg') return values.reduce((a, b) => a + b, 0) / values.length;
  if (fn === 'max') return Math.max(...values);
  if (fn === 'min') return Math.min(...values);
  return values.reduce((a, b) => a + b, 0);
}

Downsampling and Retention Policies

Querying raw points over long windows is expensive. A dashboard showing 90 days of CPU utilization does not need per-second granularity. Downsampling pre-computes aggregates at coarser resolutions and stores them as separate series.

The retention tier structure looks like this in practice:

Tier	Resolution	Retention	Storage
Hot	Raw (1s)	7 days	NVMe SSD
Warm	1 minute	30 days	HDD
Cold	1 hour	1 year	Object storage (S3/GCS)
Archive	1 day	5+ years	Compressed object storage

interface RetentionPolicy {
  tierId: string;
  resolutionMs: number;
  retentionMs: number;
  storageClass: 'nvme' | 'hdd' | 'object';
  aggregations: ('avg' | 'max' | 'min' | 'sum' | 'count')[];
}

const defaultPolicies: RetentionPolicy[] = [
  {
    tierId: 'hot',
    resolutionMs: 1000,
    retentionMs: 7 * 24 * 60 * 60 * 1000,
    storageClass: 'nvme',
    aggregations: ['avg', 'max', 'min'],
  },
  {
    tierId: 'warm',
    resolutionMs: 60 * 1000,
    retentionMs: 30 * 24 * 60 * 60 * 1000,
    storageClass: 'hdd',
    aggregations: ['avg', 'max', 'min', 'sum', 'count'],
  },
  {
    tierId: 'cold',
    resolutionMs: 60 * 60 * 1000,
    retentionMs: 365 * 24 * 60 * 60 * 1000,
    storageClass: 'object',
    aggregations: ['avg', 'max', 'min'],
  },
];

class RetentionManager {
  async runRollup(
    reader: SSTableReader,
    sourcePolicy: RetentionPolicy,
    targetPolicy: RetentionPolicy,
    seriesIds: string[]
  ): Promise<void> {
    const now = Date.now();
    const windowStart = now - sourcePolicy.retentionMs;

    for (const seriesId of seriesIds) {
      for (const aggFn of targetPolicy.aggregations) {
        const rollups = await reader.aggregateWindow(
          seriesId,
          windowStart,
          now,
          targetPolicy.resolutionMs,
          aggFn
        );
        await this.writeRollups(seriesId, aggFn, targetPolicy.tierId, rollups);
      }
    }
  }

  async expireOldData(policy: RetentionPolicy, seriesIds: string[]): Promise<void> {
    const cutoff = Date.now() - policy.retentionMs;
    // Drop SSTables and blocks where maxTimestamp < cutoff
    // This is a compaction-time operation, not an immediate delete
    for (const seriesId of seriesIds) {
      await this.scheduleCompactionWithTTL(seriesId, policy.tierId, cutoff);
    }
  }

  private async writeRollups(
    seriesId: string,
    aggFn: string,
    tierId: string,
    rollups: { windowStart: number; value: number }[]
  ): Promise<void> {
    // Write to the target tier's storage engine
    // Rollup series ID convention: `${seriesId}:${tierId}:${aggFn}`
  }

  private async scheduleCompactionWithTTL(
    seriesId: string,
    tierId: string,
    cutoffMs: number
  ): Promise<void> {
    // Tag SSTables for removal on next compaction pass
  }
}

The rollup job should run continuously in the background, not as a batch cron. If it falls behind, the warm tier will have gaps and dashboard queries will fall back to the hot tier (more expensive). Monitor rollup lag as a first-class metric.

Storage Engine Tradeoffs

Dimension	LSM Tree	B-Tree	Columnar (Parquet-style)
Write throughput	High (sequential)	Medium (random I/O)	Low (batch only)
Read latency (recent)	Low (MemTable hit)	Low	High (decompression)
Read latency (historical)	Medium (SSTable merge)	Medium	Low (column pruning)
Compression ratio	Good (delta + XOR)	Poor	Excellent
Out-of-order writes	Handled natively	Expensive (page splits)	No (immutable)
Compaction overhead	Ongoing background cost	Minimal	None
Point lookups	Medium (bloom filters needed)	Fast	Slow
Range scans	Fast (sorted SSTables)	Fast (B-tree traversal)	Fast (column skipping)
Space amplification	Medium (pre-compaction)	Low	Low

For ingestion-heavy workloads with mixed recent and historical queries, LSM trees are the right choice. Columnar formats (Parquet on S3) make sense for the cold archive tier where you accept high read latency in exchange for compression and cheap storage.

Production Considerations

Cardinality Explosion

This is the most common production failure mode in TSDBs. Cardinality refers to the number of unique series: the cross-product of all label combinations.

Consider a metric http_request_duration with these labels:

• service: 50 values
• endpoint: 200 values
• status_code: 10 values
• region: 5 values

Naive cardinality: 50 * 200 * 10 * 5 = 500,000 series. Now add user_id as a label and you have 500,000 * 1,000,000 = 500 billion series. Every unique series needs an entry in the inverted label index, and queries that filter by high-cardinality labels require scanning enormous posting lists.

The fix is a cardinality limit enforced at ingestion, combined with strict label governance. Never put user IDs, request IDs, or UUIDs in labels. Those are for log correlation, not metric dimensions.

class CardinalityGuard {
  private readonly labelIndex = new Map<string, Set<string>>();
  private readonly limits: Record<string, number>;

  constructor(limits: Record<string, number>) {
    this.limits = limits;
  }

  check(labels: Record<string, string>): { allowed: boolean; reason?: string } {
    for (const [key, value] of Object.entries(labels)) {
      const values = this.labelIndex.get(key) ?? new Set();
      if (!values.has(value)) {
        const limit = this.limits[key] ?? this.limits['*'] ?? 10000;
        if (values.size >= limit) {
          return {
            allowed: false,
            reason: `Label '${key}' has reached cardinality limit of ${limit}`,
          };
        }
        values.add(value);
        this.labelIndex.set(key, values);
      }
    }
    return { allowed: true };
  }
}

Schema Design

Good time-series schema design has three rules:

1. Labels describe what the series is, not individual data points. region=us-east-1 is a label. request_id is not.
2. Metric names should be namespaced. http_request_duration_seconds is better than latency because it encodes the unit and avoids collisions.
3. Keep label sets stable. Adding a new label to an existing metric creates new series and breaks any queries that rely on label absence.

When to Use a Dedicated TSDB vs TimescaleDB

Use a dedicated TSDB (InfluxDB, VictoriaMetrics, Prometheus + Thanos) when:

• Ingestion rate exceeds 500k points/second
• You have thousands of distinct metric names
• Your team needs PromQL or Flux query compatibility
• You cannot afford the operational overhead of tuning PostgreSQL for high-write workloads

Use TimescaleDB when:

• Your data already lives in PostgreSQL and migration cost is high
• You need JOIN semantics between time-series and relational data (e.g., joining metrics with a deployments table)
• Your ingestion rate is below 100k points/second
• Your team already knows SQL well and does not want to learn a new query language

TimescaleDB’s hypertable partitioning handles B-tree fragmentation by chunking data into time-bounded child tables. Each chunk is a small B-tree, so inserts always append to the most recent chunk rather than splitting pages in a global index. This gets you most of the write performance of an LSM tree without abandoning SQL.

// TimescaleDB ingestion via pg driver
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function ingestBatch(points: TimeSeriesPoint[]): Promise<void> {
  const values = points
    .map((p, i) => `($${i * 4 + 1}, to_timestamp($${i * 4 + 2} / 1000.0), $${i * 4 + 3}, $${i * 4 + 4}::jsonb)`)
    .join(', ');

  const params = points.flatMap((p) => [
    p.seriesId,
    p.timestamp,
    p.value,
    JSON.stringify(p.labels),
  ]);

  await pool.query(
    `INSERT INTO metrics (series_id, time, value, labels) VALUES ${values}
     ON CONFLICT (series_id, time) DO NOTHING`,
    params
  );
}

// Downsampling query using TimescaleDB's time_bucket
async function queryDownsampled(
  seriesId: string,
  startMs: number,
  endMs: number,
  bucketSeconds: number
): Promise<{ bucket: Date; avg: number; max: number }[]> {
  const result = await pool.query(
    `SELECT
       time_bucket($1::interval, time) AS bucket,
       avg(value) AS avg,
       max(value) AS max
     FROM metrics
     WHERE series_id = $2
       AND time >= to_timestamp($3 / 1000.0)
       AND time < to_timestamp($4 / 1000.0)
     GROUP BY bucket
     ORDER BY bucket`,
    [`${bucketSeconds} seconds`, seriesId, startMs, endMs]
  );
  return result.rows;
}

Observability on the Write Path

Instrument the ingestion pipeline for these signals:

• WAL fsync latency (p99): if this climbs above 10ms, you’re seeing I/O pressure or a disk bottleneck
• MemTable flush duration: spikes indicate slow SSTable writes
• Compaction backlog: how many SSTables are waiting to be merged; a growing backlog increases read amplification
• Out-of-order point rate: a sudden spike means an upstream client reconnected with buffered data
• Cardinality per metric: alert when any metric crosses 80% of its cardinality limit

Choosing the Right Architecture

Start here:

Are you ingesting metrics from infrastructure (servers, containers, databases) at a rate under 100k points/second? Use Prometheus with a remote write adapter to VictoriaMetrics or Thanos. You do not need to build a custom storage engine.

Are you building a product that stores customer-generated time-series data (IoT sensor readings, user activity streams, financial tick data) and ingestion rate matters? Use InfluxDB or VictoriaMetrics as the storage backend and build your ingestion pipeline on top of their write APIs.

Do you need SQL JOINs, and your write rate is moderate? Use TimescaleDB. The operational cost is lower than running a separate TSDB cluster.

Are you building a specialized TSDB from scratch? You are almost certainly solving a problem that existing tooling already handles. Validate the gap before committing.

Closing

The properties of time-series data (append-heavy, monotonic timestamps, redundant values, aggregation-focused reads) directly dictate every design choice in a TSDB: LSM trees over B-trees for the write path, columnar compression for storage efficiency, pre-computed rollups for query performance, and tiered retention to manage storage cost. The failure modes (cardinality explosions, rollup lag, compaction pressure) are predictable if you understand the underlying structures. Build your mental model around those tradeoffs and the right tool for any specific workload becomes obvious.