This article is only relevant for v0.9.5 and earlier.

Optimize Database Datatypes

This page describes how to change the datatype of some columns in the database in order to optimize the performance.

In version 0.9.5 and prior, some tables in the database were created with the varchar data type. This data type is not optimal for storing large amounts of data. In version 0.9.6, the data type of some columns was changed from varchar to text. This migration optimizes the database, by changing the data type of some columns from varchar to text.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

  1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

  2. Click the Pod Shell button to open a shell in the container.

    Lens Pod Shell
    Lens Pod Shell

  3. Enter the postgres shell:

    psql
    
  4. Connect to the database:

    \c factoryinsight
    

Alter the tables

Execute the following SQL statements:

ALTER TABLE assettable ALTER COLUMN assetid TYPE text;
ALTER TABLE assettable ALTER COLUMN location TYPE text;
ALTER TABLE assettable ALTER COLUMN customer TYPE text;
ALTER TABLE producttable ALTER COLUMN product_name TYPE text;
ALTER TABLE ordertable ALTER COLUMN order_name TYPE text;
ALTER TABLE configurationtable ALTER COLUMN customer TYPE text;
ALTER TABLE componenttable ALTER COLUMN componentname TYPE text;

Then confirm the changes by using the following SQL statements:

SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'assettable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'producttable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'ordertable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'configurationtable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'componenttable';
Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)