Efficient Time Series Management with TimescaleDB at OVHcloud

PGDay Lowlands 2024

Julien RIOU

September 13, 2024



Who am I


Disclaimer


Disclaimer

Timescale and TimescaleDB are trademarks of Timescale, Inc.

The Grafana Labs Marks are trademarks of Grafana Labs, and are used with Grafana Labs’ permission. We are not affiliated with, endorsed or sponsored by Grafana Labs or its affiliates.


Who are we


Internal databases


Summary

  1. Basics
  2. Metrics platform
  3. Zabbix
  4. Billing
  5. Version management
  6. Tooling
  7. Conclusion
  8. What’s next

TimescaleDB

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.

https://github.com/timescale/timescaledb


Time series

In mathematics, a time series is a series of data points indexed (or listed or graphed) in time order.

https://en.wikipedia.org/wiki/Time_series


Time series

Graph showing the evolution of TimescaleDB stars on Github


Time series

Date Stars
2017-08-27 2220
2018-01-24 3330
2018-05-11 4470
2024-07-30 16950
2024-08-08 16992

Features


Partitioning


Compression

From

Date Count
2023-03-30 14670
2023-12-23 15810

Compression

To

Date Count
[2023-03-30, 2023-12-23] [14670, 15810]

+

Compression algorithm based on the data type


Aggregation


Job scheduling


Licenses

Edition Features License Permissions
Community Advanced chunk operations
Compression
Continuous aggregates
Data retention
Jobs
Advanced hyperfunctions
Timescale License (TSL) Internal Use (2.1.a) ✅
Value Added Products or Services (2.1.b) ✅
DBaaS to third parties (2.2) ❌
Apache 2 The rest Apache 2.0 Open source initiative (OSI) ✅

FOSS alternatives

Feature Software
Time-series management pg_timeseries
Partition management pg_partman
Job scheduling pg_cron, pg_timetable, cron jobs
Compression Citus columnar, Hydra columnar

Metrics platform

2021


Infrastructure


Problems


State of the art

State of the art


State of the art

State of the art


Why


TimescaleDB with Promscale

Proof of concept infrastructure with TimescaleDB and Promscale


Deployment

For example only


Performance


Tunning

For I/O bound workloads

min_wal_size = '64GB'
max_wal_size = '125GB'

Screenshot: © 2024 Pokémon. © 1995–2024 Nintendo/Creatures Inc./GAME FREAK inc. Pokémon


Disk space

700GB in 24 hours

Disk space used by TimescaleDB in 24 hours


Tunning

Configure enough background workers* to compress chunks

timescaledb.max_background_workers = 48

*at least one per database


compressed_chunks count
1 18
0 437

Screenshot: © 2024 Pokémon. © 1995–2024 Nintendo/Creatures Inc./GAME FREAK inc. Pokémon


Conclusion for the benchmark

List of issues:


Aside note


Two years later

SpongeBob Time Cards Generator


Zabbix


The Enterprise-Class Open Source Network Monitoring Solution

Generate and store metrics in PostgreSQL


Table Number of rows Size (GB) Compression Retention
history 35,789,655,160 452 7d 15d
history_uint 146,317,269,524 1,238 7d 15d
history_log 0 0 7d 15d
history_text 8,644,967,725 196 7d 15d
history_str 13,985 0.2 7d 15d
trends 1,549,631,486 34 7d 180d
trends_uint 6,192,249,880 88 7d 180d

Snapshot taken on June 20, 2024 on hypertables managed by TimescaleDB on 2 out of 3 information systems.


Massive DELETE


The housekeeper (“hk”)

Regular spikes of delete operations


Retention query

Performed by Zabbix

delete from %s where clock < now - n hours;

The query has been simplified. For the real code, see delete_history function.


Enable TimescaleDB

SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);
SELECT create_hypertable('trends', 'clock', chunk_time_interval => 2592000, migrate_data => true);
SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, migrate_data => true);
BEGIN;
  UPDATE config SET db_extension='timescaledb',hk_history_global=1,hk_trends_global=1;
  UPDATE config SET compression_status=1,compress_older='7d';
COMMIT;

Retention query

Replaced by

select drop_chunks(relation=>'%s',older_than=> now - history_seconds);

The query has been simplified. For the real code, see hk_drop_partition function.


Deletes

Fixed


Big databases

⚠️ migrate_data => true ⚠️

Very slow and heavy

The process took several hours for only 50GB with a degraded quality of service (“Zabbix database data ingestion failure”).


Migration plan


Migration


Migration


Migration


Migration


Migration


Migration


Migration


Disk space


Compression


Disk space

Fixed


I/O

Fixed

Instead of going wild on max_wal_size, we have raised its value from 1GB to 10GB. The image shows a saturation of IOPS with 1GB and a sane usage with 10GB.


Versions

Zabbix TimescaleDB
7.0 2.13.0-2.15.X
6.4 2.1.0-2.15.X
6.0 2.1.0-2.15.X
5.0.10+ 1.X, 2.0.1-2.15.X
5.0.0-5.0.9 1.X

Conclusion for Zabbix


Billing


Before cloud computing

Subscription system


First day of the month

The production databases were heavily loaded every first day of the month because of the subscription-based billing system.

Link to image


Cloud computing

Consumption system


Requirements

  • Store consumption metrics
  • Aggregate consumption metrics
  • Write old data points

Timescale logo

Timescale brand


Architecture

Billing architecture


Mediation


Events (hypertable)


Daily events (continuous aggregate)

CREATE MATERIALIZED VIEW "daily_events" WITH (timescaledb.continuous) AS
    SELECT
        customer_id, subscription_id, time_bucket('1 day', begin) AS time,
        MIN(duration) AS duration_min, MAX(duration) AS duration_max,
        AVG(duration) AS duration_avg, SUM(duration) AS duration_sum,
        MIN(quantity) AS quantity_min, MAX(quantity) AS quantity_max,
        AVG(quantity) AS quantity_avg, SUM(quantity) AS quantity_sum,
        SUM(1) AS nb_events
    FROM "events"
    WHERE quantity > 0
    GROUP BY time, customer_id, subscription_id
    WITH NO DATA;

The time_bucket function is similar to the standard PostgreSQL date_bin function The return value is the bucket’s start time

https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/


Refresh policies

  1. Refresh continuous aggregate
    • [1 hour, 7 days]
    • Every hour
  2. Refresh continuous aggregate (via UDF)
    • [1 hour, 2 months]
    • Every day

At first, the continuous aggregation was scheduled to be refreshed every 10 minutes for 2 months of data. We have reduced the frequency and splitted in two refresh policies to reduce the pressure on the database service.

How to implement the UDF:

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

CREATE PROCEDURE refresh_continuous_aggregate_udf(job_id INT, config jsonb) 
LANGUAGE PLPGSQL AS $$ 

BEGIN
RAISE NOTICE 'Executing job % with config %', job_id, config;
CALL refresh_continuous_aggregate(
    config->>'view'::TEXT,
    NOW()-(config->>'lower_window')::INTERVAL,
    NOW()-(config->>'upper_window')::INTERVAL
);

END $$;

SELECT
    add_job(
        'refresh_continuous_aggregate_udf',
        '1 day',
        config => '{"view": "daily_event", "lower_window": "2 month", "upper_window": "1 hour"}',
        initial_start => '2024-05-01T23:00:00Z'::TIMESTAMP WITH TIME ZONE
    );

Continuous aggregate

Continuous aggregate


Real-time aggregate

ALTER MATERIALIZED VIEW "daily_events"
    SET (timescaledb.materialized_only = false);

Real-time aggregate

Real-time aggregate


Rating


Schema migrations

Let’s add a simple “id” column

alter table massive_archive
    add column id bigserial not null;

ERROR: cannot add column with non-constant default expression to a hypertable that has compression
enabled

Try again

  1. Decompress chunks
  2. Add the column
  3. Compress chunks

Disk full


Try again

  1. Create a second hypertable
  2. For each chunk (oldest to newest)
    1. Copy rows
    2. Compress chunk
  3. Swap hypertables
  4. Drop unused hypertables

Fixed


Compression tips


Compression example

Time ID Subscription Quantity
2024-09-04 00:00:00Z 1 1 10
2024-09-04 00:00:00Z 2 2 20
2024-09-04 01:00:00Z 3 1 10
2024-09-04 01:00:00Z 4 2 30

Segment by ID

Very high cardinality

Time ID Subscription Quantity
[2024-09-04 00:00:00Z] [1] [1] [10]
[2024-09-04 00:00:00Z] [2] [2] [20]
[2024-09-04 01:00:00Z] [3] [1] [10]
[2024-09-04 01:00:00Z] [4] [2] [30]

Screenshot: © 2024 Pokémon. © 1995–2024 Nintendo/Creatures Inc./GAME FREAK inc. Pokémon


Segment by subscription

Natural compression

Time ID Subscription Quantity
[2024-09-04 00:00:00Z,2024-09-04 01:00:00Z] [1,3] [1,1] [10,10]
[2024-09-04 00:00:00Z,2024-09-04 01:00:00Z] [2,4] [2,2] [20,30]

Fixed


Conclusion for Billing


Version management


How to upgrade?

  1. Read the changelog
  2. Update packages on the operating system
  3. Update the extension
psql -X
ALTER EXTENSION timescaledb UPDATE;

Or via the community.postgresql.postgresql_ext Ansible module

The community.postgresql.postgresql_ext patches:

  • fix(postgresql_ext): Reconnect before upgrade #689
  • fix(postgresql_ext): Reconnect before upgrade #695

Restart if needed

Management of PostgreSQL extension failed: loader version out-of-date
HINT: Please restart the database to upgrade the loader version.

Tooling


check_timescaledb


check_timescaledb


What’s next?


Takeways


Thanks

🙏


Questions