Time Series Forecasting with PostgreSQL: Methods, SQL Examples, and When to Use Python

Time-series forecasting is the process of using historical time-indexed observations to estimate future values of a variable. It differs from time-series analysis, which examines patterns in past data, in that forecasting is specifically predictive and forward-looking. The methods range from a 7-day moving average in a single SQL query to a zero-shot foundation model trained on billions of time points.

Most time-series forecasting guides assume Python from line one. The imports come first, then the DataFrame setup, then statsmodels or Prophet. If your data lives in PostgreSQL, you're left figuring out the connection layer yourself.

This guide works the other way. It maps what's achievable directly in the database against what genuinely requires a Python layer, and explains the practical criteria for drawing that line. Because Tiger Data builds on PostgreSQL, there's a natural bias worth naming: the in-database examples use TimescaleDB hyperfunctions where they add value. But the SQL methods here also apply to standard PostgreSQL, and the guide includes cases where Python is clearly the right call.

Two myths come up repeatedly in developer communities. The first: you need Python for any real forecasting. The second: ARIMA is the default or standard model for time-series work. Both are wrong in ways that matter, and this guide addresses them directly.

For the conceptual breakdown of time-series components (trend, seasonality, noise decomposition), see the foundational overview. This article picks up where that one leaves off: implementation.

The framework used throughout: each method is classified as in-database (pure SQL), Python-required, or a reasonable approximation in SQL with Python as the cleaner alternative. The goal is deliberate choice over reflexive defaults.

The in-database vs. Python decision

Methods are classified below by what they require, not what's theoretically possible. A recursive CTE can approximate exponential smoothing, but whether it should depends on dataset size, refresh requirements, and team familiarity.

For most Tiger Data setups, PostgreSQL is the source of truth and query engine, Python is the model layer for complex forecasting, and results write back to Postgres for serving. That's not a limitation of the database stack; it's a sensible division of responsibilities. Postgres handles high-throughput ingestion, time-range queries, and result serving at scale. Python handles iterative model fitting and the statistical machinery that SQL was never designed for.

Myth 1: "You need Python for any real forecasting." Moving averages, time-weighted averages, and linear regression-based trend extrapolation are all production-viable in SQL for monitoring, alerting, and short-horizon projection. The threshold for "you need Python" is iterative model fitting and parameter optimization, not forecasting in general.

Myth 2: "ARIMA is the standard model." For short-horizon, trend-following use cases, simpler methods often match ARIMA's accuracy with far less complexity. The broader community has moved toward foundation models and tree-based ML for structured business data. ARIMA remains useful when autocorrelation structure is strong, but it stopped being a default years ago.

Time series forecasting methods: what runs in PostgreSQL vs. Python

These methods are implementable entirely in SQL, without a Python process or external model server. All examples assume a table with a timestamp column and a numeric value column. time_bucket() requires TimescaleDB and time_weight() requires the TimescaleDB Toolkit extension (bundled on Tiger Cloud); regr_slope() and regr_intercept() are native PostgreSQL aggregates. 

Simple moving average with time_bucket()

A simple moving average smooths short-term noise by averaging values over a rolling window. The result is a lag-adjusted trend signal, not a point forecast of a specific future value. For dashboards, alerting thresholds, and short-horizon projection on well-behaved series, this is often enough.

time_bucket() groups data into consistent hourly intervals; after averaging to one row per hour, a window over 168 of those hourly rows covers 7 days: 

The limitation matters: standard SQL window functions treat rows equally regardless of timestamp gaps. If data is irregular (missing hours, variable reporting intervals), the window spans the wrong duration and produces incorrect averages. For irregular data, time_weight() is the correct tool.

For a deeper implementation guide including SMA variants and edge case handling, see moving averages in SQL.

Time-weighted averages for irregular timestamps

Real sensor data often arrives with gaps or variable reporting intervals. A row-based window average double-counts dense periods and under-weights sparse ones. time_weight() weights each observation by the duration it represents, so a reading held for 10 minutes contributes proportionally more than one held for 30 seconds.

Use this for IoT sensor data, financial tick data, and any series where data arrival rate is not constant. It's a TimescaleDB Toolkit function with no direct equivalent in standard PostgreSQL.

Linear regression for trend extrapolation

regr_slope() and regr_intercept() are native PostgreSQL aggregate functions that fit a least-squares regression line over a time window. No extension required. The slope and intercept project the trend forward in a single query with no external dependencies.

Computing a regression-based 24-hour-ahead forecast from the past 7 days of hourly data:

This approach assumes a linear trend, so it degrades quickly on seasonal or cyclical data and becomes unreliable beyond short forecast horizons. Use it when the trend is genuinely near-linear over the recent window. For longer horizons or non-linear patterns, the next tier of methods applies.

Exponential smoothing via recursive CTE

Exponential smoothing assigns decreasing weights to older observations, with a smoothing factor alpha (0 < alpha < 1). Higher alpha means more weight on recent data; lower alpha means more weight on history. It's computationally simple and performs well on trend-following series without seasonal components.

Recursive CTEs in PostgreSQL process rows one at a time and slow down on large datasets. For production use at scale, pre-compute via continuous aggregates or move the EMA computation to a Python layer. That's the real boundary: when the recursive CTE becomes a bottleneck, you've crossed into Python territory.

Holt-Winters extends exponential smoothing with trend and seasonality components. A double-exponential version (Holt's linear method) is approximable in SQL using two-pass recursive CTEs, but the triple-exponential seasonal variant requires iterative optimization. At that point, Python's statsmodels is the right tool. The SQL approximation works as a prototype or low-volume implementation; production seasonal forecasting belongs in Python.

Pre-computing trend inputs with continuous aggregates

If your moving average or regression scans the full historical table on every query, performance degrades as data grows. A query that took 100ms against 30 days of data may take seconds against 18 months.

Continuous aggregates solve this by pre-computing and incrementally refreshing rolled-up statistics on a schedule. Forecasting queries hit the materialized view, not the raw hypertable.

Continuous aggregates refresh only changed time buckets, not the full dataset. A 5-minute refresh cycle on hourly buckets keeps your forecasting inputs within 5-10 minutes of current without a full-table scan on every query.

This is the practical answer to the batch vs. real-time tradeoff. Pre-aggregation work is incremental, so near-real-time forecasting inputs are tractable without sacrificing query performance. For implementation detail and refresh policy options, see continuous aggregates in TimescaleDB.

When to reach for Python: ARIMA, Prophet, and beyond

There's no penalty for using Python for forecasting. The point is to choose deliberately. Python is the right layer when the model requires iterative fitting, parameter optimization, or non-linear pattern capture that SQL was not designed to handle.

All methods in this section use PostgreSQL as the data source: connect via psycopg2 or SQLAlchemy, pull pre-aggregated data from the database, fit the model, and write results back to Postgres for serving.

ARIMA and SARIMA with statsmodels

ARIMA (Autoregressive Integrated Moving Average) models the relationship between an observation and its lagged values, applies differencing to achieve stationarity, and models residual error as a moving average. SARIMA adds seasonal terms. The model requires iterative parameter estimation (selecting p, d, q values) that is not tractable in SQL.

A minimal implementation pulling pre-aggregated hourly data from PostgreSQL and fitting an ARIMA model:

The query pulls from the continuous aggregate view, not the raw hypertable. This keeps the data pull fast regardless of how much raw data has accumulated.

ARIMA tends to outperform simpler methods when autocorrelation structure is strong and the series is stationary or made stationary via differencing. For series with many external factors or non-linear patterns, tree-based ML often outperforms ARIMA on structured business data with less tuning overhead.

On stationarity: ARIMA assumes the statistical properties of the series (mean, variance, autocorrelation) do not change over time. Non-stationary series violate this assumption and produce unreliable forecasts. The Augmented Dickey-Fuller (ADF) test in Python's statsmodels.tsa.stattools is the standard check. The "I" component in ARIMA applies differencing to remove trends and achieve stationarity before fitting.

For deeper coverage of AR modeling concepts, see autoregressive time-series modeling.

Prophet for trend changepoints and holiday effects

Prophet (Meta open source) is designed for business time-series data with trend changepoints, seasonality, and known holiday or event effects. It handles missing data and outliers more gracefully than ARIMA, and requires less manual parameter selection. The trade-off is that it needs more history for reliable changepoint detection, typically at least one year.

A minimal Prophet implementation with a PostgreSQL data pull:

import psycopg2

Prophet makes sense for demand series, web traffic, sales, and other business metrics with clear weekly and seasonal patterns and identifiable event effects. For univariate series without human-driven seasonality, ARIMA or a moving average is often more appropriate and faster to run.

Multi-variate and multi-series forecasting at scale

For IoT fleets with thousands of sensor series, or any use case requiring parallelized forecasting across many series, the Python ecosystem has purpose-built libraries. Nixtla's StatsForecast and NeuralForecast, Darts, and sktime all treat forecasting as a batch-parallelizable operation. They handle cross-learning across series, multiple input features, and batch inference at a scale that single-series ARIMA cannot match.

The architectural pattern stays the same: Tiger Data as the ingestion and query layer, Python as the forecasting engine, results written back to a forecasts table in PostgreSQL for downstream serving and alerting. What changes is the scale of the batch export and the parallelism in the Python layer.

For the Python-specific tutorial including data pre-processing and model assessment, see time-series analysis and forecasting with Python.

Foundation models: the emerging option

Time-series foundation models are large models pre-trained on diverse time-series datasets, ranging from hundreds of millions to billions of time points. They can forecast new series zero-shot, without per-series training. The analogy to LLMs for text is reasonable: instead of training on tokens, they train on numerical sequences and generalize to unseen domains. This is emerging technology, not yet production-standard for most teams, but worth understanding for where the field is moving.

Three models have established themselves as benchmarks in this space:

Chronos-2 (Amazon Science): Pre-trained on a large corpus of diverse time-series data producing probabilistic (quantile) forecasts. Extends beyond univariate to multivariate and covariate-informed forecasting in a single model, zero-shot via in-context learning. Available via HuggingFace. Practical for teams with short history per series or many distinct series to forecast.

TimesFM (Google Research): The original TimesFM was trained on roughly 100 billion time points from Google-internal and public sources. The current release, TimesFM 2.5 (200M parameters, 16k context), shows strong long-horizon performance and is available as an open-weights model. 

MOIRAI-2 (Salesforce): Universal time-series forecasting model with strong zero-shot performance across diverse domains including energy, retail, and financial data. Available via HuggingFace.

For all three, the integration path is the same: a Python script pulls data from PostgreSQL, runs inference using the model's API, and returns a forecast. TimescaleDB handles the data pipeline; the foundation model handles the compute.

Worth watching on the in-database side: tspDB (MIT) is a PostgreSQL extension that builds a prediction index on top of a standard table, enabling in-database predictive queries - forecasting, imputation, and confidence bounds - through a prediction index created with create_pindex(). It's experimental and not production-ready as of June 2026, but it represents the research direction toward natively integrated forecasting without a Python layer.

Building a simple forecasting pipeline in PostgreSQL

Here's how the pieces wire together in production. The two-layer architecture below covers most SQL forecasting use cases.

Raw time-series data ingests into a hypertable. A continuous aggregate pre-computes hourly averages on a 5-minute refresh cycle. A retention policy drops raw data older than 90 days while preserving the aggregates. On top of that: a scheduled job (cron, Airflow, or another workflow orchestrator) pulls the pre-aggregated data via SQLAlchemy, fits ARIMA or Prophet, and writes forecast rows back to a forecasts table.

The schema for the forecasts table:

Writing forecasts back to Postgres matters for three reasons. First, downstream dashboards (Grafana, Metabase) query the forecasts table with standard SQL, no specialized tooling required. Second, alerting queries can join actual vs. forecast to detect deviation from expected trajectory. Third, the forecast audit trail stays in the same system as the source data, which simplifies debugging and model versioning.

For context on retention policies and tiered data resolution that feed the pre-aggregation layer, see time-series downsampling. For how to combine forecasting signals with anomaly alerts, see time-series anomaly detection.

Choosing the right method: a decision framework

Choose SQL-native moving averages if:

• You need trend smoothing or rolling baselines for dashboards or alerting

• Your forecast horizon is short (hours to a few days)

• Your data is regular, or you can tolerate a simple row-based average

• Speed and simplicity matter more than model precision

Choose SQL linear regression if:

• The series has a near-linear trend over the recent window

• You're extrapolating 24 to 72 hours ahead

• You want a one-query implementation with no external dependencies

Choose SQL exponential smoothing if:

• Recent observations should outweigh older ones

• There is no seasonal component, or the seasonal period is handled elsewhere

• Dataset is small enough that recursive CTE performance is acceptable

Choose Python (ARIMA / SARIMA) if:

• Autocorrelation structure is strong and you need proper parameter selection

• Seasonality is a factor and a Holt-Winters SQL approximation is not accurate enough

• You need confidence intervals from a statistically rigorous model

Choose Python (Prophet) if:

• Your data has human-driven seasonality (weekly, holiday, business cycle patterns)

• Trend changepoints matter (sales, traffic, demand series with promotion events)

• You have enough history for changepoint detection (typically 1+ year)

Choose Python with foundation models if:

• You have many short series without enough per-series history for ARIMA or Prophet

• You want zero-shot forecasting without per-series training

• You're evaluating state-of-the-art options and can tolerate experimental tooling

In production, hybrid combinations are common: SQL moving averages for real-time dashboards running alongside ARIMA or Prophet in a nightly batch for medium-horizon forecasts. The two layers complement each other without conflict.

Start building

TimescaleDB runs on PostgreSQL. If you're already on Postgres and want to add time-series primitives (hypertables, continuous aggregates, time_weight()) to your forecasting stack, start a Tiger Cloud trial or explore the TimescaleDB docs to get started with self-hosted deployment.