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