Why does Postgres get painfully slow when we ingest millions of time-stamped events per minute?

When you first start pushing time‑stamped events into Postgres, everything feels fine: inserts are fast, dashboards are snappy, and storage looks cheap enough. Then volume creeps up—hundreds of thousands, then millions of events per minute—and suddenly queries stall, CPU spikes, and infrastructure costs explode just to keep the cluster alive. This isn’t Postgres “failing at scale” so much as Postgres doing exactly what it was designed to do—just not for this pattern, at this volume, with this schema.

Quick Answer: Plain Postgres gets painfully slow under millions of time‑stamped events per minute because tables and indexes grow without bound, every query and insert competes for the same hot data structures, and the storage engine has to behave like a row‑oriented OLTP system and a time‑series warehouse at the same time. TigerData keeps the Postgres interface but adds hypertables, row‑columnar storage, and tiered storage so you can sustain time‑series ingest and analytics without fighting the engine.

---

The Quick Overview

• What It Is: TigerData is a Postgres‑native telemetry platform (Tiger Cloud plus the TimescaleDB extension) built to ingest, store, and query massive time‑stamped event streams without the “Postgres gets slow” cliff.
• Who It Is For: Engineering, data, and SRE teams running high‑ingest workloads—metrics, logs, IoT, tick, and event data—who want to stay on Postgres and SQL instead of stitching together Kafka, Flink, and a separate warehouse.
• Core Problem Solved: It prevents Postgres from collapsing under high‑volume time‑series ingest and analytics by giving you specialized primitives—automatic partitioning, row‑columnar storage, and tiered storage—behind the familiar Postgres interface.

---

How It Works

At a high level, your pain comes from asking one plain Postgres table to be everything:

• A write‑optimized log for millions of new events per minute.
• A read‑optimized warehouse for long‑range historical queries.
• An index‑heavy OLTP schema for highly selective lookups.

TigerData doesn’t replace Postgres; it extends it. Under the hood:

• You still connect with psql, use CREATE TABLE, and write SQL.
• You turn a normal table into a hypertable—automatically partitioned by time (and often a key like device or customer).
• Hot chunks stay in row storage for fast ingest; older chunks convert to compressed columnar storage (Hypercore) for analytics.
• Old, cold data moves to cheap object storage with tiered storage, while your compute (and indexes) stay bounded.

From your application’s perspective, it’s still just Postgres. Behind the scenes, the storage and access patterns are tuned for millions of time‑stamped events per minute.

1. Phase 1 – Diagnose why plain Postgres slows down:
   You hit bottlenecks in heap and index bloat, sequential scans, disk I/O, and autovacuum struggling to keep up—especially on a single massive time‑ordered table.

2. Phase 2 – Introduce Postgres‑native time‑series primitives:
   Convert your event tables into hypertables, enable automatic partitioning and compression, and use continuous aggregates so queries hit precomputed rollups instead of raw rows.

3. Phase 3 – Let Tiger Cloud handle scale and operations:
   Run this setup on Tiger Cloud so you get independent compute/storage scaling, HA, automated backups and PITR, plus transparent usage‑based billing (no per‑query or per‑ingress surprise fees).

---

Why Postgres Gets Painfully Slow Under Massive Time‑Stamped Ingest

Let’s be explicit about what’s going wrong when you’re ingesting millions of time‑stamped events per minute on a plain Postgres table.

1. One Huge Table, No Partitioning

Most telemetry schemas start like this:

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  device_id   TEXT NOT NULL,
  ts          TIMESTAMPTZ NOT NULL,
  payload     JSONB NOT NULL
);

CREATE INDEX ON events (ts DESC);
CREATE INDEX ON events (device_id, ts DESC);

It works… until it doesn’t.

• The events table grows into billions of rows.
• Every write hits the same heap and the same index pages near the “end” of the table.
• Queries that scan wide time windows (WHERE ts >= now() - interval '30 days') have to touch huge segments of heap and index.

Result: You see a lot of DataFileRead wait events in pg_stat_activity. Queries are reading from disk instead of shared buffers. Latency spikes as sequential scans show up on your hot path.

2. Index Bloat from Mixed OLTP + Analytics Patterns

Time‑series workloads are “append‑mostly”, but in real life:

• You backfill historical data.
• You occasionally update records (e.g., late events, corrections).
• You delete for retention (DELETE FROM events WHERE ts < now() - interval '90 days').

Every update or delete creates dead tuples. Autovacuum has to:

• Walk the table.
• Reclaim space.
• Update indexes.

Under sustained ingest, autovacuum is always behind, leading to:

• Bloated indexes that no longer fit in memory.
• Extra I/O and CPU for every query and insert.
• High and unpredictable latency as vacuum competes with user workload.

When you look at pg_stat_user_indexes, you’ll often find index sizes dwarfing the table size.

3. Disk I/O and Memory Pressure

As the table and indexes grow, your working set no longer fits in RAM:

• Hot data + large indexes + historical rows all compete for shared buffers.
• The OS page cache churns because every analytics query walks large time ranges.
• Postgres spills to disk when sorts/aggregations exceed work_mem.

You’ll see:

• Heavy DataFileRead waits.
• High I/O utilization even when CPU is under control.
• Queries that “used to be fast” suddenly hitting seconds or minutes.

4. Analytics Queries Fighting Ingest

A typical pattern:

SELECT
  device_id,
  time_bucket('1 minute', ts) AS bucket,
  avg((payload->>'temperature')::numeric) AS avg_temp
FROM events
WHERE ts >= now() - interval '7 days'
GROUP BY device_id, bucket;

Under millions of events per minute, this query:

• Scans huge raw tables.
• Touches every row, then aggregates.
• Forces the planner into sequential scans over massive ranges.

Continuous aggregation is what you actually want here, but plain Postgres doesn’t give you incrementally updated materialized views out of the box. Instead, you recompute from scratch—and pay the cost every time.

5. Autovacuum, Checkpoints, and Background Workers

Under heavy write load:

• Autovacuum runs constantly to keep bloat in check.
• Checkpoints flush large volumes of dirty pages to disk.
• Background workers are consumed by maintenance tasks.

Important: sustained vacuum on large tables is often associated with high CPU utilization and severe performance degradation when not tuned. If you also run multiple extensions or jobs, you can even run out of background workers, delaying vacuum and exacerbating bloat.

All of this is “normal Postgres behavior”—but not what you want when you’re aiming for millions of time‑stamped events per minute with low latency on both fresh and historical data.

---

How TigerData Keeps Postgres Fast for High‑Ingest Time‑Stamped Events

With TigerData you don’t leave Postgres behind. You change the underlying storage and query model while keeping SQL.

Here are the core primitives that change the calculus.

1. Automatic Partitioning with Hypertables

Instead of one huge table, you use a hypertable:

SELECT create_hypertable('events', by_range('ts'));

You can also partition by a secondary dimension (e.g., device_id):

SELECT create_hypertable(
  'events',
  by_range('ts'),
  partitioning_column => 'device_id',
  number_partitions   => 16
);

What this does:

• Splits your data into time‑based chunks (plus key‑based partitions if configured).
• Keeps active chunks small and hot in memory.
• Evicts old chunks predictably, not via random page churn.

Impact:

• Writes go to relatively small append‑only regions—lower contention and better cache behavior.
• Queries constrained by time (WHERE ts >= ...) hit only relevant chunks, not the whole table.
• Retention becomes trivial: drop old chunks instead of running huge DELETE statements.

2. Row‑Columnar Storage with Hypercore

Fresh data is stored in row format for ingest speed. As chunks age, policies convert them into compressed columnar storage:

SELECT add_compression_policy('events', INTERVAL '7 days');

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

What this does:

• Automatically converts older chunks to columnstore.
• Stores each column contiguously on disk, with aggressive compression (docs cite up to 98%).
• Keeps recent data write‑optimized while historical data becomes analytic‑optimized.

Impact:

• Massive reduction in I/O for analytical queries.
• Much smaller on‑disk footprint for historical data.
• Faster scans and aggregations (columnar reads only the columns you query).

In practice, this means your “7 days of hot data” still lives in row storage for millisecond‑level latencies, while “12 months of history” is compressed and cheap—but still queryable with standard SQL.

3. Continuous Aggregates for Precomputed Rollups

Instead of querying raw event rows every time, you create time‑bucketed rollups that are maintained incrementally:

CREATE MATERIALIZED VIEW device_minute_rollup
WITH (timescaledb.continuous) AS
SELECT
  device_id,
  time_bucket('1 minute', ts) AS bucket,
  avg((payload->>'temperature')::numeric) AS avg_temp,
  max((payload->>'temperature')::numeric) AS max_temp
FROM events
GROUP BY device_id, bucket;

Then you add a policy:

SELECT add_continuous_aggregate_policy(
  'device_minute_rollup',
  start_offset => INTERVAL '30 days',
  end_offset   => INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute'
);

What this does:

• Shifts heavy aggregation from query time to an asynchronous background process.
• Updates only the time buckets with new or changed data.
• Serves queries from precomputed results instead of raw rows.

Impact:

• Stable, predictable query times even as raw data volume explodes.
• Analytics dashboards stay snappy without re‑scanning billions of rows.
• You can chain rollups (e.g., minute → hour → day) without brittle ETL.

Important: continuous aggregates are eventually consistent by design. The watermark behavior matters—choose your start_offset and end_offset to account for late‑arriving events.

4. Tiered Storage for Cost‑Efficient History

With tiered storage on Tiger Cloud:

• Hot chunks stay on high‑performance local disks.
• Warm chunks stay compressed but local.
• Cold chunks are moved to low‑cost object storage (e.g., S3), still queryable.

Impact:

• You can keep years of event history online without blowing up storage bills.
• Compute scales independently from storage; you size CPU and memory for ingest and query, not for raw byte volume.
• Indexes and hot data structures remain bounded, avoiding the “everything grows forever” Postgres failure mode.

5. Operational Guardrails for High‑Ingest Workloads

Tiger Cloud wraps this with operational defaults tuned for telemetry:

• HA and multi‑AZ replication for resilience.
• Automated backups and point‑in‑time recovery (PITR).
• Private networking (VPC peering/Transit Gateway), IP allow lists.
• Transparent billing: you pay for compute and storage; you don’t pay per query or for automated backups or network ingress/egress.

When you’re already battling ingest bottlenecks and query slowness, not having to debug backup I/O or surprise query bills is a material difference.

---

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Hypertables	Automatically partition tables by time (and optional key) into chunks.	Keeps writes fast and queries scoped; avoids the single‑huge‑table trap.
Hypercore row‑columnar storage	Stores hot data in row format, older data in compressed columnstore.	Fast ingest plus fast analytics, with up to 98% compression for history.
Continuous aggregates	Maintains incrementally updated materialized views of rollups.	Query precomputed results instead of scanning billions of raw events.
Tiered storage	Moves cold data to cheaper object storage while keeping it queryable.	Control storage costs while keeping long‑term telemetry online.
Tiger Cloud operations	Provides managed Postgres with TimescaleDB, HA, backups, PITR, observability.	Production‑ready time‑series Postgres without fragile DIY infrastructure.

---

Ideal Use Cases

• Best for real‑time monitoring and observability pipelines:
  Because it turns raw metric/log/event firehoses into hypertables with continuous aggregates, so alerts and dashboards query small, precomputed rollups instead of live‑scanning massive tables.

• Best for IoT, device, and sensor telemetry platforms:
  Because it keeps ingest tight (append‑optimized chunks), compresses history aggressively, and lets teams stick with SQL and Postgres drivers instead of building and operating a separate time‑series database plus a data warehouse.

---

Limitations & Considerations

• You still have to model for time‑series access patterns:
  Hypertables and compression help a lot, but you still need appropriate PRIMARY KEY/indexes, realistic retention policies, and careful handling of backfills/updates. For example, heavy backfills across wide time ranges can be expensive even on hypertables.

• Continuous aggregates are not magic real‑time mirrors:
  They refresh on a schedule and follow watermarks. If your workload has extremely late or out‑of‑order events, you’ll need to tune start_offset, end_offset, and refresh policies—especially if you rely on “complete and final” aggregates for billing or compliance.

---

Pricing & Plans

TigerData’s pricing is built around clear infrastructure components rather than opaque query or ingest charges:

• You pay for compute (the size of your Tiger Cloud services).
• You pay for storage (local disk and any object storage used for tiering).
• You do not pay per query, per metric, or for automated backups and networking between Tiger Cloud components.

Alignment with their standard plan framing:

• Performance Plan: Best for teams who already feel Postgres getting painfully slow under growing time‑stamped event ingest and need hypertables, compression, and continuous aggregates to keep a single service fast and predictable.

• Scale / Enterprise Plans: Best for organizations with regulated workloads, strict availability requirements, or multi‑team telemetry platforms that need HA, multi‑AZ deployments, HIPAA/GDPR support, SOC 2 Type II reports, advanced networking (VPC peering/Transit Gateway), and 24/7 support with SLA‑backed response times.

For exact SKU names, regions, and sizing guidance, TigerData works with you to right‑size services based on ingest rates (events per second), retention windows, and query SLA.

---

Frequently Asked Questions

Why does Postgres slow down even when I’ve tuned autovacuum and added indexes?

Short Answer: Because the core issue isn’t just vacuum or missing indexes—it’s the mismatch between a single huge table and a workload that’s both high‑ingest and historically deep. As the table and indexes grow, you exceed memory, hit disk I/O for most operations, and force the planner into expensive sequential scans.

Details: Autovacuum tuning (e.g., adjusting autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_limit) can delay the inevitable but can’t change the fact that:

• A monolithic events table with billions of rows makes every index and vacuum operation expensive.
• Mixed workloads (inserts + updates + deletes + analytics) generate steady bloat.
• Queries that span wide time ranges have to touch huge parts of the table and indexes.

Hypertables change this by sharding data into smaller, time‑bounded chunks. Vacuum, index maintenance, and queries all operate on units that are much easier for Postgres to handle efficiently. Compression and continuous aggregates further reduce the amount of data that needs to be touched for typical queries.

Can’t I just partition tables manually or offload analytics to a separate system?

Short Answer: You can, but you’ll end up with the very “fragile and high‑maintenance” setup teams come to TigerData to undo: hand‑rolled partitioning plus a stitched‑together Kafka/Flink/warehouse pipeline.

Details: Manual partitioning in Postgres (CREATE TABLE events_2024_10 PARTITION OF events FOR VALUES FROM ...) works but introduces complexity:

• You manage partition creation and retention yourself (scripts, cron, or background jobs).
• Queries sometimes need explicit partition awareness for performance.
• Analytics still scan row‑oriented partitions; you don’t get columnar compression or native continuous aggregates.

Offloading to a separate system (e.g., streaming into Kafka, processing with Flink, landing into a lakehouse/warehouse) solves some analytics problems but:

• Doubles or triples your operational surface area.
• Introduces lag and consistency issues between operational and analytical views.
• Creates “data plumbing” that many customers describe as working but “fragile and high‑maintenance.”

TigerData’s approach replaces that glue with Postgres‑native primitives: hypertables, Hypercore, continuous aggregates, and lakehouse integrations (“Ingest from Kafka and S3, replicate to Iceberg”) within the same Postgres environment. You keep one logical database, one interface (SQL), and fewer moving parts.

---

Summary

Postgres gets painfully slow when you ingest millions of time‑stamped events per minute not because it’s a “bad database,” but because you’re pushing a vanilla OLTP engine into a time‑series telemetry role it wasn’t optimized for: a single huge append‑heavy table, high‑fanout indexes, wide historical scans, and constant vacuum. The symptoms—DataFileRead waits, sequential scans, index bloat, autovacuum thrash—are all signs that the storage and access model no longer match the workload.

TigerData keeps Postgres as your foundation but gives it the primitives it’s missing for this job: hypertables for automatic time and key partitioning, Hypercore row‑columnar storage and compression to separate hot writes from cold analytics, continuous aggregates to precompute rollups, and tiered storage to keep history cheap. Run that on Tiger Cloud’s managed platform and you get the performance and durability of a purpose‑built telemetry system, with the reliability and familiarity of Postgres and SQL.

---

Next Step

Get Started