Why are my Postgres indexes and vacuum getting out of control on append-only time-series tables?

Postgres usually feels “boring and reliable” right up until your time-series tables hit real scale. Then suddenly, VACUUM is running constantly, autovacuum workers are pegged, and your indexes are bigger than the table itself. On an append-only workload, this feels counterintuitive—if you mostly insert, why are vacuum and indexes getting out of control?

This guide walks through the mechanics behind that behavior, the trade-offs in a plain-Postgres design, and how TimescaleDB (and TigerData’s managed Tiger Cloud) change the calculus for append-only telemetry.

---

The quick overview

• What’s going wrong: Append-only time-series tables still generate dead tuples (from updates, deletes, HOT failures, and index maintenance) and steadily-growing BTREE indexes. Over time, that creates bloat and forces autovacuum to work overtime.
• Why it hurts: As tables and indexes grow, VACUUM and ANALYZE get slower and more frequent, queries scan more pages, and your CPU and I/O get eaten by maintenance instead of real workloads.
• What you actually want: A schema and storage model that treat time as a first-class partition key, keep hot data small and “in cache,” make vacuum work chunk-by-chunk, and compress / tier cold data without sacrificing SQL.

From here, I’ll start with plain Postgres mechanics, then show how TimescaleDB hypertables, compression, and tiering tame indexes and vacuum on append-only time-series workloads.

---

Why append-only time-series still cause vacuum and index bloat

You might think: “We don’t delete anything, we just insert metrics/events. Why is vacuum running all the time?” Because in Postgres, “append-only” is rarely truly append-only in practice.

1. Inserts still touch all your indexes

Every INSERT into a table with N indexes updates:

• The heap (the table itself)
• Each of the N index structures

On a time-series table with:

• PRIMARY KEY (device_id, ts)
• Several secondary indexes ((ts DESC), (device_id, ts), (status, ts)…)

each row turns into multiple index entries. That means:

• More pages to maintain
• More page splits and fragmentation over time
• Larger on-disk and in-RAM working set

That alone doesn’t require vacuum, but it makes index bloat more painful when it arrives.

2. “Append-only” often includes updates, late-arriving data, and deletes

Even in telemetry workloads:

• Backfills / corrections: you re-ingest days of metrics with corrected values.
• Late-arriving events: a device comes online after being offline and backfills its history.
• Upserts: you use INSERT ... ON CONFLICT DO UPDATE to avoid duplicates.
• Data retention: you delete data older than X days.

Each of these creates dead tuples:

• UPDATE → old row version becomes dead
• DELETE → row becomes dead
• ON CONFLICT DO UPDATE → same as above when conflict triggers

Vacuum must scan the table (or parts of it) to reclaim those dead tuples. On very large, unpartitioned tables, even modest percentages of dead tuples produce large absolute amounts of work.

3. HOT updates don’t always save you

Postgres has Heap-Only Tuples (HOT) updates, which avoid index changes if:

• You only update columns that are not indexed
• There is free space on the same page

On telemetry tables, it is common for “frequently updated” columns to be indexed (e.g., status flags, tags), or for pages to be full due to heavy inserts. When HOT cannot apply:

• Every update requires index changes
• Dead tuples accumulate in both heap and indexes
• Vacuum becomes more necessary and more expensive

4. Large monolithic tables make vacuum work too coarse-grained

On a single 2 TB table of metrics, vacuum behaves roughly like this:

• Autovacuum tracks n_dead_tup in pg_stat_user_tables
• Once thresholds are exceeded, it schedules a worker
• That worker scans large portions (or all) of the table
• It contends with your regular workload for I/O and CPU

Because time-series data is naturally ordered by time (and you often cluster by time or an index), vacuum may repeatedly scan old, cold regions of the table just to reclaim a tiny fraction of dead tuples.

5. Index bloat doesn’t auto-shrink

BTREE indexes don’t shrink on their own when rows are deleted or updated:

• Dead index entries remain until vacuum marks them reusable
• The index file rarely shrinks; you need REINDEX or VACUUM FULL for true compaction
• As the number of pages grows, more RAM and I/O are required for queries, even if much of the index is “logically empty”

On append-heavy workloads with even modest churn, this leads to:

• Index size >> table size
• Poor cache hit ratios
• Higher CPU usage for index scans

---

How vacuum “gets out of control” in this pattern

Put the above together, and you see a familiar failure mode:

1. Table grows steadily with time-series inserts.
2. Indexes grow with it, sometimes outpacing the heap.
3. Occasional backfills / deletes generate pockets of dead tuples scattered throughout the table and indexes.
4. Autovacuum thresholds keep firing as n_dead_tup grows, spawning more workers.
5. Each autovacuum worker must scan large ranges to find dead tuples, even though most rows are still valid.
6. CPU climbs, I/O climbs, and the feedback loop begins: “we need more vacuum”, but that vacuum itself is what is hurting performance.

In plain Postgres, the usual response is:

• Add more autovacuum workers
• Tune autovacuum cost limits
• Run VACUUM (VERBOSE, ANALYZE) at off-hours
• Periodically REINDEX bloated indexes

This works up to a point, but it’s fragile and high-maintenance—exactly the failure mode we hear from teams stitching together operational Postgres plus streaming plus lakehouse: it works, but it’s brittle.

---

Postgres-native primitives that change the calculus

What you actually want for an append-heavy, time-ordered workload is:

• Partitioning by time and key so vacuum and index operations are chunk-scoped, not table-wide.
• Storage that’s row-oriented for hot writes, columnar for cold analytics, so you aren’t paying row-store costs forever.
• Automated compression and tiering for cold chunks, so most of your “bloat” is turned into dense, compressed columnar data.
• Indexes that understand both row and columnar storage and don’t require special query syntax.

That’s precisely what TimescaleDB adds on top of Postgres. On TigerData (Tiger Cloud + TimescaleDB), you get those primitives as a managed Postgres service.

Let’s walk through them and how they specifically tame indexes and vacuum.

---

Automatic partitioning with hypertables

Primitive: CREATE TABLE + SELECT create_hypertable(...)

Instead of one giant table, TimescaleDB turns your time-series table into a hypertable:

• Under the hood, it’s many chunks, each covering a time interval (and optionally a space “partition key” like device_id).
• Inserts are automatically routed to the right chunk.
• Vacuum, indexes, and compression can operate per chunk.

Example:

CREATE TABLE metrics (
  time        timestamptz       NOT NULL,
  device_id   text              NOT NULL,
  metric_name text              NOT NULL,
  value       double precision  NOT NULL,
  tags        jsonb
);

SELECT create_hypertable(
  'metrics',
  'time',
  chunk_time_interval => INTERVAL '1 day'
);

Impact on vacuum and indexes:

• Autovacuum can process individual chunks instead of the entire logical table.
• Old chunks can be compressed and eventually dropped without touching hot, recent data.
• Index bloat becomes chunk-local; in many cases, you compress and freeze old chunks instead of REINDEX-ing them.

---

Row-columnar storage (Hypercore) and compression

Primitive: ALTER TABLE ... SET (timescaledb.compress) + compression policies

TimescaleDB’s Hypercore is a hybrid row-columnar engine:

• Row-store for recent, hot data → fast inserts, good for OLTP-like workloads.
• Columnstore + compression for older, cold data → reduced size, faster scans for analytics, little to no vacuum needed.

Example:

ALTER TABLE metrics
SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

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

This says: once data is older than 7 days, compress those chunks.

Impact:

• Compressed chunks don’t behave like regular heap pages for vacuum; they’re effectively immutable columnar segments.
• You can see compression up to 98% on typical metrics workloads, which directly reduces:
  • Disk footprint
  • Cache footprint for scans
  • The need for vacuum on those chunks

Indexes and compressed data

TimescaleDB supports sparse indexes on columnstore data, which means:

• Queries can efficiently locate subsets of data within compressed chunks without full scans.
• You still use standard SQL and Postgres indexes; TimescaleDB handles the mapping between row and columnar representations.

This is crucial: you get the storage benefits of a columnstore and compression without abandoning Postgres indexes or rewriting queries.

---

Tiered storage and data retention

Primitive: Tiered storage + retention/compression policies

On Tiger Cloud, you can enable tiered storage (row on local SSD; cold chunks on low-cost object storage). Coupled with TimescaleDB policies:

• Recent data: row-store on fast disks, vacuum operates normally but only over a small window.
• Mid-age data: compressed columnstore on the same service; minimal vacuum pressure.
• Old data: compressed and tiered out to object storage, or dropped entirely via retention.

Example retention:

SELECT add_retention_policy('metrics', INTERVAL '90 days');

Impact:

• You limit the “vacuum horizon” to a bounded window of time (e.g., last 30–90 days).
• Most dead tuple accumulation is prevented by design: old data is compressed and then removed, not rewritten repeatedly.
• Index growth is bounded by that retention window; everything older is compressed or dropped.

---

Schema and index design to reduce vacuum pain

Even with hypertables and compression, your schema decisions matter. Some practical mechanics:

1. Keep indexes lean and deliberate

Avoid “just add another index” as the default fix. Each new index:

• Increases write amplification
• Increases the scope of vacuum work
• Can bloat independently

Use pg_stat_user_indexes to monitor real usage:

SELECT
  schemaname,
  relname as table,
  indexrelname as index,
  idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

• Indexes with idx_scan = 0 over long periods are candidates for removal.
• Focus on composite indexes that match your core query patterns, e.g.:

CREATE INDEX ON metrics (device_id, time DESC);
CREATE INDEX ON metrics (time DESC);

2. Model updates to preserve HOT when possible

If you do perform updates, try to:

• Keep frequently-changing columns out of indexes where possible.
• Group infrequently-changed metadata into separate tables to avoid touching hot metric rows.
• Consider append-only corrections (insert corrected row plus a “superseded” flag) instead of in-place updates, then use a view to pick the latest version.

This reduces index churn and dead tuples, especially in hot chunks.

3. Use hypertable chunk sizing to bound maintenance

Your chunk_time_interval directly affects vacuum behavior:

• Too large (e.g., 30 days per chunk) → each chunk is big, vacuum work per chunk is heavy.
• Too small (e.g., 1 minute) → too many chunks, higher metadata overhead.

For high-ingest workloads, daily or hourly chunks often strike a good balance. Monitor:

SELECT
  show_chunks('metrics') as chunk,
  pg_total_relation_size(show_chunks) as size_bytes;

If chunks are huge, consider shorter intervals to reduce per-chunk vacuum cost.

---

Operational tuning: avoiding autovacuum worker exhaustion

When you do use many TimescaleDB features, each adds background workers (compression policies, continuous aggregates, etc.). Combined with autovacuum workers, it’s easy to exhaust max_worker_processes and related settings.

Important: Running out of background workers means both vacuum and TimescaleDB maintenance can stall, letting bloat accumulate.

Monitor:

• pg_stat_activity for autovacuum workers
• pg_stat_bgwriter for checkpoint behavior
• TimescaleDB job stats via its internal views

Typical knobs to inspect:

max_worker_processes
max_parallel_workers
max_parallel_maintenance_workers
autovacuum_max_workers

On Tiger Cloud, a lot of this is pre-tuned for telemetry workloads, and the platform monitors for “runaway vacuum/index” patterns. In self-managed environments, you’ll need explicit capacity planning around these worker counts.

---

How TigerData (Tiger Cloud) simplifies this in practice

All of the above is still just Postgres and TimescaleDB. On Tiger Cloud, those primitives are delivered as a managed PostgreSQL platform that’s built for live telemetry:

• Hypertables to automatically partition by time and key.
• Hypercore row-columnar storage to keep hot data row-oriented, cold data compressed.
• Tiered storage to move cold chunks to low-cost object storage.
• 200+ time-series functions and native Postgres indexes on both row and columnar data.
• Transparent operations: HA, automated backups, point-in-time recovery, read replicas, and no per-query fees.

Operationally, this means:

• You don’t pay extra for automated backups or vacuum; it’s included, and there are no surprise query-charges.
• You get production-grade security controls: TLS 1.2+, mutual TLS for critical internal traffic, encryption at rest with per-service keys, SOC 2 Type II (by plan), GDPR support, and HIPAA on Enterprise.
• You can focus on schema and query design, not tuning autovacuum every time ingest spikes.

For teams migrating from a “plain Postgres plus Kafka/Flink plus lakehouse” setup, Tiger Lake (TigerData’s lakehouse integration) replaces fragile streaming glue with native infrastructure:

• Ingest from Kafka and S3 into Postgres-native hypertables.
• Replicate to Iceberg without bespoke streaming code.
• Keep operational and analytical access in sync without running multiple overlapping vacuum regimes.

---

Practical checklist: bring your indexes and vacuum back under control

If your Postgres indexes and vacuum are already misbehaving on append-only time-series tables, here’s a pragmatic ordering:

1. Measure bloat and index usage
   • Use pg_stat_user_tables, pg_stat_user_indexes, and your favorite bloat inspection queries.
2. Trim unused indexes
   • Drop indexes with negligible idx_scan counts and no clear plan reason to keep.
3. Convert large time-series tables to hypertables
   • Use create_hypertable with a sensible chunk_time_interval.
4. Enable compression and retention
   • Mark tables compressible and add add_compression_policy and add_retention_policy as appropriate.
5. Revisit update and backfill patterns
   • Prefer append-only corrections and batch backfills; avoid scattered, tiny updates on old data.
6. Tune worker pools thoughtfully
   • Make sure autovacuum_max_workers and max_worker_processes support your maintenance workload without saturating CPU.
7. Consider managed operations
   • If you’re spending more time chasing vacuum than building features, evaluate a managed service like Tiger Cloud.

---

Summary

Append-only time-series workloads in Postgres still generate dead tuples, index bloat, and heavy vacuum work—especially once you account for real-world behavior like backfills, upserts, and retention deletes. On a single monolithic table, vacuum becomes coarse-grained and increasingly expensive, and indexes can quietly outgrow your heap.

The way out isn’t constant autovacuum tuning; it’s embracing Postgres-native primitives that treat time as a first-class dimension:

• Hypertables for automatic, time-based partitioning.
• Hypercore row-columnar storage plus compression for cold data.
• Tiered storage and retention policies to bound maintenance windows.
• Sparse indexes that work over both row-store and columnstore.

TimescaleDB gives you those primitives in Postgres; TigerData’s Tiger Cloud runs them as a managed Postgres platform built for telemetry, so you can keep SQL as your interface while avoiding “runaway vacuum” and index sprawl at scale.

---

Next step

If you’re seeing vacuum storms or index bloat on your append-only time-series tables and want to talk through schemas, chunk sizing, or a migration path to hypertables and compression, you can reach the TigerData team here:

Get Started