TimescaleDB: Re-engineering PostgreSQL as a time-series database - - PowerPoint PPT Presentation

TimescaleDB:

Re-engineering PostgreSQL as a time-series database

Michael J. Freedman

Co-founder / CTO, Timescale 
 Professor of Computer Science, Princeton

mike@timescale.com · github.com/timescale · Apache 2 License

Financial & Marketing Industrial Machines Datacenter & DevOps

Time-series Data is Emerging Everywhere

Web / mobile Events

Transportation & Logistics

So, what is time-series data?

What you have been told

Name Tags Data

CPU Host=Name,Region=West 1990-01-01 01:02:00 70
 1990-01-01 01:03:00 71 1990-01-01 01:04:00 72 1990-01-01 01:04:00 73 1990-01-01 01:04:00 100

What you have been told

Name Tags Data

CPU Host=Name,Region=West 1990-01-01 01:02:00 70
 1990-01-01 01:03:00 71 1990-01-01 01:04:00 72 1990-01-01 01:04:00 73 1990-01-01 01:04:00 100 FreeMem Host=Name,Region=West 1990-01-01 01:02:00 800M
 1990-01-01 01:03:00 600M 1990-01-01 01:04:00 400M 1990-01-01 01:04:00 200M 1990-01-01 01:04:00 0

2 time-series?

This is wrong

Time-series data has a richer structure

Tags Data Host=Name,Region=West 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 MemFree 800M 600M 400M 200M Temp 80 81 82 83 120

Fewer queries select * where time = x

Tags Data Host=Name,Region=West 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 MemFree 800M 600M 400M 200M Temp 80 81 82 83 120

Complex filters select * where temp > 100

Tags Data Host=Name,Region=West 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 MemFree 800M 600M 400M 200M Temp 80 81 82 83 120

Complex aggregates avg(mem_free) group by (cpu/10)

Tags Data Host=Name,Region=West 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 MemFree 800M 600M 400M 200M Temp 80 81 82 83 120

Correlations How does temp correlate with memfree?

Tags Data Host=Name,Region=West 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 MemFree 800M 600M 400M 200M Temp 80 81 82 83 120

Leverage relations

Data 1990-01-01 01:02:00
 1990-01-01 01:03:00 1990-01-01 01:04:00 1990-01-01 01:04:00 1990-01-01 01:04:00 CPU 70 71 72 73 100 Host 1 2 3 4 5 Region 91 91 93 93 95

Region stored in separate host metadata table

How is this different than having a time field?

Treat changes as inserts, not overwrites.

You probably have time-series data and don’t even realize it

What database for time-series data?

What database for time-series data?

Relational NoSQL

https://www.percona.com/blog/2017/02/10/percona-blog-poll-database-engine-using-store-time-series-data/

32% 68%

Why so much NoSQL?

Why not use a simple relational database table?

Postgres 9.6.2 on Azure standard DS4 v2 (8 cores), SSD (premium LRS storage) Each row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics)

Postgres 9.6.2 on Azure standard DS4 v2 (8 cores), SSD (premium LRS storage) Each row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics)

B-tree Insert Pain

1 20 10 1 10 13 24 29 25 5

Insert batch:

17 8

Memory Capacity: 2 NODES

IN MEMORY WRITE TO DISK

B-tree Insert Pain

1 20 10 1 10 13 24 29 25 5

Insert batch:

17 8

Memory Capacity: 2 NODES

IN MEMORY WRITE TO DISK

1 20 10 1 10 13 24 29 25

Insert batch:

8 5 17

B-tree Insert Pain

Memory Capacity: 2 NODES

IN MEMORY WRITE TO DISK

10 13

B-tree Insert Pain

1 20 10 1 24 29 25

Insert batch:

8 5 17

Memory Capacity: 2 NODES

IN MEMORY WRITE TO DISK

Challenge in scaling up

• Indexes write to random parts of B-tree

– Example: BTREE(device_id, time DESC)

• As table grows large

– Indexes no longer fit in memory – Writes to random locations in B-tree – Causes swapping

Device: A Time: 01:01:01 Device: Z Time: 01:01:01

Device, Time DESC

Why not just use a NoSQL DB?

Key-value store with indexed key lookup at high-write rates

NoSQL champion: Log-Structured Merge Trees

+

• Compressed data storage
• Common approach for time series:

use key <name, tags, field, time>

Log-Structured Merge Tree

5

Insert batch:

17 8

Memtable

1 3 4 5 7 9 10 1 2 3 5 7 9 10 11

sstables Older

Insert batch:

8 17 5

Memtable

1 3 4 5 7 9 10 1 2 3 5 7 9 10 11

sstables Older

Log-Structured Merge Tree

Log-Structured Merge Tree

Insert batch: Memtable

1 3 4 5 7 9 10 1 2 3 5 7 9 10 11

sstables Older

5 8 17

Problems with non-KV queries

If querying for key 5: stop after finding it in first sstable

1 3 4 5 7 9 10 1 2 3 5 7 9 10 11

sstables Older

5 8 17

If querying for data in time range 5-10: have to scan all sstables Requires (in-memory) data structs to efficiently map tags to keys 
 ⇒ high-cardinality issue No secondary index support 
 ⇒ “tag lock-in”

NoSQL + LSMTs Come at a Cost

• Less powerful queries
• Weaker consistency (no ACID)
• No JOINS
• Loss of SQL ecosystem

+

Is there a better way?

TimescaleDB:


Scalable time-series database, full SQL

Packaged as a PostgreSQL extension Apache 2 Licensed

• High write rates
• Time-oriented optimizations
• Fast complex queries
• 100s billions rows / node

Scalable

• Inherits 20+ years of

PostgreSQL reliability

• Streaming replication,

backups, HA clustering

Reliable

• Supports full SQL
• Time-oriented features
• Easy to manage: looks

like a regular table

• One DB for relational 


& time-series data

Easy to Use

TimescaleDB:


Scalable time-series database, full SQL

Packaged as a PostgreSQL extension

How?

Time-series workloads are different.

Time-series

• Primarily UPDATEs
• Writes randomly distributed
• Transactions to multiple 


primary keys

• Primarily INSERTs
• Writes to recent time interval
• Writes associated with a 


timestamp and primary key

OLTP

How it works

Time


(older)

Time-space partitioning


(for both scaling up & out)

Time


(older)

Intervals

1) manually specified 2) automatically adjusted

Time-space partitioning


(for both scaling up & out)

Space Time


(older) (hash partitioning)

Intervals

1) manually specified 2) automatically adjusted

Time-space partitioning


(for both scaling up & out)

Chunk (sub-table)

Space Time


(older) (hash partitioning)

Intervals

1) manually specified 2) automatically adjusted

Time-space partitioning


(for both scaling up & out)

Chunk (sub-table)

Hypertable

Space Time


(older) (hash partitioning)

Intervals

1) manually specified 2) automatically adjusted

The Hypertable Abstraction

Chunks

Hypertable

• Indexes
• Triggers
• Constraints
• Foreign keys
• UPSERTs
• Table mgmt

PostgreSQL 10 doesn’t solve the problem 
 (more later)

Not well supported in PG10

Chunks

Hypertable

• Indexes
• Triggers
• Constraints
• Foreign keys
• UPSERTs
• Table mgmt

Automatic Space-time Partitioning

Automatic Space-time Partitioning

Chunking benefits

Chunks are “right-sized”

Recent (hot) chunks fit in memory

Single node: Scaling up via adding disks

• Faster inserts
• Parallelized queries

How Benefit

Chunks spread across many disks (elastically!) either RAIDed or via distinct tablespaces

Multi-node: Scaling out via existing mechanisms

D e v e l

• p

m e n t

Under development

• Chunks spread across servers
• Insert/query to any server
• Distributed query optimizations

(push-down LIMITs and aggregates, etc.)

SELECT time, temp FROM data
 WHERE time > now() - interval ‘7 days’
 AND device_id = ‘12345’

Avoid querying chunks via constraint exclusion

Avoid querying chunks via constraint exclusion

SELECT time, device_id, temp FROM data
 WHERE time > ‘2017-08-22 18:18:00+00’

Avoid querying chunks via constraint exclusion

SELECT time, device_id, temp FROM data
 WHERE time > now() - interval ’24 hours’

Plain Postgres won’t exclude chunks

Efficient retention policies

SELECT time, device_id, temp FROM data
 WHERE time > now() - interval ’24 hours’

Drop chunks, don’t delete rows

⇒ avoids vacuuming

Is it just about performance?

Simplify your stack

VS

TimescaleDB


(with JOINS)

RDBMS NoSQL

Application Application

Rich Time Analytics

Geospatial Temporal Analysis (with PostGIS)

Data Retention + Aggregations

Granularity raw 15 min day Retention 1 week 1 month forever

Enjoy the entire PostgreSQL ecosystem

Unlock the richness of your data

TimescaleDB + PostgreSQL

Prometheus

Remote Storage Adapter

+ pg_prometheus Prometheus Grafana

Performance benefits

144K

METRICS / S

TimescaleDB vs. PostgreSQL

(single-row inserts)

14.4K

I N S E R T S / S

TimescaleDB 0.5, Postgres 9.6.2 on Azure standard DS4 v2 (8 cores), SSD (LRS storage) Each row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics)

> 2 x

TimescaleDB vs. PostgreSQL

(batch inserts)

TimescaleDB 0.5, Postgres 9.6.2 on Azure standard DS4 v2 (8 cores), SSD (LRS storage) Each row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics)

1 . 1 1 M

METRICS / S

TimescaleDB vs. PostgreSQL

SPEEDUP

Table scans, simple column rollups

~0-20%

GROUPBYs

20-200%

Time-ordered GROUPBYs

400-10000x

DELETEs

2000x

TimescaleDB 0.5, Postgres 9.6.2 on Azure standard DS4 v2 (8 cores), SSD (LRS storage) Each row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics)

PG doesn’t know to use the index

Example Query Optimization

CREATE INDEX ON readings(time); SELECT date_trunc(‘minute’, time) as bucket, avg(cpu) FROM readings GROUP BY bucket ORDER BY bucket DESC LIMIT 10;

Timescale understands

time

What about PostgreSQL 10?

TimescaleDB: Easy to Get Started

CREATE TABLE conditions ( time timestamptz, temp float, humidity float, device text ); SELECT create_hypertable('conditions', 'time', ‘device', 4, chunk_time_interval => interval '1 week’); INSERT INTO conditions VALUES ('2017-10-03 10:23:54+01', 73.4, 40.7, 'sensor3'); SELECT * FROM conditions; time | temp | humidity | device

• -----------------------+------+----------+---------

2017-10-03 11:23:54+02 | 73.4 | 40.7 | sensor3

— Set new chunk time interval SELECT set_chunk_time_interval('conditions', interval '24 hours’); — Set new number of space partitions SELECT set_number_partitions('conditions', 6);

TimescaleDB: Repartitioning is Simple

PG10 requires a lot of manual work

CREATE TABLE conditions ( time timestamptz, temp float, humidity float, device text ); CREATE TABLE conditions_p1 PARTITION OF conditions FOR VALUES FROM (MINVALUE) TO ('g') PARTITION BY RANGE (time); CREATE TABLE conditions_p2 PARTITION OF conditions FOR VALUES FROM ('g') TO ('n') PARTITION BY RANGE (time); CREATE TABLE conditions_p3 PARTITION OF conditions FOR VALUES FROM ('n') TO ('t') PARTITION BY RANGE (time); CREATE TABLE conditions_p4 PARTITION OF conditions FOR VALUES FROM ('t') TO (MAXVALUE) PARTITION BY RANGE (time);

• - Create time partitions for the first week in each device partition

CREATE TABLE conditions_p1_y2017m10w01 PARTITION OF conditions_p1 FOR VALUES FROM ('2017-10-01') TO ('2017-10-07'); CREATE TABLE conditions_p2_y2017m10w01 PARTITION OF conditions_p2 FOR VALUES FROM ('2017-10-01') TO ('2017-10-07'); CREATE TABLE conditions_p3_y2017m10w01 PARTITION OF conditions_p3 FOR VALUES FROM ('2017-10-01') TO ('2017-10-07'); CREATE TABLE conditions_p4_y2017m10w01 PARTITION OF conditions_p4 FOR VALUES FROM ('2017-10-01') TO (‘2017-10-07');

• - Create time-device index on each leaf partition

CREATE INDEX ON conditions_p1_y2017m10w01 (time); CREATE INDEX ON conditions_p2_y2017m10w01 (time); CREATE INDEX ON conditions_p3_y2017m10w01 (time); CREATE INDEX ON conditions_p4_y2017m10w01 (time); INSERT INTO conditions VALUES ('2017-10-03 10:23:54+01', 73.4, 40.7, ‘sensor3');

TimescaleDB vs. PostgreSQL 10

(single-row inserts)

“As an update, we’ve scaled to beyond 500 billion rows now and things are still working very smoothly.
 
 The performance we’re seeing is monstrous, almost 70% faster queries!”

• Software Engineer, Large Enterprise Computer Company

500B

ROWS

400K

ROWS / SEC

50K

CHUNKS

5min

INTERVALS

500B

ROWS

400K

ROWS / SEC

50K

CHUNKS

5min

INTERVALS

Optimizing for scale

• Faster chunk exclusion

– Avoid opening / locking all chunks when

performing constraint exclusion

• Better LIMITs across chunks

– SortedAppend to avoid requiring at least one

tuple per chunk during MergeAppend / LIMIT

What about NoSQL?

> 1 x

TimescaleDB vs. Cassandra

TimescaleDB 0.5, Cassandra 3.11.0, Azure standard DS4 v2 (8 cores), SSD (LRS storage) Each TimescaleDB row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics) Each Cassandra row has 2 columns (1 key, combo of tags + host + timestamp)

TimescaleDB vs. Cassandra

SPEEDUP

Table scales simple column rollups

2-44x

GROUPBYs

1-3x

Time-ordered GROUPBYs

1900x

Lastpoint

1400x

TimescaleDB 0.5, Cassandra 3.11.0, Azure standard DS4 v2 (8 cores), SSD (LRS storage) Each TimescaleDB row has 12 columns (1 timestamp, indexed 1 host ID, 10 metrics) Each Cassandra row has 2 columns (1 key, combo of tags + host + timestamp)

Open Source (Apache 2.0)

• github.com/timescale/timescaledb

Join the Community

• slack.timescale.com

@timescale

Timescale is hiring!

• Core Database Engineers
• R&D Engineers
• Solutions Engineers
• Evangelists
• Customer Success

careers.timescale.com