Normalize reports db

README.md

How to run

Indexing normalized db

• Get the database name that you want to copy from and paste it onto the originalDbName variable at normalize.js:16
• Create a normalized_db database using the schema at schema.sql
• Run node --max-old-space-size=16384 normalize.js (had to set my memory up to the max otherwise it would fail when copying abeam)
• Run time psql normalized_db < normalized-indexes.sql to measure how long it takes to index the normalized db

Indexing denormalized db

• Drop all indexes from the original database that you used in originalDbName (a simple way of doing that is running psql DB_NAME -c "select 'DROP INDEX ' || indexname || ';' from pg_indexes where tablename = 'learners_report';" -t > drop-indexes.sql then psql DB_NAME < drop-indexes.sql)
• Run time psql DB_NAME < denormalized-indexes.sql (I'm using abeam, if using another org a different set of indexes will be needed)

denormalized-indexes.sql

CREATE INDEX ON "learners_report" USING BTREE (("org_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("org_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("first_name") ASC NULLS LAST, ("user_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("first_name") DESC NULLS LAST, ("user_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("first_name") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("last_name") ASC NULLS LAST, ("user_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_name") DESC NULLS LAST, ("user_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("last_name") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("login_id") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("login_id") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("login_id") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("grade") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("grade") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("start_date") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("start_date") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_access") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_access") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("access_count") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("access_count") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("spent_time") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("spent_time") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("completed_date") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("completed_date") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("passing_score") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("passing_score") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("parent_profile_field_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("parent_profile_field_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("all_user_parent_profile_field_gids"));
CREATE INDEX ON "learners_report" USING GIN (("user_groups"));
CREATE INDEX ON "learners_report" USING GIN (("course_groups"));
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'ja'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'ja'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'de'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'de'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'en'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'en'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'th'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'th'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'ja'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'de'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'en'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'th'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'H59epXaH8wBI') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'H59epXaH8wBI') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'H59epXaH8wBI') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'AGBK95yJ0UgQ') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'AGBK95yJ0UgQ') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'AGBK95yJ0UgQ') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'xY4wBqC4aCQ9') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'xY4wBqC4aCQ9') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'xY4wBqC4aCQ9') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'xmci2iJI9DFZ') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'xmci2iJI9DFZ') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'xmci2iJI9DFZ') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'hrtBimKRX7F4') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'hrtBimKRX7F4') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'hrtBimKRX7F4') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ft1gX2dwVLy6') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ft1gX2dwVLy6') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'ft1gX2dwVLy6') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'svl2pd7Qs0tf') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'svl2pd7Qs0tf') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'svl2pd7Qs0tf') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'zLfgRtxEDnLe') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'zLfgRtxEDnLe') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'zLfgRtxEDnLe') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'PSLnEHzFEQ3q') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'PSLnEHzFEQ3q') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'PSLnEHzFEQ3q') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'HwzOmIc8ncny') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'HwzOmIc8ncny') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'HwzOmIc8ncny') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ZN61GJz8NuHf') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ZN61GJz8NuHf') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'XCMJwf9f6NVv') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'XCMJwf9f6NVv') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'hXTMTd7hnjT2') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'hXTMTd7hnjT2') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'hXTMTd7hnjT2') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'tSKHCj7rYPM5') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'tSKHCj7rYPM5') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'tSKHCj7rYPM5') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'n3i1TS8k9qBj') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'n3i1TS8k9qBj') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'n3i1TS8k9qBj') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'LhqNmAyyJDC6') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'LhqNmAyyJDC6') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'LhqNmAyyJDC6') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'dhD5FmPmXk78') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'dhD5FmPmXk78') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'dhD5FmPmXk78') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'qzuMVhaIRon9') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'qzuMVhaIRon9') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'qzuMVhaIRon9') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'6fnXuug6r7HE') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'6fnXuug6r7HE') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'6fnXuug6r7HE') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'8Q1CSFkfPLOq') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'8Q1CSFkfPLOq') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'2aPXMq89upwe') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'2aPXMq89upwe') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'yHPxvOcuvHJT') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'yHPxvOcuvHJT') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'YaIaHYngzGiN') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'YaIaHYngzGiN') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'W9YoWvtD6NM3') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'W9YoWvtD6NM3') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'0ulWljJALsHt') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'0ulWljJALsHt') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'0ulWljJALsHt') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'4TNwWl4XENLI') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'4TNwWl4XENLI') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'4TNwWl4XENLI') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'LvCjmqnUyPpb') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'LvCjmqnUyPpb') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'LvCjmqnUyPpb') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'fOjkeDUjGZ4y') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'fOjkeDUjGZ4y') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'fOjkeDUjGZ4y') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'b17o9WJQsmmS') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'b17o9WJQsmmS') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'b17o9WJQsmmS') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'gSXc5RJan5Nd') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'gSXc5RJan5Nd') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'gSXc5RJan5Nd') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Fz8LpX9GisKk') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Fz8LpX9GisKk') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'Fz8LpX9GisKk') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'bMQzMzOGuZja') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'bMQzMzOGuZja') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'bMQzMzOGuZja') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Kd3jlP8Ii7So') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Kd3jlP8Ii7So') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'Kd3jlP8Ii7So') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ERNzF8qtmZiV') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'ERNzF8qtmZiV') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'ERNzF8qtmZiV') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'1iwxQmPCbUbu') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'1iwxQmPCbUbu') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Ut5S92KAUZcN') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'Ut5S92KAUZcN') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'Ut5S92KAUZcN') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'sts8gIhaM1Of') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'sts8gIhaM1Of') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'sts8gIhaM1Of') gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'fclpt4UFwVbg') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'fclpt4UFwVbg') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'3I5cNzuzZAzL') ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("profile"->>'3I5cNzuzZAzL') DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("profile"->>'3I5cNzuzZAzL') gin_trgm_ops);

normalize.js

const groupBy = (array, fn) => {
  return array.reduce((map, x) => {
    const key = fn(x)
    if (map[key] == undefined) {
      map[key] = []
    }

    map[key].push(x)

    return map
  }, {})
}

const config = require('./reports/src/Config.bs').make()

const originalDbName = 'reports_1637142983542'
const originalDb = require('knex')({ client: 'pg', connection: { ...config, database: originalDbName } })

const targetDbName = 'normalized_db' 
const targetDb = require('knex')({ client: 'pg', connection: { ...config, database: targetDbName } })

console.log('=============== starting', Date.now())
originalDb("learners_report")
  .then((rows) => {
    console.log("============== retrieved rows: ", rows.length, Date.now())

    let toInsert = []
    let groups = groupBy(rows, x => `${x.user_gid}:${x.course_gid}`)
    console.log("============== finished grouping by user-course", Date.now())

    Object.values(groups).forEach(rows => {
      const baseRow = rows.find(r => r.parentProfileFieldGid == null)
      if (!baseRow) { throw new Error("Couldn't find base row") }
      const learnerReport = {...baseRow}

      delete learnerReport.profile
      delete learnerReport.parent_profile_field_gid
      delete learnerReport.all_user_parent_profile_field_gids

      toInsert.push(learnerReport)
    })

    console.log("============== processed learners_report rows:", Date.now())

    return targetDb.batchInsert('learners_report', toInsert)
      .then(() => {
        console.log("============== finished inserting learners reports", Date.now())

        groups = groupBy(rows, x => x.user_gid)
        console.log("============== finished grouping by user", Date.now())
        toInsert = []

        Object.values(groups).forEach(rows => {
          const baseRow = rows.find(r => r.parentProfileFieldGid == null)
          if (!baseRow) { throw new Error("Couldn't find base row") }
          const profile = Object.entries(baseRow.profile)
          profile.forEach(([profile_field_gid, value]) => {
            toInsert.push({
              user_gid: baseRow.user_gid,
              profile_field_gid,
              value: JSON.stringify(value),
            })
          })

          const profileFieldGids = new Set(profile.map(y => y[0]))

          rows.forEach(row => {
            Object.entries(row.profile)
              .forEach(([profile_field_gid, value]) => {
                if (!profileFieldGids.has(profile_field_gid)) {
                  profileFieldGids.add(profile_field_gid)
                  toInsert.push({
                    user_gid: baseRow.user_gid,
                    profile_field_gid,
                    value: JSON.stringify(value),
                  })
                }
              })
          })
        })

        return targetDb.batchInsert('profile_values', toInsert)
      }).then(() => console.log("============== finished inserting profile values", Date.now()))
  }).then(() => console.log('========== FINISHED ALL'))

normalized-indexes.sql

CREATE INDEX ON "learners_report" USING BTREE (("org_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("org_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("first_name") ASC NULLS LAST, ("user_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("first_name") DESC NULLS LAST, ("user_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("first_name") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("last_name") ASC NULLS LAST, ("user_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_name") DESC NULLS LAST, ("user_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("last_name") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("login_id") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("login_id") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("login_id") gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (("grade") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("grade") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("start_date") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("start_date") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_access") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("last_access") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("access_count") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("access_count") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("spent_time") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("spent_time") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("completed_date") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("completed_date") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("passing_score") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("passing_score") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (("user_groups"));
CREATE INDEX ON "learners_report" USING GIN (("course_groups"));
CREATE INDEX ON "learners_report" USING BTREE (("user_gid") ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (("user_gid") DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'de'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'en'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'zh_CN'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'th'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'zh_TW'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_title"->>'ja'), ("course_title"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'de'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'en'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'zh_CN'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'th'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'zh_TW'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("course_status"->>'ja'), ("course_status"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'de'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'de'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'en'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'en'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'th'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'th'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'ja'), ("language"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "learners_report" USING BTREE (COALESCE(("language"->>'ja'), ("language"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'de'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'en'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'zh_CN'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'th'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'zh_TW'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "learners_report" USING GIN (COALESCE(("language"->>'ja'), ("language"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING BTREE (("user_gid") ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (("user_gid") DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (("value") ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (("value") DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'de'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'de'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'en'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'en'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'zh_CN'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'zh_CN'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'th'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'th'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'zh_TW'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'zh_TW'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'ja'), ("value"->>'default')) ASC NULLS LAST);
CREATE INDEX ON "profile_values" USING BTREE (COALESCE(("value"->>'ja'), ("value"->>'default')) DESC NULLS LAST);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'de'), ("value"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'en'), ("value"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'zh_CN'), ("value"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'th'), ("value"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'zh_TW'), ("value"->>'default')) gin_trgm_ops);
CREATE INDEX ON "profile_values" USING GIN (COALESCE(("value"->>'ja'), ("value"->>'default')) gin_trgm_ops);

schema.sql

CREATE TABLE learners_report (
  org_gid varchar(36) not null,
  user_gid varchar(36) not null,
  first_name text default '',
  last_name text default '',
  login_id text default NULL,
  course_gid text not null,
  course_title jsonb not null,
  grade numeric(4, 1) default null,
  start_date timestamptz default null,
  last_access timestamptz default null,
  access_count smallint default 0,
  spent_time integer default 0,
  completed_date timestamptz default null,
  course_status jsonb not null,
  passing_score numeric(4, 1) default null,
  language jsonb not null,
  user_groups text[] not null,
  course_groups text[] not null
);

CREATE TABLE profile_values (
  user_gid varchar(36) not null,
  profile_field_gid varchar(36) not null,
  value jsonb not null
);

CREATE EXTENSION pg_trgm;