Time series at scale: TimescaleDB and cloud costs

Why fitting billions of observations into a table is not enough

An energy plant's observation every ten seconds is a small record. As devices, channels and retention grow, the problem becomes more than disk capacity: retrieve the latest state, reconstruct an incident, identify missing observations and run reports concurrently. A time series is a collection of time-related observations; TimescaleDB is one particular PostgreSQL extension that can help manage it. Separating the data category from the product prevents a timestamp column from becoming an automatic justification for buying another platform.

A dedicated architecture earns its place through measured ingest, time-range queries, lifecycle requirements and operating capacity. A few thousand daily records and modest reporting may work well in indexed PostgreSQL. Before changing platforms, define acceptable data delay, loss, query response and recovery objectives. A SCADA control loop and monthly analytical report have different timing requirements. Database selection cannot independently establish the safety of physical control, and a large row count alone is not evidence that the current system must be replaced.

Section references: PostgreSQL · Table partitioning ↗Tiger Data · Create and configure a hypertable ↗

Size the records, active working set and recovery backlog

In a hypothetical fleet, 10,000 devices with ten metrics sampled every ten seconds produce 10,000 scalar metric records per second, or 864 million per day. At an assumed 80 logical bytes per metric record, that is 69.12 decimal GB daily and 2.0736 TB over thirty days. Network framing, database tuple overhead, indexes, WAL, replicas and backups are excluded. A wide row containing ten metrics changes the physical row count. Scalar observations, transmitted messages and stored rows must not be treated as interchangeable units.

Average arrival rate is only a starting point. Replay after an outage, shift reports and archival movement should be tested together. An index for the latest hour may fit in memory while a year-long scan has a very different I/O profile. Record peak ingest, concurrent queries, lateness distribution, correction frequency, hot retention and archive access. Measure recovery time alongside disk growth. A small file reading quickly on a laptop does not demonstrate production capacity for billions of observations or the ability to drain a large backlog without starving operational queries.

Model event time, receipt time, units and replay identity

Keep event_ts, when the observation occurred, separate from received_ts, when the center accepted it. Assigning delayed measurements to receipt time can distort historical analysis. Store instants consistently and apply local shift or daylight-saving boundaries explicitly when reporting. Define device and metric identity, unit, quality and schema version. A zero reading differs from an absent observation. Counter resets, negative derived consumption and sensor faults need explicit handling rather than disappearing behind zero-filled gaps. Timestamp precision and device clock quality also belong in the ingestion contract.

A narrow schema uses one row per metric, gaining flexibility while increasing row and index counts. Wide rows can suit fixed channels observed together, with different trade-offs for sparse or changing schemas. JSON does not remove indexing or validation costs. Source epoch and sequence identify replay; trusted authorization supplies tenant scope. TimescaleDB uniqueness constraints include partition dimensions. If a replay changes event time, a time-inclusive primary key alone cannot establish logical deduplication. Define immutable source identity and conflict handling separately from the physical uniqueness constraint.

Section references: Tiger Data · Primary keys, time and uniqueness ↗

Start TimescaleDB with an explicit, bounded schema

The reference uses the generalized create_hypertable/by_range interface introduced in TimescaleDB 2.13; verify the target PostgreSQL and extension combination. A one-day chunk and device/metric/time index are illustrative choices for an empty table. The query explicitly bounds tenant and time, with tenant scope derived from authorized server context. Its composite key helps reject replay of an unchanged source record, but does not justify silently discarding conflicting content. Deployments still need validation, conflict handling, permissions and a tested migration plan; this sketch does not implement those components.

The hourly view retains sum, count, minimum and maximum rather than only an average. Larger intervals can calculate a count-weighted average instead of averaging unequal groups equally. Quality=0 is only this example's acceptance convention. WITH NO DATA leaves initial history unmaterialized: schedule a bounded backfill and refresh, and separately handle corrections outside the seven-day window. The SQL is a reference sketch, not a database-executed benchmark. The later Node.js cost example is separate and executable; neither example represents a customer deployment or a ready production migration.

-- Reference sketch: TimescaleDB 2.13+ generalized API.
-- Confirm extension/PostgreSQL compatibility in an isolated database.
CREATE TABLE measurement_sample (
  event_ts timestamptz NOT NULL,
  received_ts timestamptz NOT NULL,
  tenant_id bigint NOT NULL,
  device_id bigint NOT NULL,
  metric_id integer NOT NULL,
  source_epoch uuid NOT NULL,
  source_sequence bigint NOT NULL,
  value double precision NOT NULL,
  quality smallint NOT NULL CHECK (quality BETWEEN 0 AND 2),
  PRIMARY KEY (tenant_id, device_id, metric_id,
               event_ts, source_epoch, source_sequence)
);
SELECT create_hypertable('measurement_sample',
  by_range('event_ts', INTERVAL '1 day'));
CREATE INDEX measurement_lookup ON measurement_sample
  (tenant_id, device_id, metric_id, event_ts DESC);

-- One metric/unit per metric_id; quality=0 means accepted in this example.
CREATE MATERIALIZED VIEW measurement_hourly
WITH (timescaledb.continuous) AS
SELECT tenant_id, device_id, metric_id,
       time_bucket(INTERVAL '1 hour', event_ts) AS bucket,
       count(*) AS n, sum(value) AS total_value,
       min(value) AS low_value, max(value) AS high_value
FROM measurement_sample WHERE quality = 0
GROUP BY tenant_id, device_id, metric_id, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('measurement_hourly',
  start_offset => INTERVAL '7 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes');
-- Initial history needs an explicit, bounded backfill/refresh.
-- Retention is intentionally NOT enabled in this sketch.
-- Prepared query: tenant scope MUST come from authorized server context.
SELECT event_ts, value, quality FROM measurement_sample
WHERE tenant_id = $1 AND device_id = $2 AND metric_id = $3
  AND event_ts >= $4::timestamptz AND event_ts < $5::timestamptz
ORDER BY event_ts;

Section references: Tiger Data · Create and configure a hypertable ↗Tiger Data · Refresh continuous aggregates ↗

Ingest with bounded batches and explicit acknowledgment

Opening a connection and transaction for every observation can amplify networking and logging overhead. Evaluate a bounded connection pool and batched writes or a suitable bulk-loading path. Set batch limits in bytes and waiting time as well as rows, with a recoverable failure boundary. Oversized batches can create memory and lock pressure; tiny batches amplify fixed overhead. Validation, quarantine and durable acceptance need one explicit delivery contract. A broker acknowledgment is not a database commit receipt, and retries must preserve source identity rather than manufacturing new observations.

Separate replay capacity from ordinary ingest instead of letting an unbounded recovery queue dominate both. Define tenant fairness, backpressure and retry limits. Delayed valid observations differ from corrupt future timestamps or unsupported formats. In-order and out-of-order writes have different consequences for indexes and already compacted history. Track accepted, duplicated, quarantined and missing records separately. Throughput measured only at the database endpoint can look healthy while upstream drops or repeatedly rejected records remain invisible to the operational team.

Tune chunks, indexes and columnstore for the actual workload

Chunk duration influences the active write/index working set. Very large chunks can enlarge hot indexes; very small chunks increase object, planning and maintenance overhead. One day is a starting hypothesis, not a tuning rule. Measure memory, the number of active chunks touched by late arrivals, plans and I/O together. Avoid indexing every metric attribute reflexively. Start with a small set aligned to tenant/device/metric/time access and verify their use under representative concurrency, including recovery writes and long-range reports.

For TimescaleDB columnstore/Hypercore, evaluate segmentation and ordering against data distribution and query filters. A different segment identity per row can prevent useful groups. Fresh mutable data and older mostly-read history need not use identical physical organization. Compression depends on types and repetition; an advertised maximum is not a budget guarantee. Measure transformation CPU/I/O, late-update behavior and archive query latency. Also distinguish managed-cloud tiering from features available in the chosen self-hosted edition and license instead of assuming deployment parity.

Section references: Tiger Data · Create and configure a hypertable ↗Tiger Data · Columnstore compression methods ↗

What rollups preserve, and what they discard

Minute or hourly summaries can avoid repeatedly scanning every raw point for a year-long chart. A minute average cannot preserve a short spike; incident review may need min/max, counts, quality and selected raw windows. An ordinary average of irregularly sampled power is not automatically energy: units, time weighting and gap rules matter. Averaging subgroup p95 values also does not reconstruct the combined percentile. Choose retained statistics according to the decision rather than using the same AVG template for every signal.

Continuous aggregate refresh is neither instantaneous nor unlimited. The example excludes the latest hour; explicitly choose how the dashboard serves the raw tail. A seven-day refresh window does not automatically cover older corrections. Refreshing intervals after their source data has been removed can also remove previously retained aggregates. Design raw retention, summary retention, lateness and manual recalculation together. Before enabling deletion, verify historical reporting and restore behavior; do not assume that an aggregate is an independent permanent archive simply because it is materialized.

Section references: Tiger Data · Refresh continuous aggregates ↗Tiger Data · Retention with continuous aggregates ↗

Which platforms are candidates for which problem?

Choose using joins, corrections, query diversity, operating skills and residency as well as ingest. The table is a decision framework reviewed on 20 October 2026, not a performance ranking. PostgreSQL/TimescaleDB are candidates when relational context accompanies observations; ClickHouse merits evaluation for column-oriented analytical workloads. InfluxDB needs an explicit engine, version and service edition. A query language, quota or operating capability in one offering is not a guarantee for every product carrying the same family name. Use the relevant documentation and a repeatable workload to decide.

Prometheus is a candidate for operational metrics and alerting, without automatically becoming a complete business-event ledger. Parquet in object storage with tools such as DuckDB can serve historical training and occasional batch analysis; this is not, by itself, a low-latency ingestion service. On AWS, LiveAnalytics and Timestream for InfluxDB are distinct offerings. AWS states that new-customer access to LiveAnalytics closed on 20 June 2025. Do not present it as unconditionally available for a new project; recheck the actual service and account eligibility before procurement.

Section references: PostgreSQL · Table partitioning ↗ClickHouse · MergeTree ↗InfluxDB 3 Core · Query languages and clients ↗DuckDB · Parquet reads and writes ↗AWS · LiveAnalytics new-customer availability ↗

Control cardinality and avoid duplicating every retention tier

Label cardinality is the number of distinct value combinations. Combining device, metric, software version and an unbounded request identifier can create far more series than expected. Prometheus specifically cautions against labels carrying unbounded values such as user identifiers. Engines do not all share the same limit or cost curve; measure index, metadata and memory behavior for the chosen product. Personal information in labels also expands access and retention obligations. Some debugging detail belongs in bounded-lifetime event records rather than a permanent metric dimension.

Give the hot store, rollups and historical archive separate purposes. Indefinitely keeping every raw row in the database, data lake and dashboard cache multiplies cost and deletion work. Parquet partitioning and file sizing influence analytical reads; tiny files can amplify listing and open overhead. Track manifests, schema versions, coverage and checksums. Exporting files is not proof of recovery. Test reading them back, access controls and integrity, including schema evolution and any correction that must reconcile with already exported partitions.

Section references: Prometheus · Metric and label naming ↗DuckDB · Parquet reads and writes ↗

Why the cloud bill is more than GB times a storage rate

Separate compute, hot disk, object storage, ingest/query consumption, I/O, backups, replicas, egress and support. Thirty-day retention capacity is not necessarily the average capacity billed during a growing first month. Distinguish ramp-up from steady state, decimal GB from GiB, and logical volume from physical compressed bytes. Ask how replicas affect both compute and storage, how high availability differs from read scaling, and whether minimum service sizes apply. A free introductory tier is not the total production cost or evidence that a recovery configuration is included.

The Tiger Cloud pricing page reviewed on 20 October 2026 separates compute and storage, and its effective storage headline incorporates a compression assumption. ClickHouse Cloud documents its own billing components; InfluxDB distinguishes service offerings. Starting prices are not directly comparable until units and included responsibilities align. AWS Timestream pricing also depends on the selected service/engine. This guide links current pricing references instead of presenting a static list as a production quote. The rates in the next section are deliberately synthetic and do not represent any provider's tariff.

Section references: Tiger Cloud · Pricing and billing FAQ ↗ClickHouse Cloud · Billing overview ↗InfluxData · Deployment and pricing options ↗AWS · Timestream pricing ↗

Executable sensitivity model: egress can outweigh storage savings

The model assumes thirty-day steady retention: seven hot days, twenty-three cold days, hypothetical 4:1 cold compression and two storage copies. Compute uses two nodes for a thirty-day, 720-hour month. Every USD rate is an invented educational input. The result is approximately USD 993.38. Raising egress from 2,500 GB to 10,000 GB adds USD 675 at the same synthetic rate, illustrating how repeated large result transfers can dominate a small storage saving. This arithmetic is a sensitivity exercise, not a quotation or evidence of achieved compression.

The simple model deliberately treats the assumed logical bytes as a storage proxy. Actual capacity needs measured tuple/index expansion, WAL, query scratch space, backup growth, request charges, topology and operating labor. Recalculate if compression differs. The example rejects negative inputs, a zero sampling interval and a hot window longer than retention. Zero devices can still incur fixed compute charges. Map measured consumption to the provider calculator before making a budget decision; the code's total cannot substitute for that mapping or establish that a particular availability design will work.

const assert = require('node:assert/strict');
// Synthetic USD rates and storage assumptions; NOT any vendor's quote.
const rates = { nodeHour: 0.35, hotGBMonth: 0.20,
  coldGBMonth: 0.03, backupGBMonth: 0.02, egressGB: 0.09 };
function estimate(x) {
  for (const key of ['devices','metrics','intervalSeconds','bytesPerRecord',
    'retentionDays','hotDays','coldCompression','storageCopies','nodes',
    'hours','backupGB','egressGB','otherUSD'])
    assert(Number.isFinite(x[key]) && x[key] >= 0, key);
  assert(x.intervalSeconds > 0 && x.coldCompression >= 1);
  assert(x.hotDays <= x.retentionDays && x.storageCopies >= 1);
  const recordsPerDay = x.devices * x.metrics * 86400 / x.intervalSeconds;
  const logicalGBPerDay = recordsPerDay * x.bytesPerRecord / 1e9;
  // A steady-state arithmetic model, not measured database disk usage.
  const hotGB = logicalGBPerDay * x.hotDays * x.storageCopies;
  const coldGB = logicalGBPerDay * (x.retentionDays - x.hotDays)
    / x.coldCompression * x.storageCopies;
  const parts = {
    compute: x.nodes * x.hours * rates.nodeHour,
    hot: hotGB * rates.hotGBMonth,
    cold: coldGB * rates.coldGBMonth,
    backup: x.backupGB * rates.backupGBMonth,
    egress: x.egressGB * rates.egressGB,
    other: x.otherUSD
  };
  return { id: 'timeseries-cost-sensitivity', recordsPerDay,
    logicalGBPerDay, hotGB, coldGB, parts,
    totalUSD: Object.values(parts).reduce((a,b) => a+b, 0) };
}
const base = { devices:10000, metrics:10, intervalSeconds:10,
  bytesPerRecord:80, retentionDays:30, hotDays:7, coldCompression:4,
  storageCopies:2, nodes:2, hours:720, backupGB:600,
  egressGB:2500, otherUSD:35 };
const result = estimate(base);
assert.equal(result.recordsPerDay, 864000000);
assert.equal(result.logicalGBPerDay, 69.12);
assert(Math.abs(result.totalUSD - 993.3824) < 1e-8);
const moreEgress = estimate({...base, egressGB:10000});
assert(Math.abs(moreEgress.totalUSD-result.totalUSD-675) < 1e-8);
assert(estimate({...base, coldCompression:1}).totalUSD > result.totalUSD);
assert.equal(estimate({...base, devices:0}).logicalGBPerDay, 0);
assert.throws(() => estimate({...base, intervalSeconds:0}));
assert.throws(() => estimate({...base, hotDays:31}));
assert.throws(() => estimate({...base, egressGB:-1}));
console.log(result, moreEgress);

Put cost controls on dashboards, queries and egress

A twelve-panel dashboard refreshed every ten seconds for a hundred users may repeatedly scan the same raw history if every panel issues a separate query. Establish the needed refresh, range and resolution, then evaluate shared rollups, caching and server-side query boundaries. Roles and decisions should constrain range and result volume. Avoid unnecessary cross-region or external transfers between application and database, while also checking private-network charges and responsibilities. Moving the dashboard closer to the data may reduce transfers, but its effect must be measured rather than assumed.

Analytical services charging by scans or capacity can give the same SQL different cost behavior. BigQuery documents pre-query estimates, quotas and maximum billed bytes; LIMIT alone is not a general scan-cost cap. Verify the relevant controls for each provider. A budget notification should not be assumed to stop spending automatically. Attribute usage by tenant, workload and environment, with owners for expensive queries, refresh jobs and archive reads. Define what an alert triggers and how essential operations remain available when a quota or admission limit is reached.

Section references: BigQuery · Estimate and control costs ↗

Validate operations, forecasting and residency together

A pilot should include more than easily compressed, ordered observations. Add late arrivals, clock drift, retries, poor quality, uneven tenants and corrections. During concurrent writes and reads, record latency distributions, errors, resources, background backlog and billable usage. Report cold-cache and warm-cache results separately. Comparisons must preserve retention, correctness and service objectives; reducing reliability or query coverage to make one option look cheaper is not a useful decision test. Restore and export trials belong alongside throughput measurements.

Fast time-series reads are not enough for forecasting. Preserve temporal separation between training and evaluation, control leakage from later corrections and record dataset versions. Under a residency requirement, review backups, archives, keys and support alongside the database region. Do not assume a managed service exists in the required geography or conceal self-hosting responsibilities. Gokbilge Tech can address the pipeline across data/observability, SCADA/energy, energy AI and forecasting-model improvement. These links describe engineering scope, without claiming that a named platform is already deployed in a particular product.

• Were observations, messages, rows, physical bytes and peak load distinguished?
• Were late data, conflicting replay, rollup correctness and retention/refresh interactions tested?
• Were compute, copies, backups, queries, egress and operating labor costed together?
• Were recovery, export, residency and license/version boundaries documented?