An HVAC engineer needed to build a fan selector. The problem: 18,141 manufacturer performance curves, each with dozens of data points, and a user expecting sub-second search results. The obvious solution - sample the curves into lookup tables and run nearest-neighbour queries - works but feels inelegant. The solution he actually built stores the curves as polynomial coefficients and evaluates them on the fly. It matches a duty point in 4 milliseconds. On a single Postgres read.
Why This Is Hard
Fan performance curves are non-linear. Pressure rises with flow rate up to a peak, then drops off as the fan stalls. Every fan model has a different curve. Some manufacturers publish these as tables - flow vs pressure at 10 CFM intervals. Others publish fitted polynomials. Either way, you need to store thousands of curves and answer queries like: "Give me all fans that can deliver 5000 CFM at 2.5 inches of water pressure."
The naive approach is to sample every curve into a uniform grid, store those points in a table with a spatial index, and run a nearest-neighbour query. This works. It's also wasteful. You're storing hundreds of sampled points per curve when the original polynomial had four coefficients. And if the user's duty point falls between grid points, you're interpolating on top of already-sampled data. Errors compound.
The better approach is to store the polynomials themselves and evaluate them at query time. But now you need Postgres to do maths on every row - 18,000 polynomial evaluations per query. That sounds slow. It isn't.
How Polynomial Indexing Works
Step one: fit every curve to a polynomial. Most fan curves are well-behaved and fit cubic or quartic polynomials with low residuals. You end up with a row per fan containing four to five coefficients. Store these in a NUMERIC[] column. Postgres handles arrays natively and they index well.
Step two: write a function that evaluates polynomials using Horner's method. This is a standard numerical technique that reduces floating-point errors and minimises multiplication operations. Instead of computing a + bx + cx² + dx³ directly, you rewrite it as a + x(b + x(c + dx)). Same result, fewer operations, better precision.
Step three: index the operating range, not the curve itself. You don't need to evaluate every polynomial. You only need to evaluate the ones that could match the duty point. So for each fan, precompute the min and max flow and pressure values, store those in indexed columns, and filter on them first. Postgres narrows the search space from 18,000 to maybe 200 candidates in microseconds.
Step four: evaluate the remaining polynomials and filter by tolerance. The user wants 5000 CFM at 2.5 inches. You define a tolerance - say 5% - and check which curves pass through that window. Postgres does this in a single query. No application-side loops. The database does the work.
The Three Critical Gotchas
The author hit three problems that aren't obvious until you're in production.
Gotcha one: Polynomial fitting is sensitive to scale. If your flow values are in the thousands and your pressure values are in single digits, the coefficients end up wildly different magnitudes. This causes numerical instability. The fix is to normalise your data before fitting - scale both axes to [0, 1], fit the polynomial, then store the scaling factors alongside the coefficients. At query time, you scale the duty point, evaluate the polynomial, and scale the result back.
Gotcha two: Not all curves are polynomials. Some fans have weird discontinuities or multi-modal peaks that don't fit any smooth function. The author's solution: flag these as non-polynomial and fall back to lookup tables for those specific models. Hybrid approach. Most fans use polynomials, the weird ones get special treatment.
Gotcha three: Postgres function inlining. If you write your Horner's method function in PL/pgSQL, Postgres can't inline it into the query plan. Every evaluation becomes a function call, and that adds overhead. The fix is to write it in SQL as an expression, not a stored procedure. Postgres can then inline it and optimise the whole query as one unit. This alone cut query time from 12ms to 4ms.
Why This Matters
This is not a niche HVAC problem. It's a template for any domain where you need to store and query parametric curves: motor torque curves, pump efficiency maps, battery discharge profiles, RF antenna patterns. Anywhere you have continuous functions and need fast lookups.
The lesson is that Postgres is shockingly good at maths if you structure your data correctly. You don't need a vector database or a specialised query engine. You need polynomial coefficients, Horner's method, and a smart index. The rest is just SQL.
The code is open-source and documented. If you're building something similar, start there. The author walked through every mistake so you don't have to.