TimescaleDB extends PostgreSQL with automatic time-series partitioning (hypertables), continuous aggregates, and compression. UUID v7 primary keys fit this model: each UUID encodes its creation millisecond, enabling chunk-aligned inserts and cursor pagination on the primary key column. See UUID in Databases for PostgreSQL UUID storage fundamentals.
Schema — UUID v7 PK with generated timestamp
-- Enable TimescaleDB and pg_uuidv7
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
CREATE TABLE sensor_readings (
id UUID NOT NULL DEFAULT gen_random_uuid_v7(),
sensor_id UUID NOT NULL,
value DOUBLE PRECISION NOT NULL,
-- Generated column extracts timestamp from UUID v7 primary key
created_at TIMESTAMPTZ GENERATED ALWAYS AS (
uuid_extract_timestamp(id)
) STORED
);
-- Convert to hypertable — partition by generated timestamp
SELECT create_hypertable('sensor_readings', by_range('created_at'));
-- Optional: space dimension for parallel chunk writes
SELECT add_dimension('sensor_readings', by_hash('sensor_id', 4));
Insert with UUID v7
import { Pool } from 'pg';
import { v7 as uuidv7 } from 'uuid';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
// Client-generated UUID v7 — aligns insert with current time chunk
async function insertReading(sensorId: string, value: number) {
const id = uuidv7();
await pool.query(
'INSERT INTO sensor_readings (id, sensor_id, value) VALUES ($1, $2, $3)',
[id, sensorId, value]
);
return id;
}
// Database-generated UUID v7 (uses DEFAULT)
async function insertReadingDbDefault(sensorId: string, value: number) {
const { rows } = await pool.query(
'INSERT INTO sensor_readings (sensor_id, value) VALUES ($1, $2) RETURNING id',
[sensorId, value]
);
return rows[0].id;
}
Time-range query — chunk exclusion via UUID v7
-- TimescaleDB excludes chunks outside this range automatically
-- uuid_extract_timestamp() is used in the generated column for partitioning
SELECT id, sensor_id, value, created_at
FROM sensor_readings
WHERE created_at > NOW() - INTERVAL '1 hour'
AND sensor_id = '018fbe3a-4c5d-7000-8000-000000000001'
ORDER BY id -- UUID v7 = chronological
LIMIT 100;
-- Alternatively: use UUID v7 boundary directly (no chunk exclusion, but PK scan)
SELECT id, sensor_id, value
FROM sensor_readings
WHERE id > uuid_v7_from_timestamp(NOW() - INTERVAL '1 hour')
ORDER BY id
LIMIT 100;
Continuous aggregate on UUID v7 data
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', created_at) AS bucket,
sensor_id,
AVG(value) AS avg_value,
MIN(value) AS min_value,
MAX(value) AS max_value,
COUNT(*) AS reading_count
FROM sensor_readings
GROUP BY bucket, sensor_id
WITH NO DATA;
-- Auto-refresh policy
SELECT add_continuous_aggregate_policy('sensor_hourly',
start_offset => INTERVAL '2 hours',
end_offset => INTERVAL '10 minutes',
schedule_interval => INTERVAL '10 minutes'
);
Cursor pagination
async function listReadings(sensorId: string, cursor?: string, limit = 50) {
const query = cursor
? `SELECT id, value FROM sensor_readings
WHERE sensor_id = $1 AND id > $2
ORDER BY id LIMIT $3`
: `SELECT id, value FROM sensor_readings
WHERE sensor_id = $1
ORDER BY id LIMIT $2`;
const args = cursor ? [sensorId, cursor, limit + 1] : [sensorId, limit + 1];
const { rows } = await pool.query(query, args);
const hasMore = rows.length > limit;
const data = hasMore ? rows.slice(0, limit) : rows;
const nextCursor = hasMore ? data.at(-1).id : null;
return { data, nextCursor };
}
Further reading
- UUID in Databases — Storage and Indexing
- UUID v7 for Cursor Pagination
- UUID v7 in ClickHouse
- UUID v7 in Prisma + PostgreSQL
- UUID v7 in Drizzle + PostgreSQL
External references
Frequently asked questions
Can I use UUID v7 as a TimescaleDB hypertable partition dimension?
TimescaleDB partitions by a time column or a space dimension. UUID v7 cannot be used directly as the partition dimension because TimescaleDB requires a TIMESTAMPTZ or integer type for create_hypertable(). The recommended pattern is to use UUID v7 as the primary key and add a generated column: created_at TIMESTAMPTZ GENERATED ALWAYS AS (to_timestamp(('x' || left(replace(id::text, '-', ''), 12))::bit(48)::bigint / 1000.0)) STORED. Then partition by created_at. See UUID in Databases.
How does UUID v7 improve time-range queries in TimescaleDB?
With a generated created_at column derived from the UUID v7 primary key, TimescaleDB chunk exclusion applies to time-range queries. A query for the last hour touches only the current chunk, not all historical chunks. UUID v7 also enables cursor pagination on the primary key alone — WHERE id > cursor ORDER BY id — without a secondary time index. See UUID v7 for Cursor Pagination.
How does UUID v7 work with TimescaleDB continuous aggregates?
Continuous aggregates (materialized views refreshed automatically) group by time buckets using the hypertable's time column. If you use a generated created_at from UUID v7, the continuous aggregate can bucket on time_bucket('1 hour', created_at). The UUID v7 primary key provides uniqueness; the generated timestamp drives aggregation. See UUID v7 in ClickHouse for a similar analytics pattern.