Time-Series Database Design

A timestamp column does not make a database a time-series system. The workload combines continuous observations, recent-data-heavy reads, time-bounded queries across related series, and data that changes temperature with age. Offline devices upload late, labels multiply series, and long retention turns a modest rate into a large storage obligation.

A durable design begins with a workload contract rather than a storage product. It defines what identifies a series and a point, which clock gives an observation meaning, which query shapes receive indexes, how late data changes completed windows, what summaries preserve, and when each representation expires. Time-series efficiency comes from making time and series identity explicit at every lifecycle stage, not from treating all timestamped rows as an undifferentiated log.

This article follows environmental telemetry from ingestion through querying and retirement. The same reasoning applies to infrastructure metrics, industrial sensors, market observations, and product measurements, although their point semantics and tolerance for loss differ.

Define Series Identity and Time Semantics #

A point combines a timestamp with one or more observed values. A series is the ordered set of points sharing a stable identity. In a metrics model, that identity is often a metric name plus normalized labels:

temperature_celsius{
  tenant="acme",
  site="west",
  device="thermostat-042",
  sensor="ambient"
}

The numeric temperature is a field, not a label: grouping and filtering usually use labels, while aggregation uses fields. Putting a continuously varying value into series identity would create a new series whenever the value changed.

Decide what every identity dimension means. Is a rename a new series or a metadata edit? Is site the historical location at observation time or the device’s current site? Retroactive label rewrites change old query results and may require reindexing. Prefer immutable identity labels and keep mutable descriptions separate unless historical relabeling is required.

Time also has more than one meaning:

• Event time is when the source says the observation occurred.
• Ingest time is when the database accepted it.
• Processing time is when a rollup or alert evaluated it.

Store event time for analytical ordering and ingest time for delay diagnostics. A declared server-time fallback for a missing device timestamp changes meaning and must be marked. Clock quality, source timezone, and precision belong to the input contract.

Define point identity independently of time: two readings may share a timestamp, while retries must not count twice. Use a source sequence, event ID, or stable tuple such as (series_id, observed_at, source_sequence). If only one value per series and timestamp is allowed, define whether conflicts reject, replace, or retain versions; arrival order makes “last write wins” ambiguous.

Finally, name field semantics. A gauge such as temperature can be averaged over time. A cumulative counter such as energy consumed needs differences and reset handling. A histogram or distribution requires mergeable bucket or sketch state. Storage cannot choose a correct rollup without knowing what a value represents.

Make Ingestion Append-Friendly and Idempotent #

Time-series ingestion is usually append-heavy, not append-only. Most points target the current time window, but retries create duplicates and disconnected sources create late writes into older windows. The storage path should optimize the common append while retaining a bounded correction path.

A logical schema can separate series metadata from points:

CREATE TABLE telemetry_series (
  series_id BIGINT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  site_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  sensor_kind TEXT NOT NULL,
  unit TEXT NOT NULL,
  UNIQUE (tenant_id, site_id, device_id, sensor_kind)
);

CREATE TABLE telemetry_points (
  series_id BIGINT NOT NULL,
  observed_at TIMESTAMPTZ NOT NULL,
  source_sequence BIGINT NOT NULL,
  received_at TIMESTAMPTZ NOT NULL,
  value DOUBLE PRECISION NOT NULL,
  quality SMALLINT NOT NULL,
  PRIMARY KEY (series_id, observed_at, source_sequence)
);

This is a logical model; a specialized database may encode it into chunks rather than literal SQL tables. The essential properties are stable series IDs, ordered event time, a deduplication key, and separately queryable labels.

Batch writes to amortize network, logging, parsing, and index work. Validate types and timestamp bounds, resolve series IDs, then group points by series and time. Define atomic rejection or precise item-level outcomes, and cap bytes and points per batch.

Define acknowledgment as buffered, write-ahead logged, or replicated durability. Retries are safe only with stable point identities; acknowledging before the promised boundary can lose accepted observations on restart.

Many engines keep an uncompressed head for recent appends, then seal it into immutable chunks. In-window points can be ordered or briefly buffered; older arrivals use patches, deltas, or controlled rewrites. Bound this correction path so years-old timestamps cannot trigger unlimited random work.

Expose backpressure through queue depth, log latency, head memory, and compaction debt. Retry responses need an interval and idempotency guarantee; deliberate loss needs an explicit sampling policy, not a success status.

Index for Bounded Query Shapes #

The dominant lookup is usually “these series over this time interval.” A series-first timestamp index, conceptually (series_id, observed_at), makes one series window contiguous. Chunk metadata then maps the interval to bounded blocks and skips chunks wholly before or after it.

Label queries first use an inverted or dictionary index to resolve predicates such as tenant=acme AND site=west AND sensor_kind=temperature to series IDs. The point store then scans their time ranges. This keeps repeated labels out of each point and avoids scanning all values.

Indexes should match supported query patterns:

Do not index every label combination. Inverted indexes compose equality predicates, but high-cardinality postings remain expensive. Full-text, suffix, and unrestricted regex search have distinct costs; budget them or exclude them from interactive APIs.

Time-only ordering favors fleet-wide interval scans; series-first ordering favors individual histories. No layout makes both contiguous. Tenant/time chunking, secondary structures, or rollups can serve broad scans, but every representation adds ingestion and repair work. Choose from query frequency and latency requirements.

Use half-open intervals, [start, end), so adjacent APIs and rollups do not double-count boundaries. Require interactive time bounds, cap matched series and returned points, and reject or mark partial results honestly. A dashboard typo must not trigger a full-retention scan.

Work a Telemetry Query End to End #

Consider an illustrative deployment with 1,000 devices, two sensor series per device, and one point every ten seconds. If all devices report continuously, the nominal daily point count is

This is arithmetic for the example, not a measured ingestion claim. Real capacity planning must include outages, bursts, duplicate retries, label churn, index bytes, replication, and headroom.

An operator requests five-minute average temperature for the west site over the previous day. Suppose the label index resolves site=west and sensor_kind=temperature to 400 series in this example. A raw execution has up to

candidate points before gaps and duplicates are considered. The query planner should resolve series IDs once, identify overlapping chunks from exact time bounds, and aggregate per five-minute bucket without constructing unrelated labels for each point.

For a gauge, a five-minute rollup can store sum, count, min, max, and perhaps first and last values with their timestamps:

rollup_key = (series_id, bucket_start, resolution)
state      = (sum, count, min, max, first_time, first_value,
              last_time, last_value, source_version)

There are 288 five-minute buckets in a complete day, so the query examines at most 115,200 per-series rollup rows before grouping across the 400 series. Missing observations make count essential. The site-wide mean is

not the unweighted average of per-series averages. Averaging averages would give a briefly connected device the same weight as one reporting all day.

The planner must select a representation whose resolution can answer the request. Five-minute rollups can answer five-minute or wider sums and means when bucket boundaries align. They cannot reconstruct ten-second maxima at arbitrary boundaries, nor can sum and count produce a percentile. Percentiles require retained raw values or a mergeable distribution summary with a documented error contract.

If the final bucket is still open, the query may combine completed rollups with raw head points for the partial interval. The cutoff must be one declared watermark so points are not omitted or counted in both representations. Explain output should state raw versus rollup ranges, series count, chunks selected, and estimated points.

Compress Sealed Chunks Without Freezing Corrections #

Ordered series chunks expose useful regularity. Small timestamp deltas and repetitive delta-of-delta suit regular sampling; integers support frame-of-reference or bit packing; adjacent floating-point bit patterns may compress well. Repeated quality codes and stable metadata suit run-length or dictionary encoding.

Compression ratios are workload-specific: irregular timestamps, noise, sparsity, nulls, and encryption change the result. Measure encoded bytes per point by metric family and age; a global average can hide one metric producing most storage.

After the common write window closes, sealing can sort points, resolve duplicates, build bounds and checksums, choose encodings, and atomically publish an immutable chunk. Queries must see either the valid inputs or the replacement, never a partial chunk.

Late corrections create a tradeoff. Rewriting a large compressed chunk for one point is expensive; accumulating many patch fragments makes reads merge more inputs. Use a bounded delta and compact when patch count, bytes, or read amplification crosses policy. Tombstones for deletion have the same lifecycle: they must remain visible until compaction proves the removed values cannot reappear.

Compression competes with ingestion and queries for CPU and I/O. More aggressive codecs can lower storage and transfer while increasing decode or compaction cost. Recent data may favor fast encoding; cold data may justify denser encoding if query latency permits. Keep format versions readable through the entire retention period or schedule explicit rewrites before retiring a decoder.

Coordinate Late Data, Downsampling, and Retention #

Late data is a semantic issue before it is a storage issue. A device can buffer observations during an outage and upload them hours later. Decide the maximum accepted event age, whether alerts reevaluate, whether dashboards revise historical buckets, and what clients see when data arrives beyond the correction window.

A watermark estimates that observations before a time are sufficiently complete for a processing purpose. It is not proof that no older event will ever arrive. An illustrative policy might keep raw chunks mutable for two hours, accept patch writes for thirty days, and reject or archive older points. Those durations are workload decisions, not defaults. Track watermark by tenant or source class if one delayed producer should not hold every rollup open.

Rollups must be idempotent and correctable. Store the source interval and a source version or processing position. When a late point changes a closed bucket, either update its algebraic state transactionally or mark the bucket dirty and recompute it from authoritative raw points. Recompute is simpler when the raw interval remains available; after raw expiration, the system may no longer be able to incorporate a correction exactly.

Different value types need different summaries. Gauges support sum and count. Counters need first and last values plus reset detection; subtracting across a reset produces nonsense. Histograms merge bucket counts or compatible sketches. A “last” value needs its event timestamp, because arrival order does not establish temporal order.

Retention can form tiers: raw points for a short period, fine rollups longer, and coarse rollups longest. Before deleting raw data, verify every required rollup interval is complete at the expected version and test that supported queries produce equivalent results. Retention should retire bounded chunks, persist its phase, honor legal or incident holds, and remain restartable after failure.

Downsampling is irreversible information loss. Document which queries stop being possible at each age. If a user may request exact ten-second values for a year, an hourly average is not an archive of that data. Backups protect against accidental loss; retention deliberately removes information. They need separate policies and restore tests.

Control Cardinality, Churn, and Hotspots #

Series cardinality is the number of distinct label sets, not the number of points. Its rough upper bound across independent label dimensions is

although correlations usually make the realized value smaller. In the example, 1,000 device labels times two sensor kinds produces 2,000 expected series. Adding a unique request_id to every point would make the series count grow with ingestion and defeat metadata caches, label indexes, and per-series chunking.

Classify labels at schema registration. Stable bounded dimensions such as region or sensor kind are usually safe. Device IDs are high-cardinality but intentional. User IDs, URLs, stack traces, random identifiers, timestamps, and unnormalized error text need strict review or belong in fields, logs, or exemplars rather than series identity.

Enforce per-tenant budgets for active series, new-series creation rate, label count, label-name and value length, and matched series per query. A total cardinality limit alone reacts too late to churn: a producer can create and abandon thousands of series per minute while active count appears moderate. Track both active cardinality and creations over time.

Hotspots can occur even with controlled cardinality. One series receiving many concurrent writers serializes around its active state; one tenant can dominate a time bucket; synchronized devices can create bursts at exact minute boundaries. Batch writes, jitter producer schedules where semantics allow, spread ingestion work by stable series ownership, and apply tenant admission limits. Artificially salting one series spreads writes but forces every read and rollup to merge salts, so use it only for mergeable values with an explicit query cost.

Other failure modes include indexing ingest time while querying event time, using a latest-value cache that cannot be rebuilt, accepting timestamps far in the future, averaging averages, ignoring counter resets, deleting raw points before rollup verification, and allowing unbounded label regular expressions. Each is a broken workload contract rather than a mysterious storage defect.

Verify Correctness and Operate the Lifecycle #

Build a small reference model that stores points plainly and computes queries directly. Generate ordered, shuffled, duplicated, missing, and late observations, then compare the time-series engine at every supported resolution. Include equal timestamps, sequence reuse, clock skew, future timestamps, interval boundaries, leap days, and daylight-saving transitions at the API edge while keeping stored time normalized.

Rollup properties are especially testable. Merging disjoint sum and count states must equal aggregation over their union. Reprocessing the same source version must not change a bucket. Combining raw and rolled ranges must neither overlap nor leave a gap. Counter fixtures should include resets and wrap policy; percentile fixtures should validate the declared approximation bound rather than exact equality when a sketch is used.

Inject crashes before and after durable acknowledgment, chunk publication, rollup publication, compaction replacement, and retention deletion. Restore from backup, replay the durable log, rebuild latest-value indexes, and confirm point and series invariants. Corrupt chunk checksums and metadata so readers fail visibly instead of returning partial values as complete.

Operational dashboards should expose ingest points and bytes, durable-log latency, rejected batches, deduplication rate, event-time lag, active and newly created series, active-chunk memory, sealed-chunk backlog, encoded bytes per point, patch fragments, rollup watermark and dirty buckets, query series fan-out, chunks and points scanned, retention backlog, and restore status. Segment by tenant and metric family so a fleet average cannot conceal one abusive label.

Capacity estimates should connect the workload to resources. For $C$ continuously active series sampled at frequency $f$ points per second, nominal daily points are

Multiply by measured encoded bytes per point, indexes, replication, temporary compaction space, and safety headroom. Then model query concurrency and late-write amplification separately; stored bytes alone do not predict whether the head path or rollup workers will saturate.

A time-series database succeeds when it makes the lifecycle predictable: point identity survives retries, event time survives delay, indexes bound supported queries, compression respects correction windows, rollups preserve declared mathematics, and retention removes only representations the product no longer promises. Designing those contracts first keeps continuous data useful after its initial stream has become years of history.