5 PostgreSQL Functions for Monitoring & Analytics

TimescaleDB

1 year ago

#PostgreSQL is ideal for real-time monitoring and historical analysis, but how do you write *efficient* queries to track real-time performance metrics and spot trends?

We know it can be tricky, and in this coding session, @avthars demos his favorite (essential!) queries for common #DevOps scenarios, including TimescaleDB-specific functions for complex #timeseries analysis.

You’ll get tips, best practices, and resources, so you leave ready to customize each query for your projects.

🛠 𝗥𝗲𝗹𝗲𝘃𝗮𝗻𝘁 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀
📌 Get the Air Quality sample app (ft in our demo) ⇒ https://tsdb.co/air-qual-repo
📌 Check out more advanced analytic functions (Timescale Docs) ⇒ https://tsdb.co/advanced-queries-docs
📌 Learn key SQL functions for time-series analysis ⇒ https://tsdb.co/time-series-sql
📌 Join Timescale Developer Slack ⇒ https://tsdb.co/TimescaleSlack
📌 Start a free Timescale Cloud trial ($300 in cloud credits to start 🎉) ⇒ https://tsdb.co/get-started-cloud

🐯 𝗔𝗯𝗼𝘂𝘁 𝗧𝗶𝗺𝗲𝘀𝗰𝗮𝗹𝗲
At Timescale, we are dedicated to serving developers worldwide, enabling them to build exceptional data-driven products that measure everything that matters. Analyzing this data across the time dimension (“time-series data”) enables developers to understand what is happening right now, how that is changing, and why that is changing. We are backed by top-tier investors with a track record of success in the industry.

💻 𝗙𝗶𝗻𝗱 𝗨𝘀 𝗢𝗻𝗹𝗶𝗻𝗲!
🔍 Website ⇒ https://tsdb.co/homepage
🔍 Slack ⇒ https://slack.timescale.com
🔍 GitHub ⇒ https://github.com/timescale
🔍 Twitter ⇒ https://twitter.com/timescaledb
🔍 Twitch ⇒ https://www.twitch.tv/timescaledb
🔍 LinkedIn ⇒ https://www.linkedin.com/company/timescaledb
🔍 Timescale Blog ⇒ https://tsdb.co/blog
🔍 Timescale Documentation ⇒ https://tsdb.co/docs

📚 𝗖𝗵𝗮𝗽𝘁𝗲𝗿𝘀:
⏱ 0:00 ⇒ Introduction
⏱ 2:44 ⇒ Roadmap & motivation: what you’ll learn and why it matters
⏱ 4:37 ⇒ Why use Postgres for monitoring and analytics?
⏱ 7:18 ⇒ Demo scenario, real-world dataset, and schema
⏱ 13:32 ⇒ Function #1: Using Window Functions
⏱ 19:20 ⇒ Function #2: Using Window Functions & LAG()
⏱ 25:51 ⇒ Function #3: Using percentile_cont()
⏱ 29:30 ⇒ TimescaleDB-unique functions (and quick background on TimescaleDB)
⏱ 30:36 ⇒ Function #4: Using first() or last()
⏱ 34:10 ⇒ Function #5: Using time_bucket
⏱ 39:16 ⇒ (Bonus!) Function. #6: Using time_bucket_gapfill(); locf(), interpolate()
⏱ 46:45 ⇒ Recap & Resources