Database Indexes

The database is the core of how process-automation works. The assumption is that queries will always be performant. As your application gets larger, so will the amount of data flowing through it. The standard SQL provisioning does not declare any indexes. Once tables get large enough you may need them at some point. It is a good practice to add indexes on critical columns even when you currently do not encounter any performance issues.

ProcessAutomation Internals

The TaskJobQueue dataElement manages a queue through its database connection. It has to fetch new jobs using a query, which can cause a performance hit if the table gets large. It is recommended to add an index on status.

add-taskjobqueue-index.sql (postgresql)

CREATE INDEX IF NOT EXISTS idx_processautomation_taskjobqueue_status
                        ON processautomation.TaskJobQueue (status);

Status fields

The same issue can occur with any status field which uses database queries for its updates (Schedule or FlowEngine). It is a best practice to add indexes to all status fields, even if they are currently unused. This ensures extending a workflow in the future will not suddenly cause performance issues.

Similar to the index on TaskJobQueue, you can add an index on its status using the following query.

add-element-index.sql (postgresql)

CREATE INDEX IF NOT EXISTS idx_schema_element_status
                        ON schema.Element (status);