---
title: "Exponential Smoothing, Holt-Winters in Postgres"
description: " Implement simple exponential smoothing, Holt, and Holt-Winters in Postgres with SQL recursive CTEs, a statsmodels check, and when to switch to Python. "
section: "Time-series basics"
published: 2026-11-02T12:25:44.962Z
updated: 2026-11-02T00:00:00.000Z
---

*Updated at Nov 2, 2026*

> **TimescaleDB is now Tiger Data.**

Exponential smoothing is a forecasting method that averages past observations with weights that decay geometrically with age, so recent data counts most and old data fades gradually. Simple exponential smoothing (SES) models a level, Holt's method adds a trend, and Holt-Winters adds seasonality. Each is a short recurrence, which makes all three practical to write in SQL.

This guide gives you a runnable PostgreSQL implementation of each variant, a numeric check against statsmodels, and a clear view of where SQL stops being the right tool. For the wider method taxonomy, see our guide to [<u>time-series forecasting methods</u>](https://www.tigerdata.com/learn/time-series-forecasting).

## Which method do you need? Simple vs. double vs. triple exponential smoothing

Pick the method by the pattern in your data. Use SES when the series fluctuates around a level with no trend or seasonality, Holt's linear method when it also drifts up or down, and Holt-Winters when it additionally repeats a fixed-length cycle, such as a weekly load pattern. Each step adds one component and one smoothing parameter.

| **SES** | **Holt (double)** | **Holt-Winters (triple)** |
| --- | --- | --- |
| Components modeled | Level | Level + trend | Level + trend + seasonality |
| Data pattern | No trend, no seasonality | Trend, no seasonality | Fixed-length seasonal cycle, with or without trend |
| Parameters | alpha | alpha, beta | alpha, beta, gamma (plus season length m) |
| Forecast shape | Flat line | Straight line | Straight line plus repeating seasonal pattern |
| Difficulty in SQL | Easy: one state column | Moderate: two state columns, trend initialization | Hardest: array state, seasonal initialization |

**Naming overlap.** "Double" and "triple" exponential smoothing usually mean Holt and Holt-Winters. Some texts use "double" for Brown's variant, which smooths the smoothed series; this guide uses it for Holt. In finance and statistical process control, "EWMA" (exponentially weighted moving average) is the same recurrence as SES.

When to use which:

- **SES:** noisy but stable readings, such as a sensor hovering around a setpoint. Forecasts are flat, so it is smoother and a baseline more than a long-range forecaster.
- **Holt:** a series with a steady slope, such as monotonic growth in storage or load.
- **Holt-Winters:** a series with a repeating cycle. It needs fixed-length cycles, evenly spaced input, and ideally at least two full cycles of history.

## Setup: a hypertable, evenly spaced buckets, and gap handling

The examples use one synthetic dataset: hourly kW readings for a single site over 84 days starting Monday, 2026-01-05. The series has a baseline of 100 kW, a trend of +0.8 kW per day, a weekly swing of about 5 kW, a daily cycle that averages out within each day, deterministic pseudo-noise, and one missing day. Every number below comes from this dataset, so you can reproduce and compare it.

`SET timezone = 'UTC';

CREATE TABLE energy_readings (
  ts      TIMESTAMPTZ      NOT NULL,
  site_id INTEGER          NOT NULL,
  kw      DOUBLE PRECISION NOT NULL
) WITH (tsdb.hypertable, tsdb.partition_column = 'ts');

INSERT INTO energy_readings (ts, site_id, kw)
SELECT TIMESTAMPTZ '2026-01-05 00:00:00+00' + h * INTERVAL '1 hour',
       1,
       100.0::float8
       + 0.8::float8 * (h / 24)
       + 5 * sin(2 * pi() * ((h / 24) % 7) / 7.0)
       + 20 * sin(2 * pi() * (h % 24) / 24.0)
       + 30 * sin(h::float8 * 12.9898)
FROM generate_series(0, 84 * 24 - 1) AS h
WHERE h / 24 <> 40;   -- day 40 is missing on purpose`

Smoothing recurrences assume one observation per equal time step. Raw sensor data rarely arrives that way, and a recurrence has no notion of elapsed time: it treats the next row as the next step. Regularize first with time_bucket, which collapses raw rows into fixed-width buckets.

`time_bucket_gapfill` then creates the missing buckets and fills them with` `[`<u>locf()</u>`](https://www.tigerdata.com/docs/reference/timescaledb/hyperfunctions/time_bucket_gapfill/locf) (last observation carried forward) or [`<u>interpolate()</u>`](https://www.tigerdata.com/docs/reference/timescaledb/hyperfunctions/time_bucket_gapfill/interpolate) (linear interpolation). The gapfill needs a bounded time range in the `WHERE` clause. Here we interpolate, number the days with `row_number()`, and store the result:

`CREATE TABLE daily_series AS
SELECT row_number() OVER (ORDER BY bucket) AS n, bucket, y
FROM (
  SELECT time_bucket_gapfill('1 day', ts) AS bucket,
         interpolate(avg(kw))             AS y
  FROM energy_readings
  WHERE site_id = 1
    AND ts >= TIMESTAMPTZ '2026-01-05 00:00:00+00'
    AND ts <  TIMESTAMPTZ '2026-03-30 00:00:00+00'
  GROUP BY bucket
) g;`

You should get 84 rows (`SELECT count(*) FROM daily_series`). Days 1 to 70 are the training window, and days 71 to 84 are the hold-out we forecast later. For irregular timestamps, where a row-based recurrence misleads, see [<u>moving averages in SQL</u>](https://www.tigerdata.com/learn/moving-averages-time-series-sql) and the` `[`<u>time_weight()</u>`<u> reference</u>](https://www.tigerdata.com/docs/reference/toolkit/time_weight/time_weight). Gapfill details are in the [<u>time_bucket_gapfill() reference</u>](https://www.tigerdata.com/docs/reference/timescaledb/hyperfunctions/time_bucket_gapfill/time_bucket_gapfill).

## Simple exponential smoothing (SES) in SQL

SES keeps one number, the level, and nudges it toward each new observation.

### The exponential smoothing formula

`level_t = alpha * y_t + (1 - alpha) * level_(t-1)
forecast_(t+1) = level_t`

Alpha is between 0 and 1. The one-step-ahead forecast is just the latest level, and every multi-step forecast is the same flat value.

### Worked example

Seed the level with the first observation (105.480) and use alpha = 0.35. Day 2 is 0.35 x 101.237 + 0.65 x 105.480 = 103.995, which is also the forecast for day 3.

| **Day (n)** | **Observed y** | **One-step forecast** | **Level after update** |
| --- | --- | --- | --- |
| 1 | 105.480 | none | 105.480 |
| 2 | 101.237 | 105.480 | 103.995 |
| 3 | 106.135 | 103.995 | 104.744 |
| 4 | 108.545 | 104.744 | 106.074 |
| 5 | 95.485 | 106.074 | 102.368 |
| 6 | 103.358 | 102.368 | 102.715 |

### Recursive CTE implementation

A recursive CTE computes the same levels. The recursive term may reference itself only once (see the [<u>PostgreSQL </u>`<u>SELECT</u>`<u> reference</u>](https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH)). So the row numbers were assigned earlier in `daily_series`, and the recursion joins on `n = n + 1`. For more on the mechanics, see our guide to [<u>recursive queries in SQL</u>](https://www.tigerdata.com/learn/recursive-query-in-sql-what-it-is-and-how-to-write-one).

`CREATE TABLE ses_fit AS
WITH RECURSIVE
params AS (SELECT 0.35::float8 AS alpha),
ses AS (
  SELECT n, y, NULL::float8 AS fc, y AS level          -- seed: first observation
  FROM daily_series
  WHERE n = 1
  UNION ALL
  SELECT d.n, d.y,
         r.level,                                       -- one-step forecast for day n
         p.alpha * d.y + (1 - p.alpha) * r.level
  FROM ses r
  JOIN daily_series d ON d.n = r.n + 1
  CROSS JOIN params p
  WHERE d.n <= 70                                       -- training window
)
SELECT * FROM ses;

SELECT n, round(y::numeric, 3) AS y, round(fc::numeric, 3) AS fc,
       round(level::numeric, 3) AS level
FROM ses_fit WHERE n <= 6 ORDER BY n;`

### Seeding and alpha intuition

**Seeding.** This query seeds with the first observation. The common alternative is the mean of the first few points. Seeding with the 7-day mean (102.916) instead moves the day-2 level from 103.995 to 102.912. The seed's influence shrinks by a factor of 0.65 each step, so after about 11 steps less than 1% of the original gap remains, and by day 70 the two levels agree to many decimals.

**Alpha intuition.** A high alpha reacts quickly and passes noise through; a low alpha smooths heavily and lags real changes. With alpha = 0.35, the weights on the last five observations are 0.350, 0.228, 0.148, 0.096, and 0.062.

### Choosing alpha for exponential smoothing in SQL

Choose the exponential smoothing alpha by minimizing one-step-ahead squared error on the training window. A recursive CTE can carry every candidate alpha through the recursion at once, then score each:

`WITH RECURSIVE
alphas AS (
  SELECT (g * 0.05)::float8 AS alpha FROM generate_series(1, 19) AS g
),
grid AS (
  SELECT a.alpha, d.n, d.y, NULL::float8 AS fc, d.y AS level
  FROM alphas a CROSS JOIN daily_series d
  WHERE d.n = 1
  UNION ALL
  SELECT r.alpha, d.n, d.y, r.level,
         r.alpha * d.y + (1 - r.alpha) * r.level
  FROM grid r
  JOIN daily_series d ON d.n = r.n + 1
  WHERE d.n <= 70
)
SELECT alpha, round(sum((y - fc)^2)::numeric, 1) AS sse
FROM grid WHERE fc IS NOT NULL
GROUP BY alpha ORDER BY sse LIMIT 3;`

` alpha |  sse
-------+--------
  0.35 | 2829.8
  0.30 | 2845.4
  0.40 | 2849.9`

On this data the minimum is alpha = 0.35, with the neighbors close behind, so the error surface is flat near the optimum. This is a coarse grid search, not a numerical optimizer. Maximum-likelihood fitting belongs in Python; see [<u>when to leave SQL</u>](https://www.tigerdata.com/learn/exponential-smoothing-holt-winters-postgres#when-to-leave-sql-cross-checking-and-handing-off-to-statsmodels).

## Holt's linear trend (double exponential smoothing) in SQL

SES forecasts a flat line, so it lags any series that keeps rising. Holt's method carries a trend alongside the level:

`level_t = alpha * y_t + (1 - alpha) * (level_(t-1) + trend_(t-1))
trend_t = beta * (level_t - level_(t-1)) + (1 - beta) * trend_(t-1)
forecast_(t+h) = level_t + h * trend_t`

Alpha smooths the level and beta smooths the trend. This is the parameterization statsmodels uses, where beta is the trend smoothing parameter (smoothing_trend). [<u>Hyndman and Athanasopoulos</u>](https://otexts.com/fpp3/exponential-smoothing.html#holts-linear-trend-method) write the same parameter as beta-star (β*). 

**Initialization.** The recursion needs a starting level and trend. We fit an ordinary least-squares line to the first 14 days with `regr_intercept` and `regr_slope`, which are core PostgreSQL aggregates. Two full weeks keep the weekly pattern from dominating the slope. The simpler alternative is the first difference, `y_2 - y_1`, which is much noisier. The query starts from a state at day 0 (intercept and slope) and steps forward:

`CREATE TABLE holt_fit AS
WITH RECURSIVE
params AS (SELECT 0.05::float8 AS alpha, 0.35::float8 AS beta),
ols AS (
  SELECT regr_intercept(y, n) AS l0, regr_slope(y, n) AS b0
  FROM daily_series WHERE n <= 14
),
holt AS (
  SELECT 0::bigint AS n, NULL::float8 AS y, NULL::float8 AS fc,
         l0 AS level, b0 AS trend
  FROM ols
  UNION ALL
  SELECT d.n, d.y,
         r.level + r.trend,
         p.alpha * d.y + (1 - p.alpha) * (r.level + r.trend),
         -- trend update uses the new level, written out to keep one recursive reference
         p.beta * (p.alpha * d.y + (1 - p.alpha) * (r.level + r.trend) - r.level)
           + (1 - p.beta) * r.trend
  FROM holt r
  JOIN daily_series d ON d.n = r.n + 1
  CROSS JOIN params p
  WHERE d.n <= 70
)
SELECT * FROM holt;`

The starting values here are level 102.101 and trend 0.463 per day. A grid over alpha and beta (the SES pattern with a second `generate_series` cross join) picked alpha = 0.05 and beta = 0.35 on the training window.

**Where SES lags.** Compare one-step errors on the training window. A positive mean error means the forecast sits below the observed values.

`SELECT 'SES' AS method, round(avg(y - fc)::numeric, 2) AS mean_error,
       round(sqrt(avg((y - fc)^2))::numeric, 2) AS rmse
FROM ses_fit WHERE n >= 2
UNION ALL
SELECT 'Holt', round(avg(y - fc)::numeric, 2),
       round(sqrt(avg((y - fc)^2))::numeric, 2)
FROM holt_fit WHERE n >= 2;`

 `method | mean_error | rmse
--------+------------+------
 SES    |       1.88 | 6.40
 Holt   |       0.13 | 5.54`

SES runs about 1.9 kW below the series on average because it keeps chasing a rising line, while Holt's bias is near zero.

**Damped trend.** A straight-line forecast can run away over long horizons. A damped trend multiplies the trend by a factor phi, which [<u>Hyndman and Athanasopoulos</u>](https://otexts.com/fpp3/exponential-smoothing.html#damped-trend-methods) usually restrict to between 0.8 and 0.98, so forecasts flatten out. It is an optional extension and is not implemented here.

## Holt-Winters (triple exponential smoothing) in SQL

Holt Winters exponential smoothing adds a seasonal component with season length m (7 for daily data with a weekly cycle).

### Additive and multiplicative equations

Additive Holt-Winters assumes the seasonal swing is roughly constant in size:

`level_t = alpha * (y_t - s_(t-m)) + (1 - alpha) * (level_(t-1) + trend_(t-1))
trend_t = beta * (level_t - level_(t-1)) + (1 - beta) * trend_(t-1)
s_t     = gamma * (y_t - level_(t-1) - trend_(t-1)) + (1 - gamma) * s_(t-m)
forecast_(t+h) = level_t + h * trend_t + s_(t+h-m*k),  k = floor((h-1)/m) + 1`

The multiplicative form is better when the seasonal swing scales with the level, for example weekly peaks that grow as overall demand grows. The seasonal term is divided out of each observation instead of subtracted, and multiplies the forecast instead of being added: `forecast = (level + h * trend) * s`. This guide implements the additive form.

### Implementation: array state

A recursive CTE sees only the previous row, but `s_(t-m)` is the seasonal value from m steps back. The fix is to carry the last m seasonal values as an array in the recursion state. Each step reads `seas[1]` (the value from m steps ago), appends the new seasonal value, and drops the oldest. The `init` CTE below builds the day-0 state; the next subsection explains it.

`CREATE TABLE hw_fit AS
WITH RECURSIVE
params AS (SELECT 0.05::float8 AS alpha, 0.55::float8 AS beta, 0.10::float8 AS gamma),
ols AS (
  SELECT regr_intercept(y, n) AS l0, regr_slope(y, n) AS b0
  FROM daily_series WHERE n <= 14
),
by_dow AS (
  SELECT EXTRACT(isodow FROM d.bucket)::int AS dow,
         avg(d.y - (o.l0 + o.b0 * d.n))     AS s
  FROM daily_series d CROSS JOIN ols o
  WHERE d.n <= 14
  GROUP BY 1
),
init AS (
  SELECT o.l0, o.b0,
         (SELECT array_agg(s - (SELECT avg(s) FROM by_dow) ORDER BY dow)
          FROM by_dow) AS seas
  FROM ols o
),
hw AS (
  SELECT 0::bigint AS n, NULL::float8 AS y, NULL::float8 AS fc,
         l0 AS level, b0 AS trend, seas
  FROM init
  UNION ALL
  SELECT d.n, d.y,
         r.level + r.trend + r.seas[1],
         p.alpha * (d.y - r.seas[1]) + (1 - p.alpha) * (r.level + r.trend),
         p.beta * (p.alpha * (d.y - r.seas[1]) + (1 - p.alpha) * (r.level + r.trend)
                   - r.level) + (1 - p.beta) * r.trend,
         r.seas[2:7] || (p.gamma * (d.y - r.level - r.trend)
                         + (1 - p.gamma) * r.seas[1])
  FROM hw r
  JOIN daily_series d ON d.n = r.n + 1
  CROSS JOIN params p
  WHERE d.n <= 70
)
SELECT * FROM hw;`

### Initialization

The state at day 0 needs a level, a trend, and m seasonal values. We reuse the least-squares fit over the first 14 days, which gives the two full cycles that are commonly recommended for initialization (see the [<u>NIST handbook</u>](https://itl.nist.gov/div898/handbook/pmc/section4/pmc435.htm)). We then average the detrended values by day of week with `EXTRACT(isodow ...)` and center them to sum to zero. Day 1 is a Monday, so the array runs Monday to Sunday.

The initial seasonal values (Monday to Sunday) are -0.226, 3.831, 1.577, 4.663, -3.856, -4.992, and -0.996. A grid over alpha, beta, and gamma (19 values each) chose alpha = 0.05, beta = 0.55, and gamma = 0.10; the error surface is flat in beta, so several nearby values score almost the same.

`   level  | trend |                      seas
---------+-------+------------------------------------------------
 155.085 | 0.785 | {0.304,3.675,4.147,3.119,-2.810,-4.197,-3.068}`

### Limits

This Holt-Winters implementation handles one seasonality with a fixed m. The season length is hard-coded in the array slice, and each series runs sequentially. A grid search over 6,859 parameter combinations works but is brute force, so many teams fit parameters offline and score in SQL (see the hybrid pattern below). To pick m, look for a peak in the autocorrelation function at the seasonal lag.

SQL scoring with fixed parameters is feasible. Python wins for fitting.

## Forecasting and evaluating in SQL

Forecasts come from the final state at day 70. SES is flat, Holt adds `h * trend`, and Holt-Winters adds the seasonal value at position `((h - 1) % 7) + 1` of the array. For a hold-out backtest, compare against three baselines: the last value (naive), the value from one week earlier (seasonal naive), and a 7-day moving average.

`WITH actual AS (
  SELECT n - 70 AS h, y FROM daily_series WHERE n > 70
),
errors AS (
  SELECT 'naive' AS method,
         a.y - (SELECT y FROM daily_series WHERE n = 70) AS err
  FROM actual a
  UNION ALL
  SELECT 'seasonal naive',
         a.y - (SELECT y FROM daily_series WHERE n = 64 + ((a.h - 1) % 7))
  FROM actual a
  UNION ALL
  SELECT '7-day moving average',
         a.y - (SELECT avg(y) FROM daily_series WHERE n BETWEEN 64 AND 70)
  FROM actual a
  UNION ALL
  SELECT 'SES', a.y - s.level
  FROM actual a, ses_fit s WHERE s.n = 70
  UNION ALL
  SELECT 'Holt', a.y - (t.level + a.h * t.trend)
  FROM actual a, holt_fit t WHERE t.n = 70
  UNION ALL
  SELECT 'Holt-Winters (additive)',
         a.y - (w.level + a.h * w.trend + w.seas[((a.h - 1) % 7) + 1])
  FROM actual a, hw_fit w WHERE w.n = 70
)
SELECT method,
       round(avg(abs(err))::numeric, 2) AS mae,
       round(sqrt(avg(err ^ 2))::numeric, 2) AS rmse
FROM errors GROUP BY method ORDER BY rmse;`

| **Method** | **MAE (kW)** | **RMSE (kW)** |
| --- | --- | --- |
| Holt-Winters (additive) | 3.42 | 3.86 |
| Holt | 4.54 | 5.46 |
| 7-day moving average | 8.35 | 9.81 |
| Seasonal naive | 9.09 | 11.17 |
| SES | 10.32 | 11.57 |
| Naive | 13.84 | 14.80 |

SES scores worse than a plain 7-day moving average here because the series trends, and a flat forecast cannot follow it. Holt-Winters wins because the data has both a trend and a weekly cycle. These results are specific to this synthetic dataset and one 14-day hold-out, so backtest on your own data before trusting any ranking.

Published comparisons of Holt-Winters against Prophet and ARIMA disagree by dataset, so we do not name a winner. Holt-Winters is a strong, cheap baseline to beat before reaching for a heavier model.

## Moving average vs. exponential smoothing

A simple moving average gives equal weight to the last N points and zero to everything older. Exponential smoothing gives every past point a nonzero weight that decays geometrically, and it needs only the previous state, not a stored window. With alpha = 0.35 the weights are 0.350, 0.228, 0.148, and so on, and the first seven weights sum to about 95%. A 7-day average assigns 0.143 to each of seven days.

As a rough rule of thumb, a window of N points corresponds to alpha near 2 / (N + 1), the same conversion [<u>pandas uses for its span parameter</u>](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.ewm.html). A 7-day average behaves roughly like alpha = 0.25. The two are not equivalent: the moving average drops old points abruptly, so a spike causes a step N days later, while exponential smoothing lets it fade. For window-based implementations, see [<u>moving averages in SQL</u>](https://www.tigerdata.com/learn/moving-averages-time-series-sql).

## Running it at scale: shrink the series before the recurrence

The recurrence is sequential, so cost is O(n) per series. Recursive CTEs are not inherently slow, but you should feed them fewer rows. A sensor sampling every second writes 86,400 rows a day, and a daily forecast needs one.

The pattern: `time_bucket` to the forecast granularity, materialize the result in a [<u>continuous aggregate</u>](https://www.tigerdata.com/docs/build/continuous-aggregates/create-a-continuous-aggregate) kept current by a [<u>refresh policy</u>](https://www.tigerdata.com/docs/build/continuous-aggregates/refresh-policies), and run the smoothing query over the aggregated series.

CREATE MATERIALIZED VIEW daily_kw

WITH (timescaledb.continuous) AS

SELECT time_bucket('1 day', ts) AS bucket, site_id, avg(kw) AS avg_kw

FROM energy_readings

GROUP BY bucket, site_id;



SELECT add_continuous_aggregate_policy('daily_kw',

  start_offset      => INTERVAL '3 days',

  end_offset        => INTERVAL '1 hour',

  schedule_interval => INTERVAL '1 hour');

With end_offset at 1 hour, each day's bucket is materialized at the first hourly run after 01:00, so a morning forecast includes yesterday. 

The recurrence runs in a query, scheduled job, or refresh process over `daily_kw`, not inside the continuous aggregate definition. The documented definition form is a `time_bucket` aggregation over a hypertable, and the continuous aggregate's job here is to make the series short.

A continuous aggregate can't use `time_bucket_gapfill`, so a day with no readings is simply missing from `daily_kw`. Fill gaps when you read it, the same way `daily_series` was built:

`SELECT row_number() OVER (ORDER BY bucket) AS n, bucket, y`

`SELECT row_number() OVER (ORDER BY bucket) AS n, bucket, y
FROM (
  SELECT time_bucket_gapfill('1 day', bucket) AS bucket,
         interpolate(avg(avg_kw))             AS y
  FROM daily_kw
  WHERE site_id = 1
    AND bucket >= TIMESTAMPTZ '2026-01-05 00:00:00+00'
    AND bucket <  TIMESTAMPTZ '2026-03-30 00:00:00+00'
  GROUP BY 1
) g;`

To run many series, carry the key through the recursion: seed one anchor row per `site_id` and join on `d.site_id = r.site_id AND d.n = r.n + 1`. Each site is an independent chain, so you can also split sites across workers or scheduled jobs. Write forecasts to a regular table or hypertable, and keep heavy per-series fitting out of the request path.

For irregular series, `time_weight()` is a time-weighted average aggregate, covered in the [<u>time-weighted averages guide</u>](https://www.tigerdata.com/docs/build/data-management/hyperfunctions/time-weighted-averages). It is not an exponential smoothing function.

### Smoothing for charts is not forecasting: asap_smooth

`asap_smooth()` in the Toolkit (since Toolkit 1.11.0) downsamples a series for visualization while preserving its overall shape. It does not produce forecasts and is not a substitute for exponential smoothing. See the [`<u>asap_smooth()</u>`<u> reference</u>](https://www.tigerdata.com/docs/reference/toolkit/downsampling/asap_smooth).

## When to leave SQL: cross-checking and handing off to statsmodels

Check the SQL against [<u>statsmodels ExponentialSmoothing</u>](https://www.statsmodels.org/stable/generated/statsmodels.tsa.holtwinters.ExponentialSmoothing.html) using the same initial values and fixed parameters, so you compare recurrences rather than optimizers. Read the series and the initial state from Postgres, then run:

`import numpy as np, pandas as pd
from sqlalchemy import create_engine
from statsmodels.tsa.holtwinters import ExponentialSmoothing

engine = create_engine("postgresql+psycopg2://user:password@host:5432/dbname")
y = pd.read_sql("SELECT y FROM daily_series WHERE n <= 70 ORDER BY n", engine)["y"].to_numpy()

model = ExponentialSmoothing(
    y, trend="add", seasonal="add", seasonal_periods=7,
    initialization_method="known",
    initial_level=102.100825, initial_trend=0.462784,
    initial_seasonal=[-0.226468, 3.830533, 1.576664, 4.662703, -3.855792, -4.991660, -0.995979],
)
fit = model.fit(smoothing_level=0.05, smoothing_trend=0.55, smoothing_seasonal=0.10, optimized=False)
print(fit.level[-1], fit.trend[-1], fit.season[-7:])   # compare with hw_fit at n = 70
print(np.abs(fit.fittedvalues - pd.read_sql("SELECT fc FROM hw_fit WHERE n >= 1 ORDER BY n", engine)["fc"].to_numpy()).max())`

With the same initialization and parameters, the in-sample one-step fits and the final level, trend, and seasonal state should agree to within the rounding of the printed starting values, roughly 1e-5. SES and Holt check the same way with `initial_level` set to the first observation (SES) or the intercept and slope (Holt). In statsmodels 0.14.6 and 0.15.0, additive Holt-Winters `forecast()` matched the textbook formula at every horizon except h = 7 and h = 14, where it ran 0.55 higher. The cause is visible in the library source, where the seasonal array is extended for forecasting and the last seasonal state is overwritten with the value from one cycle earlier. Horizons that are multiples of the season length therefore pick up a stale seasonal value (-2.519 instead of -3.068 for Sunday here). Fitted values and final states are not affected, so if your forecasts differ at multiples of the season length, compare final states first, since they are unambiguous.

Initialization and optimizer choices change results, so state them whenever you compare numbers. When we let statsmodels estimate both parameters and initial states, it chose smoothing values near zero and a hold-out RMSE of 3.98 against 3.86 for our grid-picked parameters, which is no meaningful difference on 14 points.

### What SQL does not do well

- Maximum-likelihood parameter optimization.
- ETS model selection by AIC across error, trend, and seasonal types.
- Multiple seasonalities, such as daily and weekly cycles together.
- Prediction intervals.
- State-space ETS models.

A practical split: use SQL for fixed, already-fit parameters scored where the data lives, and for baselines in scheduled jobs. Use Python for fitting, model selection, and intervals. A hybrid fits parameters in Python, stores them in a table such as `forecast_params(site_id, alpha, beta, gamma)`, and scores in SQL by replacing the params CTE with `SELECT alpha, beta, gamma FROM forecast_params WHERE site_id = 1`. For the Python side, see our guide to [<u>time-series analysis and forecasting with Python</u>](https://www.tigerdata.com/learn/time-series-analysis-and-forecasting-with-python), and the [<u>forecasting overview</u>](https://www.tigerdata.com/learn/time-series-forecasting) for how the options compare.

**ETS vs. ARIMA.** ETS models describe level, trend, and seasonality directly. ARIMA models autocorrelation, usually after differencing to reach stationarity. Some exponential smoothing models are special cases of ARIMA: SES corresponds to ARIMA(0,1,1), while many other ARIMA models have no exponential smoothing counterpart ([<u>FPP3</u>](https://otexts.com/fpp3/arima.html#sec-arima-ets)). Neither wins universally, so backtest both on your data. Our guide to [<u>stationary time-series analysis</u>](https://www.tigerdata.com/learn/stationary-time-series-analysis) covers the ARIMA side.

Some systems ship exponential smoothing natively. Oracle Machine Learning for SQL includes an exponential smoothing algorithm, and InfluxData documents `HOLT_WINTERS()` in InfluxQL and `holtWinters()` in Flux. In Postgres you write the recurrence, as shown here.

## Common misunderstandings

- **"Exponential smoothing is just a moving average."** The weighting differs: equal weights over a window versus geometrically decaying weights over all history.
- **"An alpha near 1 means more accuracy."** It means more reactive and more sensitive to noise. Accuracy depends on the series.
- **"Holt-Winters works on any seasonal data."** It needs fixed-length cycles, evenly spaced input, and ideally at least two full cycles.
- **"Recursive CTEs are too slow for this."** Cost is linear in rows. Shrink the series with `time_bucket` and continuous aggregates first.
- **"You need an optimizer for Holt-Winters."** For maximum-likelihood fitting, yes. You can also grid-search, or fit offline and score in SQL.

## Decision framework: SQL, Python, or something else

- **Choose SQL (Postgres or Tiger Cloud) if** the data already lives in Postgres or a hypertable, you need scheduled in-database baselines and scoring with already-fit parameters, the series has a single fixed-length seasonality, and you want no export or ETL step.
- **Choose Python (statsmodels, statsforecast) if** you need maximum-likelihood fitting, automatic ETS model selection, prediction intervals, multiple seasonalities, or per-series tuning across many series.
- **Choose a hybrid if** you fit parameters offline in Python and score or refresh forecasts in SQL next to the data.
- **Choose neither exponential smoothing nor Holt-Winters if** the series has several strong seasonalities, many external regressors, or structural breaks. Consider ARIMA, regression with exogenous features, or machine-learning approaches, validated by backtest.

## Next steps

Run the examples on [<u>Tiger Cloud</u>](https://www.tigerdata.com/cloud). On plain PostgreSQL 16 or later, the recursive CTEs, grid searches, and backtest run unchanged once daily_series exists, but the hypertable, gapfill, and continuous aggregate steps need TimescaleDB. Build the continuous aggregate with the [<u>continuous aggregates guide</u>](https://www.tigerdata.com/docs/build/continuous-aggregates/create-a-continuous-aggregate), then compare your results against statsmodels before scheduling anything. For wider context, read our guide to [<u>time-series analysis</u>](https://www.tigerdata.com/blog/time-series-analysis-what-is-it-how-to-use-it).