Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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:

REINDEX TABLE "ContextIndices";
REINDEX TABLE "ContextLogs";
REINDEX TABLE "Contexts";
REINDEX TABLE "PerformanceRecords";

To reorganize all Connect application-specific indexes, use the following SQL script:

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:

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:

VACUUM FULL;

The VACUUM FULL command defragments and compacts the database reclaiming space occupied by deleted rows. After shrinking the database, it may be beneficial to reorganize the indexes.

  • No labels