WM4QyR61

1-- metric
2DROP TABLE IF EXISTS public.metric CASCADE;
3CREATE TABLE public.metric
4(
5    id bigserial NOT NULL,
6    node_id bigint NOT NULL,
7    name text NOT NULL,
8    tick_rate_seconds int NOT NULL,
9    created timestamp(0) without time zone NOT NULL,
10    updated timestamp(0) without time zone NOT NULL,
11    CONSTRAINT metric_pkey PRIMARY KEY (id),
12    CONSTRAINT metric_node_fkey FOREIGN KEY (node_id) REFERENCES public.node (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
13);
14CREATE INDEX metric_node_id_idx ON public.metric USING btree (node_id);
15
16-- metric_ttl
17DROP TABLE IF EXISTS public.metric_ttl CASCADE;
18CREATE TABLE public.metric_ttl
19(
20    id serial NOT NULL,
21    duration_seconds bigint NOT NULL,
22    CONSTRAINT metric_ttl_pkey PRIMARY KEY (id)
23);
24
25--metric_data
26DROP TABLE IF EXISTS public.metric_data CASCADE;
27CREATE TABLE public.metric_data
28(
29    metric_id bigint NOT NULL,
30    time_frame int NOT NULL,
31    ts bigint NOT NULL,
32    ttl_id int NOT NULL,
33    o real NOT NULL,
34    c real NOT NULL,
35    h real NOT NULL,
36    l real NOT NULL,
37    CONSTRAINT metric_data_pkey PRIMARY KEY (metric_id,time_frame,ts),
38    CONSTRAINT metric_data_metric_fkey FOREIGN KEY (metric_id) REFERENCES public.metric (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
39    CONSTRAINT metric_data_ttl_fkey FOREIGN KEY (ttl_id) REFERENCES public.metric_ttl (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
40);
41CREATE INDEX metric_data_metric_id_idx ON public.metric_data USING btree (metric_id);
42CREATE INDEX metric_data_ttl_id_idx ON public.metric_data USING btree (ttl_id);
43
44ALTER TABLE public.metric_data ALTER COLUMN metric_id   SET STORAGE PLAIN;
45ALTER TABLE public.metric_data ALTER COLUMN time_frame  SET STORAGE PLAIN;
46ALTER TABLE public.metric_data ALTER COLUMN ts          SET STORAGE PLAIN;
47ALTER TABLE public.metric_data ALTER COLUMN ttl_id      SET STORAGE PLAIN;
48ALTER TABLE public.metric_data ALTER COLUMN o           SET STORAGE PLAIN;
49ALTER TABLE public.metric_data ALTER COLUMN c           SET STORAGE PLAIN;
50ALTER TABLE public.metric_data ALTER COLUMN h           SET STORAGE PLAIN;
51ALTER TABLE public.metric_data ALTER COLUMN l           SET STORAGE PLAIN;
52ALTER TABLE public.metric_data DISABLE ROW LEVEL SECURITY;
53ALTER TABLE public.metric_data SET WITHOUT OIDS;