Store JSON data in JSONB columns instead of JSON

EventLogEntry data was left as JSON as this takes ages to convert on a big database...
indico · May 17, 2019 · c3a8116 · c3a8116
1 parent e55fceb
commit c3a8116
Show file tree

Hide file tree

Showing 20 changed files with 295 additions and 44 deletions.
diff --git a/indico/core/db/sqlalchemy/review_questions.py b/indico/core/db/sqlalchemy/review_questions.py
@@ -7,6 +7,7 @@
 
 from __future__ import unicode_literals
 
+from sqlalchemy.dialects.postgresql import JSONB
 from sqlalchemy.ext.declarative import declared_attr
 from sqlalchemy.ext.hybrid import hybrid_property
 
@@ -92,7 +93,7 @@ def is_required(self):
     @declared_attr
     def field_data(cls):
         return db.Column(
-            db.JSON,
+            JSONB,
             nullable=False,
             default={}
         )

diff --git a/indico/core/db/sqlalchemy/review_ratings.py b/indico/core/db/sqlalchemy/review_ratings.py
@@ -7,6 +7,7 @@
 
 from __future__ import unicode_literals
 
+from sqlalchemy.dialects.postgresql import JSONB
 from sqlalchemy.ext.declarative import declared_attr
 
 from indico.core.db import db
@@ -45,7 +46,7 @@ def review_id(cls):
     @declared_attr
     def value(cls):
         return db.Column(
-            db.JSON,
+            JSONB,
             nullable=False
         )
 

diff --git a/indico/core/settings/models/base.py b/indico/core/settings/models/base.py
@@ -11,7 +11,7 @@
 from enum import Enum
 
 from flask import g, has_request_context
-from sqlalchemy.dialects.postgresql import JSON
+from sqlalchemy.dialects.postgresql import JSONB
 
 from indico.core.db import db
 from indico.core.db.sqlalchemy.principals import PrincipalMixin, PrincipalType
@@ -91,7 +91,7 @@ class JSONSettingsBase(SettingsBase):
     __tablename__ = 'settings'
 
     value = db.Column(
-        JSON,
+        JSONB,
         nullable=False
     )
 

diff --git a/indico/migrations/versions/20190517_1313_06a4ec717b84_switch_from_json_to_jsonb.py b/indico/migrations/versions/20190517_1313_06a4ec717b84_switch_from_json_to_jsonb.py
@@ -0,0 +1,249 @@
+"""Switch from JSON to JSONB
+
+Revision ID: 06a4ec717b84
+Revises: 7024f7f66e20
+Create Date: 2019-05-17 13:13:31.784858
+"""
+
+from alembic import op
+
+
+# revision identifiers, used by Alembic.
+revision = '06a4ec717b84'
+down_revision = '7024f7f66e20'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    op.execute('''
+        ALTER TABLE categories.categories
+            ALTER COLUMN icon_metadata TYPE jsonb,
+            ALTER COLUMN logo_metadata TYPE jsonb,
+            ALTER COLUMN default_event_themes TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE categories.settings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_field_values
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_review_questions
+            ALTER COLUMN field_data TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_review_ratings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.email_logs
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.email_templates
+            ALTER COLUMN rules TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_paper_reviewing.review_questions
+            ALTER COLUMN field_data TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_paper_reviewing.review_ratings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_registration.form_field_data
+            ALTER COLUMN versioned_data TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_registration.form_items
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.answers
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.items
+            ALTER COLUMN field_data TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.submissions
+            ALTER COLUMN pending_answers TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.agreements
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.contribution_field_values
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.contribution_fields
+            ALTER COLUMN field_data TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.events
+            ALTER COLUMN logo_metadata TYPE jsonb,
+            ALTER COLUMN stylesheet_metadata TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.payment_transactions
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.requests
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE events.settings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE indico.designer_templates
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE indico.settings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE roombooking.room_attribute_values
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE users.identities
+            ALTER COLUMN multipass_data TYPE jsonb,
+            ALTER COLUMN "data" TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE users.registration_requests
+            ALTER COLUMN user_data TYPE jsonb,
+            ALTER COLUMN identity_data TYPE jsonb,
+            ALTER COLUMN settings TYPE jsonb;
+    ''')
+    op.execute('''
+        ALTER TABLE users.settings
+            ALTER COLUMN "value" TYPE jsonb;
+    ''')
+
+
+def downgrade():
+    op.execute('''
+        ALTER TABLE categories.categories
+            ALTER COLUMN icon_metadata TYPE json,
+            ALTER COLUMN logo_metadata TYPE json,
+            ALTER COLUMN default_event_themes TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE categories.settings
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_field_values
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_review_questions
+            ALTER COLUMN field_data TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.abstract_review_ratings
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.email_logs
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_abstracts.email_templates
+            ALTER COLUMN rules TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_paper_reviewing.review_questions
+            ALTER COLUMN field_data TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_paper_reviewing.review_ratings
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_registration.form_field_data
+            ALTER COLUMN versioned_data TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_registration.form_items
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.answers
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.items
+            ALTER COLUMN field_data TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE event_surveys.submissions
+            ALTER COLUMN pending_answers TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.agreements
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.contribution_field_values
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.contribution_fields
+            ALTER COLUMN field_data TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.events
+            ALTER COLUMN logo_metadata TYPE json,
+            ALTER COLUMN stylesheet_metadata TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.payment_transactions
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.requests
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE events.settings
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE indico.designer_templates
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE indico.settings
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE roombooking.room_attribute_values
+            ALTER COLUMN "value" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE users.identities
+            ALTER COLUMN multipass_data TYPE json,
+            ALTER COLUMN "data" TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE users.registration_requests
+            ALTER COLUMN user_data TYPE json,
+            ALTER COLUMN identity_data TYPE json,
+            ALTER COLUMN settings TYPE json;
+    ''')
+    op.execute('''
+        ALTER TABLE users.settings
+            ALTER COLUMN "value" TYPE json;
+    ''')
diff --git a/indico/modules/auth/models/identities.py b/indico/modules/auth/models/identities.py
@@ -9,7 +9,7 @@
 
 from datetime import datetime
 
-from sqlalchemy.dialects.postgresql import INET, JSON
+from sqlalchemy.dialects.postgresql import INET, JSONB
 from werkzeug.datastructures import MultiDict
 
 from indico.core.db import db
@@ -48,14 +48,14 @@ class Identity(db.Model):
     )
     #: internal data used by the flask-multipass system
     multipass_data = db.Column(
-        JSON,
+        JSONB,
         nullable=False,
         default=lambda: None
     )
     #: the user data from the user provider
     _data = db.Column(
         'data',
-        JSON,
+        JSONB,
         nullable=False,
         default={}
     )

diff --git a/indico/modules/auth/models/registration_requests.py b/indico/modules/auth/models/registration_requests.py
@@ -7,7 +7,7 @@
 
 from __future__ import unicode_literals
 
-from sqlalchemy.dialects.postgresql import ARRAY, JSON
+from sqlalchemy.dialects.postgresql import ARRAY, JSONB
 from werkzeug.datastructures import MultiDict
 
 from indico.core.db import db
@@ -43,16 +43,16 @@ class RegistrationRequest(db.Model):
         default=[]
     )
     user_data = db.Column(
-        JSON,
+        JSONB,
         nullable=False
     )
     _identity_data = db.Column(
         'identity_data',
-        JSON,
+        JSONB,
         nullable=False
     )
     settings = db.Column(
-        JSON,
+        JSONB,
         nullable=False
     )
 

diff --git a/indico/modules/categories/models/categories.py b/indico/modules/categories/models/categories.py
@@ -9,7 +9,7 @@
 
 import pytz
 from sqlalchemy import DDL, orm
-from sqlalchemy.dialects.postgresql import ARRAY, JSON, array
+from sqlalchemy.dialects.postgresql import ARRAY, JSONB, array
 from sqlalchemy.event import listens_for
 from sqlalchemy.ext.declarative import declared_attr
 from sqlalchemy.ext.hybrid import hybrid_property
@@ -109,7 +109,7 @@ def __table_args__(cls):
         default=None
     )
     icon_metadata = db.Column(
-        JSON,
+        JSONB,
         nullable=False,
         default=lambda: None
     )
@@ -118,7 +118,7 @@ def __table_args__(cls):
         nullable=True
     ))
     logo_metadata = db.Column(
-        JSON,
+        JSONB,
         nullable=False,
         default=lambda: None
     )
@@ -132,7 +132,7 @@ def __table_args__(cls):
         default=lambda: config.DEFAULT_TIMEZONE
     )
     default_event_themes = db.Column(
-        JSON,
+        JSONB,
         nullable=False,
         default=_get_default_event_themes
     )