pg-fft

A PostgreSQL extension that computes fast Fourier transforms directly in SQL, using Mark Borgerding's KISS FFT. It provides fft_agg(real), an aggregate that collects a series of samples and returns their power spectrum.

-- A 1 kHz signal containing a 50 Hz and a 120 Hz sine
CREATE TABLE samples AS
SELECT i AS t,
       (0.7 * sin(2 * pi() * 50 * i / 1000.0)
          + sin(2 * pi() * 120 * i / 1000.0))::real AS v
  FROM generate_series(0, 999) i;

-- Which frequencies dominate?
SELECT bin - 1 AS freq_hz, round(power::numeric, 1) AS power
  FROM (SELECT fft_agg(v ORDER BY t) AS spectrum FROM samples) s,
       unnest(spectrum) WITH ORDINALITY AS u(power, bin)
 WHERE power > 10 AND bin - 1 < 500
 ORDER BY freq_hz;

 freq_hz | power
---------+-------
      50 | 122.5
     120 | 250.0
(2 rows)

Installation

Requires PostgreSQL 13 or later and the server development headers (postgresql-server-dev-* on Debian/Ubuntu, postgresql*-devel on Red Hat, included in the Homebrew postgresql@* formulae on macOS).

make
make install       # may need sudo
make installcheck  # optional; needs a running server

If pg_config is not on your PATH, point the build at it:

make PG_CONFIG=/path/to/pg_config install

Then, in your database:

CREATE EXTENSION kissfft;

Usage

fft_agg(sample real) → real[]

Aggregates n input samples and returns an n-element power spectrum: element k+1 (arrays are 1-based) holds |X[k]|² / n, where X is the discrete Fourier transform of the inputs.

Things to know:

• Always pass an explicit ORDER BY inside the aggregate call: fft_agg(v ORDER BY t). A DFT is defined over an ordered sequence; without ORDER BY the row order — and therefore the result — is nondeterministic.
• Bin → frequency: bin k (0-based) corresponds to k * rate / n Hz, where rate is your sampling rate and n the number of samples. With 1000 samples at 1 kHz, bin index = frequency in Hz, which is what makes the example above read nicely.
• The spectrum is mirrored. Input samples are real, so the second half of the output mirrors the first (power[k] = power[n-k]); only bins 0 .. n/2 carry information.
• n is not required to be a power of two (KISS FFT handles mixed radix), but sizes with only small prime factors (2, 3, 5) are fastest.
• NULL samples raise an error. Aggregating zero rows returns {}.

Performance

The FFT itself is rarely the bottleneck: on an M-series MacBook, a 1,048,576-point transform costs about 20 ms, while accumulating and sorting those rows into the transition array costs about 100 ms. If you need to transform the same series repeatedly, store it as a real[] column instead of re-aggregating rows.

Development

Regression tests live in test/sql with expected output in test/expected; make installcheck runs them against the server pg_config points at. CI builds and tests against PostgreSQL 13–18 on every push.

History and license

This is a revival of Peter Meszaros's 2012 pgfft extension, updated for modern PostgreSQL (the original predated 64-bit Datum on most platforms and the current extension packaging rules). The bundled KISS FFT is BSD-3-Clause licensed; see COPYING.kissfft.