TimescaleDB on a VPS: PostgreSQL for Time-Series Data, IoT Metrics, and Application Monitoring

TimescaleDB is a PostgreSQL extension that transforms it into a high-performance time-series database — using hypertables (automatically partitioned by time) to achieve 10–100× faster time-range queries and inserts compared to standard PostgreSQL tables. It handles IoT sensor data, application performance metrics, financial tick data, and event streams while retaining full SQL compatibility. No new query language, no new tooling — just PostgreSQL that’s optimized for time-series patterns.

TimescaleDB vs InfluxDB vs ClickHouse for Time-Series

Step 1: Install TimescaleDB

<code"># Add TimescaleDB repository
sudo apt install -y gnupg postgresql-common apt-transport-https lsb-release
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install TimescaleDB for PostgreSQL 16
sudo apt install -y timescaledb-2-postgresql-16

# Run TimescaleDB tune (optimizes PostgreSQL config for time-series workloads)
sudo timescaledb-tune --quiet --yes

sudo systemctl restart postgresql

# Verify installation
sudo -u postgres psql -c "SELECT installed_version FROM pg_available_extensions WHERE name = 'timescaledb';"

Step 2: Enable TimescaleDB Extension

<code">sudo -u postgres psql

<code">CREATE DATABASE metrics;
\c metrics

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Verify
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
\q

Step 3: Create Hypertables

<code">sudo -u postgres psql metrics

<code">-- IoT sensor readings table
CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    location    TEXT,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);

-- Convert to hypertable partitioned by time (7-day chunks)
SELECT create_hypertable(
    'sensor_readings',
    by_range('time', INTERVAL '7 days')  -- 7-day partitions
);

-- Application metrics table
CREATE TABLE app_metrics (
    time        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    service     TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    value       DOUBLE PRECISION,
    tags        JSONB DEFAULT '{}'
);

SELECT create_hypertable('app_metrics', by_range('time'));

-- Create indexes for common query patterns
CREATE INDEX ON sensor_readings (device_id, time DESC);
CREATE INDEX ON app_metrics (service, metric_name, time DESC);

Step 4: Insert Time-Series Data

<code">pip install psycopg2-binary

<code">import psycopg2
from psycopg2.extras import execute_values
from datetime import datetime, timedelta
import random

conn = psycopg2.connect("postgresql://postgres:password@localhost/metrics")

def insert_sensor_batch(readings: list[dict]):
    """Insert multiple sensor readings efficiently."""
    with conn.cursor() as cur:
        execute_values(cur, """
            INSERT INTO sensor_readings
                (time, device_id, location, temperature, humidity, pressure, battery)
            VALUES %s
        """, [(
            r['time'], r['device_id'], r['location'],
            r['temperature'], r['humidity'], r['pressure'], r['battery']
        ) for r in readings])
    conn.commit()

def record_metric(service: str, metric: str, value: float, tags: dict = None):
    """Record a single application metric."""
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO app_metrics (service, metric_name, value, tags)
            VALUES (%s, %s, %s, %s)
        """, (service, metric, value, psycopg2.extras.Json(tags or {})))
    conn.commit()

# Generate sample IoT data
readings = []
now = datetime.now()
for i in range(1000):
    readings.append({
        'time': now - timedelta(minutes=i),
        'device_id': f'sensor-{random.randint(1, 5)}',
        'location': random.choice(['warehouse-a', 'warehouse-b', 'office']),
        'temperature': 20 + random.uniform(-5, 10),
        'humidity': 50 + random.uniform(-20, 20),
        'pressure': 1013 + random.uniform(-10, 10),
        'battery': random.uniform(0, 100),
    })

insert_sensor_batch(readings)
record_metric('api-server', 'request_duration_ms', 145.3, {'endpoint': '/api/users'})

Step 5: Time-Series Queries

<code">-- Average temperature per device per hour (last 24 hours)
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    COUNT(*) AS readings
FROM sensor_readings
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, device_id
ORDER BY bucket DESC, device_id;

-- Moving average (smooth noisy sensor data)
SELECT
    time,
    device_id,
    temperature,
    AVG(temperature) OVER (
        PARTITION BY device_id
        ORDER BY time
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS moving_avg_5
FROM sensor_readings
WHERE device_id = 'sensor-1'
  AND time > NOW() - INTERVAL '6 hours'
ORDER BY time;

-- Devices with anomalous readings (outside 3 standard deviations)
SELECT
    device_id,
    time,
    temperature,
    AVG(temperature) OVER (PARTITION BY device_id) AS mean,
    STDDEV(temperature) OVER (PARTITION BY device_id) AS stddev
FROM sensor_readings
WHERE time > NOW() - INTERVAL '7 days'
HAVING ABS(temperature - AVG(temperature) OVER (PARTITION BY device_id)) >
       3 * STDDEV(temperature) OVER (PARTITION BY device_id);

-- Continuous aggregate: pre-computed hourly averages
CREATE MATERIALIZED VIEW hourly_sensor_stats
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    device_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_readings
GROUP BY bucket, device_id;

-- Query the materialized view (much faster than raw table)
SELECT * FROM hourly_sensor_stats
WHERE bucket > NOW() - INTERVAL '7 days'
ORDER BY bucket DESC;

Step 6: Data Retention Policy

<code">-- Auto-delete data older than 90 days (saves disk, keeps recent data)
SELECT add_retention_policy('sensor_readings', INTERVAL '90 days');

-- For app_metrics, keep 30 days of raw data
SELECT add_retention_policy('app_metrics', INTERVAL '30 days');

-- But keep the continuous aggregate (hourly summaries) longer:
SELECT add_retention_policy('hourly_sensor_stats', INTERVAL '2 years');

-- View active policies
SELECT * FROM timescaledb_information.jobs;

Step 7: Compression

<code">-- Enable compression for data older than 7 days (reduces storage 90-95%)
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

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

-- Check compression ratio
SELECT
    chunk_schema, chunk_name,
    pg_size_pretty(before_compression_total_bytes) AS before,
    pg_size_pretty(after_compression_total_bytes) AS after
FROM chunk_compression_stats('sensor_readings');
-- Typical output: 10 MB → 0.5 MB (95% compression for time-series data)

Getting Started

TimescaleDB installs as a PostgreSQL extension — no additional service to run. A 2 GB Ubuntu VPS at VPS.DO handles thousands of time-series inserts per second and fast time-range queries for IoT, monitoring, and analytics use cases. Compression reduces storage requirements by 90–95% for older data, making long-term time-series retention economical on VPS disk.

Conclusion

TimescaleDB transforms standard PostgreSQL into a time-series database with 10–100× faster time-range queries, automatic chunk pruning, 90–95% compression for older data, and continuous aggregates for pre-computed summaries. For teams already running PostgreSQL, adding TimescaleDB is a single CREATE EXTENSION command — no new service, no new query language, no new operational burden. Ideal for IoT sensor pipelines, application performance monitoring, and financial data storage where SQL expressiveness and relational capabilities matter alongside time-series performance.