How do I keep per-minute/hour rollups in Postgres always up to date for metrics/events without nightly batch jobs?

Most teams start with a simple pattern: write raw events into Postgres, then run nightly batch jobs to compute per-minute or hourly rollups. It works at first, then dashboards lag, jobs overrun their windows, and backfills become a nightmare. You don’t actually need a separate batch layer for this—Postgres plus TimescaleDB’s continuous aggregates can keep your rollups incrementally up to date, with no nightly ETL.

Quick Answer: Use TimescaleDB continuous aggregates on top of hypertables to maintain per-minute/hour rollups incrementally. Postgres computes rollups asynchronously as new data arrives, so your metrics stay fresh without full-table batch jobs.

The Quick Overview

• What It Is: A Postgres-native way to maintain always-fresh time-bucketed rollups (per minute, hour, day) using TimescaleDB continuous aggregates instead of custom cron jobs or external ETL.
• Who It Is For: Teams ingesting time-based metrics/events—app observability, IoT telemetry, financial ticks, product analytics—that need real-time and historical rollups in the same Postgres database.
• Core Problem Solved: Avoid scanning huge raw tables or running brittle nightly jobs just to keep dashboards and alerts current. Rollups stay up to date continuously, with incremental recomputes.

How It Works

The core idea: treat rollups as incrementally updated materialized views that Postgres maintains for you after each ingest, not as nightly batch outputs you rebuild from scratch.

TimescaleDB adds a primitive called a continuous aggregate. It looks like a materialized view, but:

• It’s defined over a hypertable (time-partitioned table).
• It updates incrementally as new or changed data arrives.
• It’s refreshed by a background job according to a policy you control (e.g., every minute for the last 2 hours, every 15 minutes for the last 7 days).

Instead of:

• Writing events into events.
• Running a nightly job that scans events and inserts into events_rollup_minute and events_rollup_hour.

…you:

• Write events into a hypertable events.
• Define continuous aggregates like events_minute_cagg and events_hour_cagg using time_bucket and aggregates.
• Attach add_continuous_aggregate_policy jobs so TimescaleDB keeps them fresh.

At query time, your dashboards hit the continuous aggregate tables (precomputed results), not the raw event table.

1. Ingest into a hypertable

You start with a standard Postgres table:

CREATE TABLE events (
    time        TIMESTAMPTZ       NOT NULL,
    service     TEXT              NOT NULL,
    status_code INT               NOT NULL,
    latency_ms  DOUBLE PRECISION  NOT NULL
);

Convert it into a hypertable:

SELECT create_hypertable('events', 'time');

Now writes are automatically partitioned by time (and optionally a space dimension like service), which is the foundation for efficient rollups.

2. Define continuous aggregates for per-minute rollups

You create a rollup view using CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) and time_bucket:

CREATE MATERIALIZED VIEW events_minute_cagg
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time)      AS bucket,
    service,
    count(*)                           AS request_count,
    avg(latency_ms)                    AS avg_latency_ms,
    percentile_agg(latency_ms)         AS p_agg -- partial percentile state
FROM events
GROUP BY 1, 2;

Note the use of percentile_agg instead of computing p95 directly. This stores a partial aggregation state per minute, which you can later combine for hour/day rollups without rescan.

3. Attach a refresh policy

Next, you tell TimescaleDB how often and how far back to refresh:

SELECT add_continuous_aggregate_policy(
    'events_minute_cagg',
    start_offset => INTERVAL '2 hours',   -- recompute last 2 hours
    end_offset   => INTERVAL '1 minute',  -- leave last minute as live window
    schedule_interval => INTERVAL '1 minute'
);

Now, every minute, a background job:

• Identifies invalidated buckets (where new/updated data landed).
• Recomputes only those buckets.
• Updates the materialized table.

Your per-minute rollups stay current without touching most of your historical data.

4. Derive hourly/daily rollups from partials

To get hourly p95 from minute-level partials, you don’t scan raw rows. Instead, you combine percentile_agg states:

SELECT
    time_bucket('1 hour', bucket)          AS hour,
    service,
    approx_percentile(0.95, rollup(p_agg)) AS p95_latency_ms,
    sum(request_count)                     AS request_count
FROM events_minute_cagg
GROUP BY 1, 2;

For very hot workloads, you can also create a second continuous aggregate for hourly rollups that reads from the minute CAGG, so even this query is precomputed.

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Continuous aggregates	Incrementally maintain materialized rollups over hypertables.	Keep per-minute/hour metrics up to date without nightly batch jobs.
Partial percentile states	Use percentile_agg + rollup(...) to store and merge distribution stats.	Compute p95/p99 across large windows without rescanning raw events.
Configurable refresh policies	add_continuous_aggregate_policy controls schedule and time window.	Dial in freshness vs cost; avoid unnecessary recomputes on cold data.

Ideal Use Cases

• Best for real-time metrics and observability dashboards: Because you can maintain 1-second/1-minute/5-minute rollups over billions of events, with queries hitting precomputed tables instead of raw logs.
• Best for IoT and telemetry workloads: Because device/sensor streams can be aggregated continuously (per device, per region, per minute/hour) without building a separate streaming/ETL stack.

Limitations & Considerations

• Eventual consistency of rollups: Continuous aggregates refresh asynchronously. Recent buckets may lag by your schedule_interval and end_offset. For strict “up to the last second” views, combine CAGG data for the past with direct reads from the raw hypertable for the most recent slice.
• Update/backfill behavior: When you update or late-insert into historical chunks, TimescaleDB records invalidations and recomputes affected regions. This is powerful but adds work; keep your backfill windows bounded and refresh policies configured to cover the right range.

Pricing & Plans

You can run TimescaleDB yourself on Postgres, or use TigerData’s managed platform (Tiger Cloud) to offload operations while keeping a pure Postgres interface.

Tiger Cloud plans are structured around compute, storage, and features, with no per-query fees and no extra charges for automated backups or normal ingest/egress. You see itemized usage in Tiger Console and are billed monthly in arrears.

• Performance Plan: Best for teams needing always-on rollups and dashboards on a single service, with automated backups, point-in-time recovery, and basic HA. Ideal for production metrics/telemetry stacks that outgrew cron-based rollups.
• Scale/Enterprise Plans: Best for organizations with high ingest (billions/trillions of events per day), regulatory requirements, or multi-team environments. Add stronger SLAs, SOC 2 documentation access, HIPAA (Enterprise), private networking (VPC peering/Transit Gateway), and advanced support.

For exact pricing and region availability, contact TigerData directly.

Frequently Asked Questions

Do I still need batch jobs if I use continuous aggregates?

Short Answer: Usually not for rollups; continuous aggregates replace most rollup-style batch jobs.

Details:
Continuous aggregates are designed to take over the classic “nightly rollup” job:

• They incrementally recompute only affected time buckets.
• They run on a schedule you define, not just once per night.
• They keep rollups close to real time without full-table scans.

You may still use batch jobs for:

• Complex multi-source joins that aren’t time-series heavy.
• One-off historical backfills or migrations.
• Downstream exports (e.g., to a data warehouse).

But for per-minute/hour/day metrics over large event tables, continuous aggregates are the primary mechanism.

How fresh can my per-minute/hour rollups be?

Short Answer: Typically within your schedule interval; one minute lag is common for per-minute rollups.

Details:
Freshness is controlled by your continuous aggregate policy:

• schedule_interval → how frequently the background job runs.
• start_offset/end_offset → how far back and how close to “now” it recomputes.

Example:

SELECT add_continuous_aggregate_policy(
    'events_minute_cagg',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '30 seconds',
    schedule_interval => INTERVAL '30 seconds'
);

In this setup:

• Buckets up to 30 seconds behind real time are maintained by the CAGG.
• The last 0–30 seconds of data are only in the raw hypertable; if you need them in queries, UNION them in:

SELECT * FROM events_minute_cagg
WHERE bucket < now() - INTERVAL '30 seconds'
UNION ALL
SELECT
    time_bucket('1 minute', time) AS bucket,
    service,
    count(*)                      AS request_count,
    avg(latency_ms)               AS avg_latency_ms,
    percentile_agg(latency_ms)    AS p_agg
FROM events
WHERE time >= now() - INTERVAL '30 seconds'
GROUP BY 1, 2;

This pattern gives you near-real-time freshness with bounded incremental work.

Summary

Keeping per-minute and hourly rollups always up to date in Postgres doesn’t require nightly batch jobs or a separate streaming stack. By:

• Storing events in hypertables for efficient time-based partitioning.
• Defining continuous aggregates for minute/hour/day rollups.
• Using partial aggregations (percentile_agg, rollup) for high-cardinality percentiles.
• Attaching refresh policies tuned to your freshness and cost requirements.

…you get fast, reliable rollups that scale from millions to trillions of events per day, all through standard SQL and Postgres primitives.

If you’d rather not run this yourself, TigerData’s managed platform packages these primitives into a production-ready service with transparent pricing, automated ops, and Postgres-native access.

Next Step

Get Started