Amazon Timestream: Time Series Data at Scale Without Managing Infrastructure

Storing time series data in RDS or DynamoDB works at small scale. As data volume grows, the problems compound. Relational databases were not designed for append-only high-velocity writes. Indexes that speed up time range queries on small tables become expensive to maintain on billions of rows. Retention becomes a manual cleanup job. Aggregating metrics across millions of data points at query time strains any row-oriented database.

Amazon Timestream is a serverless time series database purpose-built for this workload. It automatically tiers recent data to in-memory storage for fast queries and older data to magnetic storage for cost-efficient long-term retention. In this article I will walk through the Timestream data model, ingestion via the SDK and Kinesis, and the query patterns that make time series analysis fast and cost-efficient.

Timestream Data Model

Timestream organizes data into databases and tables, similar to relational databases, but the underlying model is different. Each row in a Timestream table is a measurement: a time value, one or more dimensions that identify the source of the measurement, and one or more measure values that are the actual data points.

Dimensions are the metadata that identify what the measurement is about: the server name, the region, the service, the customer ID. They are low-cardinality labels that group time series together. Measures are the actual values being recorded: CPU utilization, request count, latency, error rate. Each unique combination of dimensions forms a distinct time series within the table.

Creating a Timestream Database and Table

resource "aws_timestreamwrite_database" "metrics" {
  database_name = "application-metrics"
  kms_key_id    = aws_kms_key.timestream.arn

  tags = {
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

resource "aws_timestreamwrite_table" "service_metrics" {
  database_name = aws_timestreamwrite_database.metrics.database_name
  table_name    = "service-metrics"

  retention_properties {
    memory_store_retention_period_in_hours    = 24
    magnetic_store_retention_period_in_days   = 365
  }

  magnetic_store_write_properties {
    enable_magnetic_store_writes = true

    magnetic_store_rejected_data_location {
      s3_configuration {
        bucket_name        = aws_s3_bucket.timestream_errors.bucket
        encryption_option  = "SSE_KMS"
        kms_key_id         = aws_kms_key.timestream.arn
      }
    }
  }

  tags = { ManagedBy = "terraform" }
}

The retention configuration defines two tiers. Memory store retains 24 hours of data in-memory for fast recent queries. Magnetic store retains 365 days in cost-efficient storage for historical analysis. Queries automatically span both tiers transparently. You pay for memory store at a higher rate and magnetic store at a fraction of the cost. Tune the memory store retention to cover your operational query window, typically the last 24 to 72 hours.

enable_magnetic_store_writes = true allows direct writes to magnetic storage for late-arriving data. Data ingested with timestamps older than your memory store retention window would otherwise be rejected. Enable this if your data sources can have delays or if you need to backfill historical data.

Writing Data

import boto3
import time
from typing import List, Dict

timestream = boto3.client("timestream-write", region_name="us-east-1")

def write_metrics(service: str, region: str, metrics: Dict[str, float]):
    current_time = str(int(time.time() * 1000))

    common_attributes = {
        "Dimensions": [
            {"Name": "service", "Value": service},
            {"Name": "region",  "Value": region}
        ],
        "Time":     current_time,
        "TimeUnit": "MILLISECONDS"
    }

    records = [
        {
            "MeasureName":  metric_name,
            "MeasureValue": str(metric_value),
            "MeasureValueType": "DOUBLE"
        }
        for metric_name, metric_value in metrics.items()
    ]

    response = timestream.write_records(
        DatabaseName     = "application-metrics",
        TableName        = "service-metrics",
        CommonAttributes = common_attributes,
        Records          = records
    )

    if response.get("RecordsIngested", {}).get("Total", 0) != len(records):
        rejected = response.get("RecordsIngested", {}).get("MagneticStore", 0)
        print(f"Warning: {rejected} records went to magnetic store rejection bucket")

write_metrics(
    service = "order-service",
    region  = "us-east-1",
    metrics = {
        "request_count": 1523.0,
        "error_count":   12.0,
        "p99_latency_ms": 145.0,
        "cpu_utilization": 42.3
    }
)

Using CommonAttributes for dimensions and timestamp reduces the payload size significantly when writing multiple metrics for the same source at the same time. Each record in the batch only needs to contain the measure name and value. Timestream applies the common attributes to all records automatically, which both reduces network payload and improves write throughput.

Querying with Timestream SQL

import boto3

timestream_query = boto3.client("timestream-query", region_name="us-east-1")

def query_service_error_rate(service: str, hours: int = 1) -> list:
    query = f"""
    SELECT
        bin(time, 5m)              AS time_bucket,
        service,
        SUM(CASE WHEN measure_name = 'error_count'   THEN measure_value::double ELSE 0 END) AS errors,
        SUM(CASE WHEN measure_name = 'request_count' THEN measure_value::double ELSE 0 END) AS requests,
        ROUND(
            SUM(CASE WHEN measure_name = 'error_count'   THEN measure_value::double ELSE 0 END) /
            NULLIF(SUM(CASE WHEN measure_name = 'request_count' THEN measure_value::double ELSE 0 END), 0) * 100,
            2
        ) AS error_rate_pct
    FROM "application-metrics"."service-metrics"
    WHERE
        time BETWEEN ago({hours}h) AND now()
        AND service = '{service}'
    GROUP BY bin(time, 5m), service
    ORDER BY time_bucket DESC
    """

    response = timestream_query.query(QueryString=query)
    return response["Rows"]

results = query_service_error_rate("order-service", hours=6)

Timestream SQL uses familiar SQL syntax with time series extensions. bin(time, 5m) is the time bucketing function that groups measurements into 5-minute windows. ago(1h) is a relative time function that returns the timestamp 1 hour before the current time. These built-in functions eliminate the need for complex timestamp arithmetic that standard SQL requires for time series work.

Integration with Grafana

Timestream has a native Grafana data source plugin. Connect it using IAM authentication from your Grafana instance’s execution role. Once connected, you write Timestream SQL queries directly in Grafana dashboard panels. The bin() function maps naturally to Grafana’s time grouping, and Grafana’s variable interpolation works with Timestream’s parameterized queries for dynamic dashboard filtering.

Closing Thoughts

Timestream is the right database for metrics, IoT sensor data, application performance data, and any workload where you are storing sequences of timestamped measurements and querying them by time range with aggregations. The automatic tiering removes storage management. The serverless model removes capacity planning. The built-in time series functions remove the complexity of doing temporal analysis in standard SQL.

Use it for your application metrics pipeline if you currently push metrics to RDS or DynamoDB and struggle with query performance or storage costs as data volume grows. The migration investment is typically a day or two of work and the query performance improvement on multi-month time range aggregations is dramatic.

Enjoy the cloud.

Osama


#AWS #Timestream #TimeSeriesDatabase #CloudArchitecture #Terraform #InfrastructureAsCode #AmazonWebServices #SolutionsArchitect #CloudComputing #DataEngineering #Metrics #Observability #IoT #TechBlog #CloudInfrastructure #Grafana #Analytics #Serverless #DatabaseEngineering #DevOps

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.