An experiment storing timeseries in postgres arrays

postgres-timeseries-arrays.sql

-- Note: There is a similar project: https://github.com/tgres/tgres
--       I haven't looked into it much.
--       This is nice and readable: http://grisha.org/blog/2015/09/23/storing-time-series-in-postgresql-efficiently/

CREATE TABLE ts ( tsid SERIAL PRIMARY KEY, name text, current INTEGER, value DOUBLE PRECISION, data DOUBLE PRECISION[], UNIQUE(name));


-- Initialise a timeseries:
CREATE OR REPLACE FUNCTION pllua_init(len INTEGER, next INTEGER, value DOUBLE PRECISION) RETURNS DOUBLE PRECISION[] AS $$
	local ans = {}; local i; for i=1,len,1 do ans[i] = 0 end
	ans[((next-1)%#ans)+1] = value
	return ans
$$ LANGUAGE pllua;

-- Append a value to a timeseries:
CREATE OR REPLACE FUNCTION pllua_inc(ray DOUBLE PRECISION[], current INTEGER, next INTEGER, value DOUBLE PRECISION) RETURNS DOUBLE PRECISION[] AS $$
	while(current < next) do current=current+1; ray[((current-1)%#ray)+1]=0; end ; ray[((next-1)%#ray)+1] = ray[((next-1)%#ray)+1] + value ; return ray
$$ LANGUAGE pllua;

-- Get a range of values:
CREATE OR REPLACE FUNCTION pllua_get(ray DOUBLE PRECISION[], current INTEGER, first INTEGER, last INTEGER) RETURNS DOUBLE PRECISION[] AS $$
	local ans = {}; local i
	local oldest = current - #ray + 1
	local min = (oldest<first) and first or oldest
	local max = (current<last) and current or last
	for i=first,(min-1),1 do ans[i-first] = nil end
	for i=min,max,1 do ans[i-first] = ray[((i-1)%#ray)+1] end
	for i=max+1,last,1 do ans[i-first] = 0 end
	return ans
$$ LANGUAGE pllua;

-- Get a range of values relative to the latest entry:
CREATE OR REPLACE FUNCTION pllua_get_relative(ray DOUBLE PRECISION[], current INTEGER, first INTEGER, last INTEGER) RETURNS DOUBLE PRECISION[] AS $$
	local ans = {}; local i
	local oldest = 1 - #ray
	local min = (oldest<first) and first or oldest
	local max = (0<last) and 0 or last
	for i=first,(min-1),1 do ans[i-first] = nil end
	for i=min,max,1 do ans[i-first] = ray[((i-1)%#ray)+1] end
	for i=max+1,last,1 do ans[i-first] = 0 end
	return ans
$$ LANGUAGE pllua;

INSERT INTO ts(name, current, value, data) VALUES ('pink', 10, 17, pllua_init(20, 10, 17));
INSERT INTO ts(name, current, value, data) VALUES ('brown', 10, 17, pllua_init(20, 10, 17));

UPDATE ts set data = pllua_inc(data, current, 11,15), current=greatest(current,11) where name='pink';
UPDATE ts set data = pllua_inc(data, current, 11,1) , current=greatest(current,11) where name='pink';
UPDATE ts set data = pllua_inc(data, current, 12,14), current=greatest(current,12) where name='pink';



Sum arrays:
select array_agg(x) from (select sum(x) as x from ts, unnest(data) with ordinality as dps(x,i) group by i order by i)dpd;

With slicing:
select array_agg(x) from (select sum(x) as x from ts, unnest(pllua_get(data,8,5,8)) with ordinality as dps(x,i) group by i order by i)dpd;