Why Are We Still Doing This the Hard Way?
If you're still building calendar tables, left-joining to fill gaps, and hand-rolling interpolation in Python— you're working too hard.
Vertica makes time series analytics effortless. With a single SELECT statement, you can resample data, fill gaps, and even interpolate—all natively within database.
🔍 The Problem with Traditional Time Series Workflows
Traditional time series handling often involves:
- Ragged inputs
- Missing slices
- Misaligned windows
While TIME_SLICE can bucket timestamps, it does not fill gaps. That’s where Gap Filling Interpolation (GFI) comes, ensuring consistent rollups and downstream joins.
⚙️ Built-In Interpolation Modes
Vertica supports two server-side interpolation strategies:
- CONST: Last value carried forward (ideal for stepwise signals)
- LINEAR: Linear interpolation (for continuous measures)
Both run at scale inside Vertica, eliminating the need for Python post-processing or temporary tables.
How It Works: A Quick Example
Let’s understand the interpolation modes with a sample example.
Step 1: Create a table.
This table stores:
- device_id: Identifier for each device
- ts: Timestamp of the metric
- value: The recorded metric value
CREATE TABLE metrics (
device_id VARCHAR(50),
ts TIMESTAMP,
value FLOAT
);
Step 2: Insert data.
This data includes irregular time intervals for two devices (sensor_1 and sensor_2), which makes it ideal for demonstrating gap filling and interpolation using Vertica’s TIMESERIES clause.
INSERT INTO metrics (device_id, ts, value) VALUES
('sensor_1', '2025-09-17 10:00:00', 10.0),
('sensor_1', '2025-09-17 10:00:07', 12.0),
('sensor_1', '2025-09-17 10:00:15', 14.0),
('sensor_1', '2025-09-17 10:00:25', 16.0),
('sensor_2', '2025-09-17 10:00:03', 20.0),
('sensor_2', '2025-09-17 10:00:11', 22.0),
('sensor_2', '2025-09-17 10:00:19', 24.0);
COMMIT;
Step 3: Lets perform gap filling with SQL
🔁 1. CONST Interpolation (Last Observation Carried Forward)
Constant Interpolation is ideal for stepwise signals like status flags, counters, or discrete events where the last known value should persist until updated.
SELECT
slice_time,
device_id,
TS_FIRST_VALUE(value, 'CONST') AS interpolated_value
FROM metrics
TIMESERIES slice_time AS '5 seconds'
OVER (PARTITION BY device_id ORDER BY ts);
slice_time | device_id | interpolated_value
---------------------+-----------+--------------------
2025-09-17 10:00:00 | sensor_1 | 10
2025-09-17 10:00:05 | sensor_1 | 10
2025-09-17 10:00:10 | sensor_1 | 12
2025-09-17 10:00:15 | sensor_1 | 14
2025-09-17 10:00:20 | sensor_1 | 14
2025-09-17 10:00:25 | sensor_1 | 16
2025-09-17 10:00:00 | sensor_2 |
2025-09-17 10:00:05 | sensor_2 | 20
2025-09-17 10:00:10 | sensor_2 | 20
2025-09-17 10:00:15 | sensor_2 | 22
(10 rows)
Based on the results, it is observed that value is missing for sensor 2 for timestamp 2025-09-17 10:00:00. It is due to how Vertica handles interpolation with the CONST method. Vertica’s TS_FIRST_VALUE (..., 'CONST') only interpolates after the first known value. the first timestamp for sensor_2 is 025-09-17 10:00:03. So the slice at '2025-09-17 10:00:00' has no prior value to carry forward and thus returns NULL.
📈 2. LINEAR Interpolation
Linear Interpolation is best for continuous measurements like temperature, pressure, or sensor readings where values change gradually over time.
SELECT
slice_time,
device_id,
TS_FIRST_VALUE(value, 'LINEAR') AS interpolated_value
FROM metrics
TIMESERIES slice_time AS '5 seconds'
OVER (PARTITION BY device_id ORDER BY ts);
slice_time | device_id | interpolated_value
---------------------+-----------+--------------------
2025-09-17 10:00:00 | sensor_1 | 10
2025-09-17 10:00:05 | sensor_1 | 11.4285714285714
2025-09-17 10:00:10 | sensor_1 | 12.75
2025-09-17 10:00:15 | sensor_1 | 14
2025-09-17 10:00:20 | sensor_1 | 15
2025-09-17 10:00:25 | sensor_1 | 16
2025-09-17 10:00:00 | sensor_2 |
2025-09-17 10:00:05 | sensor_2 | 20.5
2025-09-17 10:00:10 | sensor_2 | 21.75
2025-09-17 10:00:15 | sensor_2 | 23
(10 rows)
Based on the results, it is observed that value is missing for sensor 2 for timestamp 2025-09-17 10:00:00. This happens because the first known timestamp for sensor_2 is '2025-09-17 10:00:03', but LINEAR interpolation requires two known points to compute a value. Since there's no second point before 10:00:00, Vertica cannot interpolate and returns NULL.
🚀 Performance & Infrastructure Wins
All resampling and interpolation happen next to the data in Vertica’s columnar MPP engine. In Eon Mode, you can:
- Isolate workloads into subclusters
- Cache hot time slices in the depot
- Achieve p95 latency on lean compute
No noisy neighbors. No pandas’ extracts. Just fast, scalable SQL.
✅ Best-Fit Use Cases
This feature shines in domains where regularized time series are critical:
- IoT / OT telemetry: Resample irregular sensor feeds for alerting & KPIs
- AIOps / Monitoring: Normalize metrics for SLO dashboards
- Market Data / Ad Tech: Convert ticks to fixed bars for attribution or pacing
- Energy / Utilities: Forecasting, interval billing, anomaly detection
❌ When Not to Use It
Avoid using GFI for:
- Advanced interpolation (e.g., splines)
- Millisecond-latency streaming transforms (better handled in stream processors before landing in Vertica)
