Real-World Problem: The IoT Telemetry Bottleneck
Six months ago, our engineering team hit a hard performance wall with our IoT monitoring platform. We were tracking telemetry—temperature, humidity, voltage, and vibration—from roughly 5,000 industrial gateways deployed across four factory floors. With each unit reporting metrics every 5 seconds, our ingest layer pushed a steady stream of 1,000 writes per second directly into a standard PostgreSQL table.
Early on, the database kept up without issue. Our 8-core, 32 GB RAM instance easily absorbed the first 10 million rows. By month two, however, the telemetry table had swelled past 150 million rows. That is when latency degraded across our entire stack:
- Spiking write latency: Ingest response times jumped from 8ms to over 450ms. Batch inserts regularly timed out during peak morning factory shifts.
- Dashboard timeouts: Grafana dashboards calculating 5-minute fleet averages degraded from sub-second responses to 25+ second crawls, frequently returning HTTP 504 Gateway Timeouts.
- Severe I/O thrashing: Routine
VACUUMand autovacuum cycles saturated our NVMe disk throughput at 450 MB/s, starving live transactions of I/O.
Root Cause Analysis: Why Standard PostgreSQL Chokes on Time-Series
We dug into PostgreSQL execution plans, buffer hit rates, and kernel I/O statistics to diagnose the collapse. Three architectural bottlenecks quickly surfaced:
1. B-Tree Indexes Evicted from RAM
As standard tables grow to hundreds of millions of rows, B-Tree indexes expand alongside them. Our composite index on (device_id, recorded_at) grew beyond 32 GB, dwarfing our 8 GB shared_buffers pool. Incoming writes could no longer update index leaf pages in memory. Instead, every insert forced random reads from disk, turning sequential writes into an I/O bottleneck.
2. Expensive Scans for Analytical Aggregates
Dashboard queries calculating hourly or daily rollups scanned millions of raw rows on every refresh. PostgreSQL had to fetch thousands of heap pages just to compute simple arithmetic averages. This burned CPU and memory bandwidth on historical data that had not changed in weeks.
3. Dead Tuple Accumulation and Table Bloat
Purging data older than 90 days meant running scheduled DELETE batches. Because PostgreSQL marks deleted rows as dead tuples rather than reclaiming space immediately, our tables bloated rapidly. The resulting autovacuum runs locked table pages and degraded cache efficiency during peak traffic.
Evaluating the Options
We considered three distinct architectural paths to handle our time-series workload:
| Approach | Pros | Cons | Verdict |
|---|---|---|---|
| Native PostgreSQL Partitioning | Built directly into PostgreSQL; no third-party extensions required. | Requires custom scripts for chunk creation, lacks automated rollups, and offers no native columnar compression. | Too much operational overhead to maintain long term. |
| Dedicated TSDB (e.g., InfluxDB) | Engineered specifically for metrics; excellent compression out of the box. | Adds another database to operate. It also breaks relational joins with device metadata and customer accounts. | High migration cost and dual-write complexity. |
| TimescaleDB Extension | Native extension with automated partitioning (Hypertables), continuous aggregates, and columnar compression. | Requires installing and tuning an external PostgreSQL extension. | Selected: Full SQL compatibility with zero schema rewrites. |
Step-by-Step Implementation with TimescaleDB
TimescaleDB converts standard tables into Hypertables. Under the hood, it splits incoming data into time-based partitions called chunks while exposing a single unified table to your application layer.
Step 1: Installing the Extension and Tuning Memory
On our Ubuntu 22.04 database server running PostgreSQL 15, we added the official Timescale repository and installed the package:
# Add TimescaleDB repository and install package
curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh | sudo bash
sudo apt-get install timescaledb-2-postgresql-15
# Run the automatic memory and worker tuner
sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresqlNext, we enabled the extension inside the database:
CREATE EXTENSION IF NOT EXISTS timescaledb;Step 2: Designing the Schema and Creating a Hypertable
We defined the telemetry table using standard SQL, ensuring the timestamp column had an explicit NOT NULL constraint:
CREATE TABLE sensor_readings (
recorded_at TIMESTAMPTZ NOT NULL,
device_id INTEGER NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
voltage DOUBLE PRECISION
);
-- Convert table into a Hypertable with 1-day chunk intervals
SELECT create_hypertable(
'sensor_readings',
'recorded_at',
chunk_time_interval => INTERVAL '1 day'
);Picking the right chunk interval is critical. Aim to keep active chunks and their indexes within 25% of your available shared_buffers. For our write volume of 86.4 million rows daily, 1-day intervals created ~1.8 GB chunks that fit comfortably in RAM.
Step 3: Accelerating Dashboards with Continuous Aggregates
Continuous Aggregates precompute summary statistics incrementally in the background. When queries arrive, TimescaleDB merges the pre-aggregated historical buckets with only the latest raw data points.
-- Create hourly aggregate view
CREATE MATERIALIZED VIEW device_hourly_stats
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', recorded_at) AS bucket,
device_id,
AVG(temperature) AS avg_temp,
MAX(temperature) AS max_temp,
MIN(temperature) AS min_temp,
AVG(humidity) AS avg_humidity,
COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY bucket, device_id
WITH NO DATA;
-- Automatically refresh aggregated data every 30 minutes
SELECT add_continuous_aggregate_policy('device_hourly_stats',
start_offset => INTERVAL '3 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '30 minutes'
);Step 4: Enabling Columnar Compression and Automated Retention
Telemetry metrics rarely change once written. TimescaleDB columnar compression reorders historical rows into column arrays, delivering significant storage savings.
-- Configure columnar compression by device
ALTER TABLE sensor_readings SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device_id',
timescaledb.compress_orderby = 'recorded_at DESC'
);
-- Automatically compress chunks older than 7 days
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');
-- Drop raw partition chunks older than 180 days
SELECT add_retention_policy('sensor_readings', INTERVAL '180 days');Six Months in Production: The Numbers
Over six months of handling 1.2 billion raw telemetry records, our metrics stabilized across the board:
- Insert Latency: P95 write latency dropped from 450ms down to 6ms, completely eliminating client-side timeouts.
- Dashboard Speed: Grafana 30-day fleet queries plummeted from 25 seconds to 42 milliseconds by reading from
device_hourly_stats. - Disk Footprint: Columnar compression reduced raw telemetry storage from 480 GB down to 62 GB—an 87% space reduction.
- No Vacuum Penalties: Data retention policies drop underlying chunk files via filesystem operations. This bypasses row-by-row deletions and eliminates autovacuum bloat entirely.
If your PostgreSQL instance is struggling under high-frequency writes or growing metric tables, TimescaleDB offers an effective middle ground. You get time-series performance without ditching your existing relational data model, backup pipelines, or ORM tooling.
