Who am I

Disclaimer

• Not a Timescale, Inc. employee
• No partnership with Timescale, Inc.

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

Time series

Features

• Partition management
• Compression
• Aggregation
• Job scheduling

Partitioning

Compression

From

Compression

To

+

Compression algorithm based on the data type

Aggregation

• Materialized view 🐘
  • Cache result of a SELECT query into a table
  • Result needs to be refreshed
• Continuous aggregate 🐯
  • Refresh materialized view continuously and incrementally
  • Except the most recent data
• Real time aggregate 🐯
  • Continuous aggregate + most recent data

Job scheduling

• TimescaleDB policies
  • policy_retention
  • policy_compression
  • policy_refresh_continuous_aggregate
• User-defined actions
  • add_job(proc, schedule_interval)

Licenses

FOSS alternatives

Metrics platform

2021

Infrastructure

Problems

• Incidents with data loss or extended downtimes
• Too much servers
• Limited maintenance skills
• Too much languages (with erlenmeyer)
  • OpenTSDB, PromQL, InfluxQL, Graphite
  • Only compatible with a subpart of the ever changing languages

State of the art

State of the art

Why

• Prometheus is a standard
• We know PostgreSQL
• Compatible with push mode
• No single point of failure

TimescaleDB with Promscale

Deployment

• Disposable proof-of-concept infrastructure
• Terraform + Ansible
• Open-source 😎 (Apache 2.0)
• https://github.com/ovh/timescaledb-benchmark

For example only

Performance

• Access nodes are CPU bound
• Data nodes are I/O bound

Tunning

For I/O bound workloads

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

• Checkpoints slowed down ingestion too much
• PostgreSQL restarts caused too long downtimes
• Do not raise max_wal_size too much

Disk space

700GB in 24 hours

Tunning

Configure enough background workers* to compress chunks

timescaledb.max_background_workers = 48

*at least one per database

Conclusion for the benchmark

• Several opened issues during the bench
• Not able to ingest our minimal target
• Cortex (now Mimir™) was chosen

Aside note

• Multi-node support has been deprecated
• Promscale has been discontinued

Zabbix

The Enterprise-Class Open Source Network Monitoring Solution

Generate and store metrics in PostgreSQL

Massive DELETE

The housekeeper (“hk”)

Regular spikes of delete operations

Retention query

Performed by Zabbix

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

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);

Deletes

Big databases

⚠️ migrate_data => true ⚠️

Very slow and heavy

Migration plan

Migration

Migration

Migration

Migration

Migration

Migration

Migration

Disk space

Compression

• Configure compression with ALTER TABLE
• Grant tables owner the LOGIN privilege
• Schedule compression jobs
• Configure and start background workers

Disk space

I/O

Versions

Conclusion for Zabbix

• Enable TimescaleDB from the beginning
• Ensure background workers are started
• Fine-tune for i/o bound workload
• Install supported versions

Billing

Before cloud computing

• Loop over customers
  • Loop over services
    • Lookup service price
    • Create billing line
  • Create bill

First day of the month

Cloud computing

• More elasticity
  • Resources can be created and deleted
  • Countless times in a month
• Pay for what’s really used

Requirements

Architecture

Mediation

• Store raw events
• Compute daily aggregates

Events (hypertable)

• 18,000,000 rows per day
• Chunk interval of 1 day
• Compress after 6 months
• Drop after 7 months

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;

Refresh policies

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

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

Real-time aggregate

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

Real-time aggregate

Rating

• Query “Mediation” aggregation
• Create billing lines (“ratings”)
• 122,000,000 rows in hot table
  • Uncompressed
• 3,400,000,000 rows in archive table
  • Compressed after 3 days
• Chunk interval of 1 day
• Retention of 7 months

Schema migrations

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

Compression tips

• Do not segment by very high cardinality values
• Do not segment by very low cardinality values
• ~1000 tuples per batchs

Compression example

Segment by ID

Very high cardinality

Segment by subscription

Natural compression

Conclusion for Billing

• No more load the first day of the month
• Easier to predict the amount of next bill

Version management

• TimescaleDB is in active development
• Lots of performance improvements on continuous aggregates
• Less segmentation faults

How to upgrade?

1. Read the changelog
2. Update packages on the operating system
3. Update the extension

psql -X
ALTER EXTENSION timescaledb UPDATE;

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

• Nagios probe to check that
  • Background workers are started
  • Jobs have no error
  • Version is up-to-date
• Part of the OVHcloud Nagios Plugins
  • Open-source 😎 (Apache 2.0)
  • https://github.com/ovh/ovh-nagios-plugins

check_timescaledb

What’s next?

• Migrate remaining Zabbix deployments
• Migrate remaining billing units
• Change data capture

Takeways

• Prepare for high i/o workloads
• Ensure everything is properly configured from the beginning
• Moving large live datasets is challenging

Thanks

• My colleagues because we are One Team
• Timescale, Inc. for providing and maintaining TimescaleDB
• You for contributing to the PostgreSQL community

🙏

Questions

❓