DEV Community: Ryan Booz

How to shape sample data with PostgreSQL generate_series() and SQL

Ryan Booz — Wed, 02 Feb 2022 13:04:12 +0000

Data inception
Math class flashback!
Wrapping it up

In the lifecycle of any application, developers are often asked to create proof-of-concept features, test newly released functionality, and visualize data analysis. In many cases, the available test data is stale, not representative of normal usage, or simply doesn't exist for the feature being implemented. In situations like this, knowing how to quickly create sample time-series data with native PostgreSQL and SQL functions is a valuable skill to draw upon!

In this three-part series on generating sample time-series data, we demonstrate how to use the built-in PostgreSQL function, generate_series(), to more easily create large sets of data to help test various workloads, database features, or just to create fun samples.

In part 1 and part 2 of the series, we reviewed how generate_series() works, how to join multiple series using a CROSS JOIN to create large datasets quickly, and finally how to create and use custom PostgreSQL functions as part of the query to generate more realistic values for your dataset. If you haven't used generate_series() much before, we recommend first reading the other two posts. The first one is an intro to the generate_series() function, and the second one shows how to generate more realistic data.

With those skills in hand, you can quickly and easily generate tens of millions of rows of realistic-looking data. Even still, there's one more problem that we hinted at in part 2 - all of our data, regardless of how it's formatted or constrained, is still based on the random() function. This means that over thousands or millions of samples, every device we create data for will likely have the same MAX() and MIN() value, and the distribution of random values over millions of rows for each device generally means that all devices will have similar average values.

This third post demonstrates a few methods for influencing how to create data that mimics a desired shape or trend. Do you need to simulate time-series values that cycle over time? What about demonstrating a counter value that resets every so often to test the counter_agg hyperfunction? Are you trying to create new dashboards that display sales data over time, influenced for different months of the year when sales would ebb and flow?

Below we'll cover all of these examples to provide you with the final building blocks to create awesome sample data for all of your testing and exploration needs. Remember, however, that these examples are just the beginning. Keep playing. Tweak the formulas or add different relational data to influence the values that get generated so that it meets your use case.

Data inception

Time-series data often has patterns. Weather temperatures and rainfall measurements change in a (mostly) predictable way throughout the year. Vibration measurements from an IoT device connected to an air conditioning system usually increase in the summer and decrease in the winter. Manufacturing data that measures the total units produced per hour (and the percentage of defective units) usually follow a pattern based on shift schedules and seasonal demand.

If you want to demonstrate this kind of data without having access to the production dataset, how would you go about it using generate_series()? SQL functions ended up being pretty handy when we discussed different methods for creating realistic-looking data in part 2. Do you think they might help here? 😉

Two options to easily return the row number

Remember, for our purposes we're specifically talking about creating sample time-series data. Every row increases along the time axis, and if we use the multiplication formula from part 1, we can determine how many rows our sample data query will generate. Using built-in SQL functions, we can quickly start manipulating data values that change with the cycle of time. 💥

There are many reasons why it can be helpful to know the ordinal position of each row number in a query result. That's why standard SQL dialects have some variation of the row_number() over() window function. This simple, yet powerful, window function allows us to return the row number of a result set, and can utilize the ORDER BY and PARTITION keywords to further determine the row values.

SELECT ts, row_number() over(order by time) AS rownum
FROM generate_series('2022-01-01','2022-01-05',INTERVAL '1 day') ts;

ts                          |rownum|
-----------------------------+------+
2022-01-01 00:00:00.000 -0500|     1|
2022-01-02 00:00:00.000 -0500|     2|
2022-01-03 00:00:00.000 -0500|     3|
2022-01-04 00:00:00.000 -0500|     4|
2022-01-05 00:00:00.000 -0500|     5|

In a normal query, this can be useful for tasks like paging data in a web API when there is a need to consistently return values based on a common partition.

There's one problem though. row_number() over() requires PostgreSQL (and any other SQL database) to process the query results twice to add the values correctly. Therefore, it's very useful, but also very expensive as datasets grow.

Fortunately, PostgreSQL helps us once again for our specific use case of generating sample time-series data.

Through this series of blog posts on generating sample time-series data, we've discussed that generate_series() is a Set Returning Function (SRF). Like the results from a table, set data can be JOINed and queried. Additionally, PostgreSQL provides the WITH ORDINALITY clause that can be applied to any SRF to generate an additional, incrementing BIGINT column. The best part? It doesn't require a second pass through the data in order to generate this value!

SELECT ts AS time, rownum
FROM generate_series('2022-01-01','2022-01-05',INTERVAL '1 day') WITH ORDINALITY AS t(ts,rownum);

time                         |rownum|
-----------------------------+------+
2022-01-01 00:00:00.000 -0500|     1|
2022-01-02 00:00:00.000 -0500|     2|
2022-01-03 00:00:00.000 -0500|     3|
2022-01-04 00:00:00.000 -0500|     4|
2022-01-05 00:00:00.000 -0500|     5|

Because it serves our purpose and is more efficient, the remainder of this post will use WITH ORDINALITY. However, remember that you can accomplish the same results using row_number() over() if that's more comfortable for you.

Harnessing the row value

With increasing timestamps and an increasing integer on every row, we can begin to use other functions to create interesting data.

Remember from the previous blog posts that calling a function as part of your query executes the function for each row and returns the value. Just like a regular column, however, we don't have to actually emit that column in the final query results. Instead, the function value for that row can be used in calculating values in other columns.

As an example, let's modify the previous query. Instead of displaying the row number, let's multiply the value by 2. That is, the function value is treated as an input to a multiplication formula.

SELECT ts AS time, 2 * rownum AS rownum_by_two
FROM generate_series('2022-01-01','2022-01-05',INTERVAL '1 day') WITH ORDINALITY AS t(ts,rownum);

time                         |rownum_by_two|
-----------------------------+------+
2022-01-01 00:00:00.000 -0500|     2|
2022-01-02 00:00:00.000 -0500|     4|
2022-01-03 00:00:00.000 -0500|     6|
2022-01-04 00:00:00.000 -0500|     8|
2022-01-05 00:00:00.000 -0500|   10|

Easy enough, right? What else can we do with the row number value?

Counters with reset

Many time-series datasets record values that reset over time, often referred to as counters. The odometer on a car is an example. If you drive far enough, it will "roll over" to zero again and start counting upward. The same is true for many utilities, like water and electric meters, that track consumption. Eventually, the total digits will increment to the point where the counter resets and starts from zero again.

To simulate this with time-series data, we can use the incrementing row number and after some period of time, reset the count and start over using the modulus operator (%).

– This example resets the counter every 10 rows 
WITH counter_rows AS (
    SELECT ts, 
        CASE WHEN rownum % 10 = 0 THEN 10
             ELSE rownum % 10 END AS row_counter
    FROM generate_series(now() - INTERVAL '5 minutes', now(), INTERVAL '1 second') WITH ORDINALITY AS t(ts, rownum)
)
SELECT ts, row_counter
FROM counter_rows;


ts                         |row_counter|
-----------------------------+-----------+
2022-01-07 13:17:46.427 -0500|          1|
2022-01-07 13:17:47.427 -0500|          2|
2022-01-07 13:17:48.427 -0500|          3|
2022-01-07 13:17:49.427 -0500|          4|
2022-01-07 13:17:50.427 -0500|          5|
2022-01-07 13:17:51.427 -0500|          6|
2022-01-07 13:17:52.427 -0500|          7|
2022-01-07 13:17:53.427 -0500|          8|
2022-01-07 13:17:54.427 -0500|          9|
2022-01-07 13:17:55.427 -0500|         10|
2022-01-07 13:17:56.427 -0500|          1|
… | …

By putting the CASE statement inside of the CTE, the counter data can be selected more easily to test other functions. For instance, to see how the rate() and delta() hyperfunctions work, we can use time_bucket() to group our 1-second readings into 1-minute buckets.

WITH counter_rows AS (
    SELECT ts, 
        CASE WHEN rownum % 10 = 0 THEN 10
             ELSE rownum % 10 END AS row_counter
    FROM generate_series(now() - INTERVAL '5 minutes', now(), INTERVAL '1 second') WITH ORDINALITY AS t(ts, rownum)
)
SELECT time_bucket('1 minute', ts) bucket, 
  delta(counter_agg(ts,row_counter)),
  rate(counter_agg(ts, row_counter))
FROM counter_rows
GROUP BY bucket
ORDER BY bucket;

bucket                       |delta|rate|
-----------------------------+-----+----+
2022-01-07 13:25:00.000 -0500| 33.0| 1.0|
2022-01-07 13:26:00.000 -0500| 59.0| 1.0|
2022-01-07 13:27:00.000 -0500| 59.0| 1.0|
2022-01-07 13:28:00.000 -0500| 59.0| 1.0|
2022-01-07 13:29:00.000 -0500| 59.0| 1.0|
2022-01-07 13:30:00.000 -0500| 26.0| 1.0|

time_bucket() outputs the starting time of the bucket, which based on our date math for generate_series() produces four complete buckets of 1-minute aggregated data, and two partial buckets - one for the minute we are currently in, and a second bucket for the partial 5 minutes ago. We can see that the delta correctly calculates the difference between the last and first readings of each bucket, and the rate of change (the increment between each reading) correctly displays a unit of one.

What are some other ways we can use these PostgreSQL functions to generate different shapes of data to help you explore other features of SQL and TimescaleDB quickly?

Increasing trend over time

With the knowledge of how to create an ordinal value for each row of data produced by generate_series(), we can explore other ways of generating useful time-series data. Because the row number value will always increase, we can easily produce a random dataset that always increases over time but has some variability to it. Consider this a very rough representation of daily website traffic over the span of two years.

SELECT ts, (10 + 10 * random()) * rownum as value FROM generate_series
       ( '2020-01-01'::date
       , '2021-12-31'::date
       , INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

Sample daily website traffic growing over time with random daily values

In reality this chart isn't very realistic or representative. Any website that gains and loses viewers upwards of 50% per day probably isn't going to have great long-term success. Don't worry, we can do better with this example after we learn about another method for creating shaped data using sine waves.

Simple cycles (sine wave)

Using the built-in sin() and cos() PostgreSQL functions, we can generate data useful for graphing and testing functions that need a predictable data trend. This is particularly useful for testing TimescaleDB downsampling hyperfunctions like lttb or asap. These functions can take tens of thousands (or millions) of data points and return a smaller, but still accurately representative dataset for graphing.

We'll start with a basic example that produces one row per day, for 30 days. For each row number value, we'll get the sine value that can be used to graph a wave.

–- subtract 1 from the row number for wave to start
-- at zero radians and produce a more representative chart
SELECT  ts,
 cos(rownum-1) as value
FROM generate_series('2021-01-01','2021-01-30',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

ts                         |value                 |
-----------------------------+--------------------+
2021-01-01 00:00:00.000 -0500|                 1.0|
2021-01-02 00:00:00.000 -0500|  0.5403023058681398|
2021-01-03 00:00:00.000 -0500| -0.4161468365471424|
2021-01-04 00:00:00.000 -0500| -0.9899924966004454|
2021-01-05 00:00:00.000 -0500| -0.6536436208636119|
2021-01-06 00:00:00.000 -0500| 0.28366218546322625|
… | …

Unfortunately, the graph of this SINE wave doesn't look all that appealing. For one month of daily data points, we only have ~6 distinct data points from peak to peak of each wave.

Cosine wave graph using daily values for one month

The reason our sine wave is so jagged is because sine and cosine values are measured in radians (based on 𝞹), not degrees. A complete cycle (peak-to-peak) on a sine wave happens from zero to 2*𝞹 (~6.28…). Therefore, every ~6 rows of data will produce a complete period in the wave - unless we find a way to modify that value.

To take control over the sine/cosine values, we need to think about how to modify the data based on the date range and interval (how many rows) and what we want the wave to look like.

This means we need to take a quick trip back to math class to talk about radians.

Math class flashback!

Step back with me for a minute to primary school and your favorite math subject - Algebra 2 (or Trigonometry as the case may be). How many hours did you spend working with graph paper (or graphing calculators) determining the amplitude, period, and shift of a sine or cosine graph?

Sine wave period and amplitude

If you reach even further into your memory, you might remember this formula which allows you to modify the various aspects of a wave.

Mathematical formula for modifying the shape and values of a sine wave

There's a lot here, I know. Let's primarily focus on the two numbers that matter most for our current use case:

X = the "number of radians", which is the row number in our dataset
B = a value to multiply the row number by, to decrease the "radian" value for each row

(A, C, and D change the height and placement of the wave, but to start, we want to elongate each period and provide more "points" on the line to graph.)

Let's start with a small dataset example, generating cosine data for three months of daily timestamps with no modifications.

SELECT  ts, 
cos(rownum) as value
FROM generate_series('2021-01-01','2021-03-31',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);


ts                         |value                  |
-----------------------------+---------------------+
2021-01-01 00:00:00.000 -0500|   0.5403023058681398|
2021-01-02 00:00:00.000 -0500|  -0.4161468365471424|
2021-01-03 00:00:00.000 -0500|  -0.9899924966004454|
2021-01-04 00:00:00.000 -0500|  -0.6536436208636119|
2021-01-05 00:00:00.000 -0500|  0.28366218546322625|
2021-01-06 00:00:00.000 -0500|    0.960170286650366|
2021-01-07 00:00:00.000 -0500|   0.7539022543433046|
2021-01-08 00:00:00.000 -0500| -0.14550003380861354|
2021-01-09 00:00:00.000 -0500|  -0.9111302618846769|
… | …
2021-03-29 00:00:00.000 -0400|   0.9993732836951247|
2021-03-30 00:00:00.000 -0400|   0.5101770449416689|
2021-03-31 00:00:00.000 -0400|  -0.4480736161291701|

Cosine wave with daily data for three months

In this example, we see ~14 peaks in our wave because there are 90 points of data and without modification, the wave will have a period (peak-to-peak) every ~6.28 points. To lengthen the cycle, we need to perform some simple division.

[cycle modifying value] = 6.28/[total interval (rows) per cycle]

Using the same 3 months of generated daily values, let's see how to modify the data to lengthen the period of the wave.

One cycle per month (30 days)

If we want our daily data to cycle every 30 days, multiply our row number value by 6.28/30.

6.28/30 = .209 (the row number radians modifier)

SELECT  ts, cos(rownum * 6.28/30) as value
FROM generate_series('2021-01-01','2021-03-31',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

Cosine wave using daily data for three months, adjusted to have monthly periods

One cycle per quarter (90 days)

6.28/90 = .07 (this is our radians modifier)

SELECT  ts, cos(rownum * 6.28/90) as value
FROM generate_series('2021-01-01','2021-03-31',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

Cosine wave using daily data for three months, adjusted to have one 3-month period

To modify the overall length of the period, you need to modify the row number value based on the total number of rows in the result and the granularity of the timestamp.

Here are some example values that you can use to modify the wave period based on the interval used with generate_series().

generate_series() interval	Desired period length	Divide 6.28 by…
daily	1 month	30
daily	3 months	90
hourly	1 day	24
hourly	1 week	168
hourly	1 month	720
minute	1 hour	60
minute	1 day	1440

Modifying the wave amplitude and shift

Another tweak we can make to our wave data is to change the amplitude (difference between the min and max peaks) and, as necessary, shift the wave up or down on the Y-axis.

To do this, multiply the cosine value by the value that maximum value you want the wave to have. For example, we can multiply the monthly cycle data by 10, which changes the overall minimum and maximum values of the data.

SELECT  ts, 10 * cos(rownum * 6.28/30) as value
FROM generate_series('2021-01-01','2021-03-31',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

Cosine wave using daily data for three months with increased amplitude

Notice that the min/max values are now from -10 to 10.

We can take it one step further by adding a value to the output which will shift the final values up or down on the Y-axis. In this example, we modified the previous query by adding 10 to the value of each row which results in values from 0 to 20.

SELECT  ts, 10 + 10 * cos(rownum * 6.28/30) as value
FROM generate_series('2021-01-01','2021-03-31',INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum);

Cosine wave using daily data for three months with shifted min/max values on the Y-axis

Why spend so much time showing you how to generate and manipulate sine or cosine wave data, especially when we rarely see repeatable data this smooth in real life?

One of the main advantages of using consistent, predictable data like this in testing is that you can easily tell if your application, charting tools, and query are working as expected. Once you begin adding in unpredictable, real-life data, it can be difficult to determine if the data, query, or application are producing unexpected results. Quickly generating known data with a specific pattern can help rule out errors with the query, at least.

The second advantage of using a known dataset is that it can be used to shape and influence the results of other queries. Earlier in this post, we demonstrated a very simplistic example of increasing website traffic by multiplying the row number and a random value. Let's look at how we can join both datasets to create a better shape for the sample website traffic data.

Better website traffic samples

One of the key takeaways from this series of posts is that generate_series() returns a set of data that can be JOINed and manipulated like data from a regular table. Therefore, we can join together our rough "website traffic" data and our sine wave to produce a smoother, more realistic set of data to experiment with. SQL for the win!

Overall this is one of the more complex examples we've presented, utilizing multiple common table expressions (CTE) to break the various sets into separate tables that we can query and join. However, this also means that you can independently modify the time range and other values to change the data that is generated from this query for your own experimentation.

-- This is the generate series data
-- with a "short" date to join with later
WITH daily_series AS ( 
    SELECT ts, date(ts) AS day, rownum FROM generate_series
       ( '2020-01-01'
       , '2021-12-31'
       , '1 day'::interval) WITH ORDINALITY AS t(ts, rownum)
),
-- This selects the time, "day", and a 
-- random value that represents our daily website visits
daily_value AS ( 
    SELECT ts, day, rownum, random() AS val
    FROM daily_series
    ORDER BY day
),
-- This cosine wave dataset has the same "day" values which allow 
-- it to be joined to the daily_value easily. The wave value is used to modify
-- the "website" value by some percentage to smooth it out 
-- in the shape of the wave.
daily_wave AS ( 
    SELECT
       day,
       -- 6.28 radians divided by 180 days (rows) to get 
       -- one peak every 6 months (twice a year)
       1 + .2 * cos(rownum * 6.28/180) as p_mod
       FROM daily_series
       day
)
-- (500 + 20 * val) = 500-520 visits per day before modification
-- p_mod = an adjusted cosine value that raises or lowers our data each day
-- row_number = a big incremental value for each row to quickly increase "visits" each day
SELECT dv.ts, (500 + 20 * val) * p_mod * rownum as value
FROM daily_value dv
    INNER JOIN daily_wave dw ON dv.DAY=dw.DAY
    order by ts;

Combining wave data and random increasing data to shape the data pattern

Without much effort, we are able to generate a time-series dataset, use two different SQL functions, and join multiple sets together to create fun, graphical data. In this example, our traffic peaks twice a year (every ~180 days) during July and late December.

But we don't have to stop there. We can carry our website traffic example one step further by applying just a little more control over how much the data increases or decreases during certain periods.

Once again, relational data to the rescue!

Influence the pattern with relational data

As a final example, let's consider one other type of data that we can include in our queries which influence the final generated values - relational data. Although we've been using data that was created using generate_series() to produce some fun and interesting sample datasets, we can just as easily JOIN to other data in our database to further manipulate the final result.

There are many ways you could JOIN to and use additional data depending on your use case and the type of time-series data you're trying to mimic. For example:

IoT data from weather sensors: store the typical weekly temperature highs/lows in a database table and use those values as input to the random_between() function we created in post 2
Stock data analysis: store the dates for quarterly disclosures and a hypothetical factor that will influence the impact on stock price moving forward
Sales or website traffic: store the monthly or weekly change observed in a typical sales cycle. Does traffic or sales increase a quarter-end? What about during the end-of-year holiday season?

To demonstrate this, we'll use the fictitious website traffic data from earlier in this post. Specifically, we've decided that we want to see a spike in traffic during June and December.

First, we create a regular PostgreSQL table to store the numerical month (1-12) and a float value which will be used to modify our generated data (up or down). This will allow us to tweak the overall shape for a given month.

CREATE TABLE overrides (
    m_val INT NOT NULL,
    p_inc FLOAT4 NOT null
);

INSERT INTO overrides(m_val, p_inc) VALUES 
    (1,.1.04), – 4% residual increase from December
    (2,1),
    (3,1),
    (4,1),
    (5,1),
    (6,1.10),-- June increase of 10%
    (7,1),
    (8,1),
    (9,1),
    (10,1),
    (11,1.08), -- 8% early shoppers sales/traffic growth
    (12,1.18); -- 18% holiday increase

Using this simple dataset, let's first join it to the "simplistic" query that had randomly growing data over time.

WITH daily_series AS (
-- a random value that increases over time based on the row number
SELECT ts, date_part('month',ts) AS m_val, (10 + 10*random()) * rownum as value FROM generate_series
       ( '2020-01-01'::date
       , '2021-12-31'::date
       , INTERVAL '1 day') WITH ORDINALITY AS t(ts, rownum)
)
-- join to the `overrides` table to get the 'p_inc' value 
-- for the month of the current row
SELECT ts, value * p_inc AS value FROM daily_series ds
INNER JOIN overrides o ON ds.m_val=o.m_val
ORDER BY ts;

Sample website traffic for two years, modified during some months with relational data

Joining to the overrides table based on the month of each data point, we are able to multiply the percentage increase (p_inc) value and the fake website traffic value to influence the trend of our data during specific time periods.

Combining everything we've learned and taking this example one step further, we can enhance the cosine data query with the same monthly override values to tweak our fake, cyclical time-series data that represents growing website traffic with a more realistic shape.

-- This is the generate series data
-- with a "short" date to join with later
WITH daily_series AS ( 
    SELECT ts, date(ts) AS day, rownum FROM generate_series
       ( '2020-01-01'
       , '2021-12-31'
       , '1 day'::interval) WITH ORDINALITY AS t(ts, rownum)
),
-- This selects the time, "day", and a 
-- random value that represents our daily website visits
-- 'm_val' will be used to join with the 'overrides' table
daily_value AS ( 
    SELECT ts, day, date_part('month',ts) as m_val, rownum, random() AS val
    FROM daily_series
    ORDER BY day
),
-- This cosine wave dataset has the same "day" values which allow 
-- it to be joined to the daily_value easily. The wave value is used to modify
-- the "website" value by some percentage to smooth it out 
-- in the shape of the wave.
daily_wave AS ( 
    SELECT
       day,
       -- 6.28 radians divided by 180 days (rows) to get 
       -- one peak every 6 months (twice a year)
       1 + .2 * cos(rownum * 6.28/180) as p_mod
       FROM daily_series
       day
)
-- (500 + 20 * val) = 500-520 visits per day before modification
-- p_mod = an adjusted cosine value that raises or lowers our data each day
-- row_number = a big incremental value for each row to quickly increase "visits" each day
-- p_inc = a monthly adjustment value taken from the 'overrides' table
SELECT dv.ts, (500 + 20 * val) * p_mod * rownum * p_inc as value
FROM daily_value dv
    INNER JOIN daily_wave dw ON dv.DAY=dw.DAY
    inner join overrides o on dv.m_val=o.m_val
    order by ts;

Sample website traffic for two years combined with sine wave data and modified during some months with relational data

Wrapping it up

In this 3rd and final blog post of our series about generating sample time-series datasets, we demonstrated how to add shape and trend into your sample time-series data (e.g., increasing web traffic over time and quarterly sales cycles) using built-in SQL functions and relational data. With a little bit of math mixed in, we learned how to manipulate the pattern of generated data, which is particularly useful for visualizing time-series data and learning analytical PostgreSQL or TimescaleDB functions.

To see some of these examples in action, watch my video on creating realistic sample data:

If you have questions about using generate_series() or have any questions about TimescaleDB, please join our community Slack channel, where you'll find an active community and a handful of the Timescale team most days.

If you want to try creating larger sets of sample time-series data using generate_series() and see how the exciting features of TimescaleDB work, sign up for a free 30-day trial or install and manage it on your instances. (You can also learn more by following one of our many tutorials.)

Generating more realistic sample time-series data with PostgreSQL generate_series()

Ryan Booz — Mon, 13 Dec 2021 12:15:24 +0000

A brief review of generate_series()
Creating more realistic numbers
Creating more realistic text
Creating sample JSON
Putting it all together
Reviewing our progress

In part 1 of the series, we reviewed how generate_series() works, including the ability to join multiple series into a larger table of time-series data - through a feature known as a CROSS (or Cartesian) JOIN. We ended the first post by showing you how to quickly calculate the number of rows a query will produce and modify the parameters for generate_series() to fine-tune the size and shape of the data.

However, there was one problem with the data we could produce at the end of the first post. The data that we were able to generate was very basic and not very realistic. Without more effort, using functions like random() to generate values doesn't provide much control over precisely what numbers are produced, so the data still feels more fake than we might want.

This second post will demonstrate a few ways to create more realistic-looking data beyond a column or two of random decimal values. Read on for more.

In the coming weeks, part 3 of this blog series will add one final tool to the mix - combining the data formatting techniques below with additional equations and relational data to shape your sample time-series output into something that more closely resembles real-life applications.

By the end of this series, you'll be ready to test almost any feature that TimescaleDB offers and create quick datasets for your testing and demos!

A brief review of generate_series()

In the first post, we demonstrated how generate_series() (a Set Returning Function) could quickly create a data set based on a range of numeric values or dates. The generated data is essentially an in-memory table that can quickly create large sets of sample data.

-- create a series of values, 1 through 5, incrementing by 1
SELECT * FROM generate_series(1,5);

generate_series|
---------------|
              1|
              2|
              3|
              4|
              5|


-- generate a series of timestamps, incrementing by 1 hour
SELECT * from generate_series('2021-01-01','2021-01-02', INTERVAL '1 hour');

    generate_series     
------------------------
 2021-01-01 00:00:00+00
 2021-01-01 01:00:00+00
 2021-01-01 02:00:00+00
 2021-01-01 03:00:00+00
 2021-01-01 04:00:00+00

We then discussed how the data quickly becomes more complex as we join the various sets together (along with some value returning functions) to create a multiple of both sets together.

This example from the first post joined a timestamp set, a numeric set, and the random() function to create fake CPU data for four fake devices over time.

-- there is an implicit CROSS JOIN between the two generate_series() sets
SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series('2021-01-01 00:00:00','2021-01-01 04:00:00',INTERVAL '1 hour') as time, 
generate_series(1,4) device_id;


time               |device_id|cpu_usage          |
-------------------+---------+-------------------+
2021-01-01 00:00:00|        1|0.35415126479989567|
2021-01-01 01:00:00|        1| 14.013393572770028|
2021-01-01 02:00:00|        1|   88.5015939122006|
2021-01-01 03:00:00|        1|  97.49037810105996|
2021-01-01 04:00:00|        1|  50.22781125586846|
2021-01-01 00:00:00|        2|  46.41196423062297|
2021-01-01 01:00:00|        2|  74.39903569177027|
2021-01-01 02:00:00|        2|  85.44087332221935|
2021-01-01 03:00:00|        2|  4.329394730750735|
2021-01-01 04:00:00|        2| 54.645873866589056|
2021-01-01 00:00:00|        3|  63.01888063314749|
2021-01-01 01:00:00|        3|  21.70606884856987|
2021-01-01 02:00:00|        3|  32.47610779097485|
2021-01-01 03:00:00|        3| 47.565982341726354|
2021-01-01 04:00:00|        3|  64.34867263419619|
2021-01-01 00:00:00|        4|   78.1768041898232|
2021-01-01 01:00:00|        4|  84.51505102850199|
2021-01-01 02:00:00|        4| 24.029611792753514|
2021-01-01 03:00:00|        4|  17.08996115345549|
2021-01-01 04:00:00|        4| 29.642690955760997|

And finally, we talked about how to calculate the total number of rows your query would generate based on the time range, the interval between timestamps, and the number of "things" for which you are creating fake data.

Range of readings	Length of interval	Number of "devices"	Total rows
1 year	1 hour	4	35,040
1 year	10 minutes	100	5,256,000
6 months	5 minutes	1,000	52,560,000

Still, the main problem remains. Even if we can generate 50 million rows of data with a few lines of SQL, the data we generate isn't very realistic. It's all random numbers, with lots of decimals and minimal variation.

As we saw in the query above (generating fake CPU data), any columns of data that we add to the SELECT query are added to each row of the resulting set. If we add static text (like 'Hello, Timescale!'), that text is repeated for every row. Likewise, adding a function as a column value will be called one time for each row of the final set.

That's what happened with the random() function in the CPU data example. Every row has a different value because the function is called separately for each row of generated data. We can use this to our advantage to begin making the data look more realistic.

With a little more thought and custom PostgreSQL functions, we can start to bring our sample data "to life."

What is realistic data?

This feels like a good time to make sure we're on the same page. What do I mean by "realistic" data?

Using the basic techniques we've already discussed allows you to create a lot of data quickly. In most cases, however, you often know what the data you're trying to explore looks like. It's probably not a bunch of decimal or integer values. Even if the data you're trying to mimic are just numeric values, they likely have valid ranges and maybe a predictable frequency.

Take our simple example of CPU and temperature data from above. With just two fields, we have a few choices to make if we want the generated data to feel more realistic.

Is CPU a percentage? Out of 100% or are we representing multi-core CPUs that can present as 200%, 400%, or 800%?
Is temperature measured in Fahrenheit or Celsius? What are reasonable values for CPU temperature in each unit? Do we store temperature with decimals or as an integer in the schema?
What if we added a "note" field to the schema for messages that our monitoring software might add to the readings from time to time? Would every reading have a note or just when a threshold was reached? Is there a special diagnostic message at the top of each hour that we need to replicate in some way?

Using random() and static text by themselves allows us to generate lots of data with many columns, but it's not going to be very interesting or as useful in testing features in the database.

That's the goal of the second and third posts in this series, helping you to produce sample data that looks more like the real thing without much extra work. Yes, it will still be random, but it will be random within constraints that help you feel more connected to the data as you explore various aspects of time-series data.

And, by using functions, all of the work is easily reusable from table to table.

Walk before you run

In each of the examples below, we'll approach our solutions much as we learned in elementary math class: show your work! It's often difficult to create a function or procedure in PostgreSQL without playing with a plain SQL statement first. This abstracts away the need to think about function inputs and outputs at the outset so that we can focus on how the SQL works to produce the value we want.

Therefore, the examples below show you how to get a value (random numbers, text, JSON, etc.) in a SELECT statement first before converting the SQL into a function that can be reused. This kind of iterative process is a great way to learn features of PostgreSQL, particularly when it's combined with generate_series().

So, take one foot and put it in front of the other, and let's start creating better sample data.

Creating more realistic numbers

In time-series data, numeric values are often the most common data type. Using a function like random() without any other formatting creates very… well... random (and precise) numbers with lots of decimal points. While it works, the values aren't realistic. Most users and devices aren't tracking CPU usage to 12+ decimals. We need a way to manipulate and constrain the final value that's returned in the query.

For numeric values, PostgreSQL provides many built-in functions to modify the output. In many cases, using round() and floor() with basic arithmetic can quickly start shaping the data in a way that better fits your schema and use case.

Let's modify the example query for getting device metrics, returning values for CPU and temperature. We want to update the query to ensure that the data values are "customized" for each column, returning values within a specific range and precision. Therefore, we need to apply a standard formula to each numeric value in our SELECT query.

Final value = random() * (max allowed value - min allowed value) + min allowed value

This equation will always generate a decimal value between (and inclusive of) the min and max value. If random() returns a value of 1, the final output will equal the maximum value. If random() returns a value of 0, then the result will equal the minimum value. Any other number that random() returns will produce some output between the min and max values.

Depending on whether we want a decimal or integer value, we can further format the "final value" of our formula with round() and floor().

This example produces a reading every minute for one hour for 10 devices. The cpu value will always fall between 3 and 100 (with four decimals of precision), and the temperature will always be an integer between 28 and 83.

SELECT
  time,
  device_id,
  round((random()* (100-3) + 3)::NUMERIC, 4) AS cpu,
  floor(random()* (83-28) + 28)::INTEGER AS tempc
FROM 
    generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id;


time                         |device_id|cpu    |tempc        |
-----------------------------+---------+-------+-------------+
2021-11-03 12:47:01.181 -0400|        1|53.7301|           61|
2021-11-03 12:48:01.181 -0400|        1|34.7655|           46|
2021-11-03 12:49:01.181 -0400|        1|78.6849|           44|
2021-11-03 12:50:01.181 -0400|        1|95.5484|           64|
2021-11-03 12:51:01.181 -0400|        1|86.3073|           82|
…|...|...|...

By using our simple formula and formatting the result correctly, the query produced the "curated" output (random as it is) we wanted.

The power of functions

But there's also a bit of a letdown here, isn't there? Typing that formula repeatedly for each value - trying to remember the order of parameters and when I need to cast a value - will become tedious quickly. After all, you only have so many keystrokes left.

The solution is to create and use PostgreSQL functions that can take the inputs we need, do the correct calculations, and return the formatted value that we want. There are many ways we could accomplish a calculation like this in a function. Use this example as a starting place for your learning and exploration.

Note: In this example, I chose to return the value from this function as a numeric data type because it can return values that look like integers (no decimals) or floats (decimals). As long as the return values are inserted into a table with the intended schema, this is a "trick" to visually see what we expect - an integer or a float. In general, the numeric data type will often perform worse in queries and features like compression because of how numeric values are represented internally. We recommend avoiding numeric types in schema design whenever possible, preferring the float or integer types instead.

/*
 * Function to create a random numeric value between two numbers
 * 
 * NOTICE: We are using the type of 'numeric' in this function in order
 * to visually return values that look like integers (no decimals) and 
 * floats (with decimals). However, if inserted into a table, the assumption
 * is that the appropriate column type is used. The `numeric` type is often
 * not the correct or most efficient type for storing numbers in a table.
 */
CREATE OR REPLACE FUNCTION random_between(min_val numeric, max_val numeric, round_to int=0) 
   RETURNS numeric AS
$$
 DECLARE
    value NUMERIC = random()* (min_val - max_val) + max_val;
BEGIN
   IF round_to = 0 THEN 
     RETURN floor(value);
   ELSE 
     RETURN round(value,round_to);
   END IF;
END
$$ language 'plpgsql';

This example function uses the minimum and maximum values provided, applies the "range" formula we discussed earlier, and finally returns a numeric value that either has decimals (to the specified number of digits) or not. Using this function in our query, we can simplify creating formatted values for sample data, and it cleans up the SQL, making it easier to read and use.

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c
FROM 
    generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id;

This query provides the same formatted output, but now it's much easier to repeat the process.

Creating more realistic text

What about text? So far, in both articles, we've only discussed how to generate numeric data. We all know, however, that time-series data often contain more than just numeric values. Let's turn to another common data type: text.

Time-series data often contains text values. When your schema contains log messages, item names, or other identifying information stored as text, we want to generate sample text that feels more realistic, even if it's random.

Let's consider the query used earlier that creates CPU and temperature data for a set of devices. If the devices were real, the data they create might contain an intermittent status message of varying length.

To figure out how to generate this random text, we will follow the same process as before, working directly in a stand-alone SQL query before moving our solution into a reusable function. After some initial attempts (and ample Googling), I came up with this example for producing random text of variable length using a defined character set. As with the random_between() function above, this can be modified to suit your needs. For instance, it would be fairly easy to get unique, random hexadecimal values by limiting the set of characters and lengths.

Let your creativity guide you.

WITH symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 {}')),
w1 AS (
    SELECT string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '') r_text, 'g1' AS idx
    FROM symbols,
generate_series(1,10) as word(chr_idx) -- word length
    GROUP BY idx)
SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  w1.r_text AS note
FROM w1, generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;

time                         |device_id|cpu     |temperature_c|note      |
-----------------------------+---------+--------+-------------+----------+
2021-11-03 16:49:24.218 -0400|        1| 88.3525|           50|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        2| 29.5313|           53|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        3| 97.6065|           70|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        4| 96.2170|           40|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        5| 53.2318|           82|I}3U}FIsX9|
2021-11-03 16:49:24.218 -0400|        6| 73.7244|           56|I}3U}FIsX9|

In this case, it was easier to generate a random value inside of a CTE that we could reference later in the query. However, this approach has one problem that's pretty easy to spot in the first few rows of returned data.

While the CTE does create random text of 10 characters (go ahead and run it a few times to verify), the value of the CTE is generated once each time and then cached, repeating the same result over and over for every row. Once we transfer the query into a function, we expect to see a different value for each row.

For this second example function to generate "words" of random lengths (or no text at all in some cases), the user will need to provide an integer for the minimum and maximum length of the generated text. After some testing, we also added a simple randomizing feature.

Notice the IF...THEN condition that we added. Any time the generated number is divided by five and has a remainder of zero or one, the function will not return a text value. There is nothing special about this approach to providing randomness to the frequency of the output, so feel free to adjust this part of the function to suit your needs.

/*
 * Function to create random text, of varying length
 */
CREATE OR REPLACE FUNCTION random_text(min_val INT=0, max_val INT=50) 
   RETURNS text AS
$$
DECLARE 
    word_length NUMERIC  = floor(random() * (max_val-min_val) + min_val)::INTEGER;
    random_word TEXT = '';
BEGIN
    -- only if the word length we get has a remainder after being divided by 5. This gives
    -- some randomness to when words are produced or not. Adjust for your tastes.
    IF(word_length % 5) > 1 THEN
    SELECT * INTO random_word FROM (
        WITH symbols(characters) AS (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz 0123456789 '))
        SELECT string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), ''), 'g1' AS idx
        FROM symbols
        JOIN generate_series(1,word_length) AS word(chr_idx) on 1 = 1 -- word length
        group by idx) a;
    END IF;
    RETURN random_word;
END
$$ LANGUAGE 'plpgsql';

When we use this function to add random text to our sample time-series query, notice that the text is random in length (between 2 and 10 characters) and frequency.

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  random_text(2,10) AS note
FROM generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;

time                         |device_id|cpu     |temperature_c|note     |
-----------------------------+---------+--------+-------------+---------+
2021-11-04 14:17:03.410 -0400|        1| 86.5780|           67|         |
2021-11-04 14:17:03.410 -0400|        2|  3.5370|           76|pCVBp AZ |
2021-11-04 14:17:03.410 -0400|        3| 59.7085|           28|kMrr     |
2021-11-04 14:17:03.410 -0400|        4| 69.6153|           46|3UdA     |
2021-11-04 14:17:03.410 -0400|        5| 33.0906|           56|d0sSUilx |
2021-11-04 14:17:03.410 -0400|        6| 44.2837|           74|         |
2021-11-04 14:17:03.410 -0400|        7| 14.2550|           81|TOgbHOU  |

Hopefully, you're starting to see a pattern. Using generate_series() and some custom functions can help you create time-series data of many shapes and sizes.

We've demonstrated ways to create more realistic numbers and text data because they are the primary data types used in time-series data. Are there any other data types included with time-series data that you might need to generate with your sample data?

What about JSON values?

Creating sample JSON

Note: The sample queries below create JSON strings as the output with the intention that it would be inserted into a table for further testing and learning. In PostgreSQL, JSON string data can be stored in a JSON or JSONB column, each providing different features for querying and displaying the JSON data. In most circumstances, JSONB is the preferred column type because it provides more efficient storage and the ability to create indexes over the contents. The main downside is that the actual formatting of the JSON string, including the order of the keys and values, is not retained and may be difficult to reproduce exactly. To better understand the differences of when you would store JSON string data with one column type over the other, please refer to the PostgreSQL documentation.

PostgreSQL has supported JSON and JSONB data types for many years. With each major release, the feature set for working with JSON and overall query performance improves. In a growing number of data models, particularly when REST or Graph APIs are involved, storing extra meta information as a JSON document can be beneficial. The data is available if needed while facilitating efficient queries on serialized data stored in regular columns.

We used a design pattern similar to this in our NFT Starter Kit. The OpenSea JSON API used as the data source for the starter kit includes many properties and values for each asset and collection. A lot of the values weren't helpful for the specific analysis in that tutorial. However, we knew that some of the values in the JSON properties could be useful in future analysis, tutorials, or demonstrations. Therefore, we stored additional metadata about assets and collections in a JSONB field to query it if needed. Still, it didn't complicate the schema design for otherwise common data like nameand asset_id.

Storing data in a JSON field is also a common practice in areas like IIoT device data. Engineers usually have an agreed-upon schema to store and query metrics produced by the device, followed by a "free form" JSON column that allows engineers to send error or diagnostic data that changes over time as hardware is modified or updated.

There are several approaches to add JSON data to our sample query. One added challenge is that JSON data includes both a key and a value, along with the possibility of numerous levels of child object nesting. The approach you take will depend on how complex you want the PostgreSQL function to be and the end goal of the sample data. In this example, we'll create a function that takes an array of keys for the JSON and generates random numerical values for each key without nesting. Generating the JSON string in SQL from our values is straightforward, thanks to built-in PostgreSQL functions for reading and writing JSON strings. 🎉

As with the other examples in this post, we'll start by using a CTE to generate a random JSON document in a stand-alone SELECT query to verify that the result is what we want. Remember, we'll observe the same issue we had earlier when generating random text in the stand-alone query because we are using a CTE. The JSON is random every time the query runs, but the string is reused for all rows in the result set. CTE's are materialized once for each reference in a query, whereas functions are called again for every row. Because of this, we won't observe random values in each row until we move the SQL into a function to reuse later.

WITH random_json AS (
SELECT json_object_agg(key, random_between(1,10)) as json_data
    FROM unnest(array['a', 'b']) as u(key))
  SELECT json_data, generate_series(1,5) FROM random_json;

json_data       |generate_series|
----------------+---------------+
{"a": 6, "b": 2}|              1|
{"a": 6, "b": 2}|              2|
{"a": 6, "b": 2}|              3|
{"a": 6, "b": 2}|              4|
{"a": 6, "b": 2}|              5|

We can see that the JSON data is created using our keys (['a','b']) with numbers between 1 and 10. We just have to create a function that will create random JSON data each time it is called. This function will always return a JSON document with numeric integer values for each key we provide for demonstration purposes. Feel free to enhance this function to return more complex documents with various data types if that's a requirement for you.

CREATE OR REPLACE FUNCTION random_json(keys TEXT[]='{"a","b","c"}',min_val NUMERIC = 0, max_val NUMERIC = 10) 
   RETURNS JSON AS
$$
DECLARE 
    random_val NUMERIC  = floor(random() * (max_val-min_val) + min_val)::INTEGER;
    random_json JSON = NULL;
BEGIN
    -- again, this adds some randomness into the results. Remove or modify if this
    -- isn't useful for your situation
    if(random_val % 5) > 1 then
        SELECT * INTO random_json FROM (
            SELECT json_object_agg(key, random_between(min_val,max_val)) as json_data
                FROM unnest(keys) as u(key)
        ) json_val;
    END IF;
    RETURN random_json;
END
$$ LANGUAGE 'plpgsql';

With the random_json() function in place, we can test it in a few ways. First, we'll simply call the function directly without any parameters, which will return a JSON document with the default keys provided in the function definition ("a", "b", "c") and values from 0 to 10 (the default minimum and maximum value).

SELECT random_json();

random_json             |
------------------------+
{"a": 7, "b": 3, "c": 8}|

Next, we'll join this to a small numeric set from generate_series().

SELECT device_id, random_json() FROM generate_series(1,5) device_id;

device_id|random_json              |
---------+-------------------------+
        1|{"a": 2, "b": 2, "c": 2} |
        2|                         |
        3|{"a": 10, "b": 7, "c": 1}|
        4|                         |
        5|{"a": 7, "b": 1, "c": 0} |

Notice two things with this example.

First, the data is different for each row, showing that the function gets called for each row and produces different numeric values each time. Second, because we kept the same random output mechanism from the random_text() example, not every row includes JSON.

Finally, let's add this into the sample query for generating device data that we've used throughout this article to see how to provide an array of keys ("building" and "rack") for the generated JSON data.

SELECT
  time,
  device_id,
  random_between(3,100, 4) AS cpu,
  random_between(28,83) AS temperature_c,
  random_text(2,10) AS note,
  random_json(ARRAY['building','rack'],1,20) device_location
FROM generate_series(now() - interval '1 hour', now(), interval '1 minute') AS time, 
    generate_series(1,10,1) AS device_id
ORDER BY 1,2;


time                         |device_id|cpu     |temperature_c|note     |device_location             |
-----------------------------+---------+--------+-------------+---------+----------------------------+
2021-11-04 16:19:22.991 -0400|        1| 14.7614|           70|CTcX8 2s4|                            |
2021-11-04 16:19:22.991 -0400|        2| 62.2618|           81|x1V      |{"rack": 4, "building": 5}  |
2021-11-04 16:19:22.991 -0400|        3| 10.1214|           50|1PNb     |                            |
2021-11-04 16:19:22.991 -0400|        4| 96.3742|           29|aZpikXGe |{"rack": 12, "building": 4} |
2021-11-04 16:19:22.991 -0400|        5| 22.5327|           30|lM       |{"rack": 2, "building": 3}  |
2021-11-04 16:19:22.991 -0400|        6| 57.9773|           44|         |{"rack": 16, "building": 5} |
...

There are just so many possibilities for creating sample data with generate_series(), PostgreSQL functions, and some custom logic.

Putting it all together

Let's put what we've learned into practice, using these three functions to create and insert ~1 million rows of data and then query it with the hyperfunctions time_bucket(), time_bucket_ng(), approx_percentile() and time_weight(). To do this, we'll create two tables: one will be a list of computer hosts and the second will be a hypertable that stores fake time-series data about the computers.

Step 1: Create the schema and hypertable

CREATE TABLE host (
    id int PRIMARY KEY,
    host_name TEXT,
    LOCATION jsonb
);

CREATE TABLE host_data (
    date timestamptz NOT NULL,
    host_id int NOT NULL,
    cpu double PRECISION,
    tempc int,
    status TEXT 
);

SELECT create_hypertable('host_data','date');

Step 2: Generate and insert data

-- Insert data to create fake hosts
INSERT INTO host
SELECT id, 'host_' || id::TEXT AS name, 
    random_json(ARRAY['building','rack'],1,20) AS LOCATION
FROM generate_series(1,100) AS id;


-- insert ~1.3 million records for the last 3 months
INSERT INTO host_data
SELECT date, host_id,
    random_between(5,100,3) AS cpu,
    random_between(28,90) AS tempc,
    random_text(20,75) AS status
FROM generate_series(now() - INTERVAL '3 months',now(), INTERVAL '10 minutes') AS date,
generate_series(1,100) AS host_id;

Step 3: Query data using `time_bucket()` and `time_bucket_ng()`

-- Using time_bucket(), query the average CPU and max tempc
SELECT time_bucket('7 days', date) AS bucket, host_name,
    avg(cpu),
    max(tempc)
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '1 month'
GROUP BY 1,2
ORDER BY 1 DESC, 2;


-- try the experimental time_bucket_ng() to query data in month buckets
SELECT timescaledb_experimental.time_bucket_ng('1 month', date) AS bucket, host_name,
    avg(cpu) avg_cpu,
    max(tempc) max_temp
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '3 month'
GROUP BY 1,2
ORDER BY 1 DESC, 2;

Step 4: Query data using toolkit hyperfunctions

-- query all host in building 10 for 7 day buckets
-- also try the new percentile approximation function to 
-- get the p75 of data for each 7 day period
SELECT time_bucket('7 days', date) AS bucket, host_name,
    avg(cpu),
    approx_percentile(0.75,percentile_agg(cpu)) p75,
    max(tempc)
FROM host_data
JOIN host ON host_data.host_id = host.id
WHERE date > now() - INTERVAL '1 month'
    AND LOCATION -> 'building' = '10'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;



-- To test time-weighted averages, we need to simulate missing
-- some data points in our host_data table. To do this, we'll
-- randomly select ~10% of the rows, and then delete them from the
-- host_data table.
WITH random_delete AS (SELECT date, host_id FROM host_data
     JOIN host ON host_id = id WHERE 
    date > now() - INTERVAL '2 weeks'
    ORDER BY random() LIMIT 20000
)
DELETE FROM host_data hd
USING random_delete rd
WHERE hd.date = rd.date
AND hd.host_id = rd.host_id;


-- Select the daily time-weighted average and regular average
-- of each host for building 10 for the last two weeks.
-- Notice the variation in the two numbers because of the missing data.
SELECT time_bucket('1 day',date) AS bucket,
    host_name,
    average(time_weight('LOCF',date,cpu)) weighted_avg,
    avg(cpu) 
FROM host_data
    JOIN host ON host_data.host_id = host.id
WHERE LOCATION -> 'building' = '10'
AND date > now() - INTERVAL '2 weeks'
GROUP BY 1,2
ORDER BY 1 DESC, 2;

In a few lines of SQL, we created 1.3 million rows of data and were able to test four different functions in TimescaleDB, all without relying on any external source. 💪

Still, you may notice one last issue with the values in our host_data table (even though the values are not more realistic in nature). By using random() as the basis for our queries, the calculated numeric values all tend to have an equal distribution within the specified range which causes the average of the values to always be near the median. This makes sense statistically, but it highlights one other area of improvement to the data we generate. In the third post of this series, we'll demonstrate a few ways to influence the generated values to provide shape to the data (and even some outliers if we need them).

Reviewing our progress

When using a database like TimescaleDB or testing features in PostgreSQL, generating a representative dataset is a beneficial tool to have in your SQL toolbelt.

In the first post, we learned how to generate lots of data by combining the result sets of multiple generate_series() functions. Using the implicit CROSS JOIN, the total number of rows in the final output is a product of each set together. When one of the data sets contains timestamps, the output can be used to create time-series data for testing and querying.

The problem with our initial examples was that the actual values we generated were random and lacked control over their precision - and all of the data was numeric. So in this second post, we demonstrated how to format the numeric data for a given column and generate random data of other types, like text and JSON documents. We also added an example in the text and JSON functions that created randomness in how often the values were emitted for each of those columns.

Again, all of these are building block examples for you to use, creating functions that generate the kind of data you need to test.

To see some of these examples in action, watch my video on creating realistic sample data:

In part 3 of this series, we will demonstrate how to add shape and trends into your sample time-series data (e.g., increasing web traffic over time and quarterly sales cycles) using the formatting functions in this post in conjunction with relational lookup tables and additional mathematical functions. Knowing how to manipulate the pattern of generated data is particularly useful for visualizing time-series data and learning analytical PostgreSQL or TimescaleDB functions.

If you have questions about using generate_series() or have any questions about TimescaleDB, please join our community Slack channel, where you'll find an active community and a handful of the Timescale team most days.

If you want to try creating larger sets of sample time-series data using generate_series() and see how the exciting features of TimescaleDB work, sign up for a free 30-day trial or install and manage it on your instances. (You can also learn more by following one of our many tutorials.)

How to create (lots!) of sample time-series data with PostgreSQL generate_series()

Ryan Booz — Mon, 13 Dec 2021 11:31:22 +0000

Intro to PostgreSQL generate_series()
PostgreSQL generate_series() with dates
How to generate time-series data
Performance considerations when generating sample data
Tips for quickly increasing sample dataset scale
Choosing a date range
Calculating total rows
Let´s review

As the makers of TimescaleDB, we often need to quickly create lots of sample time-series data to demonstrate a new database feature, run a benchmark, or talk about use cases internally. We also see users in our community Slack asking how they can test a feature or decide if their data is a good fit for TimescaleDB.

Although using real data from your current application would be great (and ideal), knowing how to quickly create a representative time-series dataset using varying cardinalities and different lengths of time, is a helpful - and advantageous - skill to have.

Fortunately PostgreSQL provides a built-in function to help us create this sample data using the SQL that we already know and love - no external tools required.

In this three-part blog series, we'll go through a few ways to use the generate_series() function to create large datasets in PostgreSQL, including:

What PostgreSQL generate_series() is and how to use it for basic data generation (similar to our tutorial to Simulate IoT Data)
How to create more realistic looking time-series data with custom PostgreSQL functions
Ways to create complex time-series data, using additional PostgreSQL math functions and JOINs.

By the end of this series, you'll be ready to test almost any PostgreSQL or TimescaleDB feature, create quick datasets for general testing, meetup presentations, demos, and more!

Intro to PostgreSQL generate_series()

generate_series() is a built-in PostgreSQL function that makes it easy to create ordered tables of numbers or dates. The PostgreSQL documentation calls it a Set Returning Function because it can return more than one row.

The function is simple and easy to get started with, taking at least two required arguments to specify the start and stop parameters for the generated data.

SELECT * FROM generate_series(1,5);

This will produce output that looks like this:

generate_series|
---------------|
              1|
              2|
              3|
              4|
              5|

From this first example, we can see that generate_series() returns sequential numbers between a start parameter and stop parameter. When used to generate numeric data, generate_series() will increment the values by 1. However, there is an optional third parameter that can be used to specify the increment length, known as the step parameter.

For example, if we wanted to generate rows that counted by two from 0 to 10, we could use this SQL instead:

SELECT * from generate_series(0,10,2);

 generate_series 
-----------------
               0
               2
               4
               6
               8
              10

PostgreSQL generate_series() with dates

Using generate_series() to produce a range of dates is equally straightforward. The only difference is that the third parameter, the step INTERVALused to increment the date, is required for date generation, as shown here:

SELECT * from generate_series(
    '2021-01-01',
    '2021-01-02', INTERVAL '1 hour'
  );

    generate_series     
------------------------
 2021-01-01 00:00:00+00
 2021-01-01 01:00:00+00
 2021-01-01 02:00:00+00
 2021-01-01 03:00:00+00
 2021-01-01 04:00:00+00
 2021-01-01 05:00:00+00
 2021-01-01 06:00:00+00
 2021-01-01 07:00:00+00
 2021-01-01 08:00:00+00
 2021-01-01 09:00:00+00
 2021-01-01 10:00:00+00
 2021-01-01 11:00:00+00
 2021-01-01 12:00:00+00
 2021-01-01 13:00:00+00
 2021-01-01 14:00:00+00
 2021-01-01 15:00:00+00
 2021-01-01 16:00:00+00
 2021-01-01 17:00:00+00
 2021-01-01 18:00:00+00
 2021-01-01 19:00:00+00
 2021-01-01 20:00:00+00
 2021-01-01 21:00:00+00
 2021-01-01 22:00:00+00
 2021-01-01 23:00:00+00
 2021-01-02 00:00:00+00
(25 rows)

Notice that the returned dates are inclusive of the start and stop values, just as we saw with the numeric example before. The reason we got 25 rows (representing 25 hours rather than 24 as you might expect) is that the stop value can be reached using the equal one-hour INTERVAL (the step parameter). As long as the INTERVAL can increment evenly up to the stop date, it will be included.

However, if the step interval resulted in the stop value being skipped over, it will not be included in your output. For example, if we modify the step INTERVAL above to '1 hour 25 minutes', the result only returns 17 rows, the last of which is before the stop value.

SELECT * from generate_series(
    '2021-01-01','2021-01-02', 
    INTERVAL '1 hour 25 minutes'
   );

    generate_series     
------------------------
 2021-01-01 00:00:00+00
 2021-01-01 01:25:00+00
 2021-01-01 02:50:00+00
 2021-01-01 04:15:00+00
 2021-01-01 05:40:00+00
 2021-01-01 07:05:00+00
 2021-01-01 08:30:00+00
 2021-01-01 09:55:00+00
 2021-01-01 11:20:00+00
 2021-01-01 12:45:00+00
 2021-01-01 14:10:00+00
 2021-01-01 15:35:00+00
 2021-01-01 17:00:00+00
 2021-01-01 18:25:00+00
 2021-01-01 19:50:00+00
 2021-01-01 21:15:00+00
 2021-01-01 22:40:00+00
(17 rows)

How to generate time-series data

Now that we understand how to use generate_series(), how do we create some time-series data to insert into TimescaleDB for testing and visualization?

To do this, we utilize a standard feature of relational databases and SQL. Recall that generate_series() is a Set Returning Function that returns a "table" of data (a set) just as if we had selected it from a table. Therefore, just like when we select data from a regular table with SQL, we can add more columns of data using other functions or static values.

You may have done this at some point with a string of text that needed to be repeated for each returned row.

SELECT 'Hello Timescale!' as myStr, * FROM generate_series(1,5);

     myStr     | generate_series 
------------------+-----------------
 Hello Timescale! |               1
 Hello Timescale! |               2
 Hello Timescale! |               3
 Hello Timescale! |               4
 Hello Timescale! |               5
(5 rows)

In this example, we simply added data to the rows being returned from generate_series(). For every row it returned, we added a column with some static text.

But, these added columns don't have to be static data! Using the built-in random() function (for example), we can generate data that starts to look a little more like data we'd see when monitoring computer CPU values (i.e., realistic data to use for our demos and tests!).

SELECT random()*100 as CPU, * FROM generate_series(1,5);

        cpu         | generate_series 
--------------------+-----------------
 48.905450626783775 |               1
  71.94031820213382 |               2
 25.210553719011486 |               3
  19.24163308357194 |               4
  8.434915599133674 |               5
(5 rows)

In this example, generate_series() produced 5 rows of data and for every row, PostgreSQL also executed the random() function to produce a value. With a little creativity, this can become a pretty efficient method for generating lots of data quickly.

Performance considerations when generating sample data

Functions

Before getting too deep into the many ways we can use various functions with generate_series() to create more realistic data, let's tackle one potential downside with functions before your mind starts thinking about a myriad of ways to produce interesting, sample data.

Functions are a powerful database tool, allowing complex code to be hidden behind a standard interface. In the example above, I don't have to know how random() produces a value. I just have to call it in my SQL statement (without arguments) and a random value is returned for every row.

Unfortunately, functions can slow down your query and use lots of resources if you're not careful. This is true any time you call functions in SQL, not just when creating sample data.

The reason for this inefficiency is that scalar functions are executed once for each column and row in which they are used. So, if you produce a set of 1,000 rows with generate_series() and then add on 5 additional columns with data generated by functions, PostgreSQL has to effectively process 5,001 function calls, once to generate the initial series of data which is returned as a set, and 5 times that for each row because of the additional 5 columns.

The main issue is that PostgreSQL has no idea how to determine how much "work" it will take to generate the result from each function in the query. Something like random() required very minimal effort, so calling it 5,000 or even 1 million times won't break the bank for most machines. However, if the function you're calling generates lots of temporary data internally before producing a result, be aware that it could perform more slowly and consume more resources on your database server.

The takeaway here is that nothing comes for free, especially when functions are called once for every column, for every row. Most of the time the data you generate will easily complete in a handful of seconds for tens of millions of rows. But, if you notice a query that generates data taking a long time, consider finding alternative ways to generate data for the columns that require more resources.

Transactions

There is a second caveat to be aware of when using a tool like generate_series(), at least as demonstrated throughout this series. All data is created and inserted as a single transaction. You can put in more time and effort to create functions and methods for breaking up the work, but understand if you create (SELECT) one large set of data with 100 million rows, it's likely to consume a lot of memory and CPU on the server while the process occurs.

This doesn't mean it's broken or any less valid of a method for generating data. However, as we demonstrate additional ways to create more realistic data in parts 2 and 3 of this series, recognize that the more data you create, the more resources you'll need from the server without managing batches and transactions with a more advanced setup.

Alright, let's get back to the fun!

Tips for quickly increasing sample dataset scale

So far we've looked at how generate_series() works and how you can use functions to add additional dynamic content to the output. But how do we quickly get to the scale of tens of millions of rows (or more)?

This is where the concept of a Cartesian product comes into play with databases. A Cartesian product (otherwise known as a CROSS JOIN) takes two or more result sets (rows from multiple tables or functions) and produces a new result set that contains rows equal to the count of the first set multiplied by the count of the second set.

In doing so, the database outputs every row from the first table with the value of the first row from the second table. It does this over and over until all rows in both tables have been iterated. (and yes, if you join more than two tables this way, the process just keeps multiplying, tables processed left to right)

Let's look at a small example using two generate_series() in the same select statement.

SELECT * from generate_series(1,10) a, generate_series(1,2) b;

a |b|
--+-+
 1|1|
 2|1|
 3|1|
 4|1|
 5|1|
 6|1|
 7|1|
 8|1|
 9|1|
10|1|
 1|2|
 2|2|
 3|2|
 4|2|
 5|2|
 6|2|
 7|2|
 8|2|
 9|2|
10|2|

As you can see, PostgreSQL generated 10 rows for the first series and 2 rows for the second series. After processing and iterating over each table consecutively, the query produced a total of 20 rows (10 x 2). For generating lots of data quickly, this is about as easy as it gets!

The same process applies when using generate_series() to return a set of dates. If you (effectively) CROSS JOIN multiple sets (numbers or dates), the total number of rows in the final set will be a product of all sets. Again, for generating sample time-series data, you'd be hard-pressed to find an easier method!

In this example, we generate 12 timestamps an hour apart, a random value representing CPU usage, and then a second series of four values that represent IDs for fake devices. This should produce 48 rows (eg. 12 timestamps x 4 device IDs = 48 rows).

SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series(
    '2021-01-01 00:00:00',
    '2021-01-01 11:00:00',
    INTERVAL '1 hour'
  ) as time, 
generate_series(1,4) device_id;


time               |device_id|cpu_usage          |
-------------------+---------+-------------------+
2021-01-01 00:00:00|        1|0.35415126479989567|
2021-01-01 01:00:00|        1| 14.013393572770028|
2021-01-01 02:00:00|        1|   88.5015939122006|
2021-01-01 03:00:00|        1|  97.49037810105996|
2021-01-01 04:00:00|        1|  50.22781125586846|
2021-01-01 05:00:00|        1|  77.93431470586931|
2021-01-01 06:00:00|        1|  45.73481750582076|
2021-01-01 07:00:00|        1|   70.7999843735724|
2021-01-01 08:00:00|        1|   4.72949831884506|
2021-01-01 09:00:00|        1|  85.29122113229981|
2021-01-01 10:00:00|        1| 14.539664281598874|
2021-01-01 11:00:00|        1|  45.95244258556228|
2021-01-01 00:00:00|        2|  46.41196423062297|
2021-01-01 01:00:00|        2|  74.39903569177027|
2021-01-01 02:00:00|        2|  85.44087332221935|
2021-01-01 03:00:00|        2|  4.329394730750735|
2021-01-01 04:00:00|        2| 54.645873866589056|
2021-01-01 05:00:00|        2|  6.544334492894777|
2021-01-01 06:00:00|        2|  39.05071228953645|
2021-01-01 07:00:00|        2|  71.07264365438404|
2021-01-01 08:00:00|        2|   72.4732704336219|
2021-01-01 09:00:00|        2| 34.533280927542975|
2021-01-01 10:00:00|        2| 26.764760864598003|
2021-01-01 11:00:00|        2|  62.32048879645227|
2021-01-01 00:00:00|        3|  63.01888063314749|
2021-01-01 01:00:00|        3|  21.70606884856987|
2021-01-01 02:00:00|        3|  32.47610779097485|
2021-01-01 03:00:00|        3| 47.565982341726354|
2021-01-01 04:00:00|        3|  64.34867263419619|
2021-01-01 05:00:00|        3|  57.74424991855476|
2021-01-01 06:00:00|        3| 55.593286571750156|
2021-01-01 07:00:00|        3|  36.92650110894995|
2021-01-01 08:00:00|        3| 53.166926049881624|
2021-01-01 09:00:00|        3| 10.009505806123897|
2021-01-01 10:00:00|        3| 58.067700285561585|
2021-01-01 11:00:00|        3|  81.58883725078034|
2021-01-01 00:00:00|        4|   78.1768041898232|
2021-01-01 01:00:00|        4|  84.51505102850199|
2021-01-01 02:00:00|        4| 24.029611792753514|
2021-01-01 03:00:00|        4|  17.08996115345549|
2021-01-01 04:00:00|        4| 29.642690955760997|
2021-01-01 05:00:00|        4|  90.83844806413275|
2021-01-01 06:00:00|        4| 6.5019080489854275|
2021-01-01 07:00:00|        4|    32.336484070672|
2021-01-01 08:00:00|        4|    55.595524107963|
2021-01-01 09:00:00|        4|   97.5442141375293|
2021-01-01 10:00:00|        4|   37.0741925805568|
2021-01-01 11:00:00|        4| 19.093927249791776|

Choosing a date range

Now it's time to put all of the features and concepts together. We've seen how to use generate_series() to create a sample table of data (both numbers and dates), add static and dynamic content to each row, and finally how to join multiple sets of data together to create a deterministic number of rows to create test data.

The final piece of the puzzle is to figure out how to create date ranges that are more dynamic using date math. Once again, PostgreSQL makes this straightforward because date math is handled automatically.

When generating sample data you usually have an idea of the duration of time you want to generate - 1 month, 6 months, or a year - for instance. But calculating the exact start and end timestamps for your use case can get pretty tedious.

Instead, it's often easier to use now() or a static ending timestamp (ie. '2021-06-01 00:00:00'), and then using date math to get the starting timestamp based on your chosen interval. This makes it very easy to generate data for different durations simply by changing the interval. And to be clear, you can go the other direction (picking a start timestamp and adding time), but that can often lead to data in the future.

Let's look at three examples to demonstrate ways of creating dynamic date ranges.

Create 6 months of data with one-hour intervals, ending now()

SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series(
    now() - INTERVAL '6 months',
    now(),
    INTERVAL '1 hour'
   ) as time, 
generate_series(1,4) device_id;

Notice that we use the PostgreSQL function now() to automatically choose the ending timestamp, and then use date math (- INTERVAL '6 months') to let PostgreSQL find the starting timestamp for us. With almost no effort we can easily generate weeks, months, or years of time-series data by changing the INTERVAL we subtract from now().

Create 1 year of data with one-hour intervals, ending on a timestamp

SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series(
    '2021-08-01 00:00:00' - INTERVAL '6 months',
    '2021-08-01 00:00:00',
    INTERVAL '1 hour'
  ) as time, 
generate_series(1,4) device_id;

This example is the same as the first, but here we specify the timestamp that we want to end on. PostgreSQL can still do the date math for us (subtracting 6 months in this example), but we get control over the exact ending timestamp to use. This is particularly useful when you want the actual time portion of the timestamp to begin and end on even, rounded hours, minutes, or seconds. When we use now(), the timestamp can produce (what feels like) random timestamp causing all of the time-series data to have timestamps that are increments of now().

Create 1 year of data with one-hour intervals, beginning on a timestamp

For this last example, we're going to specify the start timestamp instead. This can be useful when you need to test a specific fiscal period or maybe some logic that deals with the turning of each year. In this case, maybe you just need a month of data but it needs to cross over from one year to the next. In that case, trying to figure out the math of how far back to start and how far to go forward might be more complicated than you want to worry about.

SELECT time, device_id, random()*100 as cpu_usage 
FROM generate_series(
    '2020-12-15 00:00:00',
    '2020-12-15 00:00:00' + INTERVAL '2 months',
    INTERVAL '1 hour'
  ) as time, 
generate_series(1,4) device_id;

Each of these examples uses date math to help you find the appropriate start and end timestamps so that you can easily adjust to create more or less data while still fitting the range profile that you need.

Speaking of calculating how many rows you want to generate… 😉

Calculating total rows

We now have all the tools we need to create datasets of almost any size. For time-series data specifically, it's a straightforward calculation to figure out how many rows your query will generate.

Total Rows = Readings per hour * Total hours * Number of "things" being tracked

Using this formula, we can quickly determine how many rows will be created by changing any combination of the total range (start/end timestamps), how many readings per hour for each item, or the total number of items. Let's look at a couple of examples to get an idea of how quickly you could create many rows of time-series data for your testing scenario.

Range of readings	Length of interval	Number of "devices"	Total rows
1 year	1hour	4	35,040
1 year	10 minutes	100	5,256,000
6 months	5 minutes	1,000	52,560,000

As you can see, the numbers start to add up very quickly.

Let's review

In this first post, we've demonstrated that it's pretty easy to generate lots of data using the PostgreSQL generate_series() function. We also learned that when you select multiple sets (using generate_series() or selecting from tables and functions), PostgreSQL will produce what's known as a Cartesian product, the selection of all rows from all tables - the product of all rows. With this knowledge, we can quickly create large and diverse datasets to test various features of PostgreSQL and TimescaleDB.

For more on how to create, find, and use demo data in tutorials, blog posts, and more, please watch my Creating sample data video:

This was part 1 of Generating sample time-series data three-part series.

In part 2 of this series, we demonstrate how to use custom user-defined functions to create more realistic-looking data to use for testing, including generated text, numbers constrained by a range, and even fake JSON data.

Part 2: Generating more realistic sample time-series data with PostgreSQL generate_series()

Part 3: Coming soon

If you have questions about using generate_series() or have any questions about TimescaleDB, please join our community Slack channel where you'll find an active community and a handful of the Timescale team most days.

See what's possible when you join relational data in PostgreSQL with time-series superpowers!