TimescaleDB Course – PostgreSQL for Time-Series Data

[00:00] two databases on the same laptop, each [00:02] with 10 million request records. We're [00:04] asking both the same question. How many [00:07] requests arrived each hour and what was [00:10] their average response time? On the [00:13] left, PostgreSQL calculates the answer [00:15] from the individual requests. On the [00:19] right, time scale DB reads an hourly [00:21] summary we've prepared ahead of time. [00:24] So, let's run them. We can see the raw [00:26] query took 1.6 6 seconds or 1,682 [00:32] milliseconds. And the right query only [00:34] took 10 milliseconds. And if we check, [00:37] they both return the same answers. The [00:40] difference is the amount of work. One [00:43] query reads individual events. The other [00:46] reads results we already calculated. [00:49] Building that summary took time, too. [00:51] And ordinary PostgreSQL can store [00:53] summaries. What time scale DB adds is a [00:56] way to keep timebased summaries updated [00:59] without rebuilding the entire history [01:01] every time. That's one of the things [01:04] we're going to build in this course. [01:06] Hello, I'm Bo KS and in this course I'll [01:08] teach you how to use time scale DB to [01:11] work with time series data in [01:13] PostgreSQL. We'll look at how to [01:15] organize growing tables, reduce storage, [01:18] and keep dashboard queries responsive. [01:21] We'll also cover the constraints and [01:23] settings you need to understand before [01:26] using this in production. Tiger Data [01:29] provided a grant to make this course [01:30] possible. They're the company behind [01:33] Timecale DB. They have a fully managed [01:36] cloud platform called Tigercloud. It's [01:39] essentially enterprisegrade PostgreSQL [01:42] with Timecale DB built in plus [01:45] cloudnative features like automatic data [01:48] tiering to cheap S3 storage. one-click [01:51] database forking and a visual dashboard [01:54] built specifically for time series [01:56] operations. We're going to use [01:58] Tigercloud later in the course for the [02:00] second project. But for the first part [02:03] of this course, we're going to start [02:05] completely local inside Docker from [02:08] scratch. I want you to understand [02:11] exactly how this engine works under the [02:14] hood before we move to the cloud. You [02:17] can keep using familiar Postgregql tools [02:20] and SQL. Some table designs and queries [02:23] need changes and we'll work through [02:25] those as we go. So what is time series [02:27] data? Time series data is data you [02:30] analyze in relation to time. You might [02:32] ask what happened in the last hour, [02:34] compare this with last week or calculate [02:37] response times throughout the day. That [02:40] includes HTTP requests, financial [02:42] prices, sensor readings, application [02:44] logs, and the model and tool calls made [02:47] to an AI agent. These workloads often [02:51] add new records continuously. Much of [02:54] the data stays unchanged and the [02:56] questions usually concern a particular [02:59] time range. PostgreSQL can handle large [03:02] data sets, but as an event table grows, [03:05] a few costs become more noticeable. [03:08] Large active indexes can create more [03:11] disk work. Deleting old rows creates [03:14] cleanup work. And dashboards can [03:16] repeatedly scan the same historical [03:18] events to calculate the same totals. [03:21] PostgreSQL already gives us tools for [03:23] some of this including partitioning. [03:26] Time scale DB adds automatic chunk [03:29] management, column oriented storage, and [03:32] continuous aggregates. We'll see where [03:35] each helps and what it costs to [03:37] maintain. In that first demo, we focused [03:39] on continuous aggregates, storing hourly [03:43] results so the dashboard doesn't [03:45] calculate them from scratch. The initial [03:47] calculation happen before we start the [03:49] timer. Later, we'll look at refreshing [03:52] those summaries when new data arrives or [03:54] other records change. We'll also examine [03:56] storage and query plans so we can [03:59] explain the results. The runtime results [04:01] you saw are the results from this laptop [04:03] and this data set. They're not a promise [04:06] for every query. Your schema, hardware, [04:09] filters, and data distribution all [04:11] matter. We'll measure as we go, and you [04:14] can repeat the examples on your own [04:16] machine. So, we'll apply this to two [04:19] projects. The first is is an AI agent [04:22] flight recorder. It records model and [04:24] tool calls with their duration, cost, [04:26] and outcome. We'll use it to answer [04:28] questions like which agent is spinning [04:30] the most, which tool has the slowest [04:33] calls, when did the error rate change, [04:36] and when something goes wrong, we can [04:37] inspect the recorded steps in a [04:39] particular run. This project runs [04:42] locally in Docker and a a simulator [04:44] supplies data without requiring a paid [04:47] model API. The second project is EV [04:50] charger fleet telemetry. We'll model [04:53] 2,000 chargers, each reporting every 10 [04:56] seconds with one row per report. That's [04:59] 200 rows per second and about 17 million [05:01] rows per day. We'll use Tigercloud for [05:04] this project to explore managed [05:06] operations and moving older data into [05:09] into object storage. That event rate [05:12] doesn't require cloud hosting it by [05:14] itself. The point is to see how the [05:16] operational and storage choices work. [05:19] Chapters are in the description if you [05:20] want to jump to a particular topic. [05:22] Let's start with the PostgresQL concepts [05:24] we'll need. So the single most important [05:26] thing to understand about time scale DB [05:28] is that it's just Postgress. It's not [05:32] it's it's not a fork of PostgresQL. It's [05:34] not a read what write. It's just an [05:37] extension. The same mechanism that gives [05:40] you postJIS for geospatial data or PG [05:44] vector for embeddings. So that means [05:47] that you already everything you know [05:49] about Postgress still applies and [05:52] everything in your stack still works. So [05:55] let me demonstrate. I have Postgress [05:59] running in the terminal here and I'm [06:02] going to run this command that will show [06:05] that uh we're seeing that time scale DB [06:08] is sitting in the same extension list [06:10] next to PostgresQL [06:13] which ships with every Postgress install [06:16] on Earth. So it's super easy to install [06:19] just create extension if not exist time [06:21] scale DB. I already have it existing on [06:24] this machine, but this one line is the [06:27] entire installation. Once the binary is [06:29] on the machine uh per database, that's [06:33] the part people miss. If you create a [06:35] new database, you run that again. The [06:38] practical consequence is that your [06:40] Django or Rails or Prisma migrations [06:43] work. Your connection pooler works, PG [06:45] dump works, your monitoring works, your [06:47] team doesn't have to learn a new query [06:49] language because there isn't one. It's [06:51] just SQL. Okay, we're going to use some [06:54] example data. So, I just want to talk [06:56] about what's in the example data data so [06:58] you can understand. This matters more [07:00] than it sounds like it does. Every query [07:02] in this course runs against one table. [07:05] And if you don't know what's in it, the [07:07] SQL is just noise. So, here's the [07:09] scenario. Imagine you run a service that [07:12] sits in front of a lot of websites like [07:14] a CDN, a hosting platform, an API [07:16] gateway. Every time somebody's browser [07:18] asks for a page, that request passes [07:21] through your service and you write down [07:23] a line about it. Who asked, what they [07:26] asked for, how long it took, whether it [07:27] worked. That's it. That's the data. Just [07:29] one row per per HTTP request. Millions [07:33] of them. If you've ever looked at a web [07:35] server log file, this is that in a data [07:38] center table instead of a text file. I [07:40] mean, a data database table instead of a [07:43] text file. So, let me show you the shape [07:45] of it. So here's the table and I did [07:49] just slashd requests. Uh slashd is a [07:54] postgrql command not SQL. It's a [07:56] shortcut the postsql client gives you [08:00] and it means describe this table. You [08:02] get every type column and its type. So [08:06] there's a lot of columns here. So let me [08:11] just group them because they fall into [08:13] five buckets. [08:15] Uh the time is when it happened, the [08:18] time stamp of the request and this is [08:20] the most important column in the whole [08:21] course. We're going to do a lot of [08:23] sorts, filters and groups by this [08:24] column. And then we have some about who [08:27] asks for what like the server ID, the [08:30] URL domain, session ID, and then we have [08:34] stuff about what happened, the status. [08:35] This is the HTTP status code. Then how [08:38] slow it was. It's all about the duration [08:40] here. and then everything else like [08:42] where the request came from, what [08:44] browser, what operating system, how many [08:46] bytes moved. So this is pretty abstract. [08:49] Let me show you one actual row. So first [08:52] I'll do slash x on that's going to flip [08:58] SQL into expanded mode which prints one [09:01] column per line instead of one row per [09:04] line for a table this wide. That's the [09:07] difference between readable and [09:09] unreadable. And then we'll do select [09:11] everything from request limit one. Let [09:14] me just zoom out so we can see [09:16] everything on one screen here. [09:20] So you can see we have the time, URL, [09:23] domain, status, duration, total. And [09:26] those are going to be some of the most [09:27] important ones. [09:29] So there's one request. Somebody in some [09:32] city asked for some page and it returned [09:34] a status and it took some numbers some [09:35] number of milliseconds. Every row is [09:37] that there are millions of them. So, let [09:39] me just do /x off to turn off extended [09:42] display. And then I'm going to do a [09:45] query that's going to tell us how many [09:46] rows and what time period do they cover. [09:50] So, this is the number of rows and [09:52] here's the time period here. So, that's [09:54] your data set and the the newest time [09:58] stamp. We can see the data ends at [10:00] roughly the moment um uh we loaded the [10:04] data. that matters for the next few [10:06] queries because when I say the last 24 [10:09] hours, I mean the last 24 hours of this [10:11] data, which is the day leading up to [10:14] when we ran the generator. Now, let's [10:17] start going over SQL. I'm going to talk [10:20] to you about the 20% of SQL you need, [10:22] which is basically all you'll need for [10:24] this course. I'm going to show you four [10:25] things. If all four are familiar, part [10:28] one is a refresher and you can move [10:29] fast. If any are new, this is the floor. [10:32] Basically everything later in the course [10:34] is built out of these. So here's some [10:37] basic SQL code that we're going to cover [10:39] and I'm going to be running it on [10:41] PostgreSQL right over here. Every query [10:44] in this course starts by narrowing to a [10:46] time range. So let's get that out of the [10:48] way first. So I'm going to select max [10:51] time from request. This is the max time [10:54] and the time range. I'm going to save [10:57] that time, that value, and reuse it [11:00] because I want every query in this [11:02] section to point at the busy end of the [11:04] data rather than at wall clock time. So, [11:09] I'm going to run this command right [11:10] here. [11:12] Select max time and we can see right [11:15] here slashg set at the end. This is a [11:19] postql trick. It takes the result of [11:22] that query and stores it in a variable [11:24] called newest. [11:26] So I can write colon newest in later [11:30] queries instead of typing a timestamp. [11:33] So here's the filter we're going to use. [11:38] So first select count. We're going to [11:41] count the rows where the [11:44] time is at or after 24 hours before the [11:47] nearest row. So we're going to from the [11:50] request where the time is at or after [11:54] and we have interval 24 hours. So 24 [11:59] hours after the newest row. The thing to [12:02] notice here is that postgrql has real [12:06] date arithmetic. So this interval 24 [12:10] hours or you can look at the colorful [12:12] one right over here. This is a genuine [12:15] value with a type and you can subtract [12:18] it from a timestamp [12:20] and get a time stamp like interval. You [12:22] could use interval 7 days, interval 15 [12:24] minutes, interval 1 month. These all [12:26] work. You'll see this constantly in [12:29] every part of this course. So I'm just [12:31] going to run this command and we can see [12:34] how many how many uh how many rows were [12:39] within the last 24 hours. Now let's talk [12:43] about aggregation and grouping. So [12:45] basically I'm going to be using this [12:47] code here. I'm going to copy this and [12:48] paste it right over in our post P post [12:51] SQL PostgresQL [12:53] there. There's the full thing here. So [12:56] let's read this one clause at a time [12:59] because the order Postgress does the [13:02] work is not the order you read it. So [13:05] here we have from requests. [13:08] We're going to start with this table, [13:09] the request table. And then we have the [13:12] where where then we have basically this [13:15] means to throw everything outside, throw [13:19] away everything outside the last 24 [13:21] hours. And then we're going to group by [13:24] status, [13:26] which is pretty important. Take the [13:27] surviving rows and sort them into piles. [13:30] One pile per distinct status code. Like [13:33] all the 200s in one pile, all the 404s [13:35] in another. Then we have up here we're [13:40] going to get the status and we're going [13:41] to get the count. So the count with the [13:45] asterric and the average duration total [13:48] they run once per pile not once per row. [13:52] That's the mental shift. So this count [13:56] everything is how many rows in this pile [13:58] and then the average duration total is [14:00] the average duration of the rows in this [14:03] pile. Then order by request de or [14:06] descending sorts output biggest pile [14:09] first [14:11] and this as requests up here just [14:14] renames the column to the out to so the [14:16] output is readable that's all as ever [14:19] does okay I will submit this command [14:23] here and we can see what we get here so [14:26] mostly 200s [14:29] so we have the 200 we have all these [14:32] different status codes here and we can [14:34] see it's you can see it's mostly 200s [14:37] like I said um that's what you want. [14:39] There are some 404s [14:42] and there's some 500s. Those are the [14:45] ones that mean something is broken and [14:47] finding them fast is basically the plot [14:50] of this entire course. [14:52] Now we're going to talk about this [14:54] filter section here. So filter is for [14:57] counting different things at once. I [15:00] have the code already over here in the [15:02] in the PostgreSQL. [15:05] So here's a here's a real question. [15:07] What's my error rate? To answer it, you [15:10] need two numbers from the same row. The [15:12] total and how many of them failed? [15:15] You could run two queries, but don't. [15:18] Postcrest has filter. So we have this [15:21] filter wear. So this attaches a [15:25] condition conditions to a single [15:28] aggregate. The wear at the bottom which [15:32] is right here applies to the entire [15:36] query. But this filter up here only [15:40] applies to one count it's attached to. [15:45] So count filter where status is more [15:48] than or equal to 500 means count the [15:51] rows but only the ones that that were [15:54] server errors. So four different counts [15:58] one pass over the data. If you've been [16:00] writing some case when then one l0 end [16:04] and a lot of people have myself included [16:07] this is the same thing faster to read [16:10] and faster to run. So if we run that we [16:14] can see server errors not found and okay [16:16] here we can also easily turn it into a [16:20] percentage and get an error rate. So [16:23] here we're um basically running a [16:26] calculation to turn it into a percentage [16:29] and calling it air rate percentage. [16:32] And then we see we have a 25% air rate [16:36] percentage. So here we have this last [16:39] thing we're talking about is [16:40] percentiles. [16:42] So here what you can see we're using [16:43] percentile count and percentile count [16:47] 0.5 n5.99 [16:50] and I'll just submit this. So we can [16:54] compare the average milliseconds to the [16:58] average P99, P95 and percent. These are [17:01] percentiles percentile 50. The P99 [17:04] should be several times larger [17:08] um which it is. So look at the gap [17:10] between the average and P99. Same [17:13] request, wildly different story. The [17:15] average is the number that makes you [17:16] feel good, but the P99 is the number [17:19] your angriest users is living in. The [17:23] syntax is admittedly odd where it says [17:26] percentile count 0.95. [17:29] This says which percentile you want. [17:32] Then within group, then we're going to [17:35] order by duration total. This says which [17:38] column to compute it over. Postgress has [17:41] to put the values in order to find the [17:42] 95% mark. That's what you're telling it [17:45] to sort by. And remember this, it's [17:48] exact, which means it sorts every single [17:51] value to find that mark. On a few [17:53] thousand rows, fine. But on a 100 [17:56] million, that's a problem. And in the [17:59] next part, we replace it with something [18:01] that gives you a nearly identical answer [18:03] without the sort. [18:06] So another thing is types. There are [18:08] three of them that matter here. The type [18:11] of timestamp TZ. Always use this. Never [18:14] plain time stamp. The TC TZ is time [18:17] zone. So time stamp TZ stores an [18:20] absolute moment and converts it to the [18:22] reader time zone on the way out. Plain's [18:24] time stamp stores a wall clock reading [18:27] with no idea where it was taken. Our [18:30] time column is timestamp TZ and every [18:33] hyper table in the next part is [18:36] partitioned on a column like it. The [18:39] JSON B which is binary JSON uh this can [18:43] be indexed for the longtail attributes [18:46] that don't deserve their own column and [18:49] UU ID. Uh it's a 128 bit identifier. The [18:54] long hyphenated strings and request ID [18:57] are the are UU ID. It looks like a [19:00] boring choice right now, but in the [19:02] indexing section coming up, it's going [19:05] to be pretty important. And there's uh [19:08] important, I guess we could say, gotcha [19:11] uh waiting there that we'll talk about [19:12] later. So these things that we just [19:15] covered are basically the 20% of SQL [19:19] that you'll need. Everything is kind of [19:21] built on these interval, group by, [19:24] filter, percentile count. We're going to [19:28] need to know all these things as the [19:30] course progresses. Okay, I want to tell [19:32] you about another command called [19:35] explain. So if you take one skill from [19:37] this section, make it this one. Almost [19:40] every claim time scale DB makes is [19:42] verifiable with explain. And almost [19:46] every performance mystery is solved by [19:48] reading it. So let's actually learn to [19:50] read it, not just glance at it. So this [19:53] is the the the plain version which [19:56] doesn't run the query. just so it shows [19:57] the plan. So I'm going to put explain [20:00] and then select count from request where [20:02] and then just basically where the time [20:04] is in the last day here from the the end [20:07] of the results. So we can now see the [20:11] query plan. So this is the plan of what [20:14] it's going to do. So it hasn't actually [20:16] run the command. It's just what the what [20:18] the command is going to do. What the [20:20] select command is going to do. So So [20:23] first it's going to filter. Then it's [20:27] going to do the parallel seek scan on [20:30] request partial aggregate and gather. [20:33] Basically, it's going to start from the [20:35] bottom and go up. And that's what it's [20:36] going to do here. And there are [20:40] two numbers per line. Uh we have you can [20:44] see this line here cost. Cost is an [20:47] arbitrary unit. It's not milliseconds. [20:50] It's not anything physical. It's only [20:52] meaningful for comparing two plans of [20:54] the same query. And then rows here is [20:58] the planner's estimate, which is where [21:01] the interesting failure lives. Now I'm [21:04] going to run a similar command, but [21:06] we're going to do explain analyze [21:09] buffers. So analyze actually executes [21:12] the query and reports what really [21:15] happened. Buffers reports how much data [21:19] it had to touch. That second one is the [21:22] one nobody uses and really everyone [21:25] should use it. [21:27] So you can see it hit four. The shared [21:30] hit equals four. And we can see that the [21:33] true information workers plan workers [21:35] launched. [21:37] It's basically reporting what actually [21:39] happened when it run ran the command. So [21:43] when it says sik scan here that means a [21:47] a sequential scan. So let me talk about [21:50] a sequential scan versus an index scan. [21:54] A se a sequential scan reads the entire [21:57] table. That's not automatically bad. If [22:01] you're aggregating most of the table, [22:02] it's the right choice and it's faster [22:04] than an index scan would be. It's bad [22:07] when you wanted a handful of rows and [22:08] got a full read. [22:11] And here we can see for every line you [22:14] can see basically we it's showing here [22:18] the actual time and the actual rows. So [22:21] if the plan get a 100 rows and it got a [22:24] million every decision downstream is [22:26] built on a lie and you'll get a bad [22:28] plan. That usually means stale [22:30] statistics. So you should run analyze on [22:33] the table. And then here's buffers. We [22:36] see that it got shared hit. This means [22:39] the data was already in memory. Shared [22:42] read means it came off the disk. Disk [22:46] reads are the thing that actually cost [22:48] you wall clock time. And this is the [22:50] number we'll watch drop dramatically [22:52] when we get to reordering and column [22:55] store. And then you sometimes you want [22:57] to look at the planning time versus [22:59] execution time. [23:01] If they disagree wildly, the planner is [23:03] confused about something. [23:05] So let me show you something else. [23:07] basically the pathology because it's [23:09] more instructive than the happy plan. So [23:12] here we're selecting everything from [23:14] request where URL path equals SLC [23:17] checkout. So there's no index on URL [23:21] path. So we have a sequential scan and a [23:26] huge uh shared red count and the [23:29] execution time is dominated by pulling [23:32] pages off discs that we then pull away. [23:36] So I'm going to just compare that to [23:40] another command here. [23:42] We are going to create we're going to [23:47] create an index on the requests. I mean [23:51] an index on the URL path and then we're [23:54] going to run the same command again [23:56] where the URL path equals/checkout. [23:59] And now we can see we have an index [24:02] scan. So if we see the the shared if we [24:07] go up here and see the shared red we [24:10] have 106,533 [24:13] in the buffers here we have 3001. So you [24:18] see that's a very that's basically an [24:21] order of magnitude lower. So that's what [24:24] we want. We want to touch fewer pages [24:27] and it'll make things run a lot quicker. [24:30] But now I'm going to run the drop index [24:32] command because I I want the slow [24:35] version back for later. Now one here's a [24:38] one habit worth building. I'm going to [24:40] run slashtiming [24:42] on. This will turn on timing. Now every [24:45] request reports its wall time. It's the [24:49] uh psqlrc file in the repo along with a [24:53] couple other settings I use. I mean it's [24:56] in in that file if if you check the repo [24:59] on GitHub. This is the section that [25:01] explains most of time scale DB's design. [25:05] So stay with me even if it feels [25:07] low-level. Postgrql does not read rows [25:11] from disk. It reads pages. And a page is [25:14] 8 kilobytes. Always every read, every [25:17] write, every index lookup in units of 8 [25:20] kilobytes. [25:23] So if your rows are say a 100 bytes, you [25:26] get somewhere around 50 to 100 rows in a [25:29] page depending on overhead. And when you [25:32] ask for one row, the database reads all [25:35] 8 kilobytes and hands you the one you [25:37] wanted. That has a consequence that [25:40] becomes the entire argument for the [25:42] column store later. If the rows you need [25:46] are spread across many pages, you pay [25:48] for every page. 10 rows in one page is [25:52] one read. 10 rows in 10 different pages [25:56] is 10 reads. Same 10 rows. Hold on to [25:59] that. It comes back in about uh in just [26:02] later in this course and it's the key to [26:04] two different features. [26:06] Now the second half of this which is [26:09] about rights. Postgress uses MVCC [26:13] multi- version concurrency control. The [26:17] short version, when you update a row, [26:20] Postgress does not overwrite it. It [26:22] writes a new version of the row and [26:25] marks the old one as dead. When you [26:28] delete a row, it doesn't remove [26:30] anything. It just marks the row dead. [26:34] Why? So that other transactions already [26:37] reading that row don't have the ground [26:39] move under them. It's a genuinely good [26:41] design and it's why Postgress readers [26:45] never block writers. But it means dead [26:48] rows accumulate. They still occupy [26:51] pages. Your table gets bigger even as [26:54] you delete from it. And then the command [26:58] vacuum has to come along and clean up. [27:02] So let's make that concrete [27:05] because it's easier to understand once [27:07] you see it. So, I'm just going to create [27:10] a new table called dead row demo [27:13] and just uh generate some items for the [27:17] row. And let's look at the size. It's 72 [27:22] megabytes because so we generated it and [27:25] then we uh figure out the size [27:29] of the the table. Okay. So now I'm going [27:33] to delete a lot from the table and then [27:38] we'll get the size again. You can see [27:40] the size is the same. We deleted 80% of [27:45] the rows and the table did not get [27:47] smaller by a single bite. [27:51] So now I'm going to run another command. [27:52] Select inlive [27:54] tup and in dead tup [27:58] from the table from the dead row demo. [28:01] Now I'm going to run vacuum full and we [28:04] can see now the size has gone down. So [28:09] vacuum full took an exclusive lock on [28:12] the table. Uh nothing could read or [28:14] write it while that ran. On a 500,000 [28:18] row toy table that's instant. On a 200 [28:21] million row production table it can take [28:24] a long time. So here's a trap that [28:28] catches everyone. You've got a huge [28:30] events table. You decide to keep 90 days [28:33] of days of data. So you write a nightly [28:36] cron job like delete from events where [28:38] time uh in the [28:41] last 90 days. Basically that job creates [28:45] millions of dead rows every night. [28:48] Vacuum has to chase them. Your table [28:50] doesn't shrink. Your indexes get bloated [28:53] and fragmented. The delete itself takes [28:55] longer every night as the table grows. [28:58] and it competes for I/IO with your [29:01] actual traffic. [29:03] The time scale DB answer to this and I'm [29:06] pre previewing it because it's such a [29:09] clean win is that dropping old data [29:12] becomes deleting a file. Not deleting [29:15] rows, removing an entire chunk of the [29:17] table as a unit. Milliseconds, no dead [29:20] rows, no vacuum pressure, no bloat. [29:22] That's the retention section that we're [29:24] going to talk about later. [29:27] Now an object an object an objection [29:31] Postgress already has partitioning built [29:34] in. Since version 10, you can split a [29:36] table into pieces by range. So why do we [29:39] need an extension? Well, let's build it [29:42] by hand and find out. This is the most [29:45] useful five minutes here because in this [29:48] section because once you've done it [29:50] manually, you understand exactly what [29:52] time scale DB is automating. [29:56] Okay, I'm going to create this table [29:57] events native. And the table can't hold [30:01] anything yet. It's a router with no [30:05] destinations. [30:06] And we have these three fields, time, [30:08] device, and value. But if we try to [30:12] insert into something, it it's not going [30:15] to work. We can see that there is no [30:18] partition of relation needed found for [30:20] now. So I have to create the partition [30:25] first. So this is how I'll create the [30:28] partition. [30:30] I'm going to create a partition. I'll do [30:33] create table and then this is going to [30:35] be the name partition of events native [30:38] for these values and then a partition of [30:41] events native for these other values. [30:46] Okay, we've created those tables. Now [30:48] the insert should work. Yep, we insert [30:52] it into the table. And this genuinely [30:55] does buy you something real. The planner [30:58] can skip partitions entirely. [31:01] So I'm going to run explain and check [31:04] out the plan. So we can see the plan [31:07] that it's that it has here. One [31:11] partition in the plan. And you can see [31:17] we got this partition here. Um, the [31:20] August table isn't even considered the [31:22] other partition. That's partition [31:25] pruning and it's the same core idea time [31:28] scale DB uses. So, what's missing? Well, [31:32] there's four things that native [31:34] partitioning doesn't do. One, you have [31:37] to create every partition yourself [31:39] before the data arrives. Forget one and [31:42] your inserts start failing in production [31:44] at midnight on the first of the month. [31:46] People solve this with cron jobs or [31:48] pgartman and it's just one more thing [31:52] that can break. Two, no automatic [31:55] sizing. You pick the boundaries. Get [31:57] them wrong and you either have 4,000 [31:59] tiny partitions which makes planning [32:02] slow because the planner considers all [32:04] of them or three enormous ones which [32:07] defeats the purpose. Three, no life [32:10] cycle management. There's no built-in [32:13] compressed partitions older than a week [32:15] or drop partitions older than a year. [32:17] You write that yourself. Four, and this [32:19] is the big one, no time series features. [32:23] Partitioning is storage organization. It [32:26] doesn't give you the incrementally [32:28] updating aggregates or column nar [32:31] expression or compression or gap filling [32:35] or percentile sketches. Those are the [32:38] features that actually make analytics [32:39] fast and they're the bulk of the next [32:42] section. So a hypert is a partitioned [32:47] table. Time scale DB does not replace [32:49] that machinery. It builds on it. What [32:52] you're paying what you're paying the [32:55] extension for is the automation and the [32:58] analytics layer on top. Okay, that table [33:01] is just an uh example for now. So we're [33:04] going to drop that table. The last piece [33:07] of foundation and it sets up the single [33:10] best feature in uh time scale DB. Your [33:14] dashboard query is slow. So the obvious [33:18] fix is don't compute every time. Compute [33:20] it once, store the answer, read the [33:22] stored answer. Everybody's first [33:24] instinct and it's a good instinct. [33:26] Postgress gives you materialized views [33:28] for exactly this. So here's how I'd set [33:32] up a materialized view. So the request [33:36] hourly MV materialized view and then we [33:40] have the different fields that we're [33:43] adding to it. So uh while that runs [33:46] notice what it's doing. It's reading the [33:49] entire table to build the summary every [33:51] row from the beginning of time. And then [33:54] we're we'll query it which is instant. [33:57] Obviously it's a small precomputed [34:00] table. But the problem, let's say we [34:04] want to refresh the data. We'll do [34:07] refresh materialized view. And this can [34:11] take some time. That refresh recmputed [34:16] everything from scratch, all of history [34:18] to pick up the last 5 minutes or so of [34:21] new data. So materialized views have two [34:25] problems. Basically, two deal deal [34:28] breakers. The cost of refreshing grows [34:31] forever because it's proportional to [34:33] your total data, not to what changed. [34:37] And the plain refresh takes a lock that [34:41] blocks reads. There is a refresh [34:44] concurrently, which doesn't block, but [34:46] it needs a unique index and it's even [34:49] slower. So, people go one level down and [34:53] hand roll basically make it themselves. [34:56] a real summary table updated by a [34:58] trigger on insert. [35:00] That's actually worse, but it'll be like [35:02] this. We create this request summary [35:04] table. We create this function that's [35:07] going to update it. Uh, it looks [35:09] reasonable, but let's think about what [35:12] happens under load. Every insert into [35:15] requests has to update a row in the [35:18] request summary. And for any given hour [35:21] and domain, that's the same row. every [35:24] single request for example.com this hour [35:26] contends for one row row level locks [35:31] concurrent inserts queue up behind each [35:33] other and the there here's the thing [35:36] about queuing it doesn't degrade [35:38] gracefully it's fine it's fine it's fine [35:40] and then you cross a threshold and it [35:42] collapses because each waiting [35:45] transaction is holding resources while [35:47] it waits with each new query the waiting [35:51] time increases until the database [35:53] becomes comes overwhelmed by a mess of [35:55] blocked queries waiting to run. That's [35:58] the failure mode. Not slow, but stuck. [36:02] And even if you solve the contention, [36:06] you've now got a trigger to maintain [36:08] backfield logic for historical data, [36:10] correction logic for updates and deletes [36:12] on the source table, and a whole new [36:14] class of bug where your summary and your [36:16] raw data disagree. [36:19] But there's another option. Time scale [36:22] DB's continuous aggregates solve exactly [36:25] this. They keep a materialized summary, [36:28] but the refresh is incremental. It [36:31] tracks which time buckets actually [36:33] changed and recomputes only those. So no [36:36] triggers, no lock contention on write, [36:38] no full recomputee, and it handles [36:40] updates and deletes to the source. We'll [36:42] be talking about that more uh next. and [36:46] it's a great feature in the extension, [36:49] but you had to see why the obvious [36:51] approaches fail or the incremental thing [36:53] just sounds like a a nicer materialized [36:55] view. It's not. It's a different [36:57] mechanism. So, let's just drop all those [37:01] tables and functions that we just [37:02] created. Okay, let's go on to the next [37:05] part and talk about the problem for this [37:08] section. This section is inspired by the [37:10] book Make Analytics Fast by Tobias [37:13] Petri. Before any features, we need a [37:16] problem worth solving and data to solve [37:18] it on. We're going to use a web [37:20] analytics scenario for all of this part [37:24] because it's something everyone [37:25] understands and it has exactly the right [37:28] shape. So, picture this um and this is [37:32] the scenario that we uh use for some of [37:34] the sample code earlier also. But let's [37:37] say you work on a service that fronts a [37:40] lot of websites, a CDN, a hosting [37:42] platform, an API gateway, it doesn't [37:45] matter. Every HTTP request that passes [37:47] through gets logged. Your customers log [37:50] into a dashboard and want to see their [37:52] traffic like requests over time, [37:54] response times, error rates, which which [37:57] paths are slow, broken down by domain [38:00] and by service. It works great for a [38:02] year, then it doesn't. The dashboard [38:05] that took 200 milliseconds now takes 40 [38:07] seconds and support tickets are piling [38:10] up. Just imagine your colleague who set [38:13] everything up has vanished into thin [38:16] air. So you know somebody built this. It [38:18] worked. They left and now it's yours and [38:20] you don't know why any of the decisions [38:22] were made. So let me show you the actual [38:25] data. This is what I showed you earlier. [38:27] Just the slashd requests and it's going [38:30] to show all this data. This is the [38:32] table. It's wide on purpose, one row per [38:34] HTTP request with everything about that [38:38] request as a column. Timestamp, the [38:40] domain, the service, the server that [38:42] handled it, the path, the HTTP status, [38:43] the response duration broken into [38:45] components, geo info, user agent [38:47] details. So we can just see a few more [38:50] things here. And now I'm going to show [38:53] you the count. So count from requests [38:58] and [38:59] let's get the total relation size. So we [39:03] can see that there are [39:06] uh over 5 million rows and it's 16 [39:11] kilobytes. So that's what I've seated on [39:13] this computer. But the repo in the [39:17] description has a generator script with [39:19] flags. So you can scale this to whatever [39:21] your machine tolerates and test on [39:23] different uh different sizes of the [39:26] table. Now the dashboard query, this is [39:29] the one we're going to make fast. So [39:33] we're going to run this and we're we're [39:35] this is the one we're going to make [39:36] fast. We're going to come back to it [39:38] after every single feature. So we're [39:42] going to just run this many times and [39:44] see how it changes. Basically, we're [39:46] selecting we're getting all these [39:49] different pieces of information from our [39:52] our table and we're checking where URL [39:55] domain equals example.com and the [39:57] interval was in the last 7 days. And [40:00] then we're going to group by hour and [40:02] then order by hour here. So, we can see [40:05] all the results here and we can see this [40:09] was 2 thou uh 2341 [40:13] milliseconds. Now I'm going to run that [40:15] three times. So I'm just going to run it [40:18] right now again. And I want to explain [40:20] before why I'm doing this because this [40:23] is a good habit. Uh so you can see it's [40:26] already showing a different number and [40:31] they're possibly they're going to [40:34] possibly differ a lot. So the first run [40:38] pays for the reading cold data off disk. [40:41] the second and third, here's the third [40:43] one here, are going to find RD in [40:46] memory. That's not a trick. It's the [40:48] difference between a cold cache and a [40:51] warm one. And if you benchmark without [40:54] knowing which one you're looking at, you [40:55] will draw confident conclusions from [40:57] noise. So this third one is the the [41:01] number we're actually going to use. [41:04] This is going to be our baseline for [41:08] this query. 2,211 [41:11] milliseconds. [41:13] That's how we're if we run the query a [41:15] few times, we can get a more accurate [41:17] number. So this is our baseline, but the [41:22] wall clock time is the soft number. So [41:24] let's also grab the hard one, the one [41:26] that doesn't move between runs. So here [41:30] we're running explain with analyze [41:32] buffers [41:34] and then we are getting the hour and the [41:37] count of the requests and we're grouping [41:41] by hour. So we can see this is giving us [41:44] a lot of information here. We can see um [41:49] also the planning time the execution [41:50] time down here. [41:53] But what I want to look at is the this [41:56] buffers this shared hit which is 230 [42:01] 235,761. [42:04] So this is how many 8 kilobyte pages [42:06] this query touched. We're reading an [42:09] enormous amount of data to produce about [42:12] a 168 rows of output. And here's why [42:15] buffers beat milliseconds as a measure. [42:18] That page count barely moves between [42:20] runs. It doesn't care whether your cache [42:22] is warm, whether Docker is throttling or [42:25] whether something else on your laptop [42:27] laptop woke up. It's a direct measure of [42:29] how much work the database did. And [42:32] every optimization in this course is [42:34] fundamentally about doing less work. [42:36] Milliseconds are what your users feel. [42:39] Buffers are what you can actually reason [42:41] about. We'll track both. And when they [42:43] dis disagree, believe the buffers. And [42:46] there are three separate problems [42:47] tangled together in that number, which [42:50] is why there's no single fix. We're [42:53] reading rows we don't need. Every row [42:56] for every other domain because the rows [42:58] for hexample.com are scattered [43:01] everywhere. Hyper tables and the column [43:04] store fix that. We're reading columns we [43:06] don't need. This query touches four [43:09] columns out of dozens, but pagebased [43:11] storage drags all of them along. The [43:13] column store fixes that. And we're [43:16] recomputing an answer that cannot have [43:18] changed. Last Tuesday's hourly counts [43:21] are fixed forever and we recomputee them [43:23] on every dashboard load. Continuous [43:27] aggregates fix that. And one more thing, [43:30] this is a migration, not a rewrite. The [43:33] important note on scope before we start [43:35] because it's a real question people [43:36] have, you do not move your whole [43:39] database to time scale DB. Your users [43:42] table, your orders table, your products [43:44] table, those stay exactly as they are [43:46] normal Postgress tables. They're small. [43:49] They get updates up update updated [43:50] constantly. They need real foreign keys [43:53] and unique constraints. And they are not [43:55] time series data. Only the fire hose [43:58] tables become hypert. The requests, the [44:00] events, the metrics, the traces, [44:02] everything else is untouched. And this [44:05] is the good part, they all live in the [44:07] same database. So you can still join [44:09] them which we'll do in uh one of our [44:11] projects. All right, first feature [44:15] hypert. [44:17] So let's talk about hyper tables here. A [44:20] hypert looks like one table and is [44:22] physically many tables. Your query [44:25] requests you insert into requests. As [44:29] far as your application, your OM, your [44:31] SQL are concerned, there's only one [44:33] table called requests underneath. Time [44:36] scale DB is splitting it by time into [44:38] pieces called chunks and routing every [44:41] row to the right chunk automatically. [44:44] So why chunks? Well, there are three [44:47] payoffs and they map directly onto the [44:49] three problems from part one. Queries [44:52] skip irrelevant chunks entirely. You ask [44:56] for the last seven days, the planner [44:58] excludes every chunk outside that window [45:00] before reading a single page. Not scans [45:03] them efficiently. It doesn't even open [45:06] them. And indexes stay small. This is [45:09] the subtle one and it's the biggest deal [45:11] for write throughput. Every chunk has [45:14] its own index. So the index you're [45:17] actively writing into only covers the [45:20] current chunk, which means it fits in [45:22] memory, which means inserts don't hit [45:24] disk to maintain the index. Combat. [45:27] Compare that to one giant index covering [45:29] two years of data, which cannot fit in [45:32] memory. So every insert becomes a random [45:34] disk. Right. Also dropping old data is a [45:38] file deletion. The retention problem [45:40] from the previous part solved by [45:43] construction. You drop a chunk not rows. [45:46] So let's see the whole uh conversion. [45:50] I've got a copy of the table schema [45:51] without the hyper table applied. So we [45:54] can do this live. So this is the the [45:58] copy here. And to convert we just do [46:02] select create hypert from the request ht [46:05] request hyper table and we're going to [46:10] uh separate it by uh the range by [46:13] intervals [46:15] of one day. So that's it. That's the [46:18] feature that's how to create hypert. So [46:20] three things about that call. This by [46:23] range here is the modern dimension [46:26] builder syntax. If you read older [46:29] tutorials, you'll may see a different [46:31] method of uh creating different [46:33] dimensions, which still works, but it's [46:35] the legacy form. The column has to be [46:39] not null because every row must route [46:42] somewhere. And you'll typically do this [46:44] on an empty table, though there is a [46:46] migrate migrate data flag that you can [46:50] do, an option for converting a table [46:52] that already has rows in it. It works, [46:54] but it's slow and it locks. So plan for [46:58] maintenance time. Now let's watch what [47:00] happens as data arrives. So right now [47:04] there's nothing here. It's an empty [47:06] table. No chunks. Unlike native [47:08] partitioning, we did not have to create [47:10] anything in advance. So now we're going [47:13] to insert into our new hypert uh [47:16] information from our original table [47:19] where time is in basically the last 3 [47:22] days. So we inserted a bunch of records. [47:25] It took us 21 seconds. And now we are [47:28] going to show the chunks here. And now [47:32] we can see that there are four different [47:34] chunks. They appeared on demand. One per [47:36] day created as as rows arrived. That's [47:39] the automation you're paying for. No [47:41] crown jobs, no PG part man, no pager [47:44] alerts at midnight on the first of the [47:45] month. So let me show you their real [47:48] tables. We're actually going to just do [47:50] a query from them. And so this is a [47:54] query right from these chunk tables. And [47:56] we can see we're actually able to query. [48:00] They're real tables with names and they [48:03] have a schema. You can query one [48:05] directly if you ever need to, but you [48:07] normally never need to. And let me show [48:10] you the pruning. I'm going to do an [48:12] explain from request ht [48:15] for the last day. And you can see here [48:20] that we are only accessing just one of [48:24] the chunks. So the others aren't in the [48:27] plan at all. And now I'm going to show [48:30] the failure case. So I'm going to [48:32] explain select count from request ht [48:34] where your domain equals example.com. [48:37] And you can see here that we're actually [48:40] accessing accessing from every chunk. [48:43] So no time filter. So no pruning is [48:46] possible. it has to look everywhere. So [48:49] this brings us to the next point. Always [48:51] filter by time. This is the number one [48:54] rule of using time scale DB and it's the [48:57] number one reason people say I installed [48:59] it and it wasn't faster. If your query [49:02] doesn't filter by the time column, you [49:04] get none of the benefit. Chunk exclusion [49:08] is the found is the foundation [49:10] everything else sits on. when you're [49:12] writing queries, when you're writing OM [49:15] code, when you're designing an API [49:17] endpoint, the time range is not [49:20] optional. It's the price of admission. [49:23] So, write it down or I mean or remember [49:26] this rule basically because we're going [49:28] to be coming back to this. So, how big [49:31] should a chunk be? This is the one real [49:34] decision you have to make and both [49:35] directions are wrong in different ways. [49:37] So basically they can be the chunks can [49:39] be too small and then you get thousands [49:41] of them. Every query's planning time [49:43] goes up because the planner has to [49:45] consider and exclude each one. Metadata [49:48] overhead grows and you burn a [49:50] connections worth of memory on planning [49:52] for a query that returns four rows or [49:55] your chunks can be too large and then [49:58] you lose the two things chunks are for. [50:00] pruning get pruning gets coarse asking [50:02] for one hour reads a month and the [50:04] active chunks index stops fitting in [50:06] memory which kills your insert [50:09] throughput. So there's two guidelines [50:12] you should be thinking about. One, keep [50:15] it under about a thousand chunks per [50:17] hyper table. That's a soft ceiling for [50:20] planning overhead. [50:22] uh if you're projecting past it, widen [50:25] the interval or add a retention policy [50:27] so old chunks get dropped and the count [50:31] stays bounded. Also, keep the active [50:34] chunks indexes to under about 25% of [50:37] your RAM. This is the important one. [50:40] You're only ever inserting inserting [50:42] into the most recent chunk or two, and [50:45] those indexes need to live in memory. [50:47] The rest can be on desk because you [50:50] touch them rarely. So let me do the [50:52] arithmetic because it depends is useless [50:55] without a worked example. So we're going [50:58] to run this command here and we're going [51:01] to get the rows uh the total rows for [51:05] the last three days and also the bytes [51:07] per row which is 406 here. So we're [51:11] we're so suppose we're taking a 100 [51:14] million requests a day which is a [51:17] reasonable midsize service. That's [51:19] about,00 [51:20] a second. [51:22] 100 or two 400 bytes a row is 40 [51:27] gigabytes of table per day. Say indexes [51:31] add 30% and now we're at 52 gigabytes [51:34] per day total. If I have 32 gigabytes of [51:39] RAM, 25% of that is 8 gigabytes for the [51:41] active chunks indexes. My daily index [51:44] volume is three gigabytes. So a 1-day [51:45] chunk uses three of my 8 g gigabytes [51:48] budget. It's comfortable, but a oneweek [51:50] chunk would be 21 g gigabytes of index. [51:53] Uh way over. So daily chunks. Then check [51:58] the count daily chunk chunks and I keep [52:00] 90 days 90 chunks well under a thousand. [52:03] If I kept five years with no retention, [52:05] that'd be 800 chunks and I'd have to [52:07] reconsider either weekly chunks or a [52:10] retention policy. And the retention [52:12] policy is the the better answer. [52:15] So, if you don't want to do arithmetic, [52:17] here's the cheat sheet. High volume, [52:19] tens of millions of rows a day or more, [52:22] one day. Moderate, millions a day, one [52:24] week. Low volume, thousands a day, one [52:26] month. And when genuinely unsure start [52:28] with a day, it's the most common answer [52:31] in practice. And it's what both of our [52:33] projects that we're going to do in this [52:35] course use. So, you can change it with a [52:38] command like this, set chunk inter time [52:40] interval, and you can set it to uh seven [52:42] days here. So an important caveat that [52:46] only affects new chunks. Existing chunks [52:49] keep the interval they were created with [52:51] forever. There's no rewrite. So you can [52:54] end up with a hyper table that has daily [52:56] chunks for its first year and weekly [52:59] after that. And that's fine. It works. [53:01] But it means you should think about this [53:03] a little upfront rather than planning to [53:06] fix it later. [53:08] And you can partition by more than time. [53:12] This is another capability and I'm [53:15] mainly mentioning it so you know it [53:17] exists and when not to use it. You can [53:20] add a second partitioning dimension, [53:22] usually a tenant or device identifier. [53:24] So each chunk is split further by hash, [53:28] but I would say that you probably don't [53:31] need this. It was much more important in [53:33] the old multi-node deployments where it [53:36] distributed data across servers on a [53:38] single node with its main use is [53:40] paralyzing IO. across multiple physical [53:43] discs or splitting an enormous pre-chunk [53:46] row count. And it has a real cost. It [53:49] multiplies your chunk count by the [53:51] number of hash partitions. Four hash [53:53] partitions turns 90 chunks into 360. Now [53:57] you're closer to that thousand chunk [53:58] ceiling for a benefit you may not be [54:00] getting. [54:02] The column stores segment by setting [54:05] which is coming up later in the course [54:08] gets you most of what people want uh [54:11] from space partitioning which is data [54:14] grouped by tenant. So query skip other [54:16] tenants without the chunk [54:18] multiplication. So note it exists and [54:21] reach for segment by first. Okay, let's [54:24] just drop that copy because from here on [54:27] out we'll work on the real request table [54:29] which I will convert now. So we're going [54:33] to create a hypert from the requests [54:35] table and actually I already did it. [54:37] It's so it's already a hyper table but [54:40] it can take a while the first time you [54:42] do it. And let's show the chunks. We [54:45] have four chunks. And before we measure [54:49] anything, let's talk about one thing [54:51] that people sometimes forget, which is [54:54] to um analyze. So I'm going to run [54:58] analyze requests. Uh converting a table [55:02] to a hyper table moves the data into new [55:05] physical chunks and those new chunks [55:07] have no planner statist statistics yet. [55:11] Postgress is planning against guesses [55:14] until you run analyze. So do this after [55:17] every conversion. If you skip it, you [55:19] get bad plans and you can blame the [55:21] wrong thing. So now let's rerun the [55:25] baseline dashboard query. So here's the [55:28] query we ran before, but now we have a [55:31] hyper table and we're going to see how [55:33] long it takes. And we're going to run [55:36] this a few times because the the first [55:39] will likely be dramatically [55:42] slower. The migrate data just rewrote [55:44] every row into new chunks. So nothing is [55:46] in cache. [55:48] So if we run it again, we can basically [55:53] see a settled number. Sometimes it's [55:54] good to run it three times. So we can [55:59] see that this number is um it's not a [56:04] clear win. It's similar, but maybe just [56:07] somewhat slower. the so the the the [56:10] first time we ran it um it's was the old [56:14] cache because we just rewrote every row [56:16] on disk into the new chunks and so here [56:20] is ba basically just a bit worse like I [56:24] said and that's the honest result and [56:27] it's the most useful thing that will [56:28] happen in the section. So look at what [56:31] we asked for which was that every row in [56:34] the last seven days. If I kind of show [56:37] this, we're trying to get everything in [56:39] the last seven days and how much and we [56:43] have seven days of data. So we ask for [56:46] the entire table. Chunk exclusion can [56:49] only skip chunks that fall outside your [56:51] time filter. Our filter covers all of [56:54] them. So we skip nothing. We read [56:57] exactly as much as before and we've [57:00] added a little work because the planner [57:02] now plans across eight chunks and [57:04] appends eight scans instead of running [57:07] one clean sequential scan. So let's also [57:10] check the the buffers. So remember we [57:14] checked buffers before and we can see [57:17] the buffers is essentially unchanged [57:20] now. It's almost the same number. Uh [57:24] basically it's the same pages. We not [57:26] save the data database one single unit [57:29] of work and the buffer count says so [57:31] regardless of what the clock said. So [57:34] partitioning is not specifically a speed [57:37] feature. [57:39] It's not really a performance feature. [57:41] Chunks make it possible to skip data. [57:44] They don't skip anything on their own. [57:46] If your query asks for everything, you [57:47] get everything plus a little overhead. [57:49] This is the single most common [57:51] disappointment with time scale DB. [57:53] Somebody converts a table, reruns their [57:55] dashboard query, sees no improvement, [57:57] and concludes the extension doesn't [57:59] work. But the extension works fine. The [58:01] query asks for all the data. So now, so [58:05] let's ask a question a dashboard would [58:08] actually ask. [58:10] Here we're going to look for the data in [58:12] the last six hours instead of the last [58:15] seven days. Now we see that there is [58:18] only one chunk in the plan. And if we [58:23] point to the buffer count, [58:26] it's very small. It's just three now. So [58:29] it's just a small fraction of the [58:31] baseline. So there it is. One chunk in [58:34] the plan instead of eight and a fraction [58:36] of the pages read. Same table, same [58:38] data, a time filter that lets the [58:40] planner throw away seven, like tons of [58:43] the table before it reads a single row. [58:45] And notice I'm pointing at pages, not [58:47] milliseconds. On a data set this small, [58:50] the whole thing may fit in memory and [58:52] the clock times will be could be close [58:54] together. The buffer count shows the [58:56] real difference. And on a production [58:57] table where the data doesn't fit in RAM, [59:00] that difference in pages is the [59:03] difference in seconds. So that's the [59:05] shape of every win in this course. It's [59:08] not it's magically faster, but we gave [59:09] the database a way to not do the work. [59:12] So next up, making sure we find rows [59:14] efficiently inside a chunk. So let's [59:18] talk about indexes and chunk skipping. [59:21] Chunk exclusion got us from the whole [59:23] table down to the right chunks. Now we [59:26] define rows efficiently within a chunk. [59:30] And indexing on a hyper table has one [59:32] structural difference from indexing a [59:35] normal table. When you create an index [59:38] on a hypert, you don't get one index. [59:42] you get one index per chunk [59:44] automatically including on chunks [59:46] created in the future. So here I'm going [59:49] to create an index on requests [59:52] um URL domain and time descending. So [59:56] this is going to build per chunk and now [59:59] it's done here. So [60:02] now we're going to basically count the [60:04] indexes. And now we have eight here, one [60:07] per chunk. And this is why hyper tables [60:10] can sustain write rates that a normal [60:13] table can't. Think about the insert [60:16] path. On a normal table with two years [60:19] of data, that index is enormous, bigger [60:22] than RAM. Every insert has to find its [60:25] place in that giant B tree. and the [60:27] pages it needs are probably not in [60:30] memory. So, it's a random disk read [60:32] followed by a random disk write. Random [60:35] IO is the slowest thing in a that a [60:38] database does on a hyper table. Inserts [60:42] only go to the current chunk. That [60:44] chunk's index is one day of data. It's [60:48] small. It's hot. It's in memory. Insert [60:51] is a memory operation plus a sequential [60:54] write to the w. That's the difference [60:57] between thousands of inserts a second [61:00] and hundreds of thousands. [61:03] So on the index def definition itself, I [61:06] put URL domain first and time second. [61:10] And that order is deliberate. Uh the [61:13] rule of thumb is equality columns first [61:16] then the time column. You filter URL [61:20] domain equals example.com. That's [61:22] equality. It narrows to a specific point [61:24] in the B tree. Then time is more than or [61:27] equal to something. It's a range scan [61:30] from that point. If you flip the order, [61:32] you scan a huge time range and filter [61:35] domains out afterwards, which is much [61:38] more work. And the time descending is [61:41] because time series queries almost [61:43] always want recent data first. It makes [61:47] order by time uh descending limit 100 [61:51] the show me the latest query. It makes [61:53] it nearly free. [61:55] Now here's the mistake that will get you [61:58] and it follows directly from that always [62:00] filter by time rule that I talked about [62:02] earlier. So I'm getting an ID for this [62:05] next example here. So let's say I want [62:08] to one specific request by its ID. So [62:12] I'm selecting request where the request [62:14] ID equals um the ID that I just got. And [62:19] look at that. It checked every chunk. We [62:22] can see here this chunk, this chunk, [62:24] this chunk. Um [62:27] each one has an index on request ID. So [62:30] each lookup is fast, but did that look [62:33] once per chunk because nothing told it [62:35] which chunk the row is in. with if we [62:38] had 90 chunks that's 90 index probes to [62:41] fetch one row. It'll still return in [62:44] milliseconds on a small data set. So it [62:46] looks fine in development but with two [62:48] years of daily chunks it's 730 probes [62:52] and it does not look fine anymore. So [62:54] here's the fix. First I'm going to get [62:56] the request ID and also a time for that [63:00] request. [63:02] And then I will call where the request [63:05] ID and put in the request ID and also [63:07] put in the time here. And now we can see [63:11] only one chunk one probe. So the design [63:15] rule whenever you look up a row by ID [63:17] pass its timestamp also [63:21] which sounds annoying but think about [63:23] where that ID came from. It came from a [63:25] list view, a search result, a web hook [63:27] payload, a log line, and all those [63:29] already had the timestamp right next to [63:31] the ID. You just have to carry it [63:33] through your API and your URLs. [63:36] If you genuinely can't say the ID comes [63:39] from a third party who only gives you an [63:41] ID, there's an escape an escape hatch. [63:46] Now the actual limitation on hyper [63:48] tables the thing you should know before [63:50] you commit to this everything else in [63:52] this course is upside but there is one [63:54] genuine constraint basically there's no [63:58] global unique index so if I try to [64:01] create unique index on requests we got [64:04] an error so reject it the reason is [64:07] structural and not really an oversight [64:10] a unique index has to be able to answer [64:13] does this value exist anywhere in the [64:16] But there's no single index spanning the [64:18] hyper table. There are 90 separate perch [64:21] chunk indexes. To enforce global [64:23] uniqueness on insert, Postgress would [64:26] have to check all 90 on every single [64:29] insert. That would destroy exactly the [64:31] right performance that hyper tables [64:33] exist to provide. So the rule any unique [64:36] index on primary key or a hyper table [64:39] must include the partitioning column. So [64:42] we're going to do alter table request [64:44] add primary key time and request ID. And [64:47] that works because now uniqueness is [64:49] only enforced within a chunk. The time [64:52] value determines the chunk. So a single [64:54] chunks index is sufficient to check. And [64:58] here's what you're basically giving up. [65:01] Two rows with the same request ID at [65:04] different timestamps are now legal. [65:07] Usually that's fine, but sometimes it [65:09] isn't. If the ID is a payment reference, [65:12] you want hard global uniqueness. There's [65:14] a genuinely [65:16] elegant solution to that and it's my [65:19] favorite trick. The idea is to use an ID [65:21] that contains its own timestamp. Then [65:25] the ID alone is enough to find the chunk [65:27] and you get global uniqueness for free. [65:30] That's UU ID V7. same 128 bit UUID [65:34] format everyone already uses, but the [65:36] first 48 bits are a Unix millisecond [65:38] timestamp followed by random bits. So, [65:42] UUID V7s sort chronologically, and you [65:45] can extract the creation time from the [65:48] ID itself. [65:50] So, I just ran this select UU ID V7 a [65:54] few times and look at the leading [65:58] characters. [65:59] They're all going to start with the same [66:02] characters. You see, actually even goes [66:05] up through 722. [66:08] So the leading characters are nearly [66:10] identical. That's the timestamp [66:14] and generated a sec. They it's they were [66:17] generated a second apart and then they [66:19] sort in order. So then we can here we're [66:23] generating that again but then we're [66:25] calling this UUD timestamp [66:28] and we can pull the timestamp back out. [66:32] That function is provided by time scale [66:34] DB. Postgress 18 also ships a native [66:38] UUIDV7. [66:40] So depending on your version you may [66:41] have two available and either is fine. [66:44] Just be consistent. Okay, let's talk [66:47] about this uh check the check [66:51] constraint. So, this is going to tie [66:54] everything together. And so, I'm [66:57] creating the t this table events v7 with [67:00] an ID time JSON. And then I'm going to [67:03] create a hyper table. But, uh see what [67:06] we're doing is I'm running check. It's [67:10] doing something clever. [67:13] So basically it guarantees that the [67:18] time stamp embedded in the ID equals the [67:22] partitioning column which means given [67:25] only an ID you can compute the time [67:28] which means you can always find the [67:29] chunk which means the ID alone is now a [67:32] global unique key in practice. A [67:35] duplicate ID would have to have the same [67:37] embedded timestamp. So it it would land [67:40] in the same chunk where the perch chunk [67:42] unique index catches it. You get global [67:46] uniqueness and single chunk lookups from [67:50] one constraint. [67:52] So let me just uh insert into this [67:54] events v7 table. Um we're passing in so [68:00] we're having the time stamp and then [68:03] hello world. That's the data. [68:07] So that generates two different UYU ids. [68:12] So let me do it uh properly with new id [68:16] as and then select uidv as id insert [68:20] into events the id time data select the [68:24] id and then uh from new id. So if we run [68:29] that uh in the application code this is [68:32] cleaner. You generate the UID in your [68:34] app, derive the timestamp from it, and [68:37] pass both. That's exactly what one of [68:41] our projects later is going to do, and [68:43] you'll see the Python for it. So, I'm [68:46] going to run this to get the recent ID [68:48] that we put into the table. So, let's [68:50] get an ID from that table, and then we [68:54] can look it up where we're going to pass [68:57] in the ID here. And the timestamp is [69:00] going to be the exact same ID here. So [69:04] we're basically putting in the same UYU [69:07] ID twice. And then we can see this query [69:11] plan here. One chunk from an ID alone. [69:16] But one thing to keep in mind, and it's [69:19] a real one that will cost you an [69:21] afternoon if you if you just don't [69:23] understand this. um UUID V7 timestamps [69:27] are millisecond precision. Timestamp TZ [69:31] is microssecond precision. So if you [69:34] generate a time stamp any other way now [69:36] for instance and compared to the one [69:38] embedded in UU ID v7 that will not be [69:41] equal and your check constraint will be [69:43] reject the row. So always derive the [69:46] time stamp from the UIU ID using UUID [69:48] timestamp never generate them [69:51] independently and hope. So, one thing [69:53] you should know is that indexes are not [69:55] free and on a high ingest table, they're [69:59] expensive in a way they aren't on a [70:02] normal table. Every index has to be [70:04] updated on every insert on a table [70:07] taking a 100,000 rows a second. One [70:10] extra index is a 100,000 extra B tree [70:13] insertions a second. I It's possibly [70:16] could add six indexes just in case and [70:19] wonder why and just fell off a cliff. So [70:22] find the ones you're not using. So with [70:26] this code, we're selecting the indexes [70:28] and we're index scan equals zero. That [70:32] means that the index has never been used [70:35] since the stats were last reset. On a [70:38] system that's been up a while, the those [70:42] are candidates for deletion. Check that [70:45] it's not a unique constraint doing [70:47] integrity work first. And one thing you [70:50] should know which I want to uh make sure [70:53] you understand is that once we get to [70:54] the column store, you'll need [70:57] dramatically fewer indexes than you [71:00] think. Uh because columnar batches have [71:04] their own built-in skipping. Some of the [71:07] indexes you'd add to a normal analytics [71:10] table become pure overhead. So let's [71:13] measure with the index in place and [71:15] let's measure the narrow query uh the [71:17] six-hour one because that's the one an [71:21] index can actually help. So the shared [71:24] hit is three. Now we can see the the [71:29] 7-day interval, the 7-day baseline, and [71:33] we can see the shared hit 2,198 [71:37] and the read 1,100 or 11,655. [71:42] So, two different stories in those two [71:44] numbers. And the difference is the whole [71:46] point. The narrow query got faster. The [71:48] seven day query didn't. And an index was [71:51] never going to fix it. When you're [71:53] reading most of the table, the index [71:55] scan is more work than just scanning. [71:58] The planner knows that, which is why it [72:00] likely ignored our new index entirely. [72:02] That query needs a different kind of [72:04] fix, and it's coming up in a little bit [72:06] here. So, something interesting has [72:09] happened. We have chunk exclusion. We [72:12] have a good index. And the query is [72:14] faster, but it's still not fast. And the [72:17] reason it's no the reason is no longer [72:20] that we can't find the rows. So the [72:23] bottleneck is no longer identifying the [72:25] rows for the analysis but loading them. [72:28] Any big analytics calculation struggles [72:32] with the same issue. [72:34] We know exactly where the rows are. [72:37] Loading them is the problem. So here's [72:40] why and this is where uh the previous [72:44] parts 8 kilobyte page lesson pays off. [72:49] Rows are stored in insertion order. [72:52] requests arrive interle from every [72:54] domain. One for example.com, three for [72:57] other.com, one for example.com again. So [73:00] within any page, the rows for a single [73:02] domain are a small scattered fraction. [73:05] Say a page holds 80 rows and example.com [73:08] is 1% of your traffic. Then each page [73:11] contains roughly one row you want. To [73:14] read a million rows for that domain, you [73:16] read a million pages, 8 gigabytes. to [73:20] extract about a 100 megabytes of data [73:22] you actually asked for. And it's worse [73:25] than the volume suggests because those [73:28] page reads are random. Sequential reads [73:31] are 10 to 100 times faster than random [73:35] ones. Even on SSDs, you're doing the [73:38] slowest kind of IO for data you mostly [73:41] throw away. The fix is data locality. [73:45] Physically rearrange the rows so a [73:47] domain's data sits together. then [73:49] reading a million rows for one domain is [73:51] a few thousand sequential page reads [73:53] instead of a million random ones. So [73:56] let's see it in action. Create index [73:58] request ordering [74:00] index on request with the domain URL [74:04] domain and time. That index defines the [74:07] desired physical order grouped by domain [74:10] and within each domain ordered by time. [74:14] Then we rewrite the chunks in that [74:16] order. So this command here [74:20] rewrites every chunk which will [74:23] definitely take some time while it runs [74:28] one nice property. It rewrites each [74:30] chunk into a fresh copy and swaps it one [74:32] chunk at a time. So it doesn't need a [74:34] giant lock on the whole hyper table and [74:37] it fully rebuilds the indexes as a side [74:39] effect which removes bloat. [74:42] It's the kind of it's kind of like the [74:44] useful cousin of vacuum full. [74:48] Okay, so that finished. It took 38 [74:50] seconds. Now we'll run the six-hour [74:53] query again unchanged. This is the third [74:56] time we've want run it. That's the [74:58] points or control. And the shared read [75:01] is three again. I mean there's not that [75:04] many to read here. the so reordering is [75:08] going to pay off on the narrow index [75:10] driven query, not the 7-day scan. And it [75:14] shows up in pages read far more clearly [75:16] than on the clock. But let's do the [75:19] 7-day scan here. And we can see the hit [75:25] uh is and the read are up here. 163,000 [75:29] 73,000. [75:31] So same rows, same index. Uh but what we [75:35] didn't make the search smarter, we just [75:38] made the data denser. Okay, let's talk [75:41] about the column store. [75:44] This is the big one. If you take one [75:46] feature away from this course, take this [75:47] one. So let me start with a question. [75:51] Our request table has what maybe 15 [75:54] columns. Well, real production analytics [75:56] tables often have 50, 100, more. Now [76:00] look at this query. Select average [76:03] duration total from request ht where [76:06] time now minus interval 3 days. So so [76:11] look at this query time or select [76:14] average duration total from request [76:16] where time is between the last three [76:19] days. One column that query needs [76:21] exactly one column the duration total. [76:24] How many columns did postresql actually [76:27] read off disk? Well, all of them because [76:31] of how how row storage works. So, but [76:35] remember from the previous part, the [76:37] smallest unit Postgress can read is an 8 [76:40] kilobyte page and a page holds complete [76:44] rows. Every column of every row on the [76:47] page comes into memory whether you asked [76:49] for it or not. You wanted one number per [76:52] row and you paid for a 100. That's the [76:55] first problem. Here's the second. [76:58] Postgress processes those values one at [77:02] a time. Fetch a row, extract the column, [77:05] add it to a running total, next row. 10 [77:08] million rows, 10 million trips through [77:10] that loop. Modern CPUs can do arithmetic [77:13] on eight or 16 numbers in a single [77:15] instruction, but only if the numbers are [77:18] sitting next to each other in memory. In [77:20] row storage, they're 100 bytes apart. So [77:24] you read data you don't need and you [77:26] process the data you do need in the [77:28] slowest possible way for finding one row [77:31] which is what row storage is designed [77:33] for. This is exactly right for [77:36] aggregating 10 million rows. It's wrong. [77:40] Every dedicated analytics database on [77:42] Earth solves this the same way. Store [77:45] the data by column instead of by row. [77:48] Time scale DB's column store takes a [77:51] chunk and rewrites it. Instead of a [77:54] thousand rows sitting next to each [77:55] other, you get all thousand duration [77:58] total values sitting next to each other [78:01] in one compressed batch. Same for every [78:04] other column. Now the average query [78:08] reads one column's worth of data. And [78:10] because those numbers are contiguous, [78:13] the CPU can chew through them 16 at a [78:16] time. That's called vectorized [78:18] execution, and it's why the speed up is [78:21] so much bigger than the IO savings alone [78:25] would suggest. But here's the thing that [78:28] people sometimes don't expect. Column [78:31] NAR storage doesn't just make queries [78:33] faster, it makes the data smaller, [78:37] dramatically smaller. And that's because [78:39] a column of similar values compresses [78:41] far better than a row of wildly [78:43] different ones. So think about it. In [78:46] our table, URL domain for a thousand [78:48] consecutive rows might be the same [78:50] handful of values. Time for a thousand [78:53] consecutive rows is a thousand [78:55] timestamps that are all within a few [78:57] seconds of each other. Status code is [79:00] basically all 200s with uh the [79:03] occasional 500. That's enormously [79:06] compressible, but only if you put them [79:08] next to each other first. [79:10] So there are four compression techniques [79:12] and they're worth knowing because they [79:15] explain what compresses well and what [79:18] doesn't. A repeated value is stored [79:21] once. Run length encoding. A thousand [79:24] rows of service equals shop. It becomes [79:27] shop time 1,00. [79:29] Frequent strings get shortened using an [79:32] internal dictionary. Instead of storing [79:35] the literal string checkout service a [79:38] thousand times, store it once in a [79:40] dictionary and store a small integer [79:42] reference. Numbers are stored as deltas [79:46] in the smallest data type that fits. [79:49] Consecutive timestamps differing by a [79:51] few hundred milliseconds don't need [79:53] eight bytes each. So the first one then [79:56] two byte deltas. Booleans become a [79:59] single bit not a bite but a bit. So by [80:03] applying these optimizations, Times [80:05] scale DB often reduces a table size by [80:08] more than 90%. [80:11] That's not a typo and we're going to [80:13] verify on our own in a in uh later in [80:15] this course. So in one example, a table [80:19] went from 72 1/2 GB to 33. But look at [80:23] the indexes 10.6 GB down to 400 [80:27] megabytes. [80:29] So we'll come back to why the index [80:31] number is so extreme. Now if columnar is [80:35] so great, why isn't everything columnar? [80:39] Because columnar storage is bad at the [80:41] thing rows are good at writing one row, [80:44] changing one value, deleting one record. [80:46] So time scale DB does the obvious smart [80:49] thing. New chunks are row store, fast [80:52] inserts, easy updates, normal postgrql. [80:56] Once the chunk has settled down, once [80:59] you're not actively writing to it, it [81:01] gets converted to column store fast [81:04] reads tiny footprint. This is the part [81:07] where a real analytics database would [81:10] tell you that tell you the data is now [81:12] read only. But time scale DB doesn't. [81:15] With Timescale DB's column store, you [81:18] can still insert new rows or modify and [81:20] delete existing ones, which is not the [81:23] case with other analytical databases. [81:27] You still have a normal SQL table. [81:30] Update works, delete works, insert [81:32] works. It's slower than on a row store [81:35] chunk, and you shouldn't design a [81:36] workload around hammering updates into [81:39] compressed data. But late arriving [81:41] events, corrections, GDPR deletions, all [81:44] that just works. And there's a uh [81:48] counterintuitive bonus. Inserting into a [81:52] column store chunk is also the fastest [81:54] way to insert many rows. With the direct [81:57] to column store option, you can insert [81:59] more than five millions uh more than [82:02] five million rows each second. Postgress [82:06] can't do that. Five million rows a [82:08] second. If if you're doing bulk [82:11] backfill, writing straight into column [82:13] store format is faster than writing rows [82:16] and converting later. So let's do it. [82:19] Three settings. So I run that and it [82:21] says warning column request ID should be [82:23] used for segmenting or ordering. Now [82:26] Postgress is telling us hey request ID [82:28] is a unique key and because it isn't in [82:30] segment by or order by looking up a [82:33] compressed row by ID won't get pruning [82:35] benefits for analytics queries. [82:38] aggregating by domain and service over [82:40] time. That's exactly the trade-off we [82:42] want. [82:44] So the enable column store here turns [82:47] the feature on. The other two are the [82:49] ones that matter and I want to spend [82:51] some time on them because this is where [82:53] people get bad results and conclude the [82:55] feature doesn't work. So the segment by [82:58] is the one that matters. [83:01] Segment by decides how rows are grouped [83:04] into batches. rows sharing the same [83:06] segment by values go into the same [83:09] batch. And this is the point. A query [83:12] filtering on that column can skip every [83:14] batch that doesn't match without [83:17] decompressing anything. It doesn't scan [83:19] and reject. It just never touches them. [83:22] So you choose segment by by asking what [83:25] do I always filter on? For us, every [83:28] dashboard query filters on URL domain [83:31] and usually service. That's a tenency [83:35] column and tenency is the mo is the most [83:38] common right answer in real systems. [83:42] Customer ID, project ID, device ID, [83:44] account ID. Now the constraint that [83:47] trips everyone up. A batch holds up to a [83:51] thousand values. You want batches full. [83:55] A batch with three values in it wastes [83:57] more of the benefit because compression [84:01] works on volume and you get one batch's [84:03] overhead for almost no payoff. So the [84:07] rule for any given segment by value you [84:10] want at least a thousand rows in a chunk [84:13] ideally many thousands which means high [84:16] cardel cardality columns are poison [84:20] here. Let me show you rather than assert [84:23] it. So I'm going to say select count [84:26] distinct UR URL domain as domains and [84:29] then we're going to count the request [84:31] ids and then count the rows. So we got [84:35] just uh nine domains [84:38] millions of distinct request ids. And if [84:42] I set segment by equals request ID, [84:45] every batch holds exactly one row. I get [84:48] all of the overhead and none of the [84:50] compression. and we're going to measure [84:52] that soon. So order by controls the sort [84:55] order within each batch. Two things come [84:58] from it. First, it's the data locality [85:01] win from the reordering section for free [85:04] as part of compression. That is why [85:06] reordering becomes redundant. Second, [85:09] and less obvious, time scale DB stores [85:11] the minimum and maximum of each order by [85:14] column per batch. So a query with where [85:17] time between X and Y checks a batch's [85:20] min and max timestamps and skips the [85:23] whole batch if there's no overlap. [85:26] That's why you put the timestamp first [85:28] in order by you always filter by time [85:31] range. So you always want that skip [85:33] available. We use time uh deesc uh [85:37] descending URL path. Uh so time first [85:41] because it's always in the filter URL [85:44] path second so that within a given [85:47] second paths group together which helps [85:50] our per path queries. [85:52] So right now actually uh we haven't uh [85:57] gotten any there's no compression we [85:59] haven't saved any space yet. [86:02] So you shouldn't just think oh there's [86:04] no improvement and give up. Activating [86:06] the column store for a hyper table [86:09] doesn't automatically convert its chunks [86:11] to column store chunks. This [86:13] misconception often leads to [86:15] underwhelming performance during initial [86:18] performance testing. Enabling the column [86:20] store configures it. It doesn't convert [86:23] anything. Existing chunks stay exactly [86:26] as they are until you either convert [86:27] them by hand or add a policy that does [86:30] it for you. So here's how you could [86:32] convert it by hand. So, we're going to [86:35] check out what the chunks are. And then [86:37] we're going to do call convert to column [86:39] store. And I'm just going to take one of [86:41] these chunks and then put it in here. [86:45] Okay, it's converted. And it is [86:48] reversible, which is important later. [86:51] So, basically, I run the same command, [86:54] but instead of convert to column store, [86:56] I can say convert to row store. [87:00] If you're on an older time scale DB, you [87:03] may see compressed chunk and [87:05] decompressed chunk in the docs. Uh it's [87:08] the same operation but older names. The [87:11] convert to um column store row star [87:14] store procedures are the or the current [87:16] API. Now we can also add a policy add [87:20] column store policy on the request table [87:23] and the policy is interval 1 hour or [87:25] after that. Okay. So any chunk whose [87:28] data is more than an hour old gets [87:30] converted automatically by a background [87:32] job. [87:34] So why the delay? Why wait an hour at [87:36] all given that column store chunks [87:39] accept inserts? Well, two reasons. [87:41] Inserts into a row store chunk are [87:43] cheaper and real pipelines have lag. [87:47] Cute events, retried web hooks, mobile [87:49] clients that were offline. Giving the [87:52] newest hour a grace period means the [87:55] bulk of your rights land on the cheap [87:57] path. It's not a correctness [88:00] requirement. It's a throughput choice [88:04] for our 3-day data set. An hour means [88:07] almost everything converts immediately. [88:09] Let me force the whole table so we can [88:12] measure. So let me force the whole table [88:15] so we can measure. So everything this [88:19] was already a column store and now let's [88:22] see it. So I'm going to see the before [88:26] compression total bytes the after [88:28] compression total bytes and it went it [88:31] we basically saved 88% [88:35] for uh how much uh space uh this is. So [88:40] that is pretty good. Now I'm going to [88:44] turn on timing for this section. And now [88:47] we're going to run this that we've run [88:50] before where we're getting the average [88:51] duration from the last three days and [88:54] we're at 60.8. [88:56] So remember that benchmark number we [88:58] have indexes going from 10.6 gigabytes [89:00] to 400 megabytes. Here's why it's so [89:03] extreme. Column store chunks don't have [89:06] your indexes at all. And your first [89:09] reaction uh that you may be wondering [89:12] about that. So let me explain why it's [89:15] fine. An index exists to avoid reading [89:18] rows you don't need. The column store [89:21] achieves the same goal in a different [89:23] way in two steps. The step one, your [89:25] segment by filter eliminates whole [89:27] batches instantly from metadata. A [89:30] thousand rows gone per batch and no [89:34] decompression. Step two, the order by [89:36] minmax ranges eliminate more batches, so [89:39] no overlap with your time range. You can [89:42] skip it. So between these two, a typical [89:44] dashboard query has already discarded [89:46] most eving [89:49] a single value. An index would be [89:52] redundant and it would cost you the 90% [89:55] size win to maintain. But what about a [89:59] point lookup like one specific row by [90:02] ID? For that, there's a third mech [90:05] mechanism. [90:06] Time scale DB keeps small index-like [90:09] structures per batch like minmax ranges [90:13] for numbers and timestamps. Bloom [90:15] filters for strings and uyuids. A bloom [90:18] filter is a tiny probabilistic structure [90:21] that answers is this value definitely [90:23] not in here very cheaply. If the answer [90:26] is definitely not skip the batch if it's [90:28] main if it's maybe decompress and check. [90:32] So uh bloom we can here we're doing a [90:35] bloom filter on geo country because we [90:37] do equality lookups on it then min max [90:41] on duration total because we filter it [90:44] by range a request slower than 5 [90:47] seconds. [90:48] One big caveat and it's the same shape [90:51] as the misconception from earlier. These [90:55] structures are built when a chunk is [90:57] converted. Setting compressed index on [91:00] an already converted chunk does nothing [91:03] to it. If you need to add one [91:05] retroactively, convert to row store then [91:08] convert to column store on a big table [91:11] that's expensive. So think about this [91:13] before you convert a year of data. And [91:16] the limitations [91:17] uh because I don't want you deleting [91:20] your index and then filling a bug. So [91:23] they only help equality checks. So where [91:26] status equals open is great. That's what [91:28] a bloom filter answers. They do not help [91:32] in lists. Where mode in 245 gets no [91:37] benefit. [91:38] So equality yes range on a min max [91:41] column yes. Multialue in no. So three [91:45] things to remember from this section. [91:48] One enabling the column store converts [91:51] nothing. You need to add a policy. Two, [91:53] segment by is your filter column, [91:56] usually tenency, and it needs over a [91:58] thousand rows per value. And three, set [92:01] compress index before you convert and [92:03] don't expect it to help in lists. That's [92:07] the column store. 90% smaller, [92:09] dramatically faster, and it makes the [92:11] reordering section obsolete, which is [92:13] exactly um [92:16] what I was telling you earlier when we [92:19] were going through that at the end of [92:21] that section. So next um premputing [92:24] results so the query doesn't have to [92:27] aggregate anything at all. So we've made [92:30] the status smaller and the scans faster. [92:34] There's one move available and it's uh [92:37] this next one is the biggest of all. [92:41] Every dashboard query we we run has one [92:43] thing in common. It computes the same [92:46] answer over and over. Somebody loads a [92:49] dashboard. We aggregate yesterday's [92:50] data. They refresh. We aggregate [92:52] yesterday's data again. Yesterday's data [92:55] has not changed. We are doing identical [92:58] work repeatedly forever. So you could go [93:01] further. [93:03] You you could further speed up queries [93:04] by by accessing precomputed results [93:07] instead of aggregating rows each time a [93:10] query is executed. You already know why [93:13] the obvious implementations fail because [93:15] we did both in the previous part. A [93:18] materialized view has to recomputee [93:21] everything. So refreshing it gets slower [93:23] every day. A summary table maintained by [93:26] triggers hits low-level lock contention. [93:30] So with each new query, the waiting time [93:32] increases until the database becomes [93:34] overwhelmed by a mass of blocked queries [93:37] waiting to run. That's the death spiral [93:40] we watched in earlier. What we want is [93:44] the concept of a materialized view but [93:47] incrementally maintained. Recomputee [93:50] only what actually changed. That's a [93:53] continuous aggregate. [93:56] So here's what I just run here ran here [93:58] and let me walk you through it because [94:01] most of this is ordinary SQL and exactly [94:04] two things are special. So we have [94:05] create materialized view that standard [94:08] postgressql [94:10] syntax and then the with time scale db [94:13] continuous that's special the thing [94:16] that's the special thing number one and [94:18] it's what turns a regular materialized [94:20] view into an incrementally maintained [94:22] one then a normal aggregate query we [94:28] have uh time bucket [94:30] um interval 15 minutes time now this is [94:34] Another special thing sort of it's a [94:37] time scale DB function that rounds a [94:39] time stamp down to a fixed interval. [94:43] Every event between noon and 12:15 gets [94:46] bucket 12:00. That's what makes the [94:48] grouping stable and reagregatable. [94:52] Everything I grouped by a dimension I [94:54] want to filter or slice by later. [94:56] Everything I aggregate is a number I [94:58] want on a chart. Uh notice that I chose [95:02] count min max and not average. That's [95:06] deliberate and I'll explain in a little [95:09] bit. It's and it's a pretty interesting [95:12] thing. So now the refresh policy select [95:17] add continuous aggregate policy request [95:20] 15 minutes. The schedule interval start [95:23] offset null. End offset null. Every five [95:26] minutes, a background job updates the [95:28] aggregate. Start offset and end offset [95:30] are null, meaning consider all of time. [95:34] And we'll change both of those later. [95:36] And querying it is just querying a [95:39] table. So we can query it just like we [95:42] would do a normal table. And now we've [95:45] counted all the requests here where URL [95:48] domain equals example.com and service [95:50] equals shop. [95:53] Now we'll make sure timing is on for [95:55] this next one and we'll select and then [95:58] we have the time bucket with this [96:00] interval. We're summing requests from [96:02] request 15 minutes and we're again [96:04] looking for example.com. [96:07] So I'll do this and we can now read the [96:13] the time number read we can read the [96:15] timing and compare it to the same query [96:18] then compared to the same query against [96:20] the hyper table. [96:22] So I asked for hourly numbers from a [96:24] 15minute aggregate by reagggregating [96:27] four buckets at a time. That works [96:29] because uh time bucket boundaries nest [96:32] cleanly. You can always go coarser. You [96:35] can never go finer. [96:37] So here's the mechanism and it's why [96:40] this feature works. Time scale DB tracks [96:43] which buckets have been invalidated [96:46] since the last refresh. You insert a [96:48] row, that row's bucket is marked [96:51] changed. You update or delete a row. [96:54] Same thing. When the refresh job runs, [96:57] it looks at the list of changed buckets [97:00] and recomputes only those. Even with [97:04] years of data, the update process [97:05] remains quick because every time only a [97:07] few buckets may have changed since the [97:09] last run. That's the difference. A [97:12] materialized view refreshed co uh cost [97:15] grows with your total data. A continuous [97:19] aggregate refresh cost grows with your [97:21] change rate, which for time series data [97:24] is basically constant because you're [97:26] only ever writing to the present. So I [97:29] want to flag this one specifically. [97:31] These are background jobs. If they start [97:32] failing uh like bad permission, a schema [97:35] change, disc pressure, they fail [97:36] quietly. Your dashboard doesn't air out. [97:39] It just serves sta numbers and you find [97:41] out when someone asks why yesterday [97:43] looks empty. So you want to basically [97:47] run this and um check to see if it's [97:50] empty. There is a problem with what we [97:52] just built. That refresh job runs every [97:54] 5 minutes. So the aggregate lags reality [97:57] up to 5 minutes. For a daily report, [98:00] it's fine. But for an operations [98:02] dashboard where someone is watching a [98:04] deploy go out, not fine. The fix is to [98:08] combine precomputed history with live [98:10] data on the fly. So, let me uh rebuild [98:14] this. First, we'll just drop that table. [98:18] We've already created the materialized [98:21] view. And then I'll and then we'll [98:24] create it again. And then we'll create [98:26] this again. and then add the policy. [98:31] So there's three changes uh in the way [98:34] we did it this time. So let me tell you [98:37] about the different changes in this way [98:39] we created it. First of all, [98:41] materialized only is equals false. This [98:46] turns on the union. When you query the [98:48] view, time scale DB reads premputed [98:51] buckets for the older range and reads [98:53] the raw hyper table for the recent [98:55] range, then stitches them together. You [98:57] write one query and get both then with [99:01] no data. That's how we ended our [99:04] creative creation line. Without this [99:06] cring the view computes the entire [99:08] history immediately on a big table. [99:11] That's a long blocking operation at the [99:13] worst possible moment. With that [99:15] creation is instant and the policy fills [99:17] things in progressively. Then in offset [99:21] equals more is more than equal to [99:23] internal 30 minutes. This defines the [99:26] boundary. Anything older than 30 minutes [99:28] is precomputed and anything newer is [99:30] read live which means the numbers on [99:32] your dashboard are current to the second [99:35] and the expensive part is still [99:36] premputed. [99:38] So um how big should in office offset [99:41] be? Well the rule rule of thumb is at [99:43] least twice your bucket interval. We [99:46] have 15minute buckets so 30 minutes. The [99:49] reason is that you don't want to premp [99:50] compute a bucket that's still filling. [99:52] You'd store a partial result and then [99:54] have to invalidate it immediately and [99:57] increase it if your data arrives late. [100:00] If events come through a queue that can [100:03] break back up or from mobile clients [100:05] that sync when they reconnect, a 30 [100:08] minute window might close before the [100:10] data lands. Then that data goes into a [100:13] bucket that's already been materialized, [100:15] which still which does still work. it [100:18] gets invalidated and recomputed, but [100:20] you're paying rework for something for [100:23] something a wider offset would have [100:25] avoided. Now, let me show you a [100:27] technique that makes a scale to years of [100:30] data. I want hourly numbers, too. The [100:33] naive approach is a second continuous [100:35] aggregate reading the hyper table. But I [100:38] already have 15-minute buckets. Four of [100:40] them make an hour. So, let me build the [100:41] hourly aggregate from the 15minute [100:44] aggregate. So I can run this this code. [100:48] It's very similar but you can see we're [100:49] getting from the request 15 minutes uh a [100:54] bucket 1 hour from bucket 15 minutes [100:57] where I've created a materialized view [100:59] and then I'm going to add the aggregate [101:02] policy. So look at the from clause the [101:06] uh request 15 minutes it's not request [101:09] and look at the aggregates we have sum [101:13] it sum of the count min of the minimums [101:15] max of the maximums I'm already I'm [101:19] combining already computed results. So [101:22] basically there are two reasons this is [101:23] better. Um the reason one is refresh [101:26] pressure to update an hour. This reads [101:28] four precomputed rows instead of [101:30] scanning potentially millions of raw [101:32] ones. Each level of the ladder reads a [101:34] small level below it. Then the reason [101:37] two is the subtle one is query speed. [101:39] Both views are real time. When you query [101:41] the hourly view for right now, its live [101:45] portion doesn't scan the hyper table. It [101:47] reads the 15minute real-time aggregate [101:50] which is itself mostly precomputed. Even [101:53] the live part of the query is cheap. and [101:56] the end offset is two hours, not 30 [101:58] minutes. Same rule, twice the bucket [102:00] interval and the bucket is now an hour. [102:03] So let's build the rest of the ladder. [102:05] So now this is basically creating the [102:07] materialized view for request daily. And [102:12] notice I dropped the server ID and URL [102:14] path at the daily level. Nobody asks [102:16] which path was slowest on March 4th from [102:18] my yearly chart. And dropping dimensions [102:21] collapses the row count enormously. [102:23] Coarser time, coarser dimensions. So [102:25] that's the pattern. So we can create [102:28] that and add that policy. So you can see [102:31] here kind of the the ladder between the [102:34] basic request. Now mine's called request [102:36] not request ht 15 minutes hourly and [102:39] daily. And they're each having different [102:41] uh dimensions here. So let's try the [102:45] payoff the 12 months of traffic from the [102:47] top of the ladder. So doing time bucket [102:50] interval one month from request daily. [102:54] And we can see we don't have um too much [102:56] information in our table right now but [102:59] uh you can see it's giving this month [103:00] here. Now we see the time 148 [103:03] milliseconds. [103:04] Now let's do it against the raw data [103:06] from requests. If we do that same thing [103:09] now we're seeing [103:12] uh 1,646 [103:14] milliseconds. So, same answer. Well, we [103:17] see the sum is the same but wildly [103:19] different costs and the gap widens every [103:22] single day the system runs because one [103:24] of them reads a fixed handful of rows [103:25] and the other reads everything you've [103:27] ever collected. So, um one more thing [103:30] they're just hyperts. So, while [103:32] continuous aggregates and real-time [103:34] aggregates may appear magical, they are [103:35] just hyperts managed by time scale DB. [103:38] As a consequence, you can also add [103:40] indexes and activate the column store to [103:42] improve the speed of finding aggregating [103:44] row and aggregating rows. This unlocks a [103:47] lot and everything that we've been [103:51] talking about recently applies to your [103:52] aggregates too. [103:55] For instance, we can create an index. [103:59] We can compress them and an aggregate [104:02] ladder with the column store on every [104:05] rung. Old summaries cost almost nothing [104:07] to keep which is exactly what makes the [104:09] retention strategy uh that we'll talk [104:11] about later possible. Um also some other [104:15] things worth knowing. We can do a manual [104:19] refresh just like this for back fills [104:23] and you can change the settings later [104:24] with ultra materialized view including [104:27] flip flipping materialized only without [104:29] rebuilding. So continuous aggregates are [104:33] the single biggest performance tool in [104:35] time scale DB. Premputee incrementally, [104:38] add materialize only equals false for [104:40] live numbers. Stack them into a ladder [104:42] and treat each rung as the hyper table [104:45] it actually is. Now remember I said I [104:48] chose count min and max deliberately and [104:50] avoided average. Well, we're going to [104:53] find out why. Hyperfunctions are time [104:56] scale DB's extra aggregate functions. [105:00] There are dozens and I'm not going to [105:01] read you a catalog. I'm going to teach [105:02] you the three ideas that matter and then [105:05] point at the rest. First one and it's [105:08] the most immediately practical thing in [105:10] this section which is filling the gaps. [105:14] So we got this code here where we are [105:18] using this time bucket of interval 5 [105:21] minutes from request example.com [105:25] geoc country JP Japan and looking in the [105:28] last 30 hours here so look at [105:34] the timestamps [105:36] there are holes five minute buckets [105:39] narrow filters some buckets had zero [105:42] matching [105:44] for instance. [105:45] So a look at these buckets. Look at the [105:49] timestamps 35 40 45 50. So we can see [105:53] these timestamps and we can imagine that [105:57] there could be some holes. So we have [106:00] five minute buckets and narrow filter. [106:02] There could potentially be zero matching [106:05] requests for some of them. So group [106:07] group I could produce no row at all. So, [106:11] uh, that's a real problem because your [106:13] charting library will connect the dots [106:15] before the gap, uh, straight to the dot [106:18] after it. A five minute outage could [106:21] render as a smooth line and it won't [106:24] really be accurate. [106:27] So, here's the fix [106:30] I have. It shows time bucket gap fill. [106:35] So, here's the fix. We see time [106:38] bucket gap fill right here. This needs [106:41] an explicit start and end. It can't [106:44] invent a range out of nothing. It has to [106:46] be told what window to fill. [106:49] And I will submit this. And then there [106:51] are going to be three columns because [106:53] there are three honest answers to what [106:55] was the value during the gap. And if [107:00] there is going to be in this example, [107:03] there's always a count. But if there was [107:06] a period of time where there is nothing [107:10] actually here's an example right here we [107:12] have um we have null basically no data [107:16] which is truthful and correct when the [107:19] gap means we genuinely don't know [107:22] and this locf [107:25] this is going to carry the last value [107:29] forward the last obs observation carried [107:32] forward correct for things that hold [107:34] their value between readings like a [107:37] thermostat setting a device state a [107:39] configuration the sensor didn't report [107:41] because nothing changed. Now interpolate [107:46] this draws a straight line between the [107:48] neighbors which is correct for [107:50] continuously varying measurements like a [107:52] temperature a fuel gauge or a slowly [107:55] climbing value. So choose based on what [108:00] the missing data means not to which [108:03] chart looks nicer for request count. A [108:06] gap means zero request happening. So [108:09] honestly the the coales count asterk [108:14] zero is the right answer for us. So now [108:18] I want to kind of explain something I [108:19] mentioned earlier. I sort count min and [108:22] max and pointedly I did not use average. [108:25] And here's why. Imagine I had stored um [108:30] average of total duration as duration [108:32] average. Now I want the hourly average. [108:34] So I reagregate four buckets. What do I [108:37] write? So why didn't I store average? [108:41] Well, calculating sum duration average [108:44] doesn't make sense and neither does [108:46] average duration average because the [108:48] approach doesn't take into account that [108:50] each average represents a different [108:51] number of values. Think that through [108:54] because it's the kind of bug that would [108:56] you could potentially ship because you [108:58] don't understand. Sum of averages is [109:01] meaningless. Average of averages is [109:04] plausible looking and wrong. It treats a [109:07] bucket with three requests as equally [109:09] important as a bucket with 30,000. So [109:13] your dashboard shows a number and the [109:16] numbers incorrect, but there's no [109:18] errors. So min, max, count, and sum all [109:21] reagregate cleanly. average does not [109:24] neither does variance, standard [109:25] deviation, median or any percentile. The [109:29] workaround is to store this in pieces. [109:32] So basically here's I'm going to create [109:34] a materialized view with the I'm with a [109:37] time bucket sum count group by the [109:42] bucket 15 minutes and then I'm going to [109:45] get the sum divi the sum of the sum and [109:49] the sum of the duration count as the [109:53] average duration. Now, in this case, [109:56] it's showing nothing here because of the [109:58] the with no data here, but sum of sums [110:02] over sum of counts is a properly [110:05] weighted average. But it's manual and [110:07] it's easy to get wrong and only works [110:09] for average. There's no pair of columns [110:11] you can store that lets you reconstruct [110:13] a median. So, let's talk about what [110:16] fixes all that at once. Aggregating [110:19] values is a two-step process for any [110:22] database which is hidden from you. First [110:24] all values are processed by calculating [110:27] an internal state. After that the rest [110:29] is calculated from the state. Every [110:32] aggregate works this way. Average [110:35] doesn't magically know the average. [110:37] Internally it keeps a running sum and a [110:40] running count and divides at the end. [110:43] That intermediate thing is the state. [110:45] Normally the database throws it away and [110:48] hands you the final number. [110:50] Hyperfunctions hand you the state [110:52] itself. And a state unlike a result can [110:55] be stored in a row and merged with other [110:57] states later. [111:00] So here's the code that shows that we [111:02] got the state the stats a that's not [111:05] going to return a number. It returns a [111:08] state object holding everything needed [111:10] to complete descriptive statistics. [111:13] So I will do that and then we'll call [111:18] refresh continuous aggregates [111:20] and then we can use this code here and [111:24] so we have for average and variance we [111:26] have the rollup of the duration. So [111:29] rollup merges the states then average [111:33] variance and uh standard dev are [111:38] accessors. [111:39] They pull different answers out of the [111:41] merge state. One stored column, three [111:45] statistics correctly weighted and [111:48] basically any granularity that I want. [111:51] So we can see all the the results here [111:54] when we run that. So this is a gamecher [111:59] for continuous aggregates as you can now [112:01] store aggregate states and rows to re [112:04] you re reuse them later. uh hierarchial [112:07] aggregates become trivially correct. [112:09] Every rung stores states, every rung [112:13] rolls up the rung below. No manual sum [112:16] and count bookkeeping, no wrong [112:18] averages, and you get variance and [112:20] standard deviation for free because they [112:22] were already in the state you stored. So [112:26] in an aggregate, store states, not [112:28] results. That's the rule. Now, some [112:30] answers can't be stored in a small [112:33] state. [112:34] They some air grids fundamentally need [112:37] every single value. So imagine a [112:40] function int with a column and number [112:43] that returns the seventh largest value. [112:45] There's no small summary that can answer [112:47] that. You'd have to keep everything. [112:49] Percentiles have the same problem. A [112:51] media needs to know the the middle value [112:54] which means knowing the distribution. [112:57] Count distinct is worse. To know how [113:00] many unique sessions ids you've seen, [113:02] you have to remember every session ID [113:03] you've seen. [113:05] So there's a second family of hyper [113:08] functions, [113:09] approximate aggregates. They keep a [113:12] small bounded state and give you an [113:14] answer with a known error margin. [113:17] Instead of remembering 10 million [113:18] values, they remember a few kilobytes of [113:21] cleverly structured summary. So the [113:24] histogram is exact. [113:27] There are four arguments um the the [113:31] column the minimum the maximum and the [113:35] number of buckets. So from 2 seconds to [113:39] 10 seconds [113:41] in from see 2 seconds to 10 seconds in [113:45] four buckets meaning 2 to 4, 4 to 6, 6 [113:48] to 8, 8 to 10 [113:50] but you get you get six numbers back not [113:55] four. So uh you can see the result right [113:57] here. But let's imagine the result had [114:01] these numbers here in in blue here. [114:04] Basically the array annotated with which [114:07] element is which. You can see which [114:09] element is which down here. The first [114:11] element is everything below the minimum. [114:14] 121 requests faster than 2 seconds. The [114:17] last [114:18] element is everything at or above the [114:21] maximum. 43 requests slower than 10 [114:24] seconds. The four in the middle are your [114:26] requested buckets. So don't skip the [114:28] outer two. Those tails are usually the [114:30] interesting part that and that 43 over [114:34] here in this example is the problem. The [114:37] next thing I want to tell you is the [114:39] approximate percentile. [114:42] So this is median, the value separating [114:45] the faster half from the slower half. [114:48] Same two-part pattern as before. the [114:51] percentile [114:52] a right here builds the state the [114:56] approximate percentile reads an answer [114:58] out of it and once you have the state [115:02] any percentile is free so for example [115:06] now I'm getting these percentiles the [115:09] 50th percentile 95th percentile and the [115:13] 99th [115:15] percentile so um P95 the 95th percentile [115:20] is the number that matters. This is the [115:22] one you actively want for latency, and [115:24] it's worth uh a little time to explain [115:26] why. An average hides your worst [115:28] experiences. A few very slow requests [115:32] barely move in. The P95 says 95% of [115:36] requests were faster than this, which [115:37] means 5% of your users had a worse day [115:40] than that number. That's the number to [115:43] alert on. Averages make outages [115:46] invisible. Under the hood, there are two [115:48] algorithms. the UD sketch or UD sketch [115:51] and t digest the percentile a picks a [115:55] sensible default and you can choose [115:57] explicitly explicitly if you have a [115:59] reason to okay I'm going to show you [116:01] another thing which is the count [116:03] distinct approximate first I'll make [116:06] sure timing is turned on [116:09] and then we'll select count distinct [116:11] session ID from this is to be requests [116:15] and then we have 250,000 [116:19] And then the time we can see 2,13 [116:23] 34 milliseconds. [116:25] Now instead of doing select count [116:27] distinct, we're going to do select [116:28] distinct count and then do approximate [116:31] count account count distinct with the [116:33] session ID. So you can see this is a [116:39] very close number and it was a way [116:43] quicker it was way quicker to run. So [116:48] that's hyper log log. It's a slightly [116:50] different number tiny state and very [116:53] importantly it's storeable in a [116:55] continuous aggregate. Exact count [116:58] distinct can't be premputed because the [117:00] states can't be merged. The same visitor [117:03] appearing in two buckets would get [117:05] counted twice. Hyperlog log states merge [117:09] correctly. [117:10] So here I'm doing select distinct count [117:13] hyperlo log 1024 with a session ID here [117:16] the first argument trades accuracy [117:19] against size. So more buckets more [117:21] precision more bytes hyper log state [117:24] merge exact ones don't. So what else is [117:28] in the toolkit? So there's a bunch in [117:31] the toolkit uh group by what you're [117:33] measuring. I just want you to review [117:35] them really quick. You can also search [117:36] for them later to learn more. But [117:39] monotonic [117:41] monotonic counters like counter then [117:45] rate and delta on top for anything that [117:47] only goes up by serve energy delivered [117:49] to requests. It handles counter resets [117:53] which is the whole reason you don't do [117:55] this be by hand. Um gauges like gauge [117:59] egg for values that go up and down. Time [118:02] weighted averages. Time weight. If your [118:04] readings are irregularly spaced, a plain [118:06] AVG average overweights the bursts. This [118:10] weights by dur This weights by duration [118:13] instead. State machines. State egg. [118:15] Duration end. State timeline. How long [118:17] was this thing in each state? Livveness. [118:20] Heartbeat. A then uptime and downtime. [118:22] Num gaps. Did this thing stop reporting? [118:26] Finance like candlestick a VWAP. Uh down [118:30] sampling for charts. LTB and ASAP smooth [118:34] draw a month of data in 500 points [118:36] without distorting the shape. And then [118:38] there's the heavy hitters and top K like [118:41] freak a top in, min, max in. It's worth [118:46] considering whether all your statistics [118:48] require exact accuracy or if a small [118:50] margin of error is tolerable. As your [118:52] data grows, the performance benefits of [118:56] precomputing approximate results will [118:58] become increasingly important. [119:01] So I want to close the section on that [119:03] because it's a mindset set shift more [119:06] than a technique. Engineers often want [119:10] exact but nobody makes a different [119:11] decision because unique visitors was [119:13] 48,214 instead of 48,27. So if a half [119:17] percent error buys you a,000fold speed [119:19] up and let you premputee something you [119:22] otherwise couldn't premputee at all, [119:24] take the trade. Just make it [119:26] deliberately and know which of your [119:27] numbers are estimates. [119:30] Now, let's talk about data retention. [119:32] Everything so far has been about making [119:34] data faster. This section is about not [119:37] keeping all of it, and it's more [119:39] important than it sounds. [119:42] So, first, let's talk about what growing [119:44] data actually costs you. Here's what [119:47] happens when a t table grows without [119:49] limit, and it's rarely the thing people [119:52] expect. Disk is cheap, so that's not the [119:55] problem. Your backups get slow and [119:58] enormous every night and you keep [120:02] several restores also take forever which [120:05] matters exactly once at the worst [120:06] possible moment. Your recovery time [120:09] objective your recovery time objective [120:12] quietly becomes a lie and you hit disc [120:14] ceiling um basically at the worst [120:17] possible time. So, every stored backup [120:21] will be large and together can be a [120:24] significant factor in your monthly [120:26] costs, which is an often overlooked [120:28] aspect in cost calculations. [120:31] That's the part people miss. You're not [120:33] paying for one copy of the data. You're [120:35] paying for the live copy plus a replica [120:38] plus however many nightly snapshots you [120:40] retain. One gigabyte of table can be [120:42] five or 10 gigabytes of spend. [120:45] and old data becomes less important. And [120:49] then the observation that makes it all [120:52] tractable, which is the tr the truth is [120:55] that old data becomes less important [120:56] over time. Nobody queries individual [120:59] HTTP requests from 14 months ago. They [121:02] query summaries of 14 months ago, which [121:04] means the raw rows are pure cost with [121:06] essentially no value. And we already [121:09] built the summaries. So you may consider [121:13] adding a background job that can drop [121:15] any chunk whose data is entirely older [121:17] than 3 months. And it's worth [121:20] understanding why this is so much better [121:22] than a nightly delete. A delete of a 100 [121:25] million rows has to find each row, mark [121:27] it dead, write that to the right head, [121:30] write ahead log, and leave the space to [121:32] be reclaimed later by vacuum. It's IO [121:36] heavy. It bloats the table. It fights [121:37] with your live traffic. And afterwards, [121:40] you often need a vacuum full that takes [121:42] an exclusive lock. Dropping a chunk [121:45] deletes a file. The chunk is a table. [121:48] The whole table goes away. No row [121:50] scanning, no dead tpples, no vacuum, no [121:53] blow. It's essentially instant [121:54] regardless of how many rows were in it. [121:57] So chunks are all or nothing. One [121:59] consequence to internalize, a chunk can [122:01] only be dropped when every row in it [122:03] qualifies. With daily chunks and a [122:05] three-month policy, you drop roughly a [122:07] day's worth each day. Tight. With [122:10] monthly chunks, a chunk only goes when [122:12] its newest row crosses the line. So, you [122:15] can carry up to an extra month of data. [122:17] So, chunk interval and retention [122:19] interval interact. If precise retention [122:21] matters for like a compliance deadline, [122:25] your chunks need to be small relative to [122:27] the retention window. So, here's a [122:30] useful pattern in time scale DB. We'll [122:34] put the retention together with the [122:35] aggregate ladder because this [122:37] combination is the single most useful [122:39] architectural pattern in time scale DB. [122:42] Every rung of the ladder gets its own [122:45] retention policy. Fine detail expires [122:47] quickly. Course summaries live a long [122:49] time and they're small enough that [122:51] forever is genuinely affordable. [122:56] So basically you can add your retention [123:00] policy [123:01] every 15 minutes, hourly and daily. Uh [123:05] if for daily you may just want to keep [123:07] things forever. [123:09] So what you can still let's talk about [123:12] what you can still answer after 5 years. [123:14] Uh uh monthly traffic trends u yes from [123:17] daily. Which service degraded in Q3 of [123:20] three years ago? Yep. From daily. What [123:22] happened minuteby minute during the last [123:24] Tuesday's incident? Yeah, from RAW. What [123:26] happened minuteby minute during an [123:27] incident four years ago? No, but [123:30] nobody's asking about that. You get the [123:32] answers people actually need at a tiny [123:34] fraction of the storage. [123:37] But uh one warning is the order matters. [123:39] Uh and the or this will bite you if you [123:43] get backwards. Your aggregates refresh [123:45] window must not extend past your raw [123:47] retention window. If a continuous [123:49] aggregate is configured to refresh [123:51] material older than three months and the [123:53] raw chunks for that period have been [123:55] dropped, the refresh recomputes those [123:58] buckets from nothing and writes zeros [124:01] over the history. So that's what the [124:03] start offset is for. You set it narrower [124:06] than your retention interval. It's also [124:10] why our policies use null earlier. uh [124:13] it's fine on a just a few day demo data [124:16] set but it can be dangerous in [124:18] production alongside retention [124:20] retention. So, the last feature in this [124:22] section is tiered storage. And it [124:25] answers the question you should be [124:26] asking after that retention section. Uh, [124:29] what if I'm not allowed to delete? [124:31] Because that can happen. Financial [124:32] records with a 7-year requirement, [124:34] medical data, anything where an auditor [124:37] might ask a question about 2019. You [124:39] can't drop those chunks. But keeping [124:41] them on fast local storage uh is like if [124:45] when that you're replicating that you're [124:46] backing up nightly is not it doesn't [124:49] make sense for data nobody reads. So [124:52] there's a third tier we've already seen [124:55] two row store for recent column store [124:57] for older tiered storage adds object [125:00] storage s3 class dramatically cheaper [125:02] for the truly like cold stuff that [125:05] you're not going to be using. So the [125:07] most fascinating aspect of tiered [125:08] storage is that you can still query all [125:10] your data. The chunks are still part of [125:12] your hypert. So you don't have to do [125:14] anything different. And that's the whole [125:16] pitch. The chunk moves out of your [125:18] database's local storage and into a [125:20] bucket. And it is still part of the [125:22] hypert. No archive table, no separate [125:25] query path, no restore process. The same [125:28] select reads it. It's slower. It's going [125:30] to going over the network to object [125:32] storage, but it's transparent and [125:34] correctness is unaffected. [125:37] So, uh, what I just talked about is only [125:41] a feature of Tigercloud. It isn't in the [125:44] self-hosted extension you install from [125:46] Docker because it requires the object [125:49] storage infrastructure and the metadata [125:51] plumbing to be managed for you. So I'm [125:54] going to describe the API now and then [125:57] later we'll run it for real on a tiger [125:59] cloud service on real data and see a [126:02] chunk move. So here is the taring API. [126:09] So we can first we're we can add tiering [126:12] then tier chunks older than a threshold. [126:15] Here it's older than a year or we can [126:17] move a specific chunk immediately or we [126:20] can inspect what has been tiered. Select [126:23] everything from time scale DB OSM tiered [126:25] chunks. [126:27] So same mental model as the column sort [126:29] policy a threshold and a background job. [126:35] So uh what should you use retention [126:37] column stored or taring? [126:40] Uh it's basically simple once you frame [126:42] it as a who needs this data and how [126:44] fast. uh if it's queried regularly and [126:48] needs to be fast local storage column [126:51] store if it's older than a day or so. [126:53] Um, if it's rarely queried but must be [126:56] retained, tar it. Uh, cheap, slow, still [126:59] queryable, still part of the table. And [127:02] if nobody will ever need it and no rule [127:04] requires it, drop it. your retention [127:07] policy. And and these compose our the [127:13] our production shape would be raw data [127:16] local for 3 months tiered from 3 months [127:18] to 7 years dropped after seven average [127:21] local the whole time because they're [127:23] small. So the full life cycle. [127:27] So that's the complete picture and I [127:30] wanted to leave it on screen for a [127:32] second because it's the thing that makes [127:34] time scale DB different from bolting an [127:37] analytics database onto Postgress. One [127:39] table, one query interface data [127:42] automatically walking down a cost [127:44] gradient as it ages and finally expiring [127:47] all that declared in about six lines of [127:50] policy. [127:52] Okay, let's talk about all the features. [127:57] Uh just a summary of the features [127:58] basically. So this will um if you [128:02] remember the the shape just like the [128:04] basics you can always look up the [128:05] syntax. Hypert hyper tables partition by [128:09] time automatically and chunk interval is [128:12] your one real sizing decision. Indexes [128:15] work as they always did with a uyuid v7 [128:19] trick to keep them timeordered. Chunk [128:22] reordering existed to give you data [128:25] locality and the column store made it [128:28] obsolete. The column store is the big [128:30] one. 90% smaller vectorzed and segment [128:33] by is the setting that decides whether [128:36] it works. Continuous aggregates [128:38] premputee incrementally, stack into [128:41] ladders and stay live with materialize [128:44] only equals false. Hyper functions give [128:46] you gap filling storeable aggregate [128:49] states and approximations you can [128:51] premputee. Retention drops chunks [128:55] instead of deleting rows. Tiered storage [128:57] puts cold data in object storage without [129:00] leaving the table. You now know more [129:03] about timel than most people running it [129:05] in production. What you don't have yet [129:07] is the muscle memory. A schema you [129:09] designed, a pipeline you wrote, queries [129:11] you you tuned. So the next part of this [129:15] course is focused on building. Okay, [129:19] time to see how to set up our local [129:21] environment. Now a lot of courses put [129:23] setup at the beginning, but putting it [129:25] here provides the necessary context. So [129:27] you already understand the purpose of [129:29] settings like max worker pro processes [129:31] for background jobs. So now we're going [129:34] to turn that basic container into a [129:35] proper environment with a real project [129:38] layout, tuned configuration, seated [129:40] data, and essential to tooling. So [129:43] everything from here on out uses what we [129:45] build in this section. So in the other [129:48] section, I just ran a bare docker run to [129:50] get moving, but let's set it up the way [129:52] you'd actually keep it for a project. So [129:55] uh first we need to be in a terminal and [129:57] create a dedicated directory for this [129:59] course. Then get inside it. I already [130:01] got in there. So now we are going to [130:04] pull the high availability image. So I'm [130:07] going to use docker pole timecale [130:10] timescale db and then h a uh uh colon [130:15] pg18. [130:17] Basically this image right here is going [130:19] to include time scale DB the toolkit [130:22] hyperfunctions and PG vector and then [130:25] you can tag it with the Postgress [130:26] version you want and it could take some [130:30] time to finish installing okay instead [130:33] of memorizing terminal flags let's [130:36] create a configuration in a docker [130:38] composey [130:40] file so I'll just do touch docker [130:43] composey [130:46] yiml [130:47] to create the file. [130:50] Now I'll go to I'll just uh edit the [130:52] docker compose file and I've already [130:55] added this to it. Uh four things are [130:59] worth calling out. These are all the the [131:02] setup stuff. So the t-stune memory tsune [131:06] numbum CPUs [131:08] um the image runs time scale db tune at [131:11] startup which sets shared buffer workmen [131:14] effective cache size and the parallel [131:17] worker settings based on what you tell [131:18] it. So we're going to give it real [131:20] numbers what we actually allocated to [131:23] docker and you you get a sanely [131:25] configured postgress for free. leave it [131:27] out and you get defaults meant for a [131:30] just a really small workstation [131:32] basically. And then we have a named [131:35] volume um time scale db- a and then um [131:42] oh actually the name volume is down [131:45] here. So uh without this the data lives [131:48] in the container's writable layer and [131:50] then vanishes the first time you [131:51] recreate it. So we have the path which [131:54] is where the um the dash ha image keeps [131:59] this data directory. So that's the image [132:01] right up here. Um that's different from [132:05] the plain postgress image and mounting [132:07] the wrong path silently gives you a [132:09] non-persistent [132:10] database. We also have the SHM [132:14] size 1 GBTE. The Docker's default shared [132:18] memory is 64 megabytes. Parallel queries [132:20] use shared memory for worker [132:22] coordination and a big parallel [132:24] aggregate which is most of what we do [132:27] will fail with could not resize shared [132:29] memory segment. So we need to raise it. [132:32] We also have restart unless stopped. So [132:37] it comes back after reboot and you stop [132:39] wondering why your laptop lost its [132:42] database. [132:43] So I can save that and then we'll exit. [132:48] And then we just do docker compose [132:51] up-d [132:55] it. And this is already starting [132:57] actually or it's already started. And [133:00] then we can actually um check the logs. [133:04] Docker compose [133:06] logs. [133:07] Um and then once we see that uh we can [133:12] see that the database system is ready to [133:14] accept a connection. So I'll do Ctrl + [133:17] C. And now we can set the settings that [133:23] matter for time scale. So [133:27] here I'm going to run docker compose [133:29] exec which is going to execute a command [133:31] directly inside the running DB [133:33] container. We're telling it to open psql [133:37] as the postgress use. So we have the [133:40] docker compose exec which is going to [133:42] execute a command directly inside the [133:44] running db container. We're telling it [133:46] to open the the psql as the postgress [133:48] user inside the course db database. So [133:51] now we are inside the um postgrql we can [133:56] start using uh typing commands. So now [134:00] that we have the SQL prompt and we can [134:03] enable time scale DB and the toolkit. So [134:06] we're going to do a create extensions if [134:08] not exist timelb and then also times db [134:11] toolkit [134:12] and then we can do slashdx to see what [134:16] has been enabled here or what we have [134:19] here. [134:21] Now we can review the time scale db [134:24] specific configurations. [134:27] So I'm going to check four critical [134:30] settings that govern govern extension [134:33] performance. We have the max background [134:36] workers that we want at least one per [134:38] policy so jobs don't quue and run late. [134:42] Max worker processes we want to the this [134:46] is the global ceiling covering [134:47] background parallel and replication [134:48] workers. Max parallel workers determines [134:52] how many cores a single big query can [134:54] use. And the shared buffers should be [134:58] roughly 25% of RAM to keep active chunk [135:01] indexes cached. So, uh, depending on [135:04] what your max background workers is, we [135:06] already have ours to the correct one, [135:08] 24, [135:10] but you can change it with alter system [135:13] set and then max background workers set [135:15] to whatever thing you want, which we do [135:17] want 24. [135:19] Now, we can um quit this with / Q [135:24] um because we altered system level [135:26] workers, the database requires a [135:28] restart. [135:29] And now we're going to restart the [135:31] container with Docker Compose restart [135:34] DB. So now we want to configure [135:38] our psqlrc [135:41] file. So we're just going to use cat and [135:44] go right to our file that's going to be [135:48] directly on the local home directory. [135:50] And then psql will read this every time [135:53] it launches. So we're going to make sure [135:55] timing is on here. So every query [135:58] reports duration. So you can track [135:59] performance /x auto. Uh wide rows [136:03] automatically flip to a vertical key [136:05] value layout instead of wrapping into [136:06] unreadable mush on small screens. Uh [136:09] we're going to set the pager to less- [136:12] sfxr. [136:14] This stops line wrapping so you scroll [136:16] sideways and exit immediately if output [136:20] fits on one screen. Then we also have [136:23] this um pet set uh line cell unic code [136:27] and border 2. This adds proper box [136:29] drawing borders for a cleaner look. [136:33] Okay, here's a project we're going to [136:35] build. So, here's the problem for the [136:39] project. So, let's say you've built a an [136:41] AI agent. It calls a model. The model [136:44] asks for a tool. You run the tool, feed [136:46] the result back, repeat until it's done, [136:48] and it works great in development. Then [136:50] it's in production and someone says [136:53] something like our OpenAI build tripled [136:55] or it keeps failing this one customer [136:58] and then you just have logs. You have [137:00] tons of logs, text logs, thousands of [137:01] lines per run. No way to ask a question [137:04] across all of them. So what we're going [137:07] to do is build a flight recorder for [137:11] agents. Basically, it's like the thing [137:13] that aircrafts have. Every step an agent [137:15] takes emits a span, a timestamped record [137:18] of one operation with its duration, its [137:21] outcome, its token count, its cost, [137:23] millions of them. On top of that, live [137:25] dashboards, P95 latency per tool, cost [137:28] attribution per customer, erite [137:31] error rate alerting, and the ability to [137:32] replay an individual run to see exactly [137:34] what happened. So, this is going to be a [137:37] pretty good time scale DB project. Um [137:40] the reason why I picked this is because [137:41] uh agent telemetry has every property [137:44] that makes time scale DB the right [137:46] answer. It's high volume one agent run [137:50] is dozens of spans. It's appended [137:53] mostly. Uh spans are facts and you don't [137:56] update them. Every question is time [137:58] ranged like last hour, this week versus [138:00] last week. It's multi-tenant which gives [138:02] us natural segment by um also old detail [138:07] stops mattering but the summary is never [138:09] due which is the retention ladder [138:10] exactly and every single query is an [138:12] aggregate over window we'll use hyper [138:15] tables uu ID a v7 primary keys column [138:18] store with two segmenting a three-level [138:20] real-time aggregate ladder storing [138:22] stages like stat approx count distinct [138:26] state a we'll also use gap filling [138:28] retention ladder and So basically [138:32] everything about times skill DB in one [138:34] system and you also don't need an API [138:37] key. So here's just a practical note. [138:40] There's a real instrumented agent in the [138:43] repo and I'll show you its code but [138:44] there's also a simulator that generates [138:46] 72 hours of realistic traffic with no [138:49] API key and no cost. So um basically you [138:54] can follow along completely free. So [138:57] let's talk about what the dashboard like [138:59] what data we need what what questions [139:02] does this need to answer for a [139:03] dashboard. So basically because that uh [139:09] the like what the dashboard needs to say [139:10] determines everything downstream. So let [139:13] me list them because these come back as [139:15] literal SQL later. Cost per agent per [139:18] hour. P95 and P99 latency per tool. Air [139:21] rate over time. Which tools fail most? [139:23] Unique sessions per day. the [139:25] distribution of runs of run durations, [139:27] how long a run spends thinking versus [139:29] calling tools and uh replay one specific [139:32] run. So the question two is what is a [139:35] row? This is just about how we're going [139:37] to build our database. So um what's one [139:41] row? The finest useful grain is one [139:43] span, one LM call, one tool call, one [139:46] retrieval. We could go finer like one [139:48] row per token, but that would basically [139:51] be useless. No one asked token level [139:53] questions and it would multiply val [139:55] volume by a thousand it. So corser one [139:59] row per run that loses the ability to [140:03] attribute latency to or to a specific [140:06] tool which is uh question two on the [140:09] list. So one row per span which is [140:11] basically the finest grain that anyone [140:13] actually queries. Next question we want [140:15] to ask about our data is are we going to [140:16] use one table or several? Well, here's [140:19] where I'm going to deviate from what uh [140:23] basically I want to show you the [140:25] reasoning rather than the conclusion. An [140:28] LM call has tokens and a model name. A [140:32] tool call is a tool name and arguments. [140:33] A retrieval as a query and a query [140:35] count, different shapes, but um that [140:39] would sometimes say separate tables. But [140:41] if we look closer, they share 80% of [140:43] their columns. Time, run, project, agent [140:46] name, status, duration. The differences [140:49] are two or three fields each and [140:51] importantly u all the important queries [140:54] like air rate latency and the [140:56] runwaterfall they need to look at all [140:58] span kinds together which would mean a [141:00] three-way union all on every query [141:05] excuse me so we're going to use one [141:08] table with a discriminator so why it's [141:11] okay is because one table with a like a [141:14] span kind column and this the kind [141:17] specific speific extras in JSON B. This [141:19] is the exception to um the rule I talked [141:23] about earlier. And the test for when to [141:26] make the exception is do your queries [141:28] treat these as one thing or as different [141:30] things. So here here they're one thing a [141:33] span that happens to come in flavors. [141:36] Three tables will be normalizing along [141:38] an axis. Nobody queries along. The [141:41] tokens in, tokens out, and cost us [141:43] columns that we're going to create will [141:45] be zero for tool spans. That's okay. [141:48] They're eight bytes of nullish data that [141:51] run length encodes. So nothing after [141:54] compression. So now the table [141:59] and so let's actually see it in the [142:02] file. Okay, this is going to be in our [142:04] migrations folder 002 tables [142:08] and we can see what we have in it. Every [142:11] column here is a decision. [142:15] Uh let me justify the ones that matter. [142:18] So we have timestamp TZ. That's the [142:21] first column. That's the um partitioning [142:23] key right here. And agents run in every [142:27] time zone. And there's no upside to [142:28] ambiguity. [142:31] And we this the span ID is a UU ID v7. [142:34] So we see that here. Um agents generate [142:38] span ids client side. They have to [142:40] because the span exists before it's [142:42] written and children reference their [142:43] parent. So we need globally unique IDs [142:45] generated without a round trip. And u v7 [142:49] means they're timeordered. So the index [142:51] stays compact and appends to the right [142:53] edge instead of scattering rights across [142:56] the whole B tree. random UU ID V4s here [143:00] would be a genuine performance problem [143:02] at volume. We also have the primary key [143:06] span ID. Um so the partitioning column [143:10] must be in the primary key time first. [143:13] So the index is useful for range scans [143:15] on its own. Then we have the parent ID [143:19] here. Um that's a tenency column. I mean [143:23] no the the parent ID makes the span [143:26] makes the spans a tree which is what [143:28] lets us render a waterfall. It's the [143:30] project that's the um the dependency [143:34] column. So therefore our segment by was [143:38] we're going to use this for our segment [143:40] by low cardality dozens maybe hundreds [143:43] of projects every dashboard query [143:45] filters on it. And then we have the [143:49] status [143:51] um that is going to be a text rather [143:54] than a boolean because there are more [143:56] than two outcomes. Okay, air timeout [143:58] refused. That distinction matters and [144:00] refusal isn't a failure of your system. [144:02] And then we have the um attributes which [144:06] is a JSON B. It's the long tail, the [144:08] model's temperature, the tools argument [144:10] shape, the retrieval result count. So [144:13] basically things we might look at but [144:14] never uh uh that we're not going to [144:17] filter on daily. So uh now the [144:20] arithmetic about this chunk interval. [144:23] So the a busy agent platform say 200 [144:26] agents runs a minute 30 spans each. [144:29] That's 6,000 spans a minute. Call it 8 [144:33] and a half million spans a day. Our row [144:35] is roughly 160 bytes with the JSON being [144:38] mostly empty. So about 1.4 4 gigabytes [144:41] of a table a day plus ind indexes about [144:44] 1.8. Index volume is the number that [144:47] matters most. Roughly 400 megabytes a [144:50] day. On a 16 GB instance, 25% is 4 GB [144:54] for the active chunks indexes. A day of [144:56] indexes is well inside that. A week [144:58] would be under 3 gigabytes. Also fine [145:01] actually. So why daily instead of [145:05] weekly? Well, retention granularity. [145:08] We're keeping rows raw spans 14 days [145:12] with weekly chunks. A chunk can't be [145:14] dropped until its newest row is 14 days [145:17] old. So, we'd carry up to 21 days. With [145:21] daily chunks, retention is precise and [145:24] chunk count is bounded. Uh 14 days of [145:27] raw data is 14 chunks and nowhere near a [145:30] thousand. So, daily chunks when [145:32] retention is tight, chunk interval [145:34] follows retention. So I'm creating a new [145:36] file uh hyper tables. So we'll create [145:39] this hyper table agent span in our hyper [145:42] tables file. And then [145:46] um I want to talk about the metadata [145:48] tables. So these are just basically two [145:52] small relational tables because um we'll [145:56] need these for what we're doing and [145:59] these are already in the um table file. [146:03] So create project create model price. So [146:06] we have monthly budget USD it's numeric [146:10] because it's money and we have the [146:13] duration milliseconds. [146:15] Oh I mean and then we also have the the [146:17] model price. Now this is just an example [146:21] here because um this is going to change [146:25] but we can store the price table for [146:27] reference but we do have to we we'll [146:29] compute the the cost at right time and [146:34] store in on the span and then we're also [146:37] going to have some examples for um [146:40] an another uh function or to insert into [146:45] these items into the pro into the [146:48] project. So we're going to have just [146:51] three indexes. So we'll add this to our [146:54] indexes file. So I create this indexes [146:57] file and we have agent span or we have [147:01] create index on agent span uh the [147:03] project. Uh basically this is for the [147:07] dashboard's main path. Then we have it [147:10] time descending. Then we have the run id [147:13] end time for the run replay which is the [147:17] which is a point lookup by run and then [147:20] a partial index on failures only because [147:25] errors are a small fraction of rows. So [147:27] an index covering only them is tiny and [147:30] show me recent errors is the single [147:31] mostrun query in any observability tool. [147:35] So I'm not creating an index on spank [147:37] kind or on status generally. [147:40] Uh it's low cardality and after the [147:42] column store conversion those become [147:43] batch skipping metadata anyway. So one [147:47] more table which is going to be runs. So [147:49] I'll create this runs.sql file. So we're [147:53] going to keep track of the agent runs. [147:55] So we have span count and cost USD are [148:02] derable from the spans. But I'm storing [148:04] them anyways. [148:06] And because uh the run list is the most [148:09] loaded screen in the whole app and I [148:11] don't want to aggregate a million spans [148:13] to render it. So we write once at run [148:17] close and then we can read that forever. [148:20] Okay, the scheme is done. Now something [148:22] has to write to it. So I do want to be [148:26] clear about what we're building because [148:28] the instrumentation pattern here is the [148:31] reusable part. [148:33] It works for any agent framework and [148:37] really for any event stream at all. An [148:39] agent does work. Every unit of work is a [148:41] span, an LM call, a tool call, a [148:44] retrieval. We wrap each unit, time it, [148:47] record that what happened, and hand it [148:49] to a buffer. The buffer flushes to [148:51] PostSQL in batches. The agent never [148:54] waits on the database. [148:56] So, I just made this recorder.py file. [148:59] And remember all this code. You can [149:01] check out the link in the description to [149:02] get this code. So this is a process [149:06] queue and the col the column list once [149:11] it's once so the writer and the recorder [149:14] can't drift apart. And if we go down we [149:19] can see this span function. Here's the [149:23] things I want you to notice. We have [149:25] this use UID7 [149:27] and then we're going to take the a [149:30] timestamp here and remember the check [149:32] constraint on the table the UID [149:35] timestamp span ID equals time. So [149:38] remember this check constraint on here's [149:40] the check constraint on the table uids [149:43] matches time. So that's why I'm doing [149:48] this. If if I had generated UI ID from [149:51] now and the time stamp from a different [149:53] now and those two would differ micro by [149:56] microsconds and every in insert would [149:58] fail the check. So we just generate the [150:00] time stamp once and then we derive both [150:02] from it. So this helps and then we have [150:07] this finally block and the span gets [150:11] recorded whether the work succeeded, [150:13] failed or blew up. observability [150:16] that only records successes is worse [150:18] than none because it can make you too [150:21] confident. And then we can see that the [150:24] exception handlers [150:27] raise we classify the failure and then [150:30] get out of the way. A recorder that that [150:33] swallows your exceptions is uh not good. [150:37] So we have a copy batch function and [150:41] down here we have the writer loop [150:43] function. [150:44] So basically we're going to write a [150:45] batch with copy and this is you can just [150:49] use either copy or insert. Basically [150:52] it's just going to write right into our [150:53] database. And these are all helper [150:56] functions [150:57] or that we'll use later. But one thing [151:00] to kind of focus on is our batch roads [151:04] and batch seconds. So this is how long [151:07] we're going to just flush everything. So [151:10] the flush uh condition is the the do [151:14] line which is just right here in the [151:17] writer loop. So the length uh we're [151:21] we're using the batch rows or the batch [151:23] seconds as we just talked about. And so [151:26] it's either going to be 500 rows or 1 [151:28] second whichever one comes first. A busy [151:32] system in a busy system you get full [151:34] batches and maximum throughput. But in [151:36] quiet a quiet system, you still get your [151:38] data within a second. Without the time [151:41] limit, a low traffic agents spans would [151:44] sit in memory indefinitely and your [151:47] dashboard would look broken. And note [151:50] the reconnect on failure down here. It's [151:55] going to reconnect on failure because [151:57] background writers must survive a [151:59] database restart because they will [152:02] experience one. [152:04] So basically together the copy batch and [152:07] the writer loop functions here [152:10] decouple the caller's execution speed [152:13] from database latency [152:15] which can transform thousands of tiny [152:18] events into massive bulk writes. And to [152:22] kind of give a little more information [152:23] about the the writer loop, this acts as [152:27] an asynchronous background worker that [152:30] decouples the main thread from database [152:32] writes and it buffers events in memory [152:34] until they hit a specific size or time [152:37] threshold. So you can see it has a [152:40] non-blocking architecture [152:42] and uh we already talked about how the [152:44] dual trigger batching with by size or [152:47] time [152:49] and the to kind of just talk a little [152:52] bit more about the copy batch. This [152:54] streams in an entire batch of in-memory [152:56] rows directly into time scale DB using [153:00] PostgresQL's native streaming protocol. [153:03] That's the the copy that we talked about [153:07] and it by bypasses the standard SQL [153:10] parsing overhead. Now copy just to talk [153:14] a little bit more about copy which is [153:17] right here the copy command. Uh it's [153:20] pretty important for time scale DB [153:22] because time scale DB hyper tables route [153:24] data into underlying chunk tables based [153:27] on time partitions. So using copy feeds [153:30] rows directly into the storage engine in [153:32] bulk allowing times table DB to handle [153:36] thousands to hundreds of thousands of [153:38] rows per second with minimal lock [153:40] contention. Now we'll come to back to [153:42] this file but I'm going to create [153:44] another file pricing and just paste in [153:47] code. Now we already saw our pricing in [153:50] our tables where we're adding some [153:52] pricing in there but um here we're just [153:56] kind of hard coding these in again. Now, [153:58] these numbers are illustrative and they [154:00] will basically be out of date by the [154:02] time you watch this. So, you're going to [154:04] have to actually just check the vendor's [154:06] page for real ones. But what matters is [154:08] the shape. Input and output are priced [154:11] differently. Output usually costs [154:12] several times more. And that the [154:15] asymmetry is why make the model more [154:17] concise is a real cost lever. Now, we're [154:20] also going to have a file called [154:21] agent.py. [154:24] Now, this is a small tool using agent uh [154:27] with three tools. Now, we're not going [154:29] to go through all of these because we're [154:32] mainly focused on the database section, [154:35] but we have a a web fetch, a calculator, [154:39] and a database lookup. And it fails [154:43] about 8% of the time on purpose. [154:46] That flaky tool is basically a valuable [154:49] line of code in this project because it [154:51] manufactures the errors that make the [154:53] air rate dashboard [154:55] show something. Now, this is going to [155:00] basically require an OpenAI API key, but [155:03] you can uh test this out without one by [155:06] just using the simulate.py file which is [155:10] going to be in the repo. And if we go [155:13] here on the run um we can see that this [155:17] is the whole integration surface with [155:21] one width block around the call we [155:25] already making with three assignments [155:26] from the response. Tool calls are the [155:29] same shape with kind equals tool. Here's [155:33] the kind equals tool. So basically we [155:36] are adding either the LM calls or the [155:39] tool calls to our database. And here's [155:43] our simulate.py that's just going to [155:46] simulate if we don't have an API key. [155:50] I'm going to just run this simulate file [155:52] to get in some data here. But first we [155:56] actually have to make sure our database [155:58] is set up correctly. So I already have [156:02] my Docker container running and then I'm [156:07] going to create my database docker [156:09] compos exec and then we're going to run [156:12] psql and then we're going to create the [156:15] database [156:17] flight rack. That's the one that our [156:20] code uses. And then we already created [156:22] all those migration files. [156:25] So, I'm just going to create this loop [156:28] that's going to run every one of the [156:31] migration files. Okay, I got everything [156:34] created. Now, I can [156:37] run our command, our simulate command. [156:41] So, this is going to take a little bit. [156:44] And it's not just creating random noise, [156:49] which would teach us nothing. There are [156:50] four things built in. It's basically [156:54] creating three projects, eight agents, [156:56] 72 hours of history at 40 spans a [156:59] second. So we have this simulated data [157:03] and it's going to have this dal pattern. [157:07] Basically traffic peaks midday, troughs [157:11] at night. Any real dashboard would show [157:13] this. And if your test is flat, you [157:15] won't notice when your gap filling is [157:16] wrong. It's also going to use a [157:18] realistic latency distribution. Um the [157:22] it's log normal not uniform. Most cells [157:25] fast a long right tail and that tail is [157:28] why the P95 exists. Also air bursts [157:30] rather than evenly sprinkled failures. [157:32] Real systems fail in clumps and one [157:35] planted incident. The specific tool gets [157:37] dramatically slower for a 40minute [157:39] window at a known time stamp. [157:42] And uh in the query section we're going [157:45] to find it using nothing just using [157:47] aggregate queries. and then we can [157:49] explain it from raw spans. So, um I [157:52] would recommend using simulated the [157:55] simulated data if you're following [157:56] along. Okay, I've created the data and [157:58] now I'm going to go run go where we can [158:02] access the database which is called [158:04] flight wreck. Okay, let's just count [158:07] everything here. And so we now see that [158:10] there are over 9 million spans [158:14] and we have a size of 48 kilobytes. [158:19] So 3 days of history but nothing is [158:22] tuned yet. So let's fix that. Okay, we [158:27] are going to make it faster. So baseline [158:30] first. Never optimize before you've [158:33] measured the thing you're optimizing. So [158:35] let me turn on timing. I mean, I should [158:38] already be on, but we'll just make sure. [158:42] Now, we're going to do select project [158:43] agent. We're going to count uh [158:45] everything as spans, then get the [158:47] average duration, the we're going to get [158:50] the cost uh everything in the last 24 [158:54] hours. Okay. So, here is our result [158:58] here. The projects agent spans and we [159:02] can see how many spans are for each [159:04] project and agent. And this took 665 [159:09] milliseconds. [159:10] So we will compare that after we make [159:13] some changes here. So now I'm going to [159:16] set create a new migrations file and [159:18] we're going to set up the column store. [159:21] We're going to go straight to nine and [159:22] we're going to fill in the rest later. [159:25] Okay. So here's our column store file [159:29] and let me show you what how we're [159:32] setting this up. So we have segment by [159:35] project and agent. Project is the [159:39] tenency column. Every single query [159:41] filters on it. Agent is the second most [159:45] common filter and there are eight of [159:46] them. So batches stay comfortably over a [159:48] thousand rows. I did not use name even [159:52] though we filter on it a lot. There are [159:55] a dozen of of there's a dozen of tool [159:57] and model names and combined with [159:59] project and agent that would shatter the [160:02] batches into fragments. We also have [160:05] order by here and we are using time [160:08] first always because every query has a [160:10] time range and that gives us minmax [160:13] batch skipping and then we have span [160:16] kind. So lm span and tool spans cluster [160:20] separately. Uh most panels ask about one [160:23] kind at a time. And then we have the [160:26] compress index with the bloom and [160:29] minmax. A bloom filter on name because [160:32] show me spans for the DB lookup tool is [160:34] an equality check and name isn't in [160:36] segment by minmax on duration ms because [160:41] spans slower than 5 seconds is a range [160:43] filter and that's literally how we we [160:46] hunt the planted incident that we will [160:49] use later. And remember this rule there [160:52] are builtin conversion times. So set [160:56] them now before we convert anything. [160:59] So we have this column store policy. Um [161:04] for interval is two hours not one agent [161:06] spans can arrive late and a run that [161:08] hangs for 10 minutes uh writes its [161:11] parent span 10 minutes after it started. [161:13] So I want a wider grace window on the [161:15] row store side and because our and then [161:20] I also create some indexes here [161:23] materialized view and a column store [161:26] policy. So I'm going to set this up and [161:29] we're going to do a test. So first I'm [161:31] going to run the migration file and [161:34] we're going to create these later. So [161:36] I'm not going to worry about that. And [161:38] then I'm going to go back into psql [161:41] and because the data is historical we're [161:44] going to force the backlog. So convert [161:47] to column store anything older than uh [161:51] from show chunks the agency older than [161:54] two hours. So first I'm going to try to [161:57] find the job ID and then we can run it. [162:02] So we did this because both convert to [162:04] column store and run job are postgress [162:06] procedures rather than plain functions. [162:08] So we can invoke them with the call [162:11] command. And this basically kicks off [162:13] the backlog conversion. And then we'll [162:16] run the same command we ran before we [162:19] set up the column store. Okay, now it's [162:23] only 215 milliseconds. So we've saved [162:26] some time. We got the same query, same [162:29] answer, dramatically faster and a [162:32] fraction of the disk. So that's one [162:34] alter table and one policy. But we can [162:36] do better than a faster scan because we [162:38] shouldn't be scanning at all. Okay. Now [162:41] I just created this new file 06 span 5 [162:45] minutes and we are going to be talking [162:49] about hyperfunctions. [162:52] So we're basically creating a [162:54] materialized view. [162:57] This is going to basically be the last [163:00] five minutes. And if we look at the [163:03] aggregates list, [163:05] every entry is a very deliberate choice. [163:08] We have the count and we have a filtered [163:12] count here. And they are non spans. So [163:16] two integers and from them the error [163:19] rate of any granularity [163:21] because counts will reagregate. [163:24] And then we have these sums here. The [163:28] sum of the cost and the tokens sums [163:32] reagregate. Then we have the states. We [163:36] have um basically three states. We have [163:40] the stats. A is the first state for [163:44] average and standard deviation. Then the [163:47] percentile egg for P95 and P99 [163:52] and approximate count district for [163:55] unique sessions. None of those could be [163:58] stored as finished numbers. Averages an [164:02] average of averages is wrong. [164:03] Percentiles percentiles is meaningless [164:06] and count distinct double counts across [164:09] buckets. stored as states all three will [164:11] merge correctly. There's no average [164:13] column anywhere in this view. [164:16] Then if you scroll down here we are [164:19] going to do a add continuous aggregate [164:23] policy but the schedule interviewable is [164:27] 1 minute. This feeds an ops dashboard in [164:30] offset the end offset is 10 minutes [164:35] which is twice the five minute bucket [164:37] per the rule. The start offset you can [164:39] see is seven days and that one is not [164:43] arbitrary. Uh raw spans are retired are [164:47] retained 14 days. So a 7-day refresh [164:49] window is safely inside that. So it's [164:53] basically a rung rungs on a ladder from [164:57] 5 minutes hourly and daily. [165:01] So we are each row above is built from [165:04] the run rows below. And so we're going [165:07] to create the other ones now. So here's [165:09] my span hourly. So we're going to have [165:13] these sums [165:16] and we are going to roll up the states. [165:19] And that's pretty much it. So the file [165:23] is um not very long because it's built [165:28] from the previous file. [165:31] And now I have my span daily file. And [165:34] you can get all this code um from the [165:40] uh GitHub repo. So we've dropped name at [165:44] the daily level. [165:46] So because nobody's going to ask which [165:48] individual tool was slow on a yearly [165:50] cost chart. And then this is dropping [165:53] name is going to collapse the row count [165:55] by more than an order of magnitude. [165:59] So that's going to have coarser [166:03] dimensions. [166:05] And now we can actually use these in a [166:08] file that we've already created, the [166:09] column store file. So that's what um we [166:14] got here. So here we're actually using [166:18] these files. We're creating an index [166:21] using these uh things that we just [166:23] created and we're creating a [166:25] materialized view and then the column. [166:29] store. So the these are basically all [166:30] using these files that we just created. [166:33] And now I'm going to make one final [166:35] migration for retent for retention and [166:39] we're going to create the retention [166:41] policies. [166:42] Now I'm going to run this command in my [166:44] terminal to run all the migration files. [166:48] Some of them already exist so won't [166:49] create them again. Now go into the psql. [166:54] Now let's look at what this aggregate [166:56] ladder actually costs on disk. The one [166:59] we just set up. But to do that we have [167:01] to write a slightly weird query. So [167:04] before I run this let me explain why we [167:07] can't just ask for the size of the views [167:09] directly. When you write a continuous [167:12] aggregate time scale DB creates a hidden [167:14] internal hyper table behind the scenes [167:16] to store those precomputed buckets. If [167:19] you run a standard Postgress size check [167:20] on span five min, it misses the actual [167:23] data. So this query does a union. The [167:27] top half gets the size of our raw agent [167:30] span hyper table. The bottom half [167:32] queries time scale with DB's metadata to [167:34] find the system generated names of those [167:37] hidden hack hidden backing tables. It [167:40] circulates it calculates their true [167:42] sizes and stacks them together so we can [167:45] compare them side by side. So I'm just [167:48] going to run this and then here we get [167:51] the results here. So look at the size [167:53] gradient. So the it goes from 494 106 [167:57] 49. So it's slowly going down and then [168:00] we expect the daily to be the largest [168:02] here. So this is basically the whole [168:05] architecture in this one output. Our raw [168:08] data is almost 500 megabytes, dropping [168:11] down to five minute buckets, cuts down [168:15] to roughly a fifth of the size. An [168:18] hourly is a fraction of that. And the [168:20] daily summary, which contains exactly [168:22] what we need for a year-over-year chart, [168:24] is half a megabyte. It's kind of just [168:28] like a rounding error on your disk. So, [168:30] raw data is big and shortlived, but [168:32] summaries are small and live forever. [168:35] Now let's ask it some questions. So [168:38] let's find the unique sessions per day. [168:42] So we can see there's 900 on each of [168:45] these days. And it took us 29 [168:48] milliseconds here. And we're going to [168:50] count it in a different way here. And [168:53] this time we're getting basically the [168:54] same number of unique sessions, but it [168:56] took a lot longer. [169:00] Uh 300 3,647 milliseconds. [169:05] That's because this first example ran [169:07] our aggregate ladder which is what we [169:11] created to make things faster. The [169:13] distinct count rollup sessions. [169:17] Uh so remember when we built the five [169:20] minute aggregate we didn't save a number [169:22] for sessions we saved the state using [169:24] approx count distinct under the hood. [169:27] That creates a hyperlo log sketch. The [169:30] roll-up function merges those five [169:32] minute sketches into daily sketches and [169:34] distinct count extracts that final [169:36] number. And so this was the more [169:39] traditional SQL way of getting the data [169:42] where we got the same data but it took a [169:45] lot longer. And the the speed isn't [169:49] actually the most important difference [169:50] here though. It's that the fast query is [169:53] architecturally possible at all. You [169:56] cannot premputee a standard count [169:59] distinct into a materialized view and [170:02] then roll it up later. If a user logs in [170:05] during the 9:00 a.m. bucket and and the [170:07] 10 a.m. bucket and you try to sum those [170:10] buckets later, you double count that [170:12] user. The only way to get an exact [170:15] distinct count across a wider time [170:17] window is to go all the way back to the [170:19] raw data and count again. Hyperlog log [170:23] states solve this. The sketches can be [170:26] merged together mathematically without [170:28] double counting. This is the difference [170:30] between a metric you can put on a fast [170:33] live dashboard and one you can't. If you [170:36] need the exact number for a monthly [170:38] invoice, run the slow query once a [170:41] month. For a dashboard that uh nobody [170:45] stares at to six digits because it [170:48] doesn't have to be so exact, the hyper [170:50] log log estimate is going to be a good [170:52] engineering call. [170:54] So everything so far has been aggregate [170:57] queries. But when an agent misbehaves, [171:00] you need to see the actual sequences of [171:02] what it did. And so that's basically the [171:05] raw hyper table where the full fidelity [171:07] data still lives. Okay, I'm looking for [171:10] when the project is acme and the status [171:13] is error and I want to get one of these [171:16] run IDs. So the point of this is we're [171:19] trying to find the actual sequence of [171:22] what a specific agent did that had an [171:25] error that misbehaved. [171:27] So we need to go to the raw hyper table. [171:30] And so this is one of the runs that [171:32] failed here. So let's try to reconstruct [171:35] its exact execution waterfall. [171:39] So I'm going to paste in some SQL code [171:42] that we're going to do. So let me break [171:44] down what this query is doing. To draw a [171:47] waterfall UI, we need to know what [171:50] called what and we do that with the [171:53] union all. So um the top half grabs the [171:58] the root the root span which is like the [172:01] main entry point for the agent where [172:03] parent ID is null and it assigns it to a [172:06] depth of zero. The bottom half grabs all [172:09] the child operations where parent ID is [172:11] not null and assigns them a depth of [172:14] one. Then in the outer select [172:17] we use [172:19] repeat depth. [172:22] I mean in the outer se select we use the [172:24] repeat coales depth zero to visually [172:29] indent the child spans under the parent. [172:32] The coles is just as there's a [172:35] safeguard. So if the depth ever somehow [172:39] evaluate to null, it would default to [172:41] zero instead of wiping out the entire [172:44] text string. So let me run that. And we [172:48] can see here there's the run perfectly [172:52] ordered by time. The LM thinks, it [172:57] decides to do a tool call. The tool [173:01] fails, it retries, it thinks again, and [173:03] it finally answers. [173:05] This is in indented timed and cost cost [173:09] wait where we can see all these things [173:10] the costs that's the the flight recorder [173:13] doing exactly what the name promises and [173:16] notice is notice the speed we just [173:19] pulled a needle out of a haststack of [173:21] millions of rows in milliseconds this is [173:24] because back in the schema section we [173:25] created an index on the run ID and time [173:28] scale DB uses that index to bypass the [173:31] partitions entirely and jump straight to [173:34] this part specific run. So this is a two [173:37] a two-level workflow. This is the [173:39] operating pattern for the entire project [173:41] and for uh most observability [173:46] systems. Aggregates define the problem [173:48] raw data to explain it. And so it's fast [173:51] and cheap on the wide question and full [173:53] fidelity on the narrow one. Neither one [173:56] alone is enough. So we just looked at a [173:59] waterfall of individual spans, but often [174:01] you just want a simpler answer. a simple [174:04] you want to answer a simpler question. [174:05] What is the actual bottleneck of this [174:07] run? Is the LM slow or are the tools [174:10] slow? So here's some more SQL here. [174:14] Okay, let me put in some new SQL here [174:16] that I've already run. Notice that last [174:21] line of SQL. Basically, actually I'm [174:24] talking about this one right here. We [174:26] are doing a sum. We're doing a sum of a [174:29] sum using a window function. The inner [174:33] sum gets the total time for that [174:34] specific span kind. The outer sum with [174:36] over calculates the total time for the [174:38] entire run across all kinds. We cast it [174:41] to numeric to run it cleanly and [174:43] dividing them gives us a perfect [174:45] percentage without having to write a [174:47] subquery. [174:49] For most agent runs, the answer [174:51] surprises people. The model isn't the [174:55] bottleneck. The tools are, which [174:59] fundamentally changes what you'd [175:00] optimize. No amount of prompt [175:02] engineering fixes a slow database [175:03] lookup. Now, if you want to track this [175:06] over time across thousands of runs, [175:08] doing that math on the fly gets heavy. [175:10] So that's why we used the state egg [175:12] hyper function, which models exactly how [175:16] long a system spent in discrete states. [175:19] And let me show you that we are doing a [175:24] group by in the run ID run. We're doing [175:28] group by run ID in this sub query state [175:31] a models a single state machine meaning [175:33] a system that can only be in one state [175:35] at a time. If we tried to aggregate the [175:37] entire project into our timeline, the [175:39] database would throw an error the moment [175:42] agent A started an LM call in the exact [175:44] same microscond agent B started a tool [175:47] call. So by grouping by run ID, we [175:49] correctly treat each agent run as its [175:51] own independent timeline. Once those [175:53] individual timelines are built, we use [175:55] duration n to extract the micros [175:58] secondsonds spent in each state and [176:00] clearly sum them up across the whole [176:02] hour. Build the state and read the [176:04] answers. Okay, now let's uh test [176:08] everything out. I planted an incident in [176:10] the stimulated data. One tool got [176:12] dramatically slower for about 40 [176:14] minutes. So I know when but and but now [176:17] we're let's see how we would find find [176:19] it in the way you'd find a real one with [176:21] no idea what we're looking for. starting [176:23] broad and zooming in. So the first step [176:25] is is anything wrong at all? So we're [176:30] going to start with the widest possible [176:31] view using only aggrits aggregate uh [176:34] hourly aggregates. [176:36] So this is going to um [176:40] give me all some information. [176:44] We are selecting um basically for every [176:47] we're getting these 1-hour buckets and [176:51] we are looking for the project acme and [176:53] the scan of tool. So we can see a few of [176:57] these [176:59] are a little different than the others. [177:02] Specifically a lot of them are 1843. [177:06] This one's 1729. This one's 1729. [177:09] So, I'm going to look at all these [177:12] uh in uh all this P95 milliseconds and [177:16] we can see uh actually took me a little [177:20] bit to find it, but it's like you can [177:21] see this one is well above all the other [177:27] ones. But if we look at the spans column [177:30] traffic, it looks pretty normal. So, [177:34] this isn't a load spike. something [177:36] specifically got slower. So step two is [177:39] trying to figure out what tool. So let's [177:42] keep the hourly window, but now we're [177:44] going to group by name. Okay, I'm [177:47] running this code to like look at the [177:49] different tools. And we see web search [177:52] is taking over 10 seconds while [177:54] everything else is operating normally. [177:58] So now the next step, step three, is [178:01] trying to find out exactly when did it [178:03] start and stop. So, I just ran this code [178:07] here and we're going to drop down a rung [178:10] on our aggregate ladder to the five [178:12] minute buckets to get precision. So, [178:16] we're doing from span 5 minutes and we [178:19] can see that most of these numbers are [178:21] under 3,000 milliseconds. [178:24] But then right around here, we can see [178:26] it shoots up. So, now we have a tight [178:29] window. And notice the shape of the [178:33] recovery because that shape is [178:35] diagnostic. A cliff edge means a deploy [178:39] or a config change rolled back. A [178:42] gradual ramp means the satur means [178:45] saturation recovering. A Q draining a [178:48] connection pool freezing up. So now then [178:51] step four the next step is to explain [178:53] it. So now and only now we drop down to [178:56] the raw spans. But because we marked [178:59] down the time window and the tool name, [179:01] this query will be instant. So we do [179:04] this query with the tool name and then [179:08] the time that we found out from the last [179:11] query. And look what we see here. So now [179:14] we can see the exact root cause sitting [179:18] in the JSON uh attributes payload. Read [179:22] time of upstream did not respond in 10 [179:24] seconds. [179:25] uh the web API went down. The Python [179:29] agent waited the full 10-second timeout [179:31] limit before giving up, which caused [179:33] every single run in that window to stall [179:35] for 10 seconds. So, let me just name [179:39] what happened because it's the thesis [179:41] for a lot of this course. So, we had [179:43] three days, millions of spans, [179:46] four queries, each returning in [179:47] milliseconds. We walked from something [179:49] feels wrong to here is the exact tool, [179:51] the exact 35m minute window, the exact [179:53] error message from the HTCP client. The [179:56] first three queries read premputed [179:58] aggrids. Only the last one touched the [180:00] raw data and it touched a tiny slice of [180:03] it. That is what a lot of these features [180:07] are for. The ability to ask a chain of [180:09] questions fast enough that you keep [180:11] asking them instead of giving up. Okay, [180:13] time to put a face on it. We have the [180:15] queries. Now let's them wrap them up in [180:17] an API. So we can actually look at a [180:19] dashboard. This is where continuous [180:22] aggregates stop being a database concept [180:23] and start being visibly obviously [180:26] useful. So the the design rule for the [180:29] entire backend is strict. One endpoint [180:31] per panel, one SQL query per endpoint [180:34] and that query hits an aggregate. No [180:36] RMS, no query builders, no fetching [180:38] thousands of rows to join them in [180:40] Python. The database does the math and [180:42] the API just delivers the JSON. So we [180:45] have this just to set up our API file [180:49] and here is where we are starting our [180:54] connection basically to get get to get [180:57] all the data. Now production we we use a [181:02] scopy pool not a connection per request [181:06] but we're we're just uh we're not going [181:08] to detour into pooling right now. And [181:11] then we have our API endpoints. We have [181:13] our health endpoints which is this [181:16] liveness probe. Here's one of our main [181:19] panel or endpoint for our main panel. Um [181:23] the cost. So that's the exact qu that's [181:25] the query we wrote in psql [181:31] par parameterized cleanly using scop g's [181:36] named parameters. [181:38] Every other endpoint ex is exactly the [181:41] sha the same shape. [181:43] So I uh and these endpoints don't touch [181:47] raw data. So before we look at the [181:50] dashboard, let's verify that claim. But [181:53] uh let me just show you the other [181:54] endpoints. We have the latency endpoint. [181:57] And we can see a lot of these are what [181:59] are commands that we've already ran that [182:02] I showed an example, but now we're just [182:03] putting them into the API. So that's for [182:05] the errors. And then we have the top [182:08] failures [182:10] sessions, how many sessions per day and [182:13] you can see this is all just raw SQL [182:14] using everything we have been learning. [182:16] Then we have histogram and remember you [182:20] can see all this code yourself right in [182:22] the the code link in the description. We [182:25] also get the the information for one [182:29] run. So, I'm back in psql and then I'm [182:32] just going to run this query to see that [182:35] it doesn't touch the data. Okay, let me [182:39] zoom out a little bit so it doesn't go [182:41] over so much. [182:43] Basically, in this query plan, it pulls [182:47] a handful of chunks from span hourly. It [182:49] scans a few hundred rows. There's no [182:52] reference to agent span anywhere. The [182:55] raw table has millions of rows and this [182:57] dashboard query literally does not even [183:00] know it exists. A dashboard whose [183:03] computational cost is proportional to [183:05] what it displays rather than to how much [183:07] data you have ever collected. It's [183:10] better. Your dashboard is just as fast [183:13] on day one as on day 1. Okay. Right now [183:16] I'm in staticindex.html [183:20] and we are going to basically look at [183:24] the front end. So the front end is just [183:25] one HTML file. If we go down, we can see [183:30] that in our code, we fetch the [183:34] endpoints. [183:36] Um, we draw the charts and we set a loop [183:39] to refresh every 5 seconds. Now, I'm not [183:42] going to go through this in detail. This [183:45] is not an HTML JavaScript course, but [183:47] you can look through the code of in the [183:50] repo if you want. Okay, it's time to [183:54] actually [183:56] try this out and actually run this demo. [184:00] So, this is the thing that we've been [184:02] building for a while. So, I'll just run [184:05] uicorn and it'll run the application. [184:09] Okay. And we have it running down here [184:10] the terminal. But now we can we can see [184:13] this. So we have all this information [184:16] about the spin the spin spans air rate [184:20] worst P95 airate lag and then look at [184:24] these charts can go down tool latency [184:27] percentage we got the error rate latency [184:32] distribution [184:34] we got the top failing tools [184:37] and check this out I can change the [184:40] window so we went from 24 hours let's go [184:43] change it to 72 2 hours and we can see [184:45] the charts change in real time. Oh, and [184:49] when we change the 72 hours, we can see [184:52] we got a big error rate right here. So, [184:55] we can even zoom in or zoom out with our [184:57] mouse wheel on here. And it's really [185:00] nice to have the this top failing tools [185:03] thing here. We can also change the [185:05] different projects as well. And right [185:09] now we're not getting new data, but [185:13] we're going to simulate giving getting [185:14] live data. So I'm just going to open up [185:17] a new terminal window and I'm going to [185:20] run Python simulate live rate 60. [185:24] Let me change the window just last six [185:27] hours. Now let's just watch the right [185:30] edge of these charts. [185:33] So now we can see data coming in here. [185:35] So, we didn't have data between this gap [185:39] here, but now we have the live data [185:42] coming in and we can see the charge [185:45] updating in real time. So, the refresh [185:48] job runs every minute. So, the last [185:52] minute of data has not been material [185:56] materialized. That data is coming from [185:58] the live half of the real time [186:00] aggregate. time cell DB is reading the [186:03] newest rows straight from the raw hyper [186:05] table and unioning them onto the [186:09] precomputed history old data premputed [186:13] and instant. So new data live. So [186:18] basically it's combining the new data [186:20] with the old data um seamlessly. Okay, [186:24] let me just clear or stop the simulator [186:27] here. So new more no more new data is [186:30] coming in. So the traffic data is now [186:33] going to stop. The current bucket will [186:36] finish filling and within a minute the [186:38] refresh job materializes it. And then it [186:42] will basically get a gap just like we [186:44] have here. So that's a production-shaped [186:47] observ observability stack [186:49] instrumentation batched ingest column [186:52] store an aggregate ladder and a live [186:55] dashboard reading only summaries. Now [186:58] let's talk more about the retention [187:00] policy over here. So this makes it uh it [187:07] makes it so it doesn't fill the disk. So [187:10] this helps the system not to grow [187:13] forever. So we have a retention ladder. [187:18] It's a tiered retention pattern now that [187:22] we have a concrete reason for every [187:25] number. So let me just talk about each [187:28] one because how long do I keep data is a [187:30] product question disguised as an ops [187:32] question. So the raw the agent span or [187:35] the raw spans we keep for 14 days. [187:38] Individual spans are only useful for [187:40] debugging something recent. Nobody [187:42] replies a specific agent run from 3 [187:44] months ago. And if they need to, that's [187:46] what your log archive is for. 14 days [187:49] covers what happens last sprint. Then [187:52] runs we keep for 90 days, much smaller [187:55] than spans. And the run list is what [187:57] people browse. It's cheaper to keep [187:59] longer. [188:01] Then five gra minute buckets for 90 [188:03] days. Five fine grained enough to [188:05] investigate an incident and a quarter is [188:07] how far back anyone actually zooms at [188:09] that resolution. an hourly for one year, [188:13] year-over-year comparisons, capacity [188:15] planning. Um, then daily we keep [188:18] forever. It's tiny. Compressed daily [188:20] summaries for a whole year are a [188:22] rounding error on your disk and [188:24] executives ask about last year [188:26] constantly. So you should ensure the [188:29] retention policy doesn't delete data [188:31] that the continuous aggreate would still [188:32] refresh or your aggregated data will be [188:35] deleted too. Our five minute aggreate [188:38] has the start offset of in interval [188:41] seven days meaning it's willing to [188:44] recomputee any bucket in the last seven [188:46] days raw spans are retained 14 days. [188:49] Seven is less than 14. So every bucket [188:51] the aggregate might refresh still has [188:52] source data behind it. So let me help [188:55] you understand how this can fail fail [188:57] with fail with an example. [189:00] So, I'm back into psql [189:03] and I just created a new hyper table [189:06] called foot gone raw that's going to [189:10] have 30 days of history. [189:13] Now, I'm going to create a daily [189:15] aggregate over it and refresh it. So, we [189:20] can see that we have about we have 24 [189:23] day rows per day. So, that's perfect. 24 [189:27] rows seen per day and the summary is [189:29] correct. Now let's play the part of a [189:32] retention policy and delete the raw data [189:36] than 25 days. So we're seeing what [189:39] happens when you accidentally destroy [189:41] data that that you shouldn't destroy. So [189:44] I'm deleting [189:46] from foot gun where time basically um [189:51] when it's older than 25 days. [189:54] Okay. Now we can see that when we query [189:58] the last 25 days um these have all now [190:02] been deleted. Uh we only it the the most [190:09] basically recent row is on the 20th or [190:13] the oldest old rows on the 20th instead [190:15] of the 19th 18th. All these other dates [190:19] have just basically been deleted. And I [190:22] want you to understand how this [190:24] happened. Because we didn't restrict the [190:25] aggregates refresh window with a start [190:27] offset. The refresh job woke up and [190:29] asked the database, hey, let's [190:31] recalculate the count for 28 days ago. [190:34] The rod table just answered, I have zero [190:36] rows for that day. And the aggregate [190:37] faithfully wrote that down. It overwrote [190:40] your correct historical summary with a [190:42] correct looking nothing. There's no [190:44] error. There's no warning. There isn't [190:46] even a log line. So your yearly chart [190:48] just develops a hole where the past used [190:50] to be. you find out months later uh when [190:53] you finally try to look up the data and [190:55] note the ordering because um the gap [190:58] between you made the mistake and you can [191:00] see the mistake can be days which makes [191:03] it hard to uh debug. So let's just drop [191:08] that table in the view right now. So [191:11] basically every aggregate start offset [191:13] must be shorter than its sources [191:15] retention. This is the single most [191:18] common way people accidentally destroy [191:19] data with time scale DB and it's [191:21] entirely preventable with one simple [191:24] mathematical comparison. So I just ran [191:27] this command here um which is going to [191:32] put every automated policy on your [191:35] system in one output. [191:37] The retention [191:39] compression [191:41] um it will even uh show column store [191:44] refresh along with their intervals. And [191:46] then we can [191:48] eyeball the refresh out offsets against [191:51] the retentions and then it just helps us [191:53] check what all the data is, what all the [191:56] um configuration is stored at. So now I [191:59] just have run this command here to get [192:02] the bytes per span. [192:05] So we can kind of see how big [192:08] everything's going to be. We can see [192:09] it's it's close to about 200. We'll [192:12] round this to 200 about 200 bytes per [192:14] span. So let's look at three scenarios [192:18] based on that footprint. It says 25 [192:21] after column store. That means after the [192:23] column store compression. [192:25] So for a hobby project, let's say you [192:27] process 10,000 spans a day. That's about [192:30] 2 megabytes of of raw data. With a [192:32] 14-day retention, your total raw [192:33] database is under 30 megabytes. Your [192:35] aggregate tables are kilobytes. You'll [192:37] never even think about storage costs. [192:40] But if you're a startup, maybe you have [192:41] 10 million spans a day. It's pretty [192:44] busy. it. It's a profitable agent [192:46] project. That's about 2 gigabytes a day [192:48] uncompressed. Dropping to 250 megabytes [192:51] a day compressed. Keeping 14 days of raw [192:54] traces takes roughly three and a half [192:55] gigabytes plus a couple of gigs for your [192:57] aggregates. That fits comfortably on the [193:00] smallest cheapest paid database instance [193:02] available. But if you have a very large [193:06] heavy project, 500 million spans a day, [193:08] you're generating a 100 gigabytes of raw [193:11] te telemet telemetry daily. Compression [193:16] drops that to 12 gigabytes a day. So [193:18] retaining 14 days takes under 200 [193:21] gigabytes. At this scale, this is where [193:23] tiered storage earns its keep. You keep [193:26] the most recent three days of chunks on [193:29] expensive fast local SSDs for live [193:31] debugging and you automatically push [193:33] older chunks to just cheap external [193:37] storage. So that compares the comparison [193:41] that matters without retention and the [193:43] aggreate ladder that startup case is 2 [193:46] gigabytes a day forever 700 gigabytes in [193:48] the first year and every dashboard query [193:51] gets slower every single day. with the [193:53] ladder storage plateaus at a a few [193:55] gigabytes and query time is flat [193:57] forever. So that's just the same data, [193:59] the same questions, but one of those [194:01] systems can um you can basically leave [194:04] alone for 30 years for three years [194:06] because it's not going to get too big. [194:08] So four things I'd add if I were going [194:10] to get this to production. Um first [194:15] sampling for cheap spans. Not every [194:19] successful 12 mil millisecond calculator [194:22] called needs to be stored. You can keep [194:24] all errors, all slow spans, and one in [194:28] 10 of the boring ones. Your volume drops [194:30] by most of itself and you lose nothing. [194:32] You'd have you'd have to look at also um [194:36] PII scrubbing before writes prompts [194:39] contain user data. You scrub in the [194:41] recorder, not in the database once it's [194:43] written and it's in your backups. [194:45] Alerting on the regression query. Uh, we [194:48] already wrote it. Put it on a cron with [194:50] a threshold and web hook. Add semantic [194:52] search over failures with PV PG vector. [194:57] Uh, embed prompts. Then ask, show me run [195:00] similar to this failure. It's pretty [195:03] helpful when a customer reports [195:05] something vague. So, let's just overview [195:08] what we built. So what we built a a sche [195:12] a schema with the compound primary key [195:14] and UU ID v7 identifiers batch copy [195:18] ingest that doesn't block the agent a [195:19] column store configuration tuned to how [195:22] the data is actually queried three [195:24] aggregate ladder storing states rather [195:26] than the results eight real operational [195:28] queries one of which found a planted [195:30] incident in four steps a live dashboard [195:32] reading only summaries and a retention [195:34] ladder that keeps the whole thing [195:36] bounded forever that's a production [195:39] observ observability stack. It's a few [195:42] hundred lines of code, one PostgresQL [195:44] extension, and no other infrastructure. [195:46] So, I need to use Kafka, no separate [195:49] analytics warehouse, no data pipeline, [195:51] just a database. Okay, we're finished [195:54] with the first project and it's time to [195:56] make another project. Everything we've [195:59] built so far has been running locally in [196:01] Docker. That's the perfect place to [196:03] learn and build and is completely fine [196:04] for running small workloads in [196:06] production. But time scale also has a [196:09] managed cloud version and it's built by [196:12] the exact same team behind the [196:14] extension. It has some architectural [196:17] features that only exists in the cloud. [196:19] So we're going to move over to it. We're [196:21] also going to build a completely new [196:22] project so you can see these patterns [196:25] applied to a differently shaped problem. [196:28] So we're over on the Tiger Data website. [196:32] You can see it says from the creators of [196:34] Timecale DB. And if you sign up, you get [196:38] a 30-day trial of their performance [196:40] plan. You don't even need a credit card. [196:43] And that's plenty of time to just try it [196:45] out and um and even try migrating a test [196:49] workload. Also, they have a free shared [196:51] CPU service which are great for learning [196:53] inside project. So, just uh get logged [196:56] in or sign signed up for the service. So [196:59] after you get signed up or signed in, [197:02] you can start creating your first [197:04] database. So I'm going to you can choose [197:07] any sort of CPU. I'm going to choose the [197:09] free tier and continue. And then you can [197:12] change your name. Uh I'm just going to [197:14] call it fleet. You can call it anything [197:16] you want. And then create service. I'm [197:19] just going to create basically use the [197:21] defaults for everything. [197:23] My service is ready. So now that we've [197:25] created everything, we get all the [197:28] connection info. So we have a host, a [197:31] port, a database name, a user, and a [197:33] password. And it basically all goes into [197:37] here. Our primary connection string. [197:42] That's it. It's just like a standard [197:43] Post SQL connection string. [197:46] Okay. I'll connect using psql on the [197:49] command line here. And then I'll just [197:51] pass in that URL that we got from Tiger [197:54] Data. [197:55] And you'll notice this SSL mode equals [197:58] require in the connection string. That's [198:02] mandatory when you're connecting over [198:03] the open internet. And the console gives [198:06] it to you by default. [198:08] And it's going to ask for my password. [198:11] So I go back over to the dashboard over [198:15] here. If I go to more details and I can [198:18] say forgot your password and then just [198:21] create a new password if you don't know [198:22] what it what it is and then I can just [198:25] type it in here. Okay, let's check our [198:28] extensions. [198:30] So I'll say select X name X version from [198:33] PG extension or by X name and we can see [198:36] here timecale DB and time scale DB [198:39] toolkit are already installed and [198:41] active. No compile steps, no modified [198:44] anything. It's just there. [198:46] So, there are other extensions installed [198:48] on the machine that you just enable [198:49] yourself. I'm going to turn on the [198:52] extensions uh PG vector and PG vector [198:56] scale which makes vector search faster [198:59] at scale. We're going to use those at [199:02] the very end of this section. And I want [199:05] to test something right now. Basically [199:09] the hyper table, the column store, the [199:12] continuous aggregate ladders, the [199:13] retention policies, all the things that [199:15] we've kind of discussed earlier in the [199:16] course, they should run against this [199:18] managed service without changing a [199:20] single line of code. So let's find out. [199:23] So I'm back to my command line and then [199:26] I'm just going to try to run all my [199:28] migrations and put in our tiger data URL [199:32] here. [199:34] And it it's done. It actually ran every [199:37] single migration [199:39] and it's all now in the cloud. So if I [199:44] go back into psql into the database on [199:47] the cloud and we see this we ran this [199:50] query and we see uh numchucks zero num [199:55] num chunks are zero. Uh that's exactly [199:58] what we want and what we expect. I mean [200:01] time scale DB creates chunks dynamically [200:04] when data actually arise. We just create [200:05] the schema on a fresh database but until [200:08] we insert rows it doesn't waste disk [200:10] space pre-allocating empty chunks but [200:14] the tables the views and the policies [200:16] are all perfectly configured. [200:18] And now after we've run all the [200:20] migrations, I can go into our web [200:23] dashboard and we go into explore. We can [200:27] see the different objects. These hyper [200:28] tables have been created, the tables [200:30] that have been created. We can see how [200:33] many of everything we have. We have some [200:35] continuous aggregates. [200:39] And if we go into the table, we can see [200:42] some information. Right now, there is no [200:44] data in the table, but if there was [200:46] data, we would see the data here. And we [200:48] can see all the fields here that we [200:51] created right from our [200:54] migration files. [200:57] So basically we now have every table, [200:59] every materialized view, every policy [201:02] executed perfectly. [201:05] Not one line of code changed. I want to [201:08] emphasize this because it's the [201:10] strongest practical argument for using [201:12] this stack. your local development [201:14] database running in Docker, your CI [201:16] environment, and your managed production [201:18] database can all run the exact same [201:20] schema. You can develop locally and [201:23] deploy to the cloud or pull a snapshot [201:25] from the cloud to debug on your laptop. [201:28] And if you ever decide you want to leave [201:30] the managed service, you are leaving [201:31] with a standard Postgress dump that will [201:33] restore anywhere the extension is [201:35] installed. That's a meaningfully a [201:38] meaningfully different position than [201:40] adopting a proprietary cloudonly [201:42] database. [201:44] Now clicking around a web console is [201:47] fine for day one, but if you're managing [201:50] this every day, you want a CLI. [201:53] So I'm just going to run brew install [201:54] time scale tap tiger CLI. [201:58] The arts arm 64 is just for my [202:00] particular computer. [202:03] Okay, it tells what to do next. And I'm [202:05] going to do tiger o login. [202:09] And then it's going to open my browser [202:10] for authentication. [202:13] And then once it's authenticated in the [202:15] browser, it will now continue with the [202:19] the login on the in the CLI. [202:22] And then it gives a few different next [202:24] success. We can get an MCP server for [202:26] for AI coding tool. List list existing [202:30] services. Create a new service. I'm [202:32] going to list the existing services. So, [202:35] tiger service list [202:38] and here's the service we created we [202:41] just made in the browser and you can [202:44] provision infrastructure directly from [202:46] here too. [202:48] So, I'm going to do tiger service [202:49] create-c shared and then memory shared. [202:54] And now it's creating this service. So, [202:57] that single command spins up a free [202:59] shared service. No browser required. [203:04] Okay, now it's going to give me all the [203:06] information about that service that we [203:08] just created. [203:10] Now I can just run tiger db connect. [203:16] Now that command just drops us straight [203:18] into a psql session against our database [203:23] without me having ever having to copy [203:24] and paste a connection string. [203:28] So we can start doing stuff in our [203:29] database. So, I'm just going to go out [203:30] of this with slash Q. [203:33] I'll clear my screen. And before we go [203:36] into go any further, I want to set up a [203:40] guardrail. So, I want you to see me just [203:43] do this intentionally. I can do tiger [203:45] config set read only true. Now, if you [203:51] remember in our after we logged in, it [203:53] asked the question about this, but this [203:55] is how we can manually do this from the [203:57] from the the terminal. So set readonly [204:00] equals all. So what this does is [204:03] configure the CLI so that mutating [204:05] commands are refused and any database [204:08] sessions it opens are forced into [204:11] readonly mode. So if I go to tiger [204:15] service delete and then this is the ID [204:18] of the one we just created, [204:21] it's going to fail. Error. This [204:24] operation is not allowed in readonly [204:26] mode. [204:28] So, it doesn't just ask, "Are you sure?" [204:31] It just stops you. So, here's what I [204:34] think on connecting AI coding agents to [204:37] your database. Language models are very [204:40] great at exploring a schema and [204:41] suggesting optimizations, and they're [204:44] also completely capable of running a [204:46] drop table command because your prompt [204:47] was slightly ambiguous. So, the answer [204:50] isn't never use AI with your database. [204:53] The answer is to make sure the [204:54] destructive path is impossible. Uh [204:58] turning on readonly mode takes just one [205:00] command and it turns a risky experiment [205:03] into a safe one. MCP or model context [205:07] protocol is a standard way to hand [205:08] external tools to a language model. And [205:12] as we kind of saw already, Tiger ships [205:15] an MCP server directly inside the CLI. [205:17] So your AI coding agent can talk to your [205:19] database. So we can [205:23] so we can install that which just tiger [205:25] mcp install [205:27] and then we can choose which one we want [205:29] to configure. Uh depending on what you [205:31] use I'll just use cursor here. [205:34] And now it's already it's basically [205:37] configured [205:38] successfully installed tiger mcp server [205:41] configuration for cursor. So it tells [205:44] our next step by just restarting the [205:46] cursor to load the new configuration and [205:48] then it'll be tiger and then it gives us [205:50] some things we can um try testing with [205:53] one with. [205:55] So let's actually try it out. I have [205:57] cursor opened [205:59] and then I'm just going to go here to [206:02] cursor and I'll just say list my tiger [206:07] services and tell me which region each [206:09] is in. [206:11] And then I just have to make tell it to [206:13] always run our MCP server for Tiger [206:16] Cloud. Okay, we got the information. [206:20] These are the two the one we created on [206:22] the website and the one we created in [206:24] the CLI. And then we have the the [206:26] regions. We have two region. We have two [206:29] cloud services both in US East1 which [206:32] does happen to be the region closest to [206:34] where I live. [206:36] So instead of looking up the CLI flags, [206:39] it just calls the tool and gets the [206:42] data. [206:43] Okay, let's try another one. Describe [206:46] the agent span table including its hyper [206:48] table settings and column store [206:50] configuration. [206:52] So this is even more interesting. [206:57] It's not just uh reading the Postgress [206:59] tables. It's reading the time scale [207:02] metadata. [207:04] Okay. Okay. And it looks like I need to [207:05] save my password from the the fleet [207:09] table locally. [207:11] So I'll just do tiger tiger db save [207:13] password and I can put in the password [207:16] for the table that we developed. [207:19] And then I'll just tell the AI agent [207:22] that the password's there. Okay. And now [207:25] it did what we asked it for. So it [207:27] described the agent span table and then [207:31] including the column store the hyper [207:34] table [207:36] and it knows all everything including [207:39] the the chucking chunk interval and [207:41] everything like that [207:43] and one more question given how I query [207:45] agent span is my segment by [207:47] configuration reasonable what would you [207:49] change [207:51] so now it's going to use information [207:54] from that's getting from tagger data [207:57] And it's going to reason about my [208:00] cardala cardality and query patterns [208:04] which are the concepts we discussed [208:06] previously. [208:08] It can do this because the tiger MCP [208:11] server actually has time scale skills [208:14] and documentations [208:16] wired into it. It isn't just guessing [208:19] based on generic Postgress training [208:22] data. [208:23] Okay, it's giving me some uh [208:25] suggestions. is I would segment by [208:27] project only. [208:29] Um and then I saying like basically what [208:33] I did wrong, why the current pair is [208:35] weaker than it looks, what I would set. [208:38] So it's giving very specific suggestions [208:42] and uh based on this these time scale [208:46] skills, but here's my uh rule for AI, [208:51] which is basically just to verify every [208:52] suggestion yourself. [208:57] Okay, let's actually start this second [208:59] project. I intentionally chose a problem [209:02] with a completely different shape. So [209:05] before the previous project was about [209:06] irregular events spans arrived randomly [209:09] whenever an AI agent did something. [209:11] Durations varied wildly and we were [209:13] aggregating over those specific events. [209:17] This project is about regular regular [209:19] interval sensor sensor data. We're [209:22] tracking a fleet of 2,000 EV chargers. [209:25] Every single device reports exactly once [209:27] every 10 seconds forever. This requires [209:30] completely different tools. We have a [209:32] monotonic we have monotonic counters [209:35] that need to be differenced device state [209:38] machines and the hardest monitoring [209:40] problem of all which is depend detecting [209:42] when a device stops reporting. So let's [209:46] look at this schema. [209:49] So first we have the this table site and [209:52] the table device. These are two standard [209:56] relational tables. There are no time [209:59] columns here and they are not hyper [210:01] tables. These are our dimensions. They [210:04] are facts about the devices that don't [210:06] change every 10 seconds. [210:09] Then we create the index on device. And [210:12] then we have this hyper table which is a [210:15] reading. This is our [210:18] see this is where we create the hyper [210:20] table. Uh this is our fact table which [210:22] we turn into a hyper table. So let's [210:26] look at the four different kinds of [210:28] columns in here because the data tape [210:31] completely dictates how you are allowed [210:33] to aggregate it. First we have the power [210:37] W, the voltage, the temp C. These are [210:40] gauges. They go up and down. You can [210:43] average them, but as we'll see later, [210:46] how you average them really matters. [210:48] Second, the energy wh total. [210:52] It's a monotonic counter. It represents [210:55] the lifetime energy delivered. It only [210:58] ever increases unless the device [211:00] reboots, at which point it resets to [211:03] zero. Averaging this number is totally [211:05] meaningless. You want to find the [211:07] difference over a window of time. Third, [211:11] we have the next one which is the state. [211:13] It's a discrete state like charging, [211:15] idle, or faulted. You don't average a [211:17] word. You just ask how much time this [211:20] device spent in each state. Also notice [211:23] that the gauges are stored as real, [211:25] which is a which are four byte floats [211:28] instead of double precision eight byte [211:31] floats. A charger's temperature reading [211:33] doesn't have 15 significant digits. when [211:36] you're generating 17 million rows a day, [211:39] cutting those column sizes in half makes [211:42] a massive difference. So, let's look at [211:45] the primary key, which is the device ID [211:48] and time. So, in the previous project, [211:51] we used time and span ID, but here we [211:54] put the device list first. And you're [211:57] maybe wondering why. Well, that's [211:59] because the single most common query in [212:01] a fleet management app is, "Show me one [212:03] device's history over the last 24 [212:05] hours." Putting the device first means [212:07] all those rows are physically adjacent [212:10] on disk, making it a single incredibly [212:12] fast range scan. The primary key should [212:15] always follow your dominant query [212:17] pattern. [212:19] I've cleared out my fleet project or [212:21] service and so I'm going to be starting [212:23] this from scratch and I'm going to be [212:26] using the connection info. [212:29] Now I'll put in this command to run my [212:32] migration file to make sure I'm in the [212:34] same directory as my m migration file [212:36] and I have to put in my database URL. [212:40] Okay, I've ran that and created on my [212:43] database and we can verify that over on [212:46] our web dashboard. We can now see the [212:50] the objects in here, the hypert and all [212:54] the things that we just created. Here's [212:57] the devices. [212:59] There's a myth that in analytics you can [213:01] never use joins and therefore you must [213:04] denormalize everything into a single [213:06] massive fact table that leads to [213:09] terrible schemas. The reality is that [213:11] joining a small dimension table to a [213:13] massive hyper table is completely fine [213:15] and Postgress is deeply optimized for [213:18] it. So let's prove it. [213:21] Before we start quering this, we need [213:23] data. I have this Python script here to [213:26] generate the fleet telemetry. It's [213:29] basically simulating the data that we [213:32] need to do. It uses the exact same [213:34] batched copy pattern we used in the [213:37] first project. And it can generate 2,00 [213:42] devices reporting every 10 seconds. And [213:46] to save us from sitting here watching a [213:48] progress bar, I've already applied our [213:50] schema and run this simulator in the [213:53] background. It backfilled 24 hours worth [213:57] of data, which is quite a few row rows. [214:00] I've also planted three specific ana [214:04] anomalies in that data that we'll hunt [214:06] for later. And a side note, I use a [214:09] Python script here so you can see how [214:10] the data is shaped. But on Tigercloud, [214:13] you wouldn't write this inest code [214:15] yourself. You would use their fully [214:17] managed Kafka connector to consume [214:20] telemetry straight from a topic or the [214:24] S3 connector to bulk load historical [214:27] CSVs. [214:29] So we also have this queries file here [214:32] which are which are going to have some [214:33] queries that we're going to just use for [214:36] for examples. So we have tons of rows of [214:40] data and I want to address a massive [214:42] myth in analytics. The myth is that you [214:44] can never use joins and therefore you [214:47] must denormalize everything into a [214:48] single massive fact table that leads to [214:51] terrible schemas. The reality is that [214:54] joining a small dimension table to a [214:56] massive hyper table is completely fine [215:00] and Postgress is deeply optimized for [215:02] it. [215:04] So we're going to start with this first [215:07] join. [215:08] So first I going to get into psql on our [215:12] our hosted database. [215:14] Then I'll paste in this SQL code here. [215:18] So we just joined [215:20] across millions of readings and it [215:23] returned in milliseconds. [215:27] So it only took [215:30] basically 11 seconds [215:33] to run this entire query. [215:36] And now we're going to run this explain [215:37] query. [215:39] Again, you can find these all in the [215:41] code link in the description. [215:44] So if we look at the explain plan, we [215:47] can see exactly why it's so fast. [215:50] Let me zoom out so we can see more on [215:52] the screen at one time. It Postgress [215:56] takes the device and site tables [216:00] which total maybe a few thousand rows [216:02] and take up a few kilobytes and builds a [216:05] hash table in memory. Then it just [216:08] streams the millions of readings past [216:09] that hash table in the CPU cache. The [216:13] join costs virtually nothing. And here's [216:16] the actual rule for data modeling. [216:19] Denormalize facts about the measurement [216:21] itself, but normalize attributes that [216:24] describe the entity. The current state [216:27] of the charger is a fact about that [216:30] state about that exact second time. So [216:34] it goes in the hypert. But the firmware [216:37] version belongs in the device table. If [216:40] a charger gets a firmware update, you [216:42] update one row on the device table. You [216:45] not rewrite 400 million rows in your [216:48] readings table. [216:50] Let's answer the most important [216:52] configuration question. What should our [216:55] segment by column be when we compress [216:57] this hyper table? Well, we have two [217:00] obvious choices. We can segment by [217:02] device ID and there are 2,000 devices or [217:06] we can segment by site ID and there are [217:08] 40 sites. Well, previously I told you [217:11] that the rule of thumb was to aim for at [217:12] least a thousand rows per compressed [217:14] batch. Let's run an experiment and [217:17] actually measure both axis the storage [217:20] size and the query latency. [217:23] So I've already run all this code and [217:27] we've run this these alter tables. These [217:31] are going to create two identical copies [217:33] of a day's worth of data but compress [217:36] them differently. [217:38] One is by seg one is segmented by device [217:41] ID. The other is segmented by site ID. [217:44] Notice that for the site version I had [217:46] to move device ID into the order by [217:49] clause to ensure the data stays locally [217:51] clustered within the segment. [217:54] I ran this before we started recording [217:56] because copying compressing a full day [217:58] of data can take some time. So let's [218:02] compare sizes on disk first using the [218:06] hypert compression stats. So basically I [218:10] have this SQL code that does the [218:12] comparison here. So let's look at the [218:16] percentages saved. We can see the um by [218:19] device or device ID is just slightly [218:23] better at just over 89%. [218:25] The this result is exactly why we run [218:28] this experiment instead of guessing. If [218:30] you just read the documentation, you [218:32] might assume that segment by segmenting [218:34] by site ID would compress better because [218:37] the batches are larger. With a 10-second [218:40] interval, one device emits about 8,600 [218:43] readings a day. Segmenting by site [218:45] groups 50 devices together, creating a [218:48] mass creating massive batches of over [218:50] 400,000 rows. So why did device ID just [218:55] went by a bit? Well, think about the [218:58] physical shape of the data with segment [219:00] by equals device ID. Each batch [219:03] represents a single charger's continuous [219:05] timeline. The temperature curves [219:07] smoothly. The power output stays steady [219:10] and delta encoding can compress those [219:13] patterns perfectly. When you segment by [219:15] site ID, you interle 50 different [219:18] chargers into the same batch. The values [219:21] constantly jump up and down between [219:23] different machines which disrupts the [219:25] encoding and actually hurts your [219:27] compression ratio. So device ID wins on [219:31] disk space. But storage size is only [219:33] half the benchmark. Let's look at the [219:36] second axis. query latency. [219:39] So I just ran another query where we're [219:43] going to look at query speed. So here [219:46] are the results. The first one for [219:49] reading by device, 251 milliseconds, and [219:53] then 176 milliseconds for reading by [219:57] site. [219:58] Okay, so I just ran this code here to [220:04] check the query latency [220:06] and let's look at the query times. So [220:09] when we're reading by device, it's 253 [220:13] milliseconds and here's 166 [220:16] milliseconds. So if you're paying close [220:18] attention, you'll notice something [220:19] unexpected. By sight actually ran faster [220:23] here, 166 milliseconds compared to the [220:25] 253. [220:27] So, uh, why did that happen? Because of [220:30] how databases actually work under the [220:32] hood. First, a cache warning. The first [220:34] query had to fetch cold data from disk [220:36] into memory. The second query ran [220:38] immediately after, found what it needed [220:40] in the hot cache, and finish faster. [220:42] Second, we're only quering a tiny slice [220:44] of data. Right now, we only have a few [220:46] megabytes of compressed data RAM. [220:47] Scanning it is triv is very fast [220:50] regardless of how it is segmented. But [220:53] you have to protect project this [220:55] architecture choice out to a production [220:57] scale. So imagine you have three years [220:59] of fleet data totally totaling billions [221:02] of rows. If you use segment by site ID, [221:05] the database is forced to decompress and [221:07] scan 49 other device histories just to [221:09] find the one charger you asked for. That [221:12] memory overhead will crush your [221:13] database. If you segment by device ID, [221:16] the database read reads the batch [221:19] metadata metadata and and ignores the [221:21] rest of the site entirely and only [221:24] decompresses the exact device you need. [221:27] So despite the microbenchmark quirks of [221:29] caching, device ID is definitely the [221:32] right choice [221:34] uh for our production workload because [221:36] the single device query is what our API [221:38] serves on every service detail page [221:40] hundreds of times a minute. So we're [221:43] going to choose device ID. Storage is [221:45] cheap. The latency for user that you [221:46] your users feel when opening the device [221:48] detail page 50 times an hour is not the [221:52] rule of takeaway isn't always isn't [221:54] always use device ID. The rule is [221:56] segment by should be the column you [221:58] filter by the most on your raw data. So [222:01] now we can drop our scratch experiment [222:03] tables and we can apply that measure [222:06] configuration policy directly to our [222:09] primary reading hyper table. Um this is [222:11] because I had already this errors [222:13] because I had already added that we [222:15] compress after 4 hours because [222:17] uncompressed chunks are the expensive [222:19] ones. And we add sparse minmax indexes [222:23] on temp C and power W. So queries [222:25] filtering on thermos spikes or power [222:27] thresholds can skip entire batches [222:29] without decompressing them. Okay. Now [222:32] we're going to start working with the [222:34] data more. And I have deliberately [222:36] engineered three anomalies into the data [222:40] set. Basically, real world problems that [222:42] are incredibly hard to detect with plain [222:43] SQL. A site outage. Every device at one [222:47] specific site goes completely silent for [222:49] some time. No air logs, no fault state, [222:51] just silence. A counter reset. One [222:54] device reboots and its lifetime energy [222:57] WH total drops back to zero. And also a [223:00] stuck state. a device gets stuck in a [223:03] fault in a faulted state and faithfully [223:06] report that it's broken every 10 seconds [223:08] for an entire day, but it never goes [223:09] offline. [223:11] So, I just ran this to see how much we [223:14] have of um basically so you can kind of [223:16] understand the whole database right now. [223:18] These are my readings, how many devices, [223:20] and the date range. [223:23] So, this is just representing a day of [223:25] fleet operations. We could do it bigger. [223:27] We could do it more, but then it would [223:29] just create an even bigger database. [223:32] And I'm using a Python script here so [223:34] you can see how the data is shaped. But [223:36] on Tiger Cloud, you wouldn't write this [223:40] inest code yourself. You would use their [223:43] fully managed Kafka connector to consume [223:45] telemetry straight from a topic or the [223:49] S3 connector to bulk load historical [223:51] CVS. [223:53] So now let's find the anomalies. [223:57] We're going to use four specific [223:59] hyperunctions designed to solve problems [224:01] that are miserable to write in raw SQL. [224:04] So first the counter reset. Our energy [224:07] wh total column is a lifetime meter [224:11] reading. It only ever increases until [224:14] the device restarts at which point it [224:16] drops to zero. If we write a naive SQL [224:19] query to find the total energy delivered [224:22] over the last 24 hours, it looks like [224:25] this. [224:27] For the healthy device uh 1338, [224:30] it works perfectly. But you see for [224:32] device 1547 which rebooted, we get a [224:36] completely wrong answer. The max value [224:40] was from right before the reset and the [224:42] min value was zero. And what we we're [224:45] not it's not showing us any error. Look [224:48] at these numbers here for device 1338. [224:52] It reports around 2 million W hours, [224:56] about 2 megawatt hours, which makes [224:59] complete sense for a normal operating [225:01] day. But if we look at 1547, it claims [225:05] to have delivered over 44 million watt [225:07] hours, 20 times more than normal. So why [225:10] is that? It's because the device [225:12] rebooted, dropping the meter back to [225:15] zero. The min value was zero right after [225:17] the restart, and the max value was 44 [225:20] million right before the restart. So [225:22] naive math just built a customer for the [225:25] charger's entire multi-year lifetime [225:27] accumulation in a single day. And notice [225:30] the worst part, there is no error. [225:32] Postcrest happily executed the query and [225:34] handed you a completely plausible [225:36] looking invoice with a catastrophic [225:38] number. So here's the correct way to do [225:41] it using time scales hyperfunctions. [225:45] The this is that is the actual energy [225:48] delivered. The counter egg [225:53] walks the readings in chronological [225:55] order. So when it sees the value [225:58] decrease, it recognizes that as a reset [226:01] and safely adds the pre-reset [226:04] accumulation to the total. We also see [226:07] the numbum resets out of that. We also [226:11] pull the num resets out of that same [226:12] state. We clearly see that [226:16] uh device 1547 reset exactly once. That [226:21] is an incredibly useful metric on its [226:23] own. A charger that resets five times a [226:25] day is a charger you need to replace. [226:28] This uses the same two-part pattern we [226:30] learned in the previous project. You [226:33] build a state using an aggregate [226:35] function and you read the answer out [226:37] using an accessor function. And because [226:39] it's a state, we can put it in a [226:42] continuous aggregate. [226:45] Rate gives you the average rate of [226:46] energy increase scaled to watts. Because [226:50] it's derived [226:51] directly from the metered counter rather [226:54] than samples from the power gauge, it [226:57] represents what was actually delivered [226:59] according to the hardware meter. Now [227:02] let's see how that fits into our [227:03] continuous aggregate ladder. [227:06] To make our dashboards lightning fast, [227:09] we are going to use continuous [227:11] aggregates. These are materialized views [227:14] that automatically summarize your data [227:16] in the background. But look closely at [227:18] what we are actually storing in our [227:20] hourly view. We aren't storing finished [227:23] numbers. Every single aggregate column [227:27] is storing an intermediate state. We're [227:30] looking at this view right here. [227:32] So, why does this matter? If you take a [227:35] standard average of Monday's power usage [227:38] and a standard average of Tuesday's [227:40] power usage, you cannot safely average [227:43] those two numbers together to get a [227:45] weekly average. The math is wrong. By [227:49] using hyper functions like states agg [227:53] and the counterag, we are storing the [227:57] raw mathematical state. Think of it like [228:01] having the raw ingredients instead of a [228:03] baked cake. Because we save the state, [228:06] we can easily roll those hourly buckets [228:09] up into daily or weekly buckets later [228:12] with perfect accuracy. [228:14] And look at the refresh policy right [228:18] below it. We tell time scale to update [228:22] this every 5 minutes. But notice the end [228:27] offset of 2 hours. We deliberately do [228:31] not close the window right up to the [228:33] current minute. In the real world, IoT [228:36] devices lose their cellular connection [228:38] and spend their and and send their data [228:42] late. So leaving a two-hour buffer [228:45] ensures that late arriving data gets [228:48] perfectly incorporated into the [228:50] summaries before the bucket is [228:52] finalized. So now let's look at the rung [228:55] two of our ladder, the daily view. [228:58] Notice that the device daily queries the [229:03] device hourly, not the raw reading [229:06] table. Because we store the mathematical [229:09] states in rung one, rung two can just [229:12] call rollup on those hourly buckets, it [229:15] never has to scan the 100 million rows [229:18] of raw data. This is how you keep [229:20] queries fast at an infinite scale. [229:24] And then we have this uh refresh rate [229:28] here. Okay, let's apply this file. [229:32] So I'm just going to run this command. [229:34] We already I've now stored our URL into [229:37] the environment variable. [229:40] Okay. Now let's go let me go back into [229:42] our database the PSQL. [229:46] And remember continuous aggregates are [229:48] empty until refreshed. And because [229:51] device daily depends on device hourly, [229:54] we must refresh bottom first. [229:58] So I'm going to do these commands to [230:00] refresh them [230:02] and get our data in there. [230:05] Okay, now let's query total energy [230:09] delivered today from our continuous [230:12] aggregate. [230:14] So that's what this line will do. [230:18] So we just rolled up hourly counter [230:21] states across an entire day and [230:23] difference them. And this math is reset [230:26] aware across bucket boundaries. If a [230:30] charger reboots at like 145955 right on [230:34] an hourly theme, the calculation stays [230:37] completely accurate. [230:41] Now let's find our second planted anom [230:44] anomaly anomaly using state a [230:48] in fleet management. The morning [230:49] operations meeting always starts with [230:52] how much did our charger spend charging [230:54] idling or broken? So [230:57] I'm going to run this SQL here. [231:01] Notice the syntax for duration in here. [231:05] With time scale hyperunctions, you [231:08] always pass the aggregate state as the [231:11] first argument and the specific value [231:14] you are looking at you're looking for as [231:16] the second. [231:18] So if we look at this top row here, one [231:21] charger has been in the faulted state [231:25] for almost the entire period. [231:28] That is our second planted an anomaly. [231:32] The charger never went offline. It [231:34] faithfully reported every 10 seconds [231:36] that its internal state was broken. A [231:38] simple uptime alert would never catch [231:40] it, but duration in surfaces it [231:43] immediately [231:45] without state. A writing this query in [231:48] po post P post P post P post P post P [231:49] post P post P post P post P post [231:49] PostgreSQL requires a massive window [231:52] function with lag or lead to detect [231:54] state changes time timestamp differences [231:58] to compute durations and complex [232:01] boundary logic for sessions active when [232:03] the window opened or closed with time [232:06] scale it's one clean function call if [232:10] you need to draw a timeline chart on a [232:12] front end you can extract the exact [232:14] intervals [232:15] So here's the SQL to extract those [232:19] intervals. You can see state timeline [232:24] expands the state into discrete start [232:26] and end timestamps. That's an entire NAT [232:29] chart ready for a front-end UI produced [232:32] directly from SQL. [232:35] Now for the third anomaly, the site [232:38] outage. So I have this other SQL code up [232:42] here that I just ran. This represents [232:44] the hardest problem in telemetry which [232:47] is silence. There are no error rows [232:50] there. There is no faulted status. The [232:53] hardware simply stop communicating. [232:56] You cannot write a wear clause to find [232:58] records that do not exist. [233:02] So if we look at the results here, we [233:06] see multiple devices showing the exact [233:08] same downtime gap. [233:12] Let's confirm the scope of this outage [233:14] by joining our data back to the site [233:16] dimension table. [233:19] Because we need to calculate the missing [233:20] heartbeats per device before we can find [233:23] the maximum downtime per site, we will [233:26] use a common table expression, a width [233:29] clause to structure the query cleanly. [233:33] And so [233:35] here is the entire SQL. And let's just [233:39] look at the result. Every single device [233:42] showing that downtime belongs to the [233:46] exact same site. [233:49] And we can see where the the worst [233:50] downtime is. [233:53] So this tells you immediately that you [233:54] aren't dealing with individual charger [233:56] hardware failures. The physical internet [233:58] connection or cellular gateway at that [234:00] facility went down. This is a much [234:02] better health alert than threshold gou g [234:05] gouges. A device reporting weird numbers [234:09] is a problem you can triage later. A [234:11] device reporting complete silence is [234:13] broken, disconnected, or unpowered. [234:17] Let's look at another subtle trap with [234:20] continuous sensor readings which are [234:22] standard averages. [234:26] So uh you see we're calculating the [234:29] averages different. So first the naive [234:32] average and then the time weighted [234:34] average. And if we look here, we can see [234:38] the numbers are just very slightly [234:41] different. [234:42] So standard the average assumes every [234:45] row represents an equal slice of time. [234:48] But real networks readings clump [234:50] together. If a device sits idle at zero [234:52] watts for 10 minutes, reports once, and [234:54] then rapidly streams 60 readings in one [234:56] minute while pulling 50 kilowatts, a [234:58] standard average weights those high [235:00] readings 60 times more heavily than the [235:02] idle period. And so one thing is that we [235:06] don't see the time weight function in [235:08] this query and that's because um we did [235:12] the heavy lifting inside our device [235:14] hourly continuous aggregate view earlier [235:17] when we built this view. [235:20] We pass linear to the time weights [235:22] function that tells time scale to linear [235:25] linearly interpolate between missing [235:27] readings which is the correct [235:29] mathematical model for continuous values [235:31] like power draw or temperature. [235:34] Now here in our select we just use roll [235:36] up to seamlessly merge those [235:38] pre-calculated hours states together and [235:42] average to extract the final correct [235:45] number. [235:46] You can also use integral on that same [235:49] state to calculate total energy directly [235:52] from your power gauge and compare it [235:55] against your counter. [235:57] They won't match to the exact decimal [236:00] because one is sampled and what is [236:02] integrated hardware pulses, but a large [236:05] discrepancy flags a faulty sensor right [236:08] away. The final hyperunction addresses [236:12] chart rendering. A single charger [236:15] generates about 260,000 readings over a [236:18] 30-day period. If you send 260,000 JSON [236:22] coordinates to an 800 pixel browser [236:24] chart, you will freeze the UI thread and [236:26] waste bandwidth. So the LTB function [236:32] um stands for largest triangle three [236:36] buckets is a downsampling algorithm [236:39] built directly into the database engine. [236:42] it can reduce those 260,000 points down [236:45] to exactly 500 points while [236:47] mathematically preserving visual peaks, [236:49] troughs, and silhouettes. So if you use [236:52] hourly averages, a brief 3inut power [236:56] spike is smoothed into nothingness. [236:59] Uh LTB gives the front end a lightweight [237:03] payload that retains the true shape of [237:05] the data. [237:07] Okay, now let's talk about data tiering. [237:11] Now, I've already talked about it a [237:12] little bit earlier in the course, but I [237:13] want to go into even more detail. When [237:16] you're dealing with time series data, [237:18] you're dealing with massive scale. A [237:21] medium-siz IoT fleet can generate [237:23] terabytes of data a month. If you keep [237:26] all that on ultra fast, premium NVMe [237:30] solidstate drives, your database bill [237:32] will bankrupt your project. But time [237:35] series data has a very specific life [237:38] cycle. It starts out hot and quickly [237:41] turns cold. The readings from the last [237:44] 24 hours are are queried constantly for [237:47] real-time dashboards and alerting. But [237:50] the readings from six months ago, those [237:53] are only queried once a month for a [237:56] historical report. [237:58] Data tiering solves the cost problem by [238:01] splitting your database across two [238:04] different types of storage. Recent hot [238:07] data stays on fast, expensive SSDs. [238:11] Older cold data is automatically moved [238:14] into cheap Amazon S3 object storage. The [238:17] magic of time scales taring is that it [238:21] is completely invisible to your [238:22] application. You don't have to write [238:25] custom code to query on S3 bucket to [238:29] query an S3 bucket and join it with your [238:31] database. It all just looks like one [238:33] single PostgreSQL table. [238:36] And let me show you how to set it up. So [238:40] before we can write any SQL, we have to [238:42] provision the cloud infrastructure. [238:45] That's why I'm on the Tiger cloud [238:48] console right now. If you try to run the [238:51] SQL command to tear your data right now, [238:55] PostgreSQL will throw an error saying [238:58] the function does not exist. That's [239:00] because the object storage manager [239:02] extension isn't actually installed on [239:04] our database yet. So to install it, we [239:09] are on our project um and we're going to [239:11] go I'm going to go to the explore tab. [239:15] Then I'm going to click data taring and [239:19] then I'm just going to click enabled [239:20] tiered storage [239:23] and then let's go. Now depending on the [239:28] plan that you have on Tiger Data, you [239:31] may have to upgrade your account before [239:34] you can enable this. [239:37] Now behind the scenes, Tiger Cloud is [239:40] provisioning a dedicated S3 bucket, [239:43] configuring the IM security roles and [239:46] installing the time scale OSM extension [239:49] directly into our Postgress instance. [239:52] Now, because our terminal session was [239:54] already open, it might not see the new [239:56] extension yet. So, I'm going to quit out [239:58] of this. [240:00] And then I'm going to go right back. I'm [240:02] going to reconnect right into the [240:03] database. [240:05] Now that the cloud infrastructure is [240:07] wired up, creating our policy is just [240:09] one line of SQL add tiering policy and [240:13] it's added. If I would have done this [240:16] before, we did that stuff on the [240:17] dashboard, it would have given an error, [240:20] but this time it succeeds. From now on, [240:22] any chunk containing data older than 3 [240:25] days will automatically slide off your [240:27] expensive local disc and into cheap S3 [240:30] storage. Now, let's look at the most [240:33] important foot gun with tiered storage. [240:37] Tiered reads are disabled by default. [240:42] So, if you try to query data than 4 [240:45] days, it would just return zero. Not [240:48] because the data is missing. it su it it [240:51] would it successfully moved to object [240:53] storage but our current connection is [240:55] intentionally ignoring tiered chunks. Uh [240:58] why is this off by default? Well, [241:00] because scanning object storage across [241:02] the network has higher latency than [241:04] reading a local SSD. If tiered reads [241:07] were turned on globally, a poorly [241:10] written dashboard query could [241:11] accidentally scan three years off three [241:15] years of S3 data, stalling the [241:17] connection and consuming resources. [241:20] So to query your cold data, you have to [241:23] explicitly opt in with set times DB [241:26] enable tier reads to equal true. And [241:30] then I could query data older than 4 [241:32] days. Right now I actually don't have [241:35] any data over older than 4 days because [241:37] I just set it up with uh just 24 hours [241:40] of data. But if we did, we could query [241:42] all the rows. And so the production best [241:45] practice is to leave tiered reads turned [241:47] off globally for your main application [241:50] and only enable it selectively on the [241:52] analytical roles that actually need [241:55] historical access. [241:57] So for instance alter ro reporting set [242:00] and we can enable tiered reads for [242:03] reporting. Now we don't have that role [242:06] right now but that's just an example if [242:07] we had that role. [242:10] Okay, this is the final section of this [242:13] part. We're going to be talking about [242:15] semantic search over agent failures. So, [242:19] we're going to combine time series [242:20] telemetry with vector search inside a [242:23] single Postgregql database. [242:27] When an AI agent fails, standard keyword [242:30] queries often miss the root cause due to [242:32] phrasing differences. We want to run a [242:35] semantic vector search restricted to [242:38] errors from the past seven days. [242:41] So look at this SQL right here [242:45] and actually this whole file here. So we [242:48] separate the embeddings into their own [242:50] table. It's called span embeddings [242:53] because a 1,536 dimension float vector [242:57] is about 6 kilobytes. We only embed [243:00] spans that contain errors, avoiding [243:03] massive overhead on healthy spans. [243:06] Because this new table is also a hyper [243:09] table, time scale uses chunk exclusion [243:12] to skip older time windows entirely [243:15] during the vector search. [243:18] Okay, so I've now just applied this [243:23] and now let's look what hybrid query [243:25] looks like. [243:26] So let's insert a mock error log into [243:29] our new table so we have something to [243:31] search for and then we will run our [243:34] hybrid query. So this will put in the [243:38] mock error [243:40] in your application code. You would use [243:42] a parameter variable here to pass in the [243:44] real embedding generated by open AAI. [243:48] Since we are in the terminal, I'm going [243:50] to use Postgress's array fill function [243:52] to generate a Mach 13,536 [243:56] dimensional vector on the fly. So, we [243:58] can see it execute. [244:01] And if you notice this little thing [244:03] here, the distance operator, it computes [244:07] cosine distance, which compares vector [244:09] angle rather than magnitude and the and [244:12] is the standard for text embeddings. [244:14] Now remember this is remember this [244:17] critical indexing rule for vector [244:18] databases. Your index operator class [244:21] must match your match your query [244:23] operator. So we created our HNSW index [244:27] using vector cosine ops. [244:31] So watch what happens when we write a [244:34] query using this other symbol here which [244:38] is which is the operator for ukidian [244:41] distance. If we look here, we can in the [244:45] query plan, we see that it drops back to [244:47] a sequential scan. [244:49] Postgress cannot use the agents index [244:53] because the math operators don't match. [244:55] Always verify that your query operator [244:57] matches your index definition. This [245:00] pattern completely eliminates the need [245:02] for a dedicated separate vector [245:04] database. You avoid running sync jobs [245:07] between dis desperate systems. You [245:11] eliminate eventual consistency lag and [245:14] you can combine relational joins, time [245:17] scale partitions and vector similarity [245:19] all in a single unified SQL query plan. [245:23] And before we wrap up this section, [245:25] let's step out of the terminal. Writing [245:27] SQL is only half the job of managing a [245:30] database. The other half is keeping it [245:32] healthy in production. So let's talk [245:35] just a little bit more about the cloud [245:37] tiger cloud console because this [245:39] dashboard provides visibility that you [245:41] simply cannot get from a command line. [245:44] So when you open your service you land [245:46] on the overview dashboard. This gives [245:49] you your core telemetry [245:52] CPU load memory usage [245:55] uh the storage used here and more. When [245:59] you are ingesting thousands of IoT [246:01] readings per a second, this is where you [246:03] watch for bottlenecks. If your CPU is [246:07] constantly pegged at 99% or your memory [246:10] starts swapping to disk, this dashboard [246:13] tells you it's time to scale up your [246:15] compute. Now, let's look at the explore [246:18] tab. This is the visual control center [246:21] specifically built for time series data. [246:24] If you click into our reading table, you [246:28] don't just see a standard Postgress t [246:30] table schema. The explorer understands [246:34] hypert. It shows you exactly how many [246:36] chunks exist and how much data is [246:39] compressed. Now let's look at the [246:41] explore tab. This is a visual control [246:45] center specifically built for time [246:47] series data. [246:49] If we click on the reading table here, [246:53] we don't just see a standard Postgress [246:56] table schema. The explorer understands [246:59] hyper tables. It shows you exactly how [247:02] many chunks exist, how much data is [247:04] compressed and how much storage you are [247:07] saving. You can also see the continuous [247:10] aggregates we built earlier [247:13] like the device daily and device hourly. [247:17] It visually tracks the background jobs [247:20] refreshing those views. If a refresh job [247:23] fails because of a bad data type or a [247:26] timeout, it gets flagged right here. You [247:30] don't have to hunt through Postgress [247:32] system tables to figure out why your [247:34] dashboards are suddenly outdated. [247:37] Now go to the monitoring tab. This is a [247:41] continuous agentless query performance [247:44] tracker. It basically watches every [247:45] query your application runs, normalize [247:47] them and ranks them. We can we have the [247:50] logs, the jobs, we have the insights. [247:53] Okay, so insights is giving us a like [247:57] really the most information or a lot of [247:59] information. We have connectors, we have [248:02] operations, [248:04] and there's a bunch of things you can do [248:05] on the operations tabs, but one thing I [248:07] want to point out is the backup and [248:09] restore and the create recovery fork. [248:13] Basically in a traditional Postgress [248:15] setup you have to test a massive schema [248:17] migration or a destructive query. If you [248:19] have you have if you need to do that you [248:21] need you have to spend hours restoring a [248:24] backup straight to a staging server. But [248:26] in tiger data you just can create a fork [248:32] because the platform uses copy and write [248:34] storage. It spins up an exact bite for [248:36] bite clone of your production database [248:39] in minutes running an isolated running [248:41] on isolated compute. You can run your [248:45] destructive experiments on the fork, [248:47] verify the results and then safely [248:48] delete it. So production is completely [248:51] untouched. [248:53] So the visibility and operational safety [248:55] is exactly why we've moved from a local [248:57] Docker container to a managed cloud [249:00] environment. [249:02] Okay, this wraps up this section. We [249:04] moved from local docker to manage cloud. [249:08] We validated complete schema [249:10] portability. Configure an IoT fleet with [249:12] hyperfunctions to handle real world edge [249:15] cases like counter resets and network [249:17] outages. Configured tier storage and uh [249:21] unified vector search with part-time [249:24] with time series data. Before we finish, [249:27] here are 12 things to check before [249:29] running this in production. You can use [249:31] the summary slide at the end of this as [249:33] a reference. One, enabling column store [249:35] and converting chunks are separate [249:37] steps. The alter table setting [249:39] configures column store. It doesn't [249:41] immediately convert existing chunks. [249:44] Check whether a conversion policy [249:46] already exists. If it does, it can [249:49] process eligible old chunks as well as [249:51] chunks that become eligible later. You [249:54] can also convert a specific chunk [249:56] manually when you want it done [249:57] immediately. Two, check how many rows [250:00] each segment will contain. A segment [250:03] column with too many distinct values can [250:07] leave you with small batches and poor [250:09] compression. Aim for enough rows per [250:12] segment within each chunk to fill [250:15] batches about of about a thousand rows. [250:18] For example, 8,000 readings for one [250:20] device would fill several batches. It [250:23] doesn't mean there's one batch [250:25] containing all 8,000. The query on [250:28] screen gives you an average so you can [250:30] check for devices or users with very few [250:33] records. If you segment by several [250:36] columns, check their combined values. [250:39] Three, choose sparse index settings [250:42] before converting chunks. Changing the [250:45] settings doesn't retrofit chunks already [250:47] in column store. to rebuild an existing [250:50] chunk with chunk with new settings. [250:53] Convert it back to row store and then to [250:55] column store again that rewrites data. [250:58] So plan for the for the time and disk [251:00] space it needs. Four, include a time [251:04] range and point lookups when you have [251:06] one. An IDON lookup can require [251:09] searching across many chunks. A time [251:12] condition lets the planner exclude [251:15] chunks outside the range. It doesn't [251:17] automatically reduce the query to one [251:20] chunk. That depends on how wide the [251:22] range is and how your chunks are [251:24] partitioned. Use the narrowest correct [251:26] range and check the plan. Five. Unique [251:30] constraints must include every [251:32] partitioning column. For a table [251:34] partitioned only by time, that can mean [251:37] a primary key on time and ID. If you've [251:40] added another partitioning dimension, [251:43] include that column too. UUIDv7 can help [251:47] because the ID contains a timestamp, but [251:50] UU UUID V7 alone doesn't bypass the [251:53] constraint rule. You still need the [251:56] appropriate unique key and a check [251:58] constraint that ties the time the time [252:00] column to the time stamp in the ID. Six, [252:03] keep aggregate refreshes inside the data [252:06] you still retain. If a refresh covers a [252:09] period whose source rows have been [252:11] deleted, the corresponding aggregate [252:14] rows can be removed, too. They aren't [252:17] necessarily replaced with zero. A chart [252:20] might show a gap or display zero if the [252:23] application fills missing values that [252:25] way. Keep the refresh loop back [252:28] comfortably shorter than source [252:30] retention. For example, refreshing 7 [252:33] days back while retaining 14 days gives [252:35] you a buffer. Check this whenever either [252:38] policy changes and be careful with [252:41] manual refreshes over older periods. [252:45] Seven, the reorder policy processes each [252:48] chunk once and skips the two newest. A [252:52] large backfill can add rows that no [252:54] longer follow the chunk's physical [252:55] order. If that affects your queries, run [252:58] reorder chunk again and on the affected [253:01] chunks. And if you're testing on fresh [253:04] data, remember that the policy [253:05] intentionally leaves the two newest [253:07] chunks alone. Eight, separate real- time [253:11] aggregation from refresh policy [253:14] settings. Setting materialize only to [253:17] false enables real time aggregation. A [253:20] query then combines materialized results [253:23] with new or raw data beyond the [253:25] materialization watermark. with no data [253:28] avoids populating the entire view when [253:31] you create it. It's useful for [253:33] controlling the initial refresh, but it [253:35] isn't required to enable real time aggre [253:38] aggregation. The offset controls how [253:41] close to the present the refresh runs. [253:44] 30 minutes for 15minute buckets is the [253:47] choice shown here, not a universal [253:50] minimum. Refreshes only complete buckets [253:54] within their window. Choose the offset [253:57] for your workload and how late your data [254:00] arrives. Also remember that corrections [254:03] to older already materialized buckets [254:06] still need a refresh. Real time mode [254:08] doesn't automatically recalculate all of [254:11] your history. [254:12] Nine watch chunk count and active index [254:16] size. Around a thousand chunks is use is [254:20] a useful point to review planning [254:22] overhead. Not a hard limit. An hourly [254:25] interval kept for a year creates 8,760 [254:31] chunks. So that deserves a look. Keeping [254:34] the indexes on actively written chunks [254:36] within roughly a quarter of available [254:38] memory is another starting guideline. [254:40] Consider all the active hyper tables [254:43] together. Measure your workload before [254:45] changing the interval and remember that [254:47] a new interval normally applies only to [254:49] new chunks. [254:52] 10. Check whether queries include tiered [254:55] data. Tiered reads are disabled by [254:58] default. Without enabling them, your [255:00] query can emit chunks in object storage [255:02] and still return a validlooking result. [255:05] Enable the setting for sessions or roles [255:07] that need that history. Expect reads [255:11] from object storage to have different [255:13] performance from reads on local storage. [255:16] 11. Monitor background job failures. A [255:19] failed refresh can leave the dashboard [255:21] serving old results even though the [255:23] dashboard query itself succeeds. Check [255:27] job errors and arrange alerts for [255:29] failures. We'll look at the error query [255:32] shortly. [255:34] 12. Review postgressql settings before [255:37] benchmarking. Memory limits, worker [255:40] capacity, and parallelism can all affect [255:43] your results. They don't replace good [255:45] schema design or appropriate time scale [255:48] DB settings for a self-hosted database. [255:51] Time scale DB tune or PG tune can [255:54] provide a starting configuration. Review [255:56] the recommendations, apply them, and [255:58] restart if the changed settings require [256:00] it. Then measure again. Manage services [256:04] provide a configured starting point, but [256:06] you still need to monitor your workload. [256:09] Here are the 12 checks together. Pause [256:11] here if you want to save the list. It's [256:13] also in the course repository. [256:16] Now, five monitoring queries you can [256:18] keep. One, how much space does [256:21] compression save? This query shows [256:24] before and after sizes and percentage [256:26] saved for each hyper table. Watch for [256:30] changes over time. A lower saving could [256:34] come from smaller batches. a change in [256:38] segment cardality or a change in the [256:41] data itself. Treat it as a reason to [256:44] investigate rather than a diagnosis on [256:46] its own. Two, how many chunks do we [256:49] have? This shows the total chunk count. [256:52] How many are compressed and the oldest [256:54] time range? If the count keeps growing, [256:57] check planning time and whether your [256:58] interval and retention still makes [257:00] sense. Changing the interval doesn't [257:02] resize the chunks already there. Three, [257:05] are the jobs succeeding? This combines [257:07] job definitions with their execution [257:10] statistics. Check the last run status, [257:13] the last successful finish, and the [257:16] failure count. A recent success doesn't [257:18] erase earlier failures, and a job that [257:21] hasn't run yet may have no execution [257:23] statistics. [257:25] Four, what failed. This shows the recent [257:28] error messages with the job ID and [257:30] finish time. Use it to investigate [257:33] failed policies and to build alerts. An [257:36] empty result means this query found no [257:39] recorded errors in the selected period. [257:42] It doesn't prove every expected job ran. [257:45] So keep checking job status too. Five. [257:49] How recent is the data returned by each [257:51] aggregate? This compares the end of the [257:54] latest bucket with the current time. [257:57] It's a useful freshness signal, but it [257:59] isn't a direct measurement of refresh [258:01] lag. If traffic stops, the latest bucket [258:04] gets older even when every refresh [258:07] succeeds. If real time aggregation is [258:10] enabled, recent raw data can make the [258:12] view look current even while [258:14] materialized refreshes are failing. An [258:17] empty view returns no age to measure. [258:20] Use this alongside the expected arrival [258:22] rate, job status, and job errors. Don't [258:25] alert on the bucket age alone without [258:27] considering those conditions. [258:30] Two other checks are worth keeping. [258:32] First, [258:34] background worker capacity. Allow room [258:37] for the data time scale DB schedulers [258:39] and the jobs that need to run at the [258:42] same time. You don't need a dedicated [258:44] worker for every policy you've created. [258:46] Also, check PostgresQL's overall worker [258:48] limit and whether jobs are being [258:50] delayed. [258:52] Second, use the usual PostgreSQL health [258:54] checks, connections, cash hits, bloat, [258:57] and longunning transactions. Keep using [259:00] those alongside the time scale DB [259:02] specific queries. Tigercloud also [259:04] provides monitoring in its console. And [259:08] four ideas to remember. First, hyperts [259:11] organize data into chunks so queries and [259:13] retention policies can work on the [259:15] relevant ranges. Second, choose storage [259:18] around how you use the data. Recent [259:21] writes often suit row store while older [259:24] analytical data can benefit from column [259:26] store. Third, keep mergeable states when [259:30] you need to combine summaries. Averages [259:32] need their weights and supported [259:34] percentile and distinct count states let [259:36] you combine estimates correctly. [259:38] Aggregates reduce repeated work, but [259:41] they don't guarantee constant query time [259:44] as every part of your workload grows. [259:46] Fourth, you're still using Postgress. [259:49] You can keep using its drivers, joins, [259:51] and monitoring tools while accounting [259:53] for the constraints and behavior of [259:55] hypert. We've reached the end. Thanks [259:58] for watching and happy coding.