Manage and Analyze Time-Series Data
SynxDB handles time-series data through the time_series extension, which covers storage that shards automatically by time, time bucketing and gap-filling queries, incremental materialization through continuous aggregates, columnar compression of historical data, and the background job scheduler that drives these maintenance actions. This topic explains how to enable the extension, create time-series tables, and use continuous aggregates and compression policies to manage time-series data across its lifecycle.
When to use
Device monitoring, application sensor, log analysis, and IoT sensor data share several traits: data arrives continuously in time order, historical records are almost never modified, queries concentrate on recent windows, and cold data is read only in aggregate rather than row by row. Ordinary heap tables fall short in three ways for this kind of workload.
All data sits together, so querying the last hour still scans the whole table.
Historical data is stored row by row, which costs space and slows aggregate queries.
Every minute-level or hour-level rollup rescans the raw data, and the repeated computation grows linearly with data volume.
The time_series extension addresses each point. A time-series table is split automatically into chunks by time, and queries scan only the chunks that fall in range. Historical chunks can be compressed into a columnar format, which typically cuts their space consumption to a fraction of the original. Continuous aggregates materialize rollups incrementally, so only new data is computed, while queries still see the most recent data that has not been materialized yet.
Enable the extension
The time_series extension registers a custom WAL resource manager and background workers, so it must be preloaded through shared_preload_libraries. Creating the extension without preloading fails.
Configure preloading on the cluster and restart:
gpconfig -c shared_preload_libraries -v 'time_series'
gpstop -ar
If other libraries are already preloaded, append time_series to the existing value instead of replacing it. After the restart, create the extension:
CREATE EXTENSION time_series;
All objects of the extension live in the time_series schema. To call time_bucket and the other functions directly, add that schema to the search path:
SET search_path TO public, time_series;
Otherwise, qualify each call as time_series.time_bucket(...).
Warning
Once you have written time-series data, do not remove time_series from shared_preload_libraries. Writes to a time-series table produce WAL records of a custom resource manager that the extension registers. If you remove the library and restart the cluster, mirror segments exit while replaying those records. All mirrors stay down, and the cluster loses its redundancy. gp_segment_configuration might still mark the mirrors as u (Up), so check gpstate -e for the real state.
If you have already removed the library and the mirrors fail to start, rebuild them in full to recover. Incremental recovery, without -F, leaves the mirrors down.
gprecoverseg -aF
Create a time-series table
A time-series table uses USING time_series and declares three options in the WITH clause:
CREATE TABLE sensor (
ts TIMESTAMPTZ NOT NULL,
device_id INT NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION
) USING time_series WITH (
ts_partition_column = 'ts',
ts_chunk_interval = '4 hour',
ts_chunk_origin = '2026-01-01'
) DISTRIBUTED BY (device_id);
Option |
Description |
|---|---|
|
The time partitioning column. Required. Must be |
|
The time span each chunk covers. Required. Must be a positive interval |
|
The alignment origin for chunk boundaries. Defaults to |
Choose a distribution key with reasonably high cardinality, such as a device ID or tenant ID, rather than the time column. Distributing by time concentrates writes for a given period on a single segment, creating a write hotspot, and it also prevents the gap-filling queries described below from running.
Set ts_chunk_origin appropriately
Chunk numbers are counted from ts_chunk_origin, and each segment holds at most 65532 chunks. When the origin sits far from the actual data range and ts_chunk_interval is small, inserts fail outright:
CREATE TABLE metrics_bad (ts TIMESTAMPTZ NOT NULL, v INT) USING time_series
WITH (ts_partition_column = 'ts', ts_chunk_interval = '1 hour') DISTRIBUTED BY (v);
INSERT INTO metrics_bad VALUES ('2026-03-01 00:00+00', 1);
ERROR: time_series table "metrics_bad" has reached the per-segment chunk limit (65532)
The default origin of 2000-01-01 is more than 230,000 hours from 2026, well beyond the limit at one chunk per hour. Always set ts_chunk_origin near the start of your data, or raise ts_chunk_interval to match the data span.
Chunk width is a trade-off. Narrow chunks multiply metadata overhead and can hit the limit; wide chunks weaken time pruning, so queries scan more irrelevant data. As a rule of thumb, size a chunk to hold a few hundred MB to a few GB.
Inspect chunk distribution
SELECT * FROM time_series.ts_chunk_info('sensor') ORDER BY segment_id, chunk_number;
segment_id | chunk_number | nblocks | range_start | range_end | status
------------+--------------+---------+------------------------+------------------------+--------
0 | 358 | 1 | 2026-03-01 00:00:00+00 | 2026-03-01 04:00:00+00 | 0
0 | 359 | 1 | 2026-03-01 04:00:00+00 | 2026-03-01 08:00:00+00 | 0
1 | 358 | 1 | 2026-03-01 00:00:00+00 | 2026-03-01 04:00:00+00 | 0
1 | 359 | 1 | 2026-03-01 04:00:00+00 | 2026-03-01 08:00:00+00 | 0
The status column shows the compression state of each chunk.
Value |
Meaning |
|---|---|
0 |
Uncompressed |
1 |
Compressed |
2 |
Partially compressed. New data was written after the chunk was compressed |
Load data
Time-series tables are append-only. Use a standard INSERT; rows route to the matching chunk based on the value of the partitioning column:
INSERT INTO sensor
SELECT '2026-03-01 00:00+00'::timestamptz + (h || ' hour')::interval + (m || ' minute')::interval,
d, 20.0 + d + h * 0.5, 50.0 + d - h * 0.3
FROM generate_series(0, 23) h, generate_series(1, 5) d, generate_series(0, 59, 10) m;
Out-of-order and late-arriving data load normally and route to the chunk that owns their timestamp. Writes do not have to follow time order.
Time-series tables do not support UPDATE or DELETE. To correct data, insert a new record and take the latest value at query time, or rebuild the affected time range.
Time bucketing and gap filling
time_bucket
time_bucket assigns timestamps to fixed-width buckets and is the basis of time-series aggregation:
SELECT time_bucket('1 hour'::interval, ts) AS bucket,
device_id,
round(avg(temperature)::numeric, 2) AS avg_temp
FROM sensor
WHERE device_id = 1 AND ts < '2026-03-01 03:00+00'
GROUP BY bucket, device_id
ORDER BY bucket;
bucket | device_id | avg_temp
------------------------+-----------+----------
2026-03-01 00:00:00+00 | 1 | 21.00
2026-03-01 01:00:00+00 | 1 | 21.50
2026-03-01 02:00:00+00 | 1 | 22.00
The function supports timestamptz, timestamp, date, and integer time columns, and offers overloads that take an origin, an offset, or a time zone.
time_bucket_gapfill
Offline devices and collection outages leave gaps in a series. A plain aggregate query returns only the buckets that have data, which breaks the time axis when you chart a trend. time_bucket_gapfill fills in the missing buckets across a given range.
The following query assumes that collection on sensor stopped between 01:00 and 03:00 and again between 04:00 and 05:00:
SELECT time_bucket_gapfill('1 hour'::interval, ts,
'2026-03-01 00:00+00'::timestamptz,
'2026-03-01 06:00+00'::timestamptz) AS bucket,
device_id,
round(avg(temperature)::numeric, 1) AS avg_temp
FROM sensor
WHERE device_id = 1
AND ts >= '2026-03-01 00:00+00' AND ts < '2026-03-01 06:00+00'
GROUP BY bucket, device_id
ORDER BY bucket;
bucket | device_id | avg_temp
------------------------+-----------+----------
2026-03-01 00:00:00+00 | 1 | 21.0
2026-03-01 01:00:00+00 | 1 |
2026-03-01 02:00:00+00 | 1 |
2026-03-01 03:00:00+00 | 1 | 22.5
2026-03-01 04:00:00+00 | 1 |
2026-03-01 05:00:00+00 | 1 | 23.5
Two requirements apply.
Cast the start and finish arguments explicitly. Passing bare string literals fails, and the error misleadingly reports the arguments as missing:
ERROR: time_bucket_gapfill requires start and finish arguments
HINT: Use time_bucket_gapfill(bucket_width, ts, start, finish)
Add ::timestamptz or the matching type cast to both arguments.
The distribution key must match the non-time columns in GROUP BY. Otherwise rows of the same group can be split across segments and produce incorrect fill results, so such a query is rejected outright. When grouping by device_id, for example, the table must be DISTRIBUTED BY (device_id). Tables that are DISTRIBUTED RANDOMLY cannot use the function; DISTRIBUTED REPLICATED tables can.
locf and interpolate
Filled buckets are NULL by default. locf carries the last non-null value forward, which suits state and counter values. interpolate draws a straight line between the surrounding non-null values, which suits continuously varying measurements such as temperature or pressure:
SELECT time_bucket_gapfill('1 hour'::interval, ts,
'2026-03-01 00:00+00'::timestamptz,
'2026-03-01 06:00+00'::timestamptz) AS bucket,
device_id,
locf(avg(temperature)) AS carried_forward,
interpolate(max(humidity)) AS interpolated
FROM sensor
WHERE device_id = 1
AND ts >= '2026-03-01 00:00+00' AND ts < '2026-03-01 06:00+00'
GROUP BY bucket, device_id
ORDER BY bucket;
The two functions cannot wrap the same aggregate expression, which raises cannot use both locf() and interpolate() on the same expression.
Also avoid selecting the same aggregate expression both bare and wrapped in locf or interpolate. In that case the bare column is filled as well, so you do not get the original NULLs. To see both raw and filled values, use different aggregate expressions for the two columns.
Optimize time-range queries
Query efficiency on a time-series table comes mainly from chunk-level time pruning: the narrower the time range in the query, the fewer chunks the executor scans. Bound the time column to a specific range whenever you can, so the query avoids a full scan.
Beyond that, a query that buckets by time, orders by time, and takes only the first rows can use the ChunkAppend node. It pulls subplans one chunk at a time, and once the LIMIT above is satisfied it stops pulling, so the sort and scan for later chunks never start.
For the planner to generate a ChunkAppend node, the query must meet two conditions:
The time column is bounded both above and below. With only a lower bound, the range of chunks cannot be enumerated and the plan falls back to a plain
ChunkScan.The number of chunks stays within
time_series.chunk_append_max_chunks(64 by default). A time range wide enough to exceed the cap falls back toChunkScanas well.
Run EXPLAIN to confirm whether the plan contains a ChunkAppend node.
Use continuous aggregates
A continuous aggregate materializes bucketed rollups into an internal table and computes only the increment as new data arrives, instead of rescanning the raw data on every query.
Create a continuous aggregate
CREATE MATERIALIZED VIEW sensor_hourly
WITH (time_series.continuous) AS
SELECT time_bucket('1 hour'::interval, ts) AS bucket,
device_id,
count(*) AS cnt,
avg(temperature) AS avg_temp,
max(humidity) AS max_hum
FROM sensor
GROUP BY bucket, device_id;
Creation requires the following:
The source must be a
time_seriestable. Ordinary heap tables are not supported.The query must have a
GROUP BYthat includestime_bucket, with a positive constant interval as the bucket width.The
FROMclause cannot contain a subquery.TABLESAMPLEand row-level security are not supported.
You can create several continuous aggregates with different bucket widths on the same source table, for example to maintain hourly and daily rollups side by side.
Real-time and materialized-only modes
A continuous aggregate runs in real-time mode by default, where query results combine the materialized portion with the most recent data that has not been materialized. You can therefore query it immediately after creation without a refresh, and newly written rows appear right away:
INSERT INTO sensor VALUES ('2026-03-02 00:30+00', 1, 99.0, 1.0);
SELECT * FROM sensor_hourly WHERE bucket >= '2026-03-02 00:00+00';
bucket | device_id | cnt | avg_temp | max_hum
------------------------+-----------+-----+----------+---------
2026-03-02 00:00:00+00 | 1 | 1 | 99 | 1
For steadier response times, switch to materialized-only mode so that queries read only materialized data:
ALTER VIEW sensor_hourly SET (time_series.materialized_only = true);
Note
The user-facing object of a continuous aggregate is a plain view rather than a materialized view, so it is altered and dropped with ALTER VIEW and DROP VIEW. Using ALTER MATERIALIZED VIEW raises "sensor_hourly" is not a materialized view.
Switch back to real-time mode:
ALTER VIEW sensor_hourly SET (time_series.materialized_only = false);
Refresh
Refresh everything manually:
CALL time_series.refresh_continuous_aggregate('sensor_hourly', NULL, NULL);
Refresh a specific window:
CALL time_series.refresh_continuous_aggregate(
'sensor_hourly',
'2026-03-01 00:00+00'::timestamptz,
'2026-03-02 00:00+00'::timestamptz);
More commonly, register a refresh policy and let a background job run it on a schedule. See Configure background jobs and policies.
Inspect and drop
SELECT view_name, materialized_only, bucket_width
FROM time_series.continuous_aggregates;
Dropping a continuous aggregate also removes its internal materialization table and catalog entries:
DROP VIEW sensor_hourly;
The materialization table is protected and cannot be truncated directly. To rebuild its contents, truncate the source table, or drop and re-create the continuous aggregate.
Compress historical data
Historical chunks can be converted to the PAX columnar format, which cuts space consumption substantially and speeds up aggregate queries.
Configure compression
SELECT time_series.set_compress_config('sensor', 'device_id', 'ts');
The second argument, segmentby, names the segmenting columns; rows sharing a value are grouped into the same segment, so low-cardinality filter dimensions such as a device ID work well. The third argument, orderby, names the sort columns within a segment, usually the time column, because sorting brings similar values together and improves the compression ratio. The configuration is optional; without it, chunks compress with default settings.
Run compression
Compress chunks older than a given interval:
SELECT time_series.compress_chunks('sensor', INTERVAL '7 days');
Compression alone does not release disk space, because the original row-store data still occupies its blocks. Reclaim it with a second call:
SELECT time_series.reclaim_chunk_heaps('sensor');
Both steps can run as a single call, which splits the work across two transactions so that row-store space is reclaimed only after the columnar data is durable:
CALL time_series.compress_and_reclaim('sensor', INTERVAL '7 days');
You can also compress one chunk by number. Get the chunk number from ts_chunk_info:
SELECT time_series.compress_chunk('sensor', 358);
Check compression results
SELECT count(*) AS chunks,
sum(numrows) AS rows,
pg_size_pretty(sum(compressed_size)) AS compressed
FROM time_series.ts_compressed_chunk
WHERE table_oid = 'sensor'::regclass;
chunks | rows | compressed
--------+--------+------------
48 | 200001 | 2232 kB
Warning
Columnar data lives in sidecar files that pg_total_relation_size and similar functions do not count. After row-store space is reclaimed, these functions return a value close to zero, which does not mean the data is gone. To see the real footprint of compressed data, read the compressed_size column of time_series.ts_compressed_chunk.
Note
time_series.ts_compressed_chunk also has an uncompressed_size column, but that column is always empty and cannot be used to compute a compression ratio.
To gauge the benefit of compression, record pg_total_relation_size beforehand and compare it with the sum of compressed_size after compressing and reclaiming:
-- Before compression
SELECT pg_size_pretty(pg_total_relation_size('sensor'));
-- After compressing and reclaiming
SELECT pg_size_pretty(sum(compressed_size)) FROM time_series.ts_compressed_chunk
WHERE table_oid = 'sensor'::regclass;
Reads and writes after compression
Compressed data queries normally, and aggregation, filtering, and bucketing return the same results as before compression.
Writes continue to work after compression, including backfilling historical data into the time range covered by an already-compressed chunk. Such a chunk moves from compressed to partially compressed (status 2). The interval-based compress_chunks, compress_and_reclaim, and the compression policy recompress partially compressed chunks. compress_chunk, which compresses a single chunk by number, skips them.
Configure background jobs and policies
The extension runs maintenance actions such as refresh and compression through background workers, with no external scheduler. The processes start with the cluster:
SELECT application_name, backend_type FROM pg_stat_activity
WHERE backend_type LIKE 'time_series%';
application_name | backend_type
-----------------------------------------+-----------------------
time_series launcher | time_series launcher
time_series Background Worker Scheduler | time_series scheduler
Continuous aggregate refresh policy
SELECT time_series.add_continuous_aggregate_policy(
'sensor_hourly',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
This policy runs hourly and refreshes the window from one day ago to one hour ago. The end_offset leaves a buffer so that the newest bucket, which is still receiving writes, is not refreshed repeatedly.
A refresh policy does not backfill the data that existed before you created the continuous aggregate. On a table that already holds data, run one full refresh manually before you add the policy:
CALL time_series.refresh_continuous_aggregate('sensor_hourly', NULL, NULL);
Compression policy
SELECT time_series.add_compression_policy(
'sensor',
compress_after => INTERVAL '7 days',
schedule_interval => INTERVAL '12 hours');
Every 12 hours, this policy compresses chunks older than 7 days and reclaims their space. compress_after must be positive, which keeps the policy from compressing the chunk that is still being written.
Inspect jobs and run history
SELECT id, application_name, schedule_interval, scheduled FROM time_series.bgw_job ORDER BY id;
id | application_name | schedule_interval | scheduled
------+--------------------------------------------+-------------------+-----------
1 | Job History Log Retention Policy [1] | 1 mon | t
1001 | Refresh CAGG Policy [public.sensor_hourly] | 01:00:00 | t
1002 | Compression Policy [sensor] | 12:00:00 | t
Job 1 is created when the extension is installed and periodically trims the job run history.
Run history and failures are exposed through two views:
SELECT job_id, proc_name, succeeded, execution_start, duration
FROM time_series.job_history ORDER BY id DESC LIMIT 10;
SELECT * FROM time_series.job_errors ORDER BY 1 DESC LIMIT 10;
Each policy type has a summary view with run counts, success and failure totals, and the next scheduled start:
SELECT * FROM time_series.cagg_policy_stats;
SELECT * FROM time_series.compression_policy_stats;
Manage jobs
Run a job once, immediately:
CALL time_series.run_job(1001);
Change the schedule or pause a job:
SELECT time_series.alter_job(1001, schedule_interval => INTERVAL '30 minutes');
SELECT time_series.alter_job(1001, scheduled => false);
Remove a policy:
SELECT time_series.remove_continuous_aggregate_policy('sensor_hourly');
SELECT time_series.remove_compression_policy('sensor');
Register a custom job
Besides the built-in policies, you can register your own procedure to run on a schedule. The procedure must accept a job_id int and a config jsonb argument:
CREATE PROCEDURE my_housekeeping(job_id INT, config JSONB)
LANGUAGE plpgsql AS $$
BEGIN
-- custom maintenance logic
END $$;
SELECT time_series.add_job('my_housekeeping', INTERVAL '1 hour',
config => '{"retention":"30 days"}'::jsonb);
Remove it:
SELECT time_series.delete_job(1021);
How it works
Chunks and query pruning
A time-series table does not create a separate table object per chunk. Chunks are storage branches of the same table, addressed by table OID and chunk number. The chunk number is derived from the partitioning column value, ts_chunk_origin, and ts_chunk_interval; writes route by it and queries prune by it.
The corresponding plan node is Custom Scan (ChunkScan):
-> Custom Scan (ChunkScan) on sensor
Filter: ((ts >= '2026-01-10 00:00:00+00'::timestamptz) AND (ts < '2026-01-11 00:00:00+00'::timestamptz))
Storage: Time-Series Chunk
The real-time view of a continuous aggregate
Internally, a continuous aggregate consists of three objects: a materialization table holding the rollups, a view that queries the source directly, and the view that users query. In real-time mode, the user view stitches the first two together at a watermark, reading the materialization table below the watermark and aggregating the source above it. A trigger records changes to the source in an invalidation log, and a refresh uses that log to decide which time ranges to recompute.
In materialized-only mode, the user view reads the materialization table alone.
Compression and data protection
Compression rewrites the row-store data of a chunk into the PAX columnar format, segmenting by segmentby, sorting by orderby, and encoding the result into sidecar files. The original row-store branch is then truncated and reclaimed.
The columnar data is covered by WAL as well. On clusters configured with mirrors, it replicates to the mirror segments and survives failover.
Limitations
Watch for the following two behaviors in particular. The statement raises no error, but its actual effect differs from what you expect.
TABLESAMPLE returns all rows
Warning
A TABLESAMPLE clause on a time-series table has no effect and raises no error. Whatever sampling percentage you specify, both SYSTEM and BERNOULLI return the entire table. Because nothing warns you, an estimate based on the sample is wrong. Do not use TABLESAMPLE on time-series tables.
To sample a time-series table, filter in the WHERE clause instead. The following takes roughly 10% of the rows at random:
SELECT * FROM sensor WHERE random() < 0.1;
If you only want a sense of the data distribution, you can also rely on time pruning and query a narrow time window directly.
Row-locking clauses lock the whole table
Warning
Row-locking clauses such as SELECT ... FOR UPDATE and FOR SHARE lock the whole table on a time-series table instead of the selected rows. Writes to the table from other sessions wait until the transaction ends.
Do not rely on these clauses for concurrency control on time-series tables. The tables are append-only and support neither UPDATE nor DELETE, so normal usage does not need row locks. If your logic genuinely has to serialize a section of processing, use advisory locks (pg_advisory_lock) or take a lock on a separate ordinary table.
Data modification
Operation |
Notes |
|---|---|
|
Not supported. Time-series tables are append-only |
|
Not supported |
|
Supported. Chunks are removed along with the data, and the table can be written again |
|
Supported |
Schema changes
Operation |
Notes |
|---|---|
Add a column |
Supported |
Rename a non-partitioning column |
Supported |
Drop or rename the partitioning column |
Not supported |
Change the type of the partitioning column |
Not supported |
Change a column type on existing data |
Not supported while the table has compressed chunks |
Change |
Not supported once the table has chunks. These options determine row routing, so new rows would land in different chunks than existing ones |
Change the access method |
Not supported |
Add inheritance |
Not supported |
Indexes and maintenance
Time-series tables currently do not support indexes. Queries rely on chunk-level time pruning and columnar compression instead. This restriction is temporary, and a later release will remove it.
Because the tables are append-only, there are no dead tuples to compact, so VACUUM FULL and CLUSTER are neither supported nor needed. The tablespace of the table cannot be changed either.
Continuous aggregates
For the requirements a source table and query must meet, see Create a continuous aggregate. In addition, do not rename a continuous aggregate or its columns. Drop and re-create it instead.
Drop a database
The background job scheduler stays connected to every database where the extension is installed, so a plain DROP DATABASE fails:
ERROR: database "tsdb" is being accessed by other users
DETAIL: There is 1 other session using the database.
Add WITH (FORCE) to drop the database:
DROP DATABASE tsdb WITH (FORCE);
Configuration parameters
The time_series extension provides the following configuration parameters, which you can set with gpconfig or SET.
Queries and continuous aggregates
These parameters take the user context, so you can set them within a session. They control query plans and the behavior of continuous aggregates.
Parameter |
Default |
Description |
|---|---|---|
|
|
Whether the planner generates ChunkAppend plans |
|
64 |
Maximum number of subplans ChunkAppend can build |
|
|
Whether continuous aggregates can be created |
|
10 |
Materialization batches per refresh window |
|
-1 |
Default retry count for continuous aggregate refresh jobs. -1 means unlimited |
|
0 |
Default maximum runtime for continuous aggregate refresh jobs. 0 means unlimited |
Background job scheduling
These parameters control the background job subsystem itself. The defaults suit most deployments, so you rarely need to change them.
Parameter |
Default |
Context |
Description |
|---|---|---|---|
|
|
postmaster |
Whether the background job scheduler is enabled |
|
16 |
postmaster |
Upper bound on background processes the extension can hold |
|
16 |
postmaster |
Concurrent job workers a single scheduler can spawn |
|
60000 ms |
sighup |
Poll interval for detecting extension install and removal |
|
|
superuser |
Log level of the background job subsystem |
|
-1 s |
sighup |
Restart delay after a scheduler exits abnormally. -1 means the postmaster does not restart it |
|
|
sighup |
Set to |