Optimize Database Datatypes
2 minute read
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.
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psqlConnect 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';