Beyond semantic search: vectors over time
pgvector is best known as a tool for semantic search, and most write-ups about it sit in the AI space. But a vector is just a vector. The same extension can be pointed at timeseries data, and the payoff is a small amount of SQL rather than a model pipeline. The example below works through historic DAX data going back to 1987 and ending in March 2025.
Loading and inspecting the series
The table is straight historical price data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
cybertec=# SELECT * FROM stock_data ORDER BY d DESC; d | symbol | open | high | low | close | volume ------------+--------+--------------------+--------------------+--------------------+--------------------+----------- 2025-03-20 | ^GDAXI | 23009.5 | 23315.490234375 | 22842.94921875 | 23295.720703125 | 0 2025-03-19 | ^GDAXI | 23288.060546875 | 23372.080078125 | 23136.5390625 | 23272.150390625 | 79641400 2025-03-18 | ^GDAXI | 23380.69921875 | 23476.009765625 | 23240.560546875 | 23266.650390625 | 80663300 2025-03-17 | ^GDAXI | 23154.5703125 | 23154.5703125 | 22933.5703125 | 22998.529296875 | 67152000 2025-03-14 | ^GDAXI | 22986.8203125 | 23049.48046875 | 22465.119140625 | 22501.33984375 | 93287400 2025-03-13 | ^GDAXI | 22567.140625 | 22752.830078125 | 22417.51953125 | 22578.099609375 | 78955600 2025-03-12 | ^GDAXI | 22676.41015625 | 22813.83984375 | 22461.76953125 | 22525.740234375 | 80929100 2025-03-11 | ^GDAXI | 22328.76953125 | 22835.099609375 | 22258.30078125 | 22680.390625 | 97374800 2025-03-10 | ^GDAXI | 22620.94921875 | 23164.240234375 | 22519.2109375 | 23163.779296875 | 108707000 ... |
A quick row count by decade gives a feel for coverage. PostgreSQL can group by an expression, not only by columns, which the query below uses:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cybertec=# SELECT date_trunc('decade', d) AS year, count(*) FROM stock_data GROUP BY 1 ORDER BY 1; year | count ------------------------+------- 1980-01-01 00:00:00+01 | 502 1990-01-01 00:00:00+01 | 2506 2000-01-01 00:00:00+01 | 2542 2010-01-01 00:00:00+01 | 2531 2020-01-01 00:00:00+01 | 1331 (5 rows) |
date_trunc truncates years, months, days and so on, leaving the decade to count over.
Turning a series into a vector
The first move is to look at day-over-day change. A windowing function computes the difference between a row and its predecessor — the current row divided by the previous row, emitted as an extra column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cybertec=# SELECT d, close, close / lag(close) OVER (ORDER BY d) AS diff FROM stock_data; d | close | diff ------------+--------------------+------------------------ 1987-12-30 | 1005.1900024414062 | 1988-01-04 | 956.489990234375 | 0.95155143595862615489 1988-01-05 | 996.0999755859375 | 1.0414118137732488 1988-01-06 | 1006.010009765625 | 1.0099488348786055 1988-01-07 | 1014.469970703125 | 1.0084094202397359 1988-01-08 | 1026.68994140625 | 1.0120456702081141 1988-01-11 | 987.52001953125 | 0.96184834359889682645 ... |
Single-day deltas are a start, but meaningful events usually span more than one day. The next step is to collect the changes across a six-day window into one array, ready for conversion to a vector. array_agg turns values into an array holding the current row plus the five preceding it. pgvector can cast an array to a vector directly, with one constraint:
|
1 2 3 4 5 6 7 8 9 10 11 |
cybertec=# SELECT *, (array_agg(diff) OVER (ORDER BY d ROWS BETWEEN 5 PRECEDING AND CURRENT ROW))::vector AS vec FROM ( SELECT d, close, close / lag(close) OVER (ORDER BY d) AS diff FROM stock_data ) AS x ORDER BY d OFFSET 6; ERROR: array must not contain nulls |
Every value in the vector must be valid. A NULL entry makes the cast fail with the error shown above, so the cast has to be deferred until the array is guaranteed clean:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
cybertec=# CREATE VIEW v_analysis AS SELECT d, round(close::numeric, 2) AS close, round(diff::numeric, 8) AS diff, vec::vector FROM ( SELECT *, array_agg(diff) OVER (ORDER BY d ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS vec FROM ( SELECT d, close, close / lag(close) OVER (ORDER BY d) AS diff FROM stock_data ) AS x ORDER BY d OFFSET 6 ) AS y; CREATE VIEW |
Since later work needs these vectors repeatedly, wrapping the logic in a view keeps the queries on top of it short. The resulting rows look like this — essentially lists of floating point values:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
cybertec=# SELECT * FROM v_analysis ORDER BY d LIMIT 10; d | close | diff | vec ------------+---------+------------+----------------------------------------------------------------- 1988-01-11 | 987.52 | 0.96184834 | [0.95155144,1.0414118,1.0099488,1.0084094,1.0120456,0.9618483] 1988-01-12 | 986.89 | 0.99936203 | [1.0414118,1.0099488,1.0084094,1.0120456,0.9618483,0.99936205] 1988-01-13 | 965.77 | 0.97859944 | [1.0099488,1.0084094,1.0120456,0.9618483,0.99936205,0.9785994] 1988-01-14 | 974.46 | 1.00899800 | [1.0084094,1.0120456,0.9618483,0.99936205,0.9785994,1.008998] 1988-01-15 | 952.46 | 0.97742339 | [1.0120456,0.9618483,0.99936205,0.9785994,1.008998,0.97742337] 1988-01-18 | 1003.13 | 1.05319907 | [0.9618483,0.99936205,0.9785994,1.008998,0.97742337,1.053199] 1988-01-19 | 980.18 | 0.97712160 | [0.99936205,0.9785994,1.008998,0.97742337,1.053199,0.9771216] 1988-01-20 | 960.44 | 0.97986085 | [0.9785994,1.008998,0.97742337,1.053199,0.9771216,0.97986084] 1988-01-21 | 949.24 | 0.98833867 | [1.008998,0.97742337,1.053199,0.9771216,0.97986084,0.98833865] 1988-01-22 | 966.48 | 1.01816189 | [0.97742337,1.053199,0.9771216,0.97986084,0.98833865,1.0181619] (10 rows) |
Vectors even have an average. There is an "avg" function that operates on them:
|
1 2 3 4 5 |
cybertec=# SELECT avg(vec) FROM v_analysis; avg --------------------------------------------------------------- [1.0004238,1.0004286,1.0004265,1.0004267,1.0004258,1.0004246] (1 row) |
The result is the average daily change in the market: all values slightly positive but near zero. Given decades of generally rising markets, that is exactly what one would expect the vector to say.
Anomaly detection without a model
Anomalies in timeseries are often hunted with elaborate AI models, but that is not always required. Taking the average vector and asking which other vectors differ from it as much as possible is enough:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
cybertec=# SELECT * FROM v_analysis ORDER BY vec <=> (SELECT avg(vec) FROM v_analysis) DESC; d | close | diff | vec ------------+----------+------------+--------------------------------------------------------------------- 2008-10-16 | 4675.90 | 0.90139738 | [0.96520793,0.90892595,1.0103363,1.1062297,1.010175,0.9013974] 2008-10-17 | 4757.98 | 1.01755386 | [0.90892595,1.0103363,1.1062297,1.010175,0.9013974,1.0175538] 2008-10-14 | 5135.14 | 1.10622964 | [0.97222835,0.9608706,0.96520793,0.90892595,1.0103363,1.1062297] 2020-03-25 | 9987.37 | 1.08064334 | [0.942259,0.98636895,1.0688034,0.9400737,1.0826737,1.0806433] 2008-10-15 | 5187.39 | 1.01017499 | [0.9608706,0.96520793,0.90892595,1.0103363,1.1062297,1.010175] 1989-10-20 | 1520.01 | 0.99850224 | [0.99229485,0.86856604,1.0658485,1.0164639,1.013853,0.99850225] 1989-10-19 | 1522.29 | 1.01385294 | [0.9984663,0.99229485,0.86856604,1.0658485,1.0164639,1.013853] 1989-10-23 | 1518.24 | 0.99883552 | [0.86856604,1.0658485,1.0164639,1.013853,0.99850225,0.9988355] 1989-10-18 | 1501.49 | 1.01646388 | [0.97981346,0.9984663,0.99229485,0.86856604,1.0658485,1.0164639] 1989-10-17 | 1477.17 | 1.06584844 | [1.0045961,0.97981346,0.9984663,0.99229485,0.86856604,1.0658485] 2008-10-21 | 4882.80 | 0.99816836 | [1.1062297,1.010175,0.9013974,1.0175538,1.028117,0.99816835] |
The statement measures distance from the average, so the rows returned are the points furthest from it. The dates that come back are notable. October 2008 and October 1989 correspond to:
"The Friday the 13th mini-crash, or Black Friday, was a stock market crash that occurred on Friday, October 13, 1989. The crash was apparently caused by a reaction to a news story of the breakdown of a $6.75 billion leveraged buyout deal for UAL Corporation, the parent company of United Airlines."
"October 6–10, 2008: From October 6–10, 2008, the Dow Jones Industrial Average (DJIA) closed lower in all five sessions. Volume levels were record-breaking. The DJIA fell 1,874.19 points, or 18.2%, in its worst weekly decline ever on both a points and percentage basis. The S&P 500 fell more than 20%."
Two major financial events, surfaced by a window function, an array and basic vector arithmetic.
Takeaway
pgvector is the obvious choice for AI and semantic search work. It is also useful well outside that territory — simple vector operations over everyday data can already carry real weight.
