Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Paris Time Series Meetup #6 - TimescaleDB - Timescale 101

Paris Time Series Meetup #6 - TimescaleDB - Timescale 101

Edition 6 du Paris Time Series Meetup où Avthar Sewrathan, Developer Advocate chez Timescale vient nous présenter le produit TimescaleDB.

Il présente le projet, les fonctionnalités, les pros & cons de travailler avec TimescaleDB, la roadmap, etc

D0a6969000c3715087b3a00abd646d20?s=128

ParisTimeSeries

September 30, 2020
Tweet

Transcript

  1. TimescaleDB: An Introduction Paris Time-Series Meetup Avthar Sewrathan Developer Advocate

    Sept 2020
  2. Avthar Sewrathan Developer Advocate About Me @avthars @avthars avthar.com

  3. Roadmap ❓ What is TimescaleDB? Pros and Cons Popular Use

    cases Roadmap and Future Work Resources + Questions
  4. What is TimescaleDB❓

  5. TimescaleDB: An overview TimescaleDB = PostgreSQL for time-series Leading open-source

    relational database for time-series data Packaged as PostgreSQL extension Optimized for: • Consistently high ingest • High cardinality • Fast, complex queries • High performance at scale
  6. Developer productivity TimescaleDB provides all the power and convenience of

    PostgreSQL, including a proven relational database, the full SQL language, the rich and vibrant PostgreSQL tools ecosystem, and all the developer tools supported by PostgreSQL.
  7. TimescaleDB is PostgreSQL with time-series superpowers Supercharged PostgreSQL Rely on

    the same PostgreSQL you know and love, with full SQL, rock-solid reliability, and a massive ecosystem. Accelerated performance Achieve 10-100x faster queries than PostgreSQL, InfluxDB, and MongoDB. Native optimizations for time-series. Massive scale Write millions of data points per second. Store 100s of terabytes or petabytes. Don’t worry about cardinality. Relational & time series, together Simplify your stack, ask more complex questions, and build more powerful applications. Worry-free operations Let us run TimescaleDB for you, fully managed on AWS, Azure, or GCP in 75+ regions. Access top-rated support. Lower costs Spend less with 94% compression rates from best-in-class algorithms and other performance improvements.
  8. None
  9. Highly scalable data ingestion Write millions of data points per

    second from relentless streams of time-series data.
  10. Pros and Cons 10

  11. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  12. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  13. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  14. TimescaleDB is PostgreSQL with time-series superpowers Supercharged PostgreSQL Rely on

    the same PostgreSQL you know and love, with full SQL, rock-solid reliability, and a massive ecosystem. Accelerated performance Achieve 10-100x faster queries than PostgreSQL, InfluxDB, and MongoDB. Native optimizations for time-series. Massive scale Write millions of data points per second. Store 100s of terabytes or petabytes. Don’t worry about cardinality. Relational & time series, together Simplify your stack, ask more complex questions, and build more powerful applications. Worry-free operations Let us run TimescaleDB for you, fully managed on AWS, Azure, or GCP in 75+ regions. Access top-rated support. Lower costs Spend less with 94% compression rates from best-in-class algorithms and other performance improvements.
  15. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  16. None
  17. None
  18. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  19. SQL is the 3rd-most popular programming language among developers. Developers

    ❤ SQL Source: Stack Overflow, https://insights.stackoverflow.com/survey/2020#most-popular-technologies
  20. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  21. Built on PostgreSQL Use the tools and utilities you know

    and love
  22. None
  23. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  24. Pros 1. Free 2. Relational and time-series data in one

    DB 3. Performance 4. SQL 5. Ecosystem 6. Reliability of PostgreSQL 7. Time-series features
  25. Enterprise- grade data management TimescaleDB includes advanced data management not

    found in traditional relational databases, including data retention, downsampling, data tiering, hypertable and schema management, data lifecycle management, and more.
  26. None
  27. Innovative time-series analytics TimescaleDB includes a number of time-oriented features

    not found in traditional relational databases, including functions for complex windowing, gap-filling, interpolation, and much more.
  28. Cons Schema Design ✅ Can overcome for certain use-cases eg)

    DevOps & Prometheus metrics Higher storage overhead ✅ Native compression in Timescale 1.7 Scale up-only architecture ✅ Scale out (multi-node) in TimescaleDB 2.0 (Potentially) Complex to tune ✅ Timescale team is here to help!
  29. Popular Use Cases

  30. Time-series data is everywhere -- What is the change of

    memory consumption for each -- of my k8s containers over the past 10 minutes? SELECT time_bucket('10 seconds', time) AS period, container_id, avg(free_mem) FROM metrics WHERE time > NOW () - interval '10 minutes' GROUP BY period, container_id ORDER BY period DESC, container_id; -- How much of my advertising inventory is unsold or -- has unmet demand over recent time intervals? SELECT period, total_sold, (total_supply - total_sold) AS total_unsold, (total_demand - total_sold) AS total_unmet FROM ( SELECT time_bucket('10 seconds', time) AS period, SUM(sold) AS total_sold, SUM(supply) AS total_supply, SUM(demand) AS total_demand FROM inventory WHERE time > NOW () - interval '10 minutes' GROUP BY period ) AS data ORDER BY period DESC; -- For a specific machine, what are its avg, min, and max temp -- readings over time to ensure it's in proper operating range? SELECT time_bucket('10 seconds', time) AS period, min(temperature) AS min_temp, avg(temperature) AS avg_temp, max(temperature) AS max_temp, FROM measurements WHERE machine_id = 'C931baF7' AND time > NOW() - interval '150s' GROUP BY period ORDER BY period DESC; -- For financial ticker 'TIMS', what the the open, close, high, and -- low prices (and its trade volume) over the past 30 days? SELECT time_bucket('1 day', time) AS day, first(price, time) AS open, last(price, time) AS close, max(price) AS high, min(price) AS low, sum(volume) AS volume FROM prices WHERE asset_code = 'TIMS' AND time > NOW() - interval '30d' GROUP BY day ORDER BY day ASC; DevOps Monitoring Application Metrics Internet of Things Financial Data
  31. Demo Time! DevOps Microservice metrics IoT Monitoring Live bus locations

    in New York City IoT Analytics New York City Taxi ride analysis
  32. Roadmap and Future Work 32

  33. Roadmap Timescale 2.0 Multi-node, elastic, petabyte scale Promscale Analytics and

    long term store for Prometheus metrics TOBS The Observability Stack for k8s PostgreSQL 13 Support new version of Postgres
  34. Recap & Next Steps 34

  35. Summary ❓ What is TimescaleDB? Pros and Cons Popular Use

    cases Roadmap and Future Work
  36. Where can you learn more? Get started with Timescale Docs

    https://docs.timescale.com/ ✍ Stay up to date with our Newsletter tsdb.co/timescale-newsletter Join Timescale Developer Slack tsdb.co/TimescaleSlack Start a free Timescale Cloud trial ($300 in cloud credits to start) tsdb.co/get-started-cloud
  37. Thank you! Questions? @avthars Reach out on Twitter!

  38. Appendix 38

  39. 543,000+ Active TimescaleDB Databases

  40. None