Reorganize Database Indexes
The automatic clean-up functionality of Connect removes a significant number of records resulting in fragmentation of the corresponding database indexes. To address this issue, the following SQL script should be scheduled for daily database maintenance:
...
| Code Block | ||
|---|---|---|
| ||
REINDEX TABLE "ConnectObjects"; REINDEX TABLE "ConnectObjectsHistory"; REINDEX TABLE "ContextIndices"; REINDEX TABLE "ContextLogs"; REINDEX TABLE "Contexts"; REINDEX TABLE "PerformanceRecords"; REINDEX TABLE "ScenarioRegistrations"; REINDEX TABLE "SecureStoreItems"; |
Delete Obsolete Data
If there is a need to delete all Contexts and Performance Records created before a specific date, the following script can be employed. Be aware that fragmented indexes may cause poor performance during deletion. If required, defragment these indexes prior to deleting data. Ensure to adjust the date in the first line of the script as needed:
| Code Block | ||
|---|---|---|
| ||
DO $$ DECLARE delete_before DATE := '2000-12-31'; BEGIN DELETE FROM "Contexts" WHERE "FlowContextId" IN (SELECT "Id" FROM "Contexts" WHERE "FlowContextId" IS NULL AND "CreateTime" < delete_before); DELETE FROM "ContextIndices" WHERE "FlowContextId" IN (SELECT "Id" FROM "Contexts" WHERE "FlowContextId" IS NULL AND "CreateTime" < delete_before); DELETE FROM "ContextLogs" WHERE "FlowContextId" IN (SELECT "Id" FROM "Contexts" WHERE "FlowContextId" IS NULL AND "CreateTime" < delete_before); DELETE FROM "Contexts" WHERE "FlowContextId" IS NULL AND "CreateTime" < delete_before; DELETE FROM "PerformanceRecords" WHERE "Time" < delete_before; END $$; REINDEX TABLE "Contexts"; REINDEX TABLE "ContextIndices"; REINDEX TABLE "ContextLogs"; REINDEX TABLE "PerformanceRecords"; |
Reducing the size of the database
Deleting a large amount of data does not necessarily reduce the size of the database. In this case, it is advisable to instruct the database to release the no longer needed space. This can be achieved with the following SQL script:
...