TimescaleDB in Production: When Time-Series Postgres Starts Hurting

Time-series data usually enters a system as a normal table because that is the fastest path to shipping. Add created_at, add an index, write rows. Metrics, IoT readings, traces, usage events, billing samples, audit points - they all start this way.

The pain arrives later. The table grows by millions of rows per day. Dashboards scan wider windows. Deletes create vacuum pressure. Indexes stop fitting comfortably in cache. The team builds rollups by hand, then backfills a week of late data and realizes the charts are now lying.

TimescaleDB is useful because it gives Postgres a physical shape that matches time-series work. It does not remove the need to design the workload.

The framework: prove the pain is time-series pain

I reach for TimescaleDB when the dominant operations are time-shaped:

• Most reads filter by time range.
• Most deletes remove old time ranges.
• Most dashboards aggregate into buckets.
• Old data has a different access pattern than new data.
• Backfills and late arrivals are normal, not exceptional.

If a table merely has a timestamp column, that is not enough. A users table with created_at is not a time-series workload. A metrics table receiving 50 million rows per day probably is.

Hypertables fix the one-big-table problem

A hypertable splits time-series data into chunks behind the scenes. Each chunk covers a time range, so queries, compression, retention, and maintenance can operate on time-bounded pieces instead of one giant table.

CREATE TABLE metrics (
  time timestamptz NOT NULL,
  tenant_id bigint NOT NULL,
  host_id bigint NOT NULL,
  cpu_usage double precision NOT NULL,
  memory_bytes bigint NOT NULL
);

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

That is a better shape, but it is not the whole design. The first serious decision is chunk interval.

Chunk interval is not a default to forget

Chunk interval controls how much data each chunk holds. Too small, and the planner has to reason about too many chunks. Too large, and queries, retention, compression, and indexes handle more data than they need.

The default can be a decent start, but production systems should choose the interval from workload facts:

• How much data arrives per day?
• How much memory is available for active chunks and indexes?
• What time windows do users query most?
• How soon does data become cold enough to compress?
• What is the raw retention window?

A useful rule of thumb from Timescale guidance is to size chunks so the active chunk and its indexes fit comfortably in memory. The exact number depends on ingest rate and query shape, so I validate it with real chunk sizes after the system has traffic.

SELECT hypertable_name, chunk_name, range_start, range_end, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'metrics'
ORDER BY range_start DESC
LIMIT 20;

If chunk sizing is wrong, changing the interval affects new chunks, not old ones. That is another reason to catch it early.

Compression is a lifecycle decision

Compression, or columnstore in newer Timescale terminology, is not just about disk savings. It changes how old data is stored and read. The production question is: when does data become cold enough that compression helps more than it hurts?

ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'tenant_id, host_id',
  timescaledb.compress_orderby = 'time DESC'
);

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

The segment and order keys should match how historical queries read data. If dashboards filter by tenant and host, those are natural segment keys. If queries usually scan recent-to-old windows, time ordering matters.

The failure mode is compressing data that is still being updated, refreshed, or backfilled. That creates extra operational work and confusing performance changes.

Retention should drop chunks, not fight rows

Deleting old rows from a huge plain table is a classic time-series mistake. It creates bloat, vacuum work, and lock risk. Time-series retention should operate at the chunk lifecycle level.

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

But retention is rarely one number. Product may need raw samples for 30 days, hourly aggregates for one year, and daily aggregates for three years. That means raw tables and continuous aggregates can have different retention rules.

One important production detail: a retention policy on the source hypertable does not automatically apply to continuous aggregates. That is a feature, but it becomes a bug if the team assumes old aggregate data disappears with raw data.

Continuous aggregates solve dashboard pain carefully

Continuous aggregates are usually the point where TimescaleDB becomes obviously useful. They precompute bucketed summaries so dashboards do not scan raw rows on every page load.

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  tenant_id,
  host_id,
  avg(cpu_usage) AS avg_cpu,
  max(memory_bytes) AS max_memory
FROM metrics
GROUP BY bucket, tenant_id, host_id;

The hard part is not creating the aggregate. The hard part is choosing the refresh window.

SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '7 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes'
);

start_offset decides how far back the policy refreshes. end_offset usually leaves the newest bucket alone because it is still changing. schedule_interval controls how often the job runs. Those three values are product freshness choices disguised as database settings.

Late data is where the design becomes honest

Every time-series system eventually receives late data. Mobile clients reconnect. Collectors retry. Kafka consumers lag. Customers backfill historical telemetry. If your aggregate refresh window does not cover the late-arrival window, dashboards drift from truth.

I want each pipeline to answer these questions:

• How late can data arrive and still be accepted?
• Which aggregates must refresh when late data arrives?
• When is raw data no longer allowed to change?
• Do customers see approximate recent data or only materialized data?

In newer TimescaleDB versions, real-time aggregates are disabled by default, so recent raw rows may not appear in a continuous aggregate query unless you explicitly design for that behavior. That is not a database footnote; it is a dashboard freshness decision.

Compression and refresh windows must not fight

The easiest continuous aggregate incident to create is compressing a time range that the refresh policy still needs to update. The system may work for weeks, then a backfill lands and refresh jobs slow down or fail.

The rule is simple: do not compress windows that are still actively refreshed. If your continuous aggregate refreshes the last 30 days, compression should start after that refresh window, not inside it.

What I monitor in production

• Ingest rate: rows per second by hypertable and tenant.
• Chunk count: too many chunks increases planning overhead.
• Chunk size: oversized hot chunks defeat the point of partitioning.
• Compression or columnstore lag: cold chunks that remain rowstore.
• Retention lag: chunks older than policy still present.
• Refresh duration: continuous aggregate jobs competing with user queries.
• Late-arrival rate: data arriving outside the expected refresh window.
• Dashboard p99: the user-facing metric that actually matters.

When TimescaleDB is the right move

Use TimescaleDB when the pain is clearly time-series pain: range pruning, lifecycle management, compression, retention, and aggregate refresh. It is a strong fit for infrastructure metrics, IoT readings, usage metering, product analytics, and telemetry that still benefits from SQL.

Be cautious when the workload is mostly relational and just happens to have timestamps. Hypertables are not a universal performance upgrade. They are a better physical design for data whose lifecycle and access patterns are dominated by time.