Using Flow database structure
List of relations
Schema Name Type Owner Size Description
public collection table lumen 8192 bytes
public collection_entity table lumen 8192 bytes
public dashboard table lumen 8192 bytes
public dashboard_visualisation table lumen 8192 bytes
public data_source table lumen 8192 bytes
public dataset table lumen 8192 bytes
public dataset_version table lumen 8192 bytes
public geography_columns view postgres 0 bytes
public geometry_columns view postgres 0 bytes
public invite table lumen 8192 bytes
public job_execution table lumen 8192 bytes
public ragtime_migrations table lumen 8192 bytes
public raster_columns view postgres 0 bytes
public raster_dataset table lumen 8192 bytes
public raster_overviews view postgres 0 bytes
public share table lumen 8192 bytes
public spatial_ref_sys table postgres 4616 kB
public visualisation table lumen 8192 bytes
Replicating Flow DB structure
Sample JSON from Flow API
{
"id" : "562899168" ,
"name" : "My RQG Survey" ,
"registrationFormId" : "" ,
"forms" : [
{
"id" : "562919156" ,
"name" : "Form 1" ,
"questionGroups" : [
{
"id" : "572849152" ,
"name" : "General" ,
"repeatable" : true ,
"questions" : [
{
"id" : "562909151" ,
"name" : "Name" ,
"type" : "FREE_TEXT" ,
"order" : 1 ,
"variableName" : "name" ,
"createdAt" : "2020-05-04T05:24:00.933Z" ,
"modifiedAt" : "2020-05-04T05:24:17.613Z"
} ,
{
"id" : "562879159" ,
"name" : "Age" ,
"type" : "NUMBER" ,
"order" : 2 ,
"variableName" : "age" ,
"createdAt" : "2020-05-04T05:24:21.066Z" ,
"modifiedAt" : "2020-05-04T05:24:38.712Z"
} ,
{
"id" : "594369156" ,
"name" : "Literacy" ,
"type" : "OPTION" ,
"order" : 3 ,
"variableName" : "literacy" ,
"createdAt" : "2020-05-04T05:24:42.025Z" ,
"modifiedAt" : "2020-05-04T05:25:22.813Z"
}
] ,
"createdAt" : "2020-05-04T05:23:37.232Z" ,
"modifiedAt" : "2020-05-04T05:23:59.219Z"
}
] ,
"createdAt" : "2020-05-04T05:23:24.915Z" ,
"modifiedAt" : "2020-05-04T05:25:33.321Z" ,
"formInstancesUrl" : "https://api-auth0.akvotest.org/flow/orgs/uat1/form_instances?survey_id=562899168&form_id=562919156"
}
] ,
"createdAt" : "2020-05-04T05:23:08.705Z" ,
"modifiedAt" : "2020-05-04T05:23:22.939Z" ,
"dataPointsUrl" : "https://api-auth0.akvotest.org/flow/orgs/uat1/data_points?survey_id=562899168"
}
jq -M ' {id, name, registrationFormId, createdAt, modifiedAt}' survey.json
CREATE TABLE survey (
id text PRIMARY KEY ,
name text NOT NULL ,
registration_form_id text ,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO survey(id, name, created_at, modified_at)
VALUES (' 562899168' , ' My RQG Survey' , ' 2020-05-04T05:23:08.705Z' , ' 2020-05-04T05:23:22.939Z' )
SELECT * FROM survey ORDER BY id
id name registration_form_id created_at modified_at
562899168 My RQG Survey 2020-05-04 05:23:08.705+00 2020-05-04 05:23:22.939+00
jq -M ' .forms[] | keys' survey.json
jq -M ' .forms[0] | {id, name, createdAt, modifiedAt}' survey.json
CREATE TABLE form (
id text PRIMARY KEY ,
survey_id text NOT NULL REFERENCES survey(id),
name text NOT NULL ,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
Table “public.form”
Column Type Collation Nullable Default
id text not null
survey_id text not null
name text not null
created_at timestamp with time zone not null now()
modified_at timestamp with time zone not null now()
Indexes:
“form_pkey” PRIMARY KEY, btree (id)
Foreign-key constraints:
“form_survey_id_fkey” FOREIGN KEY (survey_id) REFERENCES survey(id)
INSERT INTO form(id, survey_id, name, created_at, modified_at)
VALUES (' 562919156' , ' 562899168' , ' Form 1' , ' 2020-05-04T05:23:24.915Z' , ' 2020-05-04T05:25:33.321Z' );
SELECT * FROM form ORDER BY id
id survey_id name created_at modified_at
562919156 562899168 Form 1 2020-05-04 05:23:24.915+00 2020-05-04 05:25:33.321+00
jq -M ' .forms[0].questionGroups[] | keys' survey.json
CREATE TABLE question_group (
id text PRIMARY KEY ,
form_id text NOT NULL REFERENCES form(id),
name text NOT NULL ,
repeatable boolean NOT NULL DEFAULT FALSE,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
Table “public.question_group”
Column Type Collation Nullable Default
id text not null
form_id text not null
name text not null
repeatable boolean not null false
created_at timestamp with time zone not null now()
modified_at timestamp with time zone not null now()
Indexes:
“question_group_pkey” PRIMARY KEY, btree (id)
Foreign-key constraints:
“question_group_form_id_fkey” FOREIGN KEY (form_id) REFERENCES form(id)
jq -M ' .forms[0].questionGroups[0] | {id, name, repeatable, createdAt, modifiedAt}' survey.json
INSERT INTO question_group(id, form_id, name, repeatable, created_at, modified_at)
VALUES (' 572849152' , ' 562919156' , ' General' , true, ' 2020-05-04T05:23:37.232Z' , ' 2020-05-04T05:23:59.219Z' )
SELECT * FROM question_group ORDER BY id
id form_id name repeatable created_at modified_at
572849152 562919156 General t 2020-05-04 05:23:37.232+00 2020-05-04 05:23:59.219+00
jq -M ' .forms[0].questionGroups[0].questions[0] | keys' survey.json
jq -M ' .forms[0].questionGroups[0].questions[] | .type' survey.json
-- There are many more
CREATE TYPE question_type AS ENUM(' FREE_TEXT' , ' NUMBER' , ' OPTION' );
CREATE TABLE question (
id text PRIMARY KEY ,
question_group_id text NOT NULL REFERENCES question_group(id),
name text NOT NULL ,
" order" smallint NOT NULL DEFAULT 0 ,
type question_type NOT NULL ,
variable_name text ,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
Table “public.question”
Column Type Collation Nullable Default
id text not null
question_group_id text not null
name text not null
order smallint not null 0
type question_type not null
variable_name text
created_at timestamp with time zone not null now()
modified_at timestamp with time zone not null now()
Indexes:
“question_pkey” PRIMARY KEY, btree (id)
Foreign-key constraints:
“question_question_group_id_fkey” FOREIGN KEY (question_group_id) REFERENCES question_group(id)
jq -M ' .forms[0].questionGroups[0].questions[]' survey.json
INSERT INTO question (id, question_group_id, name, " order" , type, variable_name, created_at, modified_at)
VALUES (' 562909151' , ' 572849152' , ' Name' , 1 , ' FREE_TEXT' , ' name' , ' 2020-05-04T05:24:00.933Z' , ' 2020-05-04T05:24:17.613Z' ),
(' 562879159' , ' 572849152' , ' Age' , 2 , ' NUMBER' , ' age' , ' 2020-05-04T05:24:21.066Z' , ' 2020-05-04T05:24:38.712Z' ),
(' 594369156' , ' 572849152' , ' Literacy' , 3 , ' OPTION' , ' literacy' , ' 2020-05-04T05:24:42.025Z' , ' 2020-05-04T05:25:22.813Z' );
SELECT * FROM question ORDER BY id
id question_group_id name order type variable_name created_at modified_at
562879159 572849152 Age 2 NUMBER age 2020-05-04 05:24:21.066+00 2020-05-04 05:24:38.712+00
562909151 572849152 Name 1 FREE_TEXT name 2020-05-04 05:24:00.933+00 2020-05-04 05:24:17.613+00
594369156 572849152 Literacy 3 OPTION literacy 2020-05-04 05:24:42.025+00 2020-05-04 05:25:22.813+00
{
"dataPoints" : [
{
"id" : "572859149" ,
"identifier" : "k5x6-rus4-s1k3" ,
"displayName" : "Juan" ,
"latitude" : null ,
"longitude" : null ,
"createdAt" : "2020-05-04T05:31:23.668Z" ,
"modifiedAt" : "2020-05-04T05:31:28.470Z"
}
] ,
"nextPageUrl" : "https://api-auth0.akvotest.org/flow/orgs/uat1/data_points?survey_id=562899168&cursor=CjESK2oPc35ha3ZvZmxvdy11YXQxchgLEg5TdXJ2ZXllZExvY2FsZRiNxpSRAgwYACAA"
}
jq -M ' .dataPoints[] | keys' data-points.json
CREATE TABLE datapoint (
id text PRIMARY KEY ,
display_name text NOT NULL , -- always true?
identifier text NOT NULL UNIQUE,
location geometry(Point ,4326 ), -- Use a geometry column instead of separate lat/long values
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
Table “public.datapoint”
Column Type Collation Nullable Default
id text not null
display_name text not null
identifier text not null
location geometry(Point,4326)
created_at timestamp with time zone not null now()
modified_at timestamp with time zone not null now()
Indexes:
“datapoint_pkey” PRIMARY KEY, btree (id)
“datapoint_identifier_key” UNIQUE CONSTRAINT, btree (identifier)
jq -M ' .dataPoints[]' data-points.json
INSERT INTO datapoint(id, display_name, identifier, created_at, modified_at)
VALUES (' 572859149' , ' Juan' , ' k5x6-rus4-s1k3' , ' 2020-05-04T05:31:23.668Z' , ' 2020-05-04T05:31:28.470Z' )
id display_name identifier location created_at modified_at
572859149 Juan k5x6-rus4-s1k3 2020-05-04 05:31:23.668+00 2020-05-04 05:31:28.47+00
{
"formInstances" : [
{
"deviceIdentifier" : "s7" ,
"dataPointId" : "572859149" ,
"submissionDate" : "2020-05-04T05:31:19Z" ,
"modifiedAt" : "2020-05-04T05:31:28.582Z" ,
"id" : "599259147" ,
"responses" : {
"572849152" : [
{
"562879159" : 10 ,
"562909151" : "Juan" ,
"594369156" : [
{
"text" : "Yes" ,
"code" : "1"
}
]
} ,
{
"562879159" : 50 ,
"562909151" : "Pedro" ,
"594369156" : [
{
"text" : "No" ,
"code" : "0"
}
]
} ,
{
"562879159" : 25 ,
"562909151" : "Pablo" ,
"594369156" : [
{
"text" : "Yes" ,
"code" : "1"
}
]
} ,
{
"562879159" : 33 ,
"562909151" : "Maria" ,
"594369156" : [
{
"text" : "Yes" ,
"code" : "1"
}
]
}
]
} ,
"identifier" : "k5x6-rus4-s1k3" ,
"displayName" : "Juan" ,
"formId" : "562919156" ,
"surveyalTime" : 50 ,
"submitter" : "iperdomo" ,
"createdAt" : "2020-05-04T05:31:23.996Z"
}
] ,
"nextPageUrl" : "https://api-auth0.akvotest.org/flow/orgs/uat1/form_instances?survey_id=562899168&form_id=562919156&cursor=CjESK2oPc35ha3ZvZmxvdy11YXQxchgLEg5TdXJ2ZXlJbnN0YW5jZRiL8N-dAgwYACAA"
}
jq -M ' .formInstances[] | keys' form-instances.json
CREATE TABLE form_instance (
id text PRIMARY KEY ,
form_id text NOT NULL REFERENCES form(id),
datapoint_id text NOT NULL REFERENCES datapoint(id),
device_identifier text NOT NULL ,
submitter text NOT NULL ,
submission_date timestamptz NOT NULL ,
surveyal_time numeric NOT NULL DEFAULT 0 ,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now()
);
jq -M ' .formInstances[0] | del(.responses)' form-instances.json
INSERT INTO form_instance(id, form_id, datapoint_id, device_identifier, submission_date, submitter,
surveyal_time, created_at, modified_at)
VALUES (' 599259147' , ' 562919156' , ' 572859149' , ' s7' , ' 2020-05-04T05:31:19Z' , ' iperdomo' , 50 , ' 2020-05-04T05:31:23.996Z' , ' 2020-05-04T05:31:28.582Z' )
SELECT * FROM form_instance
id form_id datapoint_id device_identifier submitter submission_date surveyal_time created_at modified_at
599259147 562919156 572859149 s7 iperdomo 2020-05-04 05:31:19+00 50 2020-05-04 05:31:23.996+00 2020-05-04 05:31:28.582+00
jq -M ' .formInstances[].responses' form-instances.json
CREATE TABLE answer (
id text PRIMARY KEY DEFAULT gen_random_uuid(),
form_instance_id text NOT NULL REFERENCES form_instance(id),
question_id text NOT NULL REFERENCES question(id),
iteration smallint NOT NULL DEFAULT 0 ,
value_text text ,
value_number numeric ,
value_date timestamptz ,
value_object jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
modified_at timestamptz NOT NULL DEFAULT now(),
UNIQUE(form_instance_id, question_id, iteration)
);
Table “public.answer”
Column Type Collation Nullable Default
id text not null gen_random_uuid()
form_instance_id text not null
question_id text not null
iteration smallint not null 0
value_text text
value_number numeric
value_date timestamp with time zone
value_object jsonb
created_at timestamp with time zone not null now()
modified_at timestamp with time zone not null now()
Indexes:
“answer_pkey” PRIMARY KEY, btree (id)
“answer_form_instance_id_question_id_iteration_key” UNIQUE CONSTRAINT, btree (form_instance_id, question_id, iteration)
Foreign-key constraints:
“answer_form_instance_id_fkey” FOREIGN KEY (form_instance_id) REFERENCES form_instance(id)
“answer_question_id_fkey” FOREIGN KEY (question_id) REFERENCES question(id)
jq -M ' .formInstances[].responses["572849152"][]["562879159"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_number)
VALUES (' 599259147' , ' 562879159' , 1 , 10 ),
(' 599259147' , ' 562879159' , 2 , 50 ),
(' 599259147' , ' 562879159' , 3 , 25 ),
(' 599259147' , ' 562879159' , 4 , 33 )
id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
jq -M ' .formInstances[].responses["572849152"][]["562909151"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_text)
VALUES (' 599259147' , ' 562909151' , 1 , ' Juan' ),
(' 599259147' , ' 562909151' , 2 , ' Pedro' ),
(' 599259147' , ' 562909151' , 3 , ' Pablo' ),
(' 599259147' , ' 562909151' , 4 , ' Maria' )
SELECT * FROM answer ORDER by form_instance_id, iteration, question_id;
id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
a29511f4-5537-43a6-9257-dfe609d72187 599259147 562909151 1 Juan 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
0d623612-af71-4878-90c9-88dc51bf1990 599259147 562909151 2 Pedro 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
a6e7e75b-239d-4e7e-bc38-9c491bd2f909 599259147 562909151 3 Pablo 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
e90ad3c7-eb36-4dd8-bf14-73a63594b7b1 599259147 562909151 4 Maria 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
jq -c -M ' .formInstances[].responses["572849152"][]["594369156"]' form-instances.json
INSERT INTO answer(form_instance_id, question_id, iteration, value_object)
VALUES (' 599259147' , ' 594369156' , 1 , ' [{"text":"Yes","code":"1"}]' ::jsonb),
(' 599259147' , ' 594369156' , 2 , ' [{"text":"No","code":"0"}]' ::jsonb),
(' 599259147' , ' 594369156' , 3 , ' [{"text":"Yes","code":"1"}]' ::jsonb),
(' 599259147' , ' 594369156' , 4 , ' [{"text":"Yes","code":"1"}]' ::jsonb)
SELECT * FROM answer ORDER by form_instance_id, iteration, question_id;
id form_instance_id question_id iteration value_text value_number value_date value_object created_at modified_at
b6e365fb-67da-4d58-8780-0a966f8840eb 599259147 562879159 1 10 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
a29511f4-5537-43a6-9257-dfe609d72187 599259147 562909151 1 Juan 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
ec46a7c4-f358-401a-86bb-fd7d88df0cdd 599259147 594369156 1 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
72d0d3fc-bd9e-4afd-b803-d4603ac08476 599259147 562879159 2 50 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
0d623612-af71-4878-90c9-88dc51bf1990 599259147 562909151 2 Pedro 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
6f234327-2df7-4e29-b049-7356b6ba1ed8 599259147 594369156 2 [{“code”: “0”, “text”: “No”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
b271b72c-95f5-4222-a572-844ec65e04f1 599259147 562879159 3 25 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
a6e7e75b-239d-4e7e-bc38-9c491bd2f909 599259147 562909151 3 Pablo 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
2fe0f858-5a3b-4149-aaae-79e76407b44c 599259147 594369156 3 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
424d4b3e-a93c-4391-8bde-c0bfaa8e00b0 599259147 562879159 4 33 2020-05-06 12:17:32.026693+00 2020-05-06 12:17:32.026693+00
e90ad3c7-eb36-4dd8-bf14-73a63594b7b1 599259147 562909151 4 Maria 2020-05-06 12:20:29.322498+00 2020-05-06 12:20:29.322498+00
55d750b9-b90e-4be2-9d8d-7ed14654def6 599259147 594369156 4 [{“code”: “1”, “text”: “Yes”}] 2020-05-06 12:29:00.705249+00 2020-05-06 12:29:00.705249+00
Presenting the data as a Dataset
CREATE OR REPLACE VIEW form_metadata_562919156 AS
SELECT dp .identifier , dp .display_name , fi .device_identifier , fi .id as instance, fi .submitter ,
fi .surveyal_time , 1 as form_version, fi .submission_date -- we don't have form version yet
FROM datapoint dp,
form_instance fi
WHERE dp .id = fi .datapoint_id ;
SELECT * FROM form_metadata_562919156
identifier display_name device_identifier instance submitter surveyal_time form_version submission_date
k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00
CREATE OR REPLACE VIEW question_group_572849152 AS
SELECT q1 .value_text as " 562909151_name" , q2 .value_number as " 562879159_age" ,
concat(q3 .value_object - > 0 - >> ' code' , ' :' , q3 .value_object - > 0 - >> ' text' ) as " 594369156_literacy" ,
q1 .form_instance_id
FROM answer q1, answer q2, answer q3
WHERE q1 .question_id = ' 562909151'
AND q2 .question_id = ' 562879159'
AND q3 .question_id = ' 594369156'
AND q1 .form_instance_id = q2 .form_instance_id
AND q2 .form_instance_id = q3 .form_instance_id
AND q1 .iteration = q2 .iteration
AND q2 .iteration = q3 .iteration
ORDER BY q1 .iteration
SELECT * FROM question_group_572849152
562909151_name 562879159_age 594369156_literacy form_instance_id
Juan 10 1:Yes 599259147
Pedro 50 0:No 599259147
Pablo 25 1:Yes 599259147
Maria 33 1:Yes 599259147
SELECT *
FROM form_metadata_562919156 f,
question_group_572849152 qg
WHERE f .instance = qg .form_instance_id
identifier display_name device_identifier instance submitter surveyal_time form_version submission_date 562909151_name 562879159_age 594369156_literacy form_instance_id
k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Juan 10 1:Yes 599259147
k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Pedro 50 0:No 599259147
k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Pablo 25 1:Yes 599259147
k5x6-rus4-s1k3 Juan s7 599259147 iperdomo 50 1 2020-05-04 05:31:19+00 Maria 33 1:Yes 599259147
We have entities that defines schema and tables that hold data .
Similar to OO concepts, classes and instances.
Entities that define schema
Survey
Form
Question Group
Question
Datapoint
Form Instance
Answer
We can clearly see that we have less entities that hold data than
those defining schema. The reason for this is that some entities that
defined only presentation/UI concepts (Question Group
) were reused
to define schema.
Structure Data
Survey Datapoint
Form Form Instance
Question Group
Question Answer
In the current DB structure, there is a missing Question Group
Instance
, so an Answer
should point to that parent and not to Form
Instance
. More over, a Datapoint
does not have a link (foreign key)
to a Survey
.
Implementation notes/thoughts
There are several ways to optimize how we store answers. Having
those all rows in a single table has some problems:
A single table means joining the same table several times (as many
as questions). We can always optimize by using subqueries and
limiting the results per question_id
but is always expensive .
As example akvoflow-23
would have 31M rows in the answer
table.
To avoid NULL
values and solve the problem of millions of rows
in a single table we can shard . Create a table per question id
and type (e.g. answer_123_text
, answer_123_date
, etc)
Those views with the crosstab/pivot (e.g. form_123_metadata
,
question_group_456
) are actual queries/joins to the database. We
could pre-compute the work using Materialized Views . Using
materialized views has the drawback of duplicating data (at least
double of data storage requirement) and they need to be refreshed .
https://www.postgresql.org/docs/11/rules-materializedviews.html