When developing applications that track measurements over time, you'll often encounter scenarios where values remain constant across multiple readings. Consider a temperature monitoring system that takes daily measurements: if the temperature stays at 20.5°C for an entire month, storing 30 identical rows is wasteful and degrades query performance.
Postgres' range types offer an elegant solution to this problem, potentially reducing storage requirements by orders of magnitude while maintaining data integrity. In this post, I'll demonstrate how range types work and show you the dramatic space savings they can deliver.
A straightforward approach: one row per day
This is how you would store one reading per day.
CREATE TABLE temperature_readings_daily (
sensor_id INTEGER,
reading_date DATE,
temperature DECIMAL(5,2),
PRIMARY KEY (sensor_id, reading_date)
);
-- Example data: same temperature for 30 days
INSERT INTO temperature_readings_daily
SELECT
1 as sensor_id,
generate_series('2025-01-01'::date, '2025-01-30'::date, '1 day'::interval)::date,
72.5 as temperature;
This approach creates 30 rows to represent a single temperature value that remained constant throughout January.
Date ranges
To use date ranges, we will have to rename our column and define it as having type daterange.
CREATE TABLE temperature_readings_range (
sensor_id INTEGER,
valid_period DATERANGE,
temperature DECIMAL(5,2)
);
-- Same data: one row covers 30 days
INSERT INTO temperature_readings_range VALUES
(1, '[2025-01-01,2025-01-31)'::daterange, 72.5);
The daterange type uses interval notation: [2025-01-01,2025-01-31) means the range includes January 1st through January 30th (the closing parenthesis excludes January 31st).
Measuring the impact
Let's have a look at what savings we can expect if we start working with range types. Let's generate some test data for our experiment.
To quantify the space savings, let's run an experiment with realistic data. We'll simulate 100 sensors tracking temperatures over six months, with values changing twice per month (on the 1st and 15th). You can find the SQL to generate this data at the end of this post [1].
Alright, how much did we gain? Here's the query we'll use to interrogate the table sizes.
SELECT
pg_size_pretty(pg_total_relation_size('temperature_readings_daily')) as daily_size,
pg_size_pretty(pg_total_relation_size('temperature_readings_range')) as range_size,
round(
100 - (pg_total_relation_size('temperature_readings_range')::numeric /
pg_total_relation_size('temperature_readings_daily') * 100),
2
) as space_savings_percent;
daily_size | range_size | space_savings_percent
------------+------------+-----------------------
1448 kB | 128 kB | 91.16
We can see a big difference of one order of magnitude.
The space savings depend entirely on your data distribution though. If values change every day, you'll see minimal benefit. But if values remain constant for weeks or months at a time, the gains can be dramatic.
Querying
Here's how you'd write queries to retrieve results of interest.
-- Find the temperature on a specific date
SELECT sensor_id, temperature
FROM temperature_readings_range
WHERE sensor_id = 1
AND valid_period @> '2025-06-15'::date;
-- Get temperature changes in a date range
SELECT sensor_id, valid_period, temperature
FROM temperature_readings_range
WHERE sensor_id = 1
AND valid_period && '[2025-11-01,2025-12-31)'::daterange;
Alternative: start and end columns
You might wonder: why use range types at all? Why not just add start_date and end_date columns?
This is a valid approach and achieves similar storage savings. So what are the tradeoffs?
Start/end columns:
- More familiar and intuitive for most developers
- Works with any database system, not just PostgreSQL
- Easier to understand in query results
- No need to learn range-specific operators
Range types:
Data integrity: range types enforce that the period is valid (start before end) at the type level
Specialized operators: @> (contains), && (overlaps), <@ (contained by) make queries more expressive
GiST indexing: PostgreSQL can build efficient indexes specifically designed for range queries
NULL handling: With start/end columns, you need to handle the case where end_date is NULL (for ongoing periods). Range types handle open-ended ranges naturally with the [2025-01-01,) notation
Cleaner semantics: A single valid_period column is conceptually clearer than two related columns
Beyond date ranges: other Postgres range types
While we've focused on daterange for this example, Postgres provides several built-in range types for different use cases.
-
int4rangeandint8range– Integer ranges, useful for ID ranges, version numbers, or inventory levels -
numrange– Numeric ranges for decimal values like prices or measurements -
tsrangeandtstzrange– Timestamp ranges (with and without timezone) for precise event tracking -
daterange– Date ranges as we've used in this post
Conclusion
Postgres's range types is an example of why it's such a feature-rich database. By representing continuous periods with a single row instead of many, you can achieve storage savings and cleaner data models. For applications dealing with time-series data that changes infrequently, always consider range types for your design.
[1]
-- Generate readings that change twice per month (1st and 15th)
CREATE TEMP TABLE temp_changes AS
SELECT
sensor_id,
day::date as change_date,
70 + (random() * 10)::numeric(5,2) as temperature
FROM
generate_series(1, 100) as sensor_id,
generate_series('2025-06-01'::date, '2025-12-31'::date, '1 day'::interval) as day
WHERE
EXTRACT(day FROM day) IN (1, 15);
-- Then, fill in all days with the temperature from the most recent change
INSERT INTO temperature_readings_daily
SELECT
s.sensor_id,
day::date,
tc.temperature
FROM
generate_series(1, 100) as s(sensor_id),
generate_series('2025-06-01'::date, '2025-12-31'::date, '1 day'::interval) as day
CROSS JOIN LATERAL (
SELECT temperature
FROM temp_changes tc2
WHERE tc2.sensor_id = s.sensor_id
AND tc2.change_date <= day::date
ORDER BY tc2.change_date DESC
LIMIT 1
) tc;
-- Populate the range table too
INSERT INTO temperature_readings_range
SELECT
sensor_id,
daterange(
change_date,
LEAD(change_date) OVER (PARTITION BY sensor_id ORDER BY change_date),
'[)'
) as valid_period,
temperature
FROM temp_changes;
Top comments (0)