tempestas

commit cee7b3fef2eedf5a58a519bf168b67b490a377d0
parent 7992b1bb522b87716b89656ff470bc00d33fa2fc
Author: Chris Bracken <chris@bracken.jp>
Date:   Mon, 11 Apr 2022 16:32:45 -0700

Add indexes on sensor_data table

This should significantly speed up querying for sensor data.

Diffstat:
M
setup_db.sql
 | 
25
++++++++++++++++---------

1 file changed, 16 insertions(+), 9 deletions(-)
diff --git a/setup_db.sql b/setup_db.sql
@@ -1,7 +1,13 @@
+-- Create tempestas user and database.
 CREATE ROLE tempestas WITH LOGIN PASSWORD '<password>';
-
 CREATE DATABASE tempestas WITH OWNER tempestas TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_CA.UTF-8' LC_CTYPE 'en_CA.UTF-8';
 
+-- Create tables.
+CREATE TABLE sensors (
+  sensor_id VARCHAR(63) PRIMARY KEY,
+  name VARCHAR(20) NOT NULL
+);
+
 CREATE TABLE sensor_data (
   sensor_id VARCHAR(63) NOT NULL,
   sw_version VARCHAR(63) NOT NULL,
@@ -10,15 +16,12 @@ CREATE TABLE sensor_data (
   reading_value NUMERIC(20,6) NOT NULL
 );
 
-GRANT SELECT, INSERT ON sensor_data TO tempestas;
-
-CREATE TABLE sensors (
-  sensor_id VARCHAR(63) PRIMARY KEY,
-  name VARCHAR(20) NOT NULL
-);
-
-GRANT SELECT, INSERT ON sensors TO tempestas;
+-- Create indexes.
+CREATE INDEX id_idx ON sensor_data (sensor_id);
+CREATE INDEX type_idx ON sensor_data (reading_type);
+CREATE INDEX time_id ON sensor_data (reading_time);
 
+-- Create stored procedures.
 CREATE OR REPLACE PROCEDURE save_reading(
   new_sensor_id VARCHAR(63),
   new_sw_version VARCHAR(63),
@@ -36,3 +39,7 @@ BEGIN
 
   COMMIT;
 END$$;
+
+-- Grant permissions.
+GRANT SELECT, INSERT ON sensor_data TO tempestas;
+GRANT SELECT, INSERT ON sensors TO tempestas;