5 PostgreSQL Functions for Monitoring & Analytics

#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.

🛠 𝗥𝗲𝗹𝗲𝘃𝗮𝗻𝘁 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀

🐯 𝗔𝗯𝗼𝘂𝘁 𝗧𝗶𝗺𝗲𝘀𝗰𝗮𝗹𝗲
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.

💻 𝗙𝗶𝗻𝗱 𝗨𝘀 𝗢𝗻𝗹𝗶𝗻𝗲!

📚 𝗖𝗵𝗮𝗽𝘁𝗲𝗿𝘀:
⏱ 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

[blessiousphiri]

Thanks so much the scripting and queries were so helpful

[MrAtomUniverse]

Could you guys do one on data compression, cold hot storage, anything that helps with saving of disk spaces