Implementing a Kalman Filter in Postgres (neon.com)

Traconiq open-sourced a full Kalman Filter implementation inside Postgres (github.com/traconiq/kalman-filter-neon), letting you smooth noisy GPS streams where the data lives — no external pipeline required. This matters because Kalman filtering is the standard for turning jittery, outlier-prone location traces into reliable trajectories, and embedding it in SQL makes smoothed positions immediately available for analytics, visualization, and downstream ML without ETL overhead. The project addresses SQL’s lack of persistent row-by-row state by storing per-device state (estimate + covariance) in kalman.devices and raw/filtered points in kalman.positions. The core is kalman.kalman_step (prediction + update using a motion model and covariance matrices); kalman.kalman_upsert_position wraps insert+filter for online updates. Offline modes include recursive CTEs (transparent but slow) and custom aggregates (efficient at scale). Benchmarks show online filtering is feasible but costly: insert latency rose from 8.543 ms to 13.560 ms and throughput fell ~35–40% (117 → 74 TPS). For batch smoothing, custom aggregates outperformed recursive queries (TPS ~4,419 vs ~3,443). Bottom line: use online upserts when immediate smoothed data is required; use custom-aggregate offline processing for production-scale backfills.