How can I run fast “last 15 minutes” and “last 12 months” analytics queries on the same Postgres dataset?

Most teams hit a wall when they try to run both “last 15 minutes” operational checks and “last 12 months” analytics on the same Postgres tables. The workload is different, the access patterns are different, and plain btree indexes and monolithic tables eventually crumble under the mix of high ingest and long-range scans.

Quick Answer: Use TimescaleDB’s hypertables for “live” windows (like last 15 minutes) and continuous aggregates for long-range analytics (like last 12 months), all inside Postgres. You keep a single dataset, but you query different physical layouts optimized for each type of workload.

---

The Quick Overview

• What It Is: A Postgres-native pattern using TimescaleDB hypertables + continuous aggregates so that “last N minutes” and “last N months” queries are both fast and predictable on the same telemetry dataset.
• Who It Is For: Teams running high-ingest time-series/event data in Postgres (IoT, observability, product analytics, trading ticks) who want real-time and historical analytics without building a separate OLAP stack.
• Core Problem Solved: Plain Postgres tables and indexes don’t scale well when you combine real-time writes, short-window lookups, and long-range aggregates in one place. This approach gives you the right storage layout for each query type without leaving Postgres.

---

How It Works

The core idea is: one logical table, multiple physical views.

You:

1. Store all raw events in a TimescaleDB hypertable. This is your source of truth and your “live” data path.
2. Define continuous aggregates on top of that hypertable to precompute longer-range rollups (hourly, daily, etc.).
3. Route queries:
   • Short windows (e.g., last 15 minutes): query the hypertable directly.
   • Long windows (e.g., last 12 months): query one or more continuous aggregates.

TimescaleDB automates the partitioning, refresh, and compression behind the scenes so you use standard SQL against Postgres objects while the engine does the heavy lifting.

1. Model your telemetry as a hypertable

Start with a normal Postgres table, then convert it into a hypertable. You pick a time column, and optionally a partition key (like device, tenant, customer_id):

CREATE TABLE telemetry (
    time        TIMESTAMPTZ       NOT NULL,
    device_id   TEXT              NOT NULL,
    metric      TEXT              NOT NULL,
    value       DOUBLE PRECISION  NOT NULL,
    tags        JSONB,
    PRIMARY KEY (time, device_id, metric)
);

SELECT create_hypertable(
    'telemetry',
    'time',
    'device_id',
    4  -- number of space partitions; tune for your cardinality/cores
);

What this gives you:

• Automatic partitioning by time + device_id.
• Fast ingest because new rows append mostly to hot chunks.
• Fast “recent window” queries because the planner only touches recent chunks.

2. Tune the hypertable for “last 15 minutes” queries

Most “last 15 minutes” queries look like:

SELECT
    device_id,
    metric,
    AVG(value) AS value_avg
FROM telemetry
WHERE
    time > now() - INTERVAL '15 minutes'
    AND metric = 'cpu_usage'
GROUP BY device_id, metric;

To make this fast:

1. Choose an appropriate chunk interval: small enough that “last 15 minutes” hits just a few chunks, but big enough not to explode chunk count.

SELECT set_chunk_time_interval('telemetry', INTERVAL '1 day');

For very high ingest, you might go shorter (e.g., 6 hours). The right value depends on data volume and retention.

2. Create targeted indexes that match your filters and sort orders:

-- Optimize time + metric filters
CREATE INDEX ON telemetry (metric, time DESC);

-- If you often filter by device and time:
CREATE INDEX ON telemetry (device_id, time DESC);

3. Keep hot data uncompressed, cold data compressed:

ALTER TABLE telemetry
SET (timescaledb.compress,
     timescaledb.compress_segmentby = 'device_id',
     timescaledb.compress_orderby   = 'time DESC');

SELECT add_compression_policy(
    'telemetry',
    compress_after => INTERVAL '7 days'
);

Recent data (like last 15 minutes) stays in row-store, uncompressed, optimized for writes and low-latency scans. Older data is aggressively compressed for long-term storage and historical queries.

Note: Queries for very recent windows (“last 15 minutes”, “last 1 hour”) should hit only a handful of uncompressed chunks and correctly indexed columns—that’s where hypertables shine.

3. Add continuous aggregates for “last 12 months” analytics

Long-range queries are where plain Postgres starts to choke:

SELECT
    date_trunc('day', time) AS day,
    AVG(value) AS avg_value
FROM telemetry
WHERE
    metric = 'cpu_usage'
    AND time > now() - INTERVAL '12 months'
GROUP BY day
ORDER BY day;

Running that on billions of rows repeatedly is expensive. Instead, create a continuous aggregate that maintains daily rollups:

CREATE MATERIALIZED VIEW telemetry_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', time) AS bucket,
    device_id,
    metric,
    AVG(value) AS avg_value,
    MIN(value) AS min_value,
    MAX(value) AS max_value,
    COUNT(*)   AS samples
FROM telemetry
GROUP BY bucket, device_id, metric;

Now, wire up an automated refresh policy:

SELECT add_continuous_aggregate_policy(
    'telemetry_daily',
    start_offset => INTERVAL '13 months', -- recompute back this far
    end_offset   => INTERVAL '5 minutes', -- avoid racing with latest ingest
    schedule_interval => INTERVAL '15 minutes'
);

Behavior:

• New data flows into telemetry.
• Every 15 minutes, TimescaleDB incrementally recomputes daily aggregates for the past 13 months, but stops 5 minutes short of “now” so it doesn’t chase the tail of incoming writes.
• Queries against telemetry_daily scan aggregated rows, not raw data.

Your 12‑month query becomes:

SELECT
    bucket AS day,
    AVG(avg_value) AS daily_avg
FROM telemetry_daily
WHERE
    metric = 'cpu_usage'
    AND bucket > now() - INTERVAL '12 months'
GROUP BY bucket
ORDER BY bucket;

This is orders of magnitude faster because:

• You’re reading one row per (day, device, metric), not every raw event.
• Historical chunks of telemetry can stay heavily compressed and even tiered out to object storage without slowing this query.

4. Combine both paths in a single “live + history” query

Sometimes you want a seamless answer covering both pre-aggregated history and ultra-fresh data. The pattern:

1. Use the continuous aggregate for everything up to a recent watermark.
2. Use the hypertable for the tail since that watermark.

Example: 12 months of data, with the last 15 minutes served straight from the hypertable:

WITH history AS (
    SELECT
        bucket AS ts,
        metric,
        device_id,
        avg_value
    FROM telemetry_daily
    WHERE
        metric = 'cpu_usage'
        AND bucket >= now() - INTERVAL '12 months'
        AND bucket <  now() - INTERVAL '15 minutes'
),
recent AS (
    SELECT
        time_bucket('1 minute', time) AS ts,
        metric,
        device_id,
        AVG(value) AS avg_value
    FROM telemetry
    WHERE
        metric = 'cpu_usage'
        AND time >= now() - INTERVAL '15 minutes'
    GROUP BY ts, metric, device_id
)
SELECT * FROM history
UNION ALL
SELECT * FROM recent
ORDER BY ts;

You can wrap this into a view or a function so application code just hits SELECT * FROM telemetry_cpu_usage_12m().

---

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Hypertables	Partition tables by time (and optional key) automatically.	Fast ingest and fast “last N minutes/hours” queries.
Continuous Aggregates	Incrementally materialize rollups over hypertables.	Fast “last 12 months” analytics without scanning raw data.
Compression & Tiering	Compress and move cold chunks to cheap storage on policies.	Store 12+ months of data at lower cost while keeping it queryable.

---

Ideal Use Cases

• Best for operational dashboards: Because you can power live tiles (error rates in last 15 minutes, latency in last 1 hour) directly from hypertables while using continuous aggregates for the 24h/7d/30d charts on the same screen.
• Best for product and IoT analytics: Because you can keep a full year (or more) of high-granularity data online, run cohort and trend analysis via continuous aggregates, and still debug “what just happened” by querying raw events.

---

Limitations & Considerations

• Eventual consistency of aggregates: Continuous aggregates are not strictly real-time. Due to start_offset, end_offset, and schedule_interval, there’s a small lag between raw data and aggregate availability. For strict “to-the-second” metrics, always pull the most recent slice from the hypertable.

  Important: Design your dashboards so “last 5–15 minutes” comes from the hypertable and everything older comes from the continuous aggregate.

• Schema changes require care: Changing columns used in continuous aggregates (e.g., dropping/renaming) can require rebuilding the aggregate. Plan migrations carefully and practice them in non‑production environments.

---

Pricing & Plans (on Tiger Cloud)

You can run this pattern yourself with open-source TimescaleDB, or use Tiger Cloud, TigerData’s managed Postgres + TimescaleDB platform.

Tiger Cloud pricing is transparent:

• You pay for provisioned compute and storage.
• You do not pay per query, for backups, or for ingest/egress networking.
• Usage is tracked in Tiger Console and billed monthly in arrears.

Typical fit:

• Performance Plan: Best for teams needing managed Postgres + TimescaleDB for production telemetry workloads, with HA, automated backups, and point-in-time recovery, but without complex compliance requirements.
• Enterprise Plan: Best for larger organizations and regulated environments that need everything in Performance plus advanced security (VPC peering/Transit Gateway, private networking), compliance (SOC 2 Type II, GDPR, HIPAA), and 24/7 support with SLA-backed response times.

(Exact SKUs and region coverage are detailed on the TigerData site; the underlying pattern—hypertables + continuous aggregates—is the same across plans.)

---

Frequently Asked Questions

Do I still need a separate data warehouse for 12‑month analytics?

Short Answer: Often no—hypertables + continuous aggregates are enough for most operational and product analytics.

Details: For many teams, the “warehouse” only exists because operational Postgres can’t handle long-range aggregations at telemetry scale. By keeping everything Postgres-native and using continuous aggregates, you can:

• Serve rollups (hourly/daily/monthly) from continuous aggregates.
• Keep raw data compressed and tiered in the same database.
• Avoid fragile pipelines to Kafka/Flink/S3/warehouse just to compute basic stats.

You might still want a separate lakehouse for very wide data, ML training, or cross-domain joins, but it’s no longer required just to run “last 12 months” metrics on your telemetry.

How fresh are continuous aggregates compared to the raw hypertable?

Short Answer: They lag by the policy settings you choose; typically a few minutes.

Details: Freshness is controlled by:

• schedule_interval: how often the policy runs.
• end_offset: how far behind “now” the aggregate stops.
• Internal watermark behavior: TimescaleDB only recomputes buckets where data may have changed.

A common production pattern is:

SELECT add_continuous_aggregate_policy(
    'telemetry_daily',
    start_offset => INTERVAL '13 months',
    end_offset   => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '15 minutes'
);

In this setup, aggregates are usually within ~20 minutes of real time. Anything tighter should come from the hypertable. You can always join/union both to get a seamless view.

---

Summary

To run fast “last 15 minutes” and “last 12 months” analytics on the same Postgres dataset, stop fighting a single table and start using the right physical layout for each job:

• Hypertables for high-ingest, short-window queries.
• Continuous aggregates for long-range rollups.
• Compression and policies so you can keep a full year (or more) of data online at reasonable cost.

All of this stays Postgres-native: standard SQL, familiar tooling, and a single operational surface area. On Tiger Cloud, you get those primitives as a managed service with HA, PITR, and transparent pricing—no per-query surprises.

---

Next Step

Get Started