Chrysolites Tech Academy

Data and Search for AI Apps · Course book

Chapter 3: Storing Data That Never Stops Arriving

Pairs with Lesson 3: Time-Series Data with TimescaleDB

Some data describes how things are: a customer's address, a product's price. Other data describes what happened and when: a temperature reading, a payment, a step taken by an AI agent. The second kind is time-series data, and it behaves so differently that ordinary database habits stop working. This chapter explains what makes it special and the four techniques that keep it manageable: partitioning by time, columnar compression, pre-computed summaries and moving old data to cheaper storage.

What makes time-series data different

Time-series data has three habits that set it apart.

It is appended, not edited. A reading taken at 10:00 is a fact about the past. New rows are added constantly, and old ones are almost never changed.

It arrives without pause, and it adds up. Consider a network of 2,000 electric vehicle chargers, each reporting its status every 10 seconds. One charger sends 6 readings a minute, which is 6 × 60 × 24 = 8,640 a day. The whole network sends 2,000 × 8,640 = 17,280,000 rows a day, and 17,280,000 × 365 = 6,307,200,000 rows a year. If a row takes about 60 bytes, that is roughly 1.04 gigabytes a day and about 378 gigabytes a year, from one modest fleet.

Recent data matters most. Questions are overwhelmingly about the latest hour or day ("is charger 714 working now?"). Older data is consulted rarely and usually in summary ("what was average usage each month last year?").

A single ordinary table handles this badly. Its indexes grow until they no longer fit in memory, every insert becomes slower, and deleting last year's data means hunting down billions of individual rows. TimescaleDB is an extension that adapts PostgreSQL to this workload while keeping standard SQL, so everything you already know about queries and joins still applies.

Hypertables and chunks

The central idea is to split one huge table into many small ones by time. In TimescaleDB you create a hypertable, which looks and behaves like a single table. Behind the scenes it is divided into chunks, each holding the rows for one period, such as one day or one week. You never address the chunks yourself; rows are routed to the correct one as they arrive, and new chunks are created automatically.

Hypertable "readings": what your queries see chunk 1chunk 2chunk 3chunk 4chunk 5chunk 6new rows tiered to cheap storagecompresseduncompressed oldernowtimeA query for the last hour opens chunk 6 only.
Figure 3.1 — One hypertable, many chunks. Each chunk covers a period of time, and its age decides how it is stored.

Chunking pays off in three ways. Inserts stay fast, because new rows go into the newest chunk, whose indexes are small enough to stay in memory. Queries stay fast, because a question about the last hour never opens last month's chunks; the database can tell from the time range alone that they are irrelevant. And removing old data becomes trivial: a whole expired chunk is dropped in one step, like deleting a file, with no row-by-row work. A retention policy does this automatically, for example by dropping chunks older than two years.

Choosing the chunk period is a balancing act. If chunks are too large, the newest one outgrows memory and inserts slow down again. If they are too small, the database must keep track of thousands of tiny tables, and planning each query takes longer. A common starting point is a period short enough that the recent chunks, together with their indexes, fit comfortably in the server's memory.

Columnar storage and compression

Databases normally store data row by row: all the fields of the first reading, then all the fields of the second. That suits looking up one whole record. Analysis of time-series data is different. A question such as "what was the average power yesterday?" needs one column from millions of rows.

Columnar storage turns the layout round and keeps each column's values together. This has two effects. A query reads only the columns it mentions and skips the rest. And values in one column tend to resemble their neighbours, which makes them highly compressible. Two simple tricks show the principle.

  • Storing differences. Timestamps 10 seconds apart, such as 10:00:00, 10:00:10, 10:00:20 and 10:00:30, can be stored as the first value followed by "+10, +10, +10". Small repeated differences take far less room than full timestamps.
  • Storing runs. A status column reading "idle" 500 times in a row can be stored as "idle × 500".

Savings of 90 percent or more are common for this kind of data. Applied to the example, a 90 percent saving would cut the 378 gigabytes a year to about 38 gigabytes. The cost is that compressed data is slower to modify, which is why the newest chunk is left in row form to accept inserts and a policy compresses each chunk once it reaches a certain age, such as seven days.

Continuous aggregates

A dashboard showing average power per charger per hour over the last month would have to read about 17 million rows for each day displayed. Since the past does not change, repeating that work on every page load is wasteful.

A continuous aggregate is a summary table that the database maintains for you. You define it once with an ordinary query, using the time_bucket function to group rows into fixed periods:

SELECT time_bucket('1 hour', time) AS hour,
       charger_id,
       avg(power_kw) AS avg_power,
       max(power_kw) AS peak_power
FROM readings
GROUP BY hour, charger_id;

The database stores the result and keeps it current in the background, recalculating only the periods where new data has arrived. The saving is easy to work out. Hourly summaries for 2,000 chargers come to 2,000 × 24 = 48,000 rows a day, against 17,280,000 raw rows. That is 360 times fewer rows to scan, since each hour holds 360 ten-second readings.

Keep summaries longer than detail. Raw readings can be dropped after a couple of years while the hourly summaries built from them are kept indefinitely. You lose the ability to zoom in on one old second, and keep the long-term trends at a tiny fraction of the storage.

Gaps, hyperfunctions and tiering

Real devices lose their connection, so real time-series data has holes. A chart drawn from it will either show a misleading straight jump or break. TimescaleDB adds a set of analytical functions for time-series work, called hyperfunctions, and several deal with gaps.

Suppose a sensor reports every five minutes: 20 at 10:00, 22 at 10:05, nothing at 10:10, then 28 at 10:15 and 30 at 10:20. A gap-filling query first creates a row for the missing time and then chooses a value for it. Last observation carried forward repeats the previous value, giving 22. Linear interpolation draws a straight line between the neighbours, giving (22 + 28) ÷ 2 = 25.

interpolated: 25carried forward: 22 20222830 10:0010:0510:1010:1510:20time of reading 2030
Figure 3.2 — Two ways to fill a missing reading. Carrying the last value forward suits things that change in steps, such as a status; interpolation suits things that change smoothly, such as temperature.
FunctionWhat it doesExample use
time_bucketGroups rows into fixed periods of timeAverage power per hour
first and lastReturn the earliest or latest value within each groupOpening and closing reading for each day
time_bucket_gapfillLike time_bucket, but also creates rows for empty periodsA chart with no missing points
locfFills a gap with the last known valueDevice status during a dropped connection
interpolateFills a gap on a straight line between its neighboursTemperature between two readings

The last technique concerns cost. Fast disks are expensive, and most old data is rarely read. Data tiering moves old chunks to low-cost object storage while leaving them visible to queries. A question about last week runs at full speed against local disks; a question about three years ago still works, only more slowly. Table 3.2 shows how the pieces combine into a lifecycle for the charger data.

Age of dataHow it is storedWhy
Under 7 daysRow form, uncompressed, on fast diskConstant inserts and frequent detailed queries
7 days to 3 monthsCompressed columnar form on fast diskRead often for analysis, almost never changed
3 months to 2 yearsTiered to object storageSeldom read, so cost matters more than speed
Over 2 yearsRaw rows dropped; hourly summaries keptOnly long-term trends are still needed

Because all this happens inside PostgreSQL, time-series tables can sit beside ordinary tables and even vector columns. That is useful for AI applications: a log recording every step an agent takes is itself time-series data, and it can be joined to customer records or searched by meaning without leaving the database.

Check your understanding

  • Name the three habits that set time-series data apart from ordinary records.
  • 500 sensors each report once every 30 seconds. How many rows arrive in a day? (Answer: 2 a minute is 2 × 60 × 24 = 2,880 per sensor, and 500 × 2,880 = 1,440,000 rows.)
  • Why is dropping a whole chunk much faster than deleting the same rows one at a time?
  • Why is the newest chunk normally left uncompressed?
  • Readings every 30 seconds are summarised into 15-minute buckets. How many times fewer rows does the summary hold? (Answer: 15 minutes holds 30 readings, so 30 times fewer.)
  • A sensor reads 40 at 09:00 and 50 at 09:20, with 09:10 missing. What value does each gap-filling method give for 09:10? (Answer: 40 carried forward, 45 interpolated.)

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.