1-----------------------
2-- Table definitions --
3-----------------------
4
5-- a special type we use in our tables so must be created here
6CREATE DOMAIN SCHEMA_PROM.label_array AS int[] NOT NULL;
7
8-- special type to store only values of labels
9CREATE DOMAIN SCHEMA_PROM.label_value_array AS TEXT[];
10
11CREATE TABLE public.prom_installation_info (
12    key TEXT PRIMARY KEY,
13    value TEXT
14);
15GRANT SELECT ON TABLE public.prom_installation_info TO PUBLIC;
16--all modifications can only be done by owner
17
18INSERT INTO public.prom_installation_info(key, value) VALUES
19    ('catalog schema',        'SCHEMA_CATALOG'),
20    ('prometheus API schema', 'SCHEMA_PROM'),
21    ('extension schema',      'SCHEMA_EXT'),
22    ('series schema',         'SCHEMA_SERIES'),
23    ('metric schema',         'SCHEMA_METRIC'),
24    ('data schema',           'SCHEMA_DATA'),
25    ('exemplar data schema',  'SCHEMA_DATA_EXEMPLAR'),
26    ('information schema',    'SCHEMA_INFO');
27
28
29CREATE TABLE SCHEMA_CATALOG.series (
30    id bigint NOT NULL,
31    metric_id int NOT NULL,
32    labels SCHEMA_PROM.label_array NOT NULL, --labels are globally unique because of how partitions are defined
33    delete_epoch bigint NULL DEFAULT NULL -- epoch after which this row can be deleted
34) PARTITION BY LIST(metric_id);
35GRANT SELECT ON TABLE SCHEMA_CATALOG.series TO prom_reader;
36GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.series TO prom_writer;
37
38
39CREATE INDEX series_labels_id ON SCHEMA_CATALOG.series USING GIN (labels);
40CREATE INDEX series_deleted
41    ON SCHEMA_CATALOG.series(delete_epoch, id)
42    WHERE delete_epoch IS NOT NULL;
43
44CREATE SEQUENCE SCHEMA_CATALOG.series_id;
45GRANT USAGE ON SEQUENCE SCHEMA_CATALOG.series_id TO prom_writer;
46
47
48CREATE TABLE SCHEMA_CATALOG.label (
49    id serial CHECK (id > 0),
50    key TEXT,
51    value text,
52    PRIMARY KEY (id) INCLUDE (key, value),
53    UNIQUE (key, value) INCLUDE (id)
54);
55GRANT SELECT ON TABLE SCHEMA_CATALOG.label TO prom_reader;
56GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.label TO prom_writer;
57GRANT USAGE ON SEQUENCE SCHEMA_CATALOG.label_id_seq TO prom_writer;
58
59CREATE TABLE SCHEMA_CATALOG.ids_epoch(
60    current_epoch BIGINT NOT NULL,
61    last_update_time TIMESTAMPTZ NOT NULL,
62    -- force there to only be a single row
63    is_unique BOOLEAN NOT NULL DEFAULT true CHECK (is_unique = true),
64    UNIQUE (is_unique)
65);
66GRANT SELECT ON TABLE SCHEMA_CATALOG.ids_epoch TO prom_reader;
67GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.ids_epoch TO prom_writer;
68
69-- uses an arbitrary start time so pristine and migrated DBs have the same values
70INSERT INTO SCHEMA_CATALOG.ids_epoch VALUES (0, '1970-01-01 00:00:00 UTC', true);
71
72--This table creates a unique mapping
73--between label keys and their column names across metrics.
74--This is done for usability of column name, especially for
75-- long keys that get cut off.
76CREATE TABLE SCHEMA_CATALOG.label_key(
77    id SERIAL,
78    key TEXT,
79    value_column_name NAME,
80    id_column_name NAME,
81    PRIMARY KEY (id),
82    UNIQUE(key)
83);
84GRANT SELECT ON TABLE SCHEMA_CATALOG.label_key TO prom_reader;
85GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.label_key TO prom_writer;
86GRANT USAGE ON SEQUENCE SCHEMA_CATALOG.label_key_id_seq TO prom_writer;
87
88CREATE TABLE SCHEMA_CATALOG.label_key_position (
89    metric_name text, --references metric.metric_name NOT metric.id for performance reasons
90    key TEXT, --NOT label_key.id for performance reasons.
91    pos int,
92    UNIQUE (metric_name, key) INCLUDE (pos)
93);
94GRANT SELECT ON TABLE SCHEMA_CATALOG.label_key_position TO prom_reader;
95GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.label_key_position TO prom_writer;
96
97CREATE TABLE SCHEMA_CATALOG.metric (
98    id SERIAL PRIMARY KEY,
99    metric_name text NOT NULL,
100    table_name name NOT NULL,
101    creation_completed BOOLEAN NOT NULL DEFAULT false,
102    default_chunk_interval BOOLEAN NOT NULL DEFAULT true,
103    retention_period INTERVAL DEFAULT NULL, --NULL to use the default retention_period
104    default_compression BOOLEAN NOT NULL DEFAULT true,
105    delay_compression_until TIMESTAMPTZ DEFAULT NULL,
106    table_schema name NOT NULL DEFAULT 'SCHEMA_DATA',
107    series_table name NOT NULL, -- series_table specifies the name of table where the series data is stored.
108    is_view BOOLEAN NOT NULL DEFAULT false,
109    UNIQUE (metric_name, table_schema) INCLUDE (table_name),
110    UNIQUE(table_schema, table_name)
111);
112GRANT SELECT ON TABLE SCHEMA_CATALOG.metric TO prom_reader;
113GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.metric TO prom_writer;
114GRANT USAGE ON SEQUENCE SCHEMA_CATALOG.metric_id_seq TO prom_writer;
115
116CREATE TABLE SCHEMA_CATALOG.default (
117    key TEXT PRIMARY KEY,
118    value TEXT
119);
120GRANT SELECT ON TABLE SCHEMA_CATALOG.default TO prom_reader;
121GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SCHEMA_CATALOG.default TO prom_admin;
122
123INSERT INTO SCHEMA_CATALOG.default(key,value) VALUES
124('chunk_interval', (INTERVAL '8 hours')::text),
125('retention_period', (90 * INTERVAL '1 day')::text),
126('metric_compression', (exists(select * from pg_proc where proname = 'compress_chunk')::text));
127