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