Architecture Decision: Database Performance

Overview

This proposal documents a temporary solution for a high-volume automation filtering issue to address the minimum requirements of an operations team.

This solution is not a long-term solution, as a long-term solution requires re-architecting the broader job-processing workflow and would take substantially longer than the short-term need allowed.

With this understanding, the proposed solution attempts to restore practical filtering for the operations team, defined as:

• Being able to filter by automation job type.
• Supporting a defined set of automation job types. [Specific job names removed.]
• Being able to filter by job status.
• Supporting the required operational statuses. [Specific status values removed.]
• Being able to filter by priority from 1-10, where 10 is the highest priority.

This proposed solution does not address long-term scalability issues, as that requires re-architecting. Instead, it proposes to increase the speed of reserving a single automation job for consumption by the operations team.

Risks and limitations to this solution are outlined below.

Problem Statement

The current job selector, relying on a single queue-and-history table, cannot support filtering without experiencing significant performance degradation. This is due to a very large active dataset being stored in one place.

This slowdown directly impacts the efficiency and throughput of automation operations. As a result, filtering was removed, and the current process uses the table as a first-in-first-out queue.

No filtering is possible with the current setup, as the system pulls the first available job it finds from the table.

The core issue stems from the monolithic nature of the job table, which serves as both an active queue and a historical archive. As the volume of jobs has grown, querying and managing this single table has become increasingly resource-intensive, resulting in slow job retrieval for workers and slower reporting for operational oversight.

Previous Attempt and Lessons Learned

The Queue / Archive Split Failure

An earlier attempt to address this performance issue involved splitting the job table into separate queue and archive tables. The theoretical premise was sound: active jobs would reside in a smaller, highly optimized queue table, while completed or historical jobs would be moved to a larger, less frequently accessed archive table. This approach aimed to reduce the active dataset that the job selector needed to scan.

However, this initiative ultimately failed. The primary reason for its failure was the deep coupling of the application logic to the single job-table structure. The workflow system, developed over time, was built with an assumption of a single, centralized job table. Attempting to introduce a split required modifications across numerous, often poorly understood, sections of the application code.

Specifics

• Undocumented dependencies: Critical pieces of application logic that interact with the job table were not fully understood or documented. [Specific internal workflow details removed.]
• Hidden business rules: Complex business rules embedded within the application code relied on specific interactions with the combined active and archived data in the single table.
• Unforeseen side effects: Changes to the table structure, even with careful planning, introduced unexpected side effects and bugs in various parts of the system, making the transition too risky and unstable.
• Resource constraints: The effort required to fully reverse-engineer and re-engineer the application's interactions with the job data proved to be beyond available resources and expertise within the necessary timeframe.

The lesson learned was that altering the fundamental table structure, while conceptually appealing, introduced unacceptable risk and complexity when critical areas of the application layer were not fully understood or documented.

Proposed Solution

The proposed solution attempts to achieve the same general benefit as the queue/archive approach, but within the database itself instead of the application layer.

A modern relational database on a managed cloud platform can support table partitioning in supported versions. Internally, this can behave similarly to splitting data into separate table segments, while the application still sees and queries a single table.

This reduces the need to modify application logic to determine which table should be queried, since the application continues to interact with one logical job table.

Since this solution still involves storing a large amount of data in [database redacted], it does not eliminate the same underlying scalability concerns as a queue/archive split. The archive portion of the partitioned table may still contain millions of records.

The minimum requirements provided for this solution are:

• Higher-priority jobs should be served fast enough to avoid becoming an operational bottleneck.
• Lower-priority jobs can tolerate slower response times when necessary.
• This lower performance is acceptable as long as high-priority workflows remain responsive.

Database-Centric Optimization

Given the lessons from the previous attempt, the revised strategy focuses on optimizing the existing job table at the database level rather than making a large structural change at the application level. This approach minimizes application code changes, reduces implementation risk, and allows the team to move faster.

The proposal centers on two database optimization techniques: advanced indexing and table partitioning. Both options can be implemented, but table partitioning should not be implemented without first performing index optimization.

Advanced Index Optimization: Option 1

After reviewing the most frequent and critical queries performed by the job selector and related reporting tools, the initial priority should be optimizing queued and active job retrieval.

• Queued jobs are defined by a narrow set of operational status values. [Specific internal status IDs removed.]
• Only priority 5-10 jobs are considered critical for the fastest response time requirement.
• This focuses optimization on the automation flows that are most important to worker throughput.

The aim is to optimize the most critical queries first.

Assumptions

• Within the critical priority range, job types are generally filtered independently. [Specific job type names removed for confidentiality.]
• Within the critical priority range, only one status is typically filtered at a time.
• The actual partitioning strategy should be based on the most common query patterns.
• The operations team is the correct stakeholder group to confirm whether these query assumptions are accurate.
• If any assumptions are incorrect, they must be corrected before finalizing the optimization approach.

Covering indexes should be added or modified for the reserve, browse, and read actions. This means the requested columns are available from the index itself where possible, reducing the need to access the full table data.

These assumptions need to be approved by the relevant stakeholders.

Pros

• Relatively easy to implement.
• Lower risk than changing the application architecture.
• Can improve the most important queries without requiring a full table redesign.

• Additional disk usage: Indexes take up more storage space. Risk: Low.
• Limited scope of improvement: Speed increases are limited to the queries that are optimized. This does not speed up every query. Risk: Low, assuming the correct queries are optimized.

Table Partitioning: Option 2

After sample testing with a full-size dataset, table partitioning may be considered for further improvement. This would physically divide the table data into smaller, more manageable segments, which can improve query performance when queries align with the partitioning strategy.

In effect, this splits the main table into smaller internal data segments, similar in concept to the earlier queue/archive proposal, but it does so at the database level rather than the application level.

By partitioning based on a relevant and frequently queried column, the database can prune partitions that do not contain the data required for a specific query. This reduces the amount of data the database needs to scan.

Specific Partitioning Strategy

Based on observed usage patterns, the proposed partitioning key is a job-status field. [Specific internal column name and status values removed.]

• The selected status field appears to align with how jobs are selected and prioritized.
• One partition would contain the active or queued job statuses.
• The second partition would contain the remaining statuses.

This partitioning decision greatly affects performance and must be carefully considered by the relevant stakeholders.

While multiple partitioning approaches are technically possible, partitioning on more than one key considerably increases complexity while providing only marginal additional benefit. For this reason, partitioning on more than one key is not recommended.

Pros

• Partitioning effectively creates multiple buckets of data.
• Queries that stay within the correct bucket can show measurable performance increases.
• The application can continue querying the table as a single logical table.

• Partitioned tables are harder to alter: Changes to the table may require a rebuild and may not fit the normal migration process used elsewhere in the application. Risk: Medium.
• Queries spanning multiple partitions may be slower: If a query crosses partition boundaries, the database may need to scan more data and performance may decrease. Risk: High if the wrong partition key is chosen.
• Foreign key limitations: Referential integrity may not be supported in the same way on a partitioned table. Risk: Medium.

The system already has areas where referential integrity is handled at the application level rather than strictly enforced by the database. For this system, that lowers the practical risk. In a more sensitive system, such as a financial or banking platform, this tradeoff would require a different level of scrutiny.

Documentation for the partitioning process should be completed separately if this option is selected.

The proposed partitioning key must be reviewed and approved before implementation.

Suggested Approach

Begin with the table index optimization approach, listed above as Option 1. This is low risk because only database indexes are modified. After finding the appropriate index strategy, those changes can be implemented in code and migrations.

Table partitioning, listed above as Option 2, should only be considered later if necessary. The performance of the system should be monitored carefully, along with complaints and feedback from the operations team.

If indexing is not fast enough, partitioning may provide additional performance improvement for specific queries. However, this is not guaranteed, and some browse functions may actually become slower. The only way to confirm the actual performance impact is to create a test server setup similar to production and benchmark the results.

If database index optimization provides adequate performance, the downsides of partitioning are no longer necessary to accept, and Option 2 is not needed for this solution.

Benefits of the Proposed Approach

The following describes the risks and benefits of a two-stage approach. Both options do not need to be completed at the same time. However, Option 2 does not make sense without first completing Option 1.

• Low risk: This approach minimizes changes to the core application logic, reducing the likelihood of introducing new bugs or unexpected system instability. Only the filtering section of the workflow is modified, while the rest of the codebase remains largely unchanged.
• Faster implementation: Database-level changes are quicker to implement and test compared to a larger application re-engineering effort.
• Direct performance improvement: The proposal directly targets the database's ability to retrieve and manage job data, leading to measurable speed improvements for the job selector.
• Tested improvement: Performance gains were already observed in a local test environment for specific queries. [Specific benchmark ranges removed or generalized for confidentiality.]
• Further validation required: Additional testing is still needed in a comparable environment before final production numbers can be confirmed.

Monitoring and Validation / Logging

The existing logging for this process is inadequate. While there are reporting dashboards that show the current state of jobs, there is no easy way for the development team to see how many jobs failed, why they failed, or how those failures should be resolved.

This lack of visibility makes it more difficult to track process improvements, identify recurring failures, or confirm whether performance changes are improving the system as expected.

Monitoring and validation would require a separate plan if this area needs to be improved. Adequate time for requirements, discovery, planning, and implementation strategy would be needed to build a sufficient solution.

As of the original review meeting, monitoring and validation were not fully discussed due to time constraints. [Specific meeting date removed for confidentiality.]

However, monitoring, logging, and reporting could be put in place because the job table already contains several useful fields.

• Attempts remaining
• Created date
• Modified date
• Completion indicator
• Last automation attempt

The following additional information would be required from the operations team to create meaningful reports. The API edit endpoint could be modified to accept this additional data and log it to the database. The database would also need to be updated to store the additional information.

For a more enterprise-level logging approach, structured JSON logs could also be pushed to a central logging platform such as Azure Monitor, Datadog, Splunk, or a similar tool.

Minimum Job Result Data

• Job ID: The identifier matching the job in the main job table.
• Bot ID: A unique identifier for the bot processing the job.
• Timestamp: When the event was created.
• Execution stage: Whether the job is starting, ending, or moving through another defined step.
• Payload: Optional processed job information if that data is needed for reporting.
• Status: Whether the bot failed, succeeded, or issued a warning.
• Exception details: Error details from the automation platform when available. [Specific vendor details removed for confidentiality.]

Monitoring Plan

Performance Monitoring

• Bot execution time: When the bot started and when it ended. This may need to be tracked inside the automation platform.
• API execution time: When the reservation request started and when it completed.
• Queue wait time: How long the job stayed in the queue, calculated from the created time and the time it was modified or reserved.
• Resource consumption: Average, minimum, and maximum resource usage if supported by the automation platform.
• Throughput: How many jobs were processed over a given period of time.

Data Volume Monitoring

• Number of records handled per job.
• Alerts if unexpected volumes are detected or expected volumes are missed.
• Historical dashboards to track data volume over time.

Data Integrity Checks

• An audit trail of each transaction could be added if required. However, given the volume of jobs processed each day, this may not be feasible or desirable for every transaction. [Specific daily volume removed.]

Operational Monitoring

Some of the following information may already be available inside the automation platform or existing reporting tools.

• Bot health checks: Track how many bots are available and whether they are online, using a heartbeat-style approach where possible.
• Job status dashboards: Track success and failure counts, graphical trends, and last run timestamps.
• Error grouping: Categorize automation errors by frequency, jurisdiction or business area, status, and job type where available.
• Trend analysis: Identify which bots fail most often, which job types fail most often, and whether failures are concentrated around specific conditions.
• Alerts: Combined with data volume monitoring, alerts could be sent through Slack, email, text message, or another notification system when bot health issues occur or when a large number of errors occur at one time.

The actual design and implementation of monitoring is out of scope for this document.

Implementation Plan

1. Detailed performance analysis: Collaborate with the operations team to identify critical slow queries and gather precise data access patterns.
   1. Use existing knowledge and simulate typical workflow usage patterns, including retrieving jobs, updating jobs, browsing jobs, and reserving jobs, to optimize query performance.
   2. Most of the queries are already known, but documenting each use case ensures that the most important queries are optimized and the slow queries are known to all parties.
   3. The analysis will help determine the correct partition key if partitioning is selected. This requires stakeholder approval before proceeding.
2. Index design and testing: Propose and test new index configurations in a staging environment with realistic production-level data volume.
   1. A fully working production-like environment is needed to accurately measure the performance of this strategy.
   2. The existing QA environments may not have sufficient resources and may need to be increased to support realistic test data volumes. [Specific row counts removed for confidentiality.]
3. Staging environment implementation and testing: Apply the chosen indexing and partitioning schemes in a staging environment and conduct performance and regression testing using realistic data volumes and workloads.
   1. This assumes that the relevant stakeholders have provided the necessary signoff for the partition and index types.
4. Phased production deployment: Plan a carefully managed deployment to production during a maintenance window, with rollback plans in place.
   1. The rollback plan should detail the manual steps necessary to revert the partitioned table if partitioning is used.
   2. The primary job table may need to be locked while data is transferred. Approval is required before this step.
5. Monitoring and validation: Continuously monitor performance metrics after deployment to validate improvements and identify any further optimization opportunities.
   1. Regularly communicate with the operations team to gather feedback on query performance.
   2. Further tuning may be required if optimizing one query path causes another query path to lose performance.

Deployment Plan

• Complete testing on a staging or test server.
• Set the database table to read-only during the migration window.
• Deploy the new branch with filtering support.
• Create the partitioned version of the primary job table. [table name redacted]
• Copy the relevant jobs into the partitioned table.
• Rename the existing job table to a temporary backup table. [table name redacted]
• Check that the number of records matches between the original and partitioned tables.
• Rename the newly created partitioned table to replace the original job table. [table name redacted]
• Truncate or archive the original table after validation, if space needs to be recovered.
• Set the database back to read/write mode.
• Monitor the system and test the API and admin pages after deployment.

Rollback Plan for Partition

• Redeploy the previous branch without filtering on the reserve API endpoint.
• Roll back the primary job table.
  • Lock the partitioned table.
  • Copy the partitioned table data back into the original table.
  • Rename the partitioned table to a backup table.
  • Rename the temporary original table back to the active job table.
  • Drop the partitioned table after rollback has been validated.

The deployment and rollback steps should happen within the same maintenance window where possible to reduce downtime and limit the risk of inconsistent data.

Risks

Downtime

The actual implementation should happen after hours for minimal disruption. For zero data loss, connections should be severed from the database where possible, and the application should be placed into maintenance mode during the migration window.

Indexes

For Option 1, an incorrectly chosen index is low risk, since the query will perform as slowly as it did before. A list of all possible queries should be reviewed to ensure the most important queries are optimized.

Not all queries can be optimized at the same time, since there are tradeoffs in how [Database redacted for confidentiality] chooses which index to use for a given query.

Incorrect Optimized Key

A medium to high risk stems from incorrectly chosen keys if the partition option is selected. Partitioning can speed up queries that use the chosen key, but it may lower performance for queries that span multiple keys.

There is a risk that browse functions in the admin interface may become too slow. This can only be confirmed with a test setup using realistic data to measure the impact of partitioning.

A possible mitigation is to alter the browse function to only allow searching across the chosen partition key. In this scenario, the admin user could search within a specific key first, and then filter by job type or status inside that subset. Broader searches across multiple keys may result in unacceptable performance.

Foreign Table Integrity

Referential integrity for certain fields may be lost when choosing to partition tables. This is a known limitation of the selected database approach. The risk is relatively low in this system because the application already handles some integrity checks outside of strict database constraints.

However, if related data is missing or manually deleted, the job may not run correctly and may throw an error.

Risk Confirmation

Confirmation of each of these risks can only be achieved by implementing a comparable test environment, benchmarking the results, and summarizing the findings.

These tests should be performed before making final decisions so the team can reduce risk and better understand the tradeoffs involved.

This assumes cooperation with an SRE, test engineer, or other infrastructure resource to create a test environment, deploy a build, and benchmark each optimization step. [Specific timing estimates removed for confidentiality.]

Time Necessary

The estimated required time for each step is shown below, assuming needed resources are available and dedicated 100%. No timing of decisions is included.

Conclusion

The current state of the job selector is a significant bottleneck. The previous attempt to split the primary job table highlighted the severe constraints imposed by the application's tight coupling to its monolithic structure.

By temporarily abandoning the two-table approach and shifting focus to database-level optimizations for a single table, specifically advanced indexing and partitioning, the system can achieve performance gains with reduced risk and a faster implementation time.

Given the visibility of the situation and the lengthy time necessary to re-architect the job-processing system correctly, this temporary solution would provide a quicker path to restoring filtered job selection.

It must be noted that this proposed solution, like the queue/archive solution, is not scalable into much larger long-term data volumes. To achieve that level of scalability, a re-architecture of the solution is required.

However, this temporary solution gives the team time to properly document and understand the current system, design and plan a new architecture, and re-architect a proper, scalable upgrade.

Approvals

The original working draft included a decision log for stakeholder signoff. Names, internal teams, and project-specific ownership details are intentionally omitted here.

Test Results

Option 1 was tested in a non-production environment configured with production-like data volume and representative workload patterns. [Names, server details, domains, and infrastructure specifications removed.]

The QA server did not fully match the production environment, but it provided a reasonable test environment because it was isolated from normal production traffic and could be used to compare query behavior before and after indexing.

Test Environment

• QA server with increased memory and storage capacity.
• Application branch containing temporary filtering changes.
• Copy of the relevant production-like job and supporting reference data.
• Dataset large enough to represent the performance issue being tested. [Exact table names and row counts redacted for confidentiality.]

Baseline

Baseline numbers were taken from an environment configured to approximate current production behavior, using copied job data and related reference data.

The largest tables contained millions of rows, which made them sufficient for measuring whether the proposed index changes improved the job reservation and filtering workflow. [Exact table names and row counts redacted for confidentiality.]

Single Job Reservation

The primary test focused on the job reservation path, since this was the most important workflow for worker throughput. The test measured how quickly the system could find and reserve the next matching job when filtering by status, job type, and priority.

The internal API URL and resulting database query were reviewed as part of the test, but are not included here for confidentiality. [Internal endpoint and query redacted.]

Result Summary

• Index optimization showed measurable improvement for the targeted reserve queries.
• The test confirmed that query shape and filter order mattered when choosing indexes.
• The results supported moving forward with the lower-risk index optimization approach before considering partitioning.
• Further testing in a more production-comparable environment would still be required before making final partitioning decisions.

Detailed benchmark screenshots, raw queries, and environment specifics are intentionally omitted because they expose internal schema, query structure, and production-like data characteristics.

Development / Demo Branch

This section contains additional technical information describing a demonstration-style deployment process.

The deployed branch was a temporary validation branch. It was not part of the normal sprint deployment process, but could be deployed separately or merged into the main codebase after approval.

[Specific branch name, ticket number, repository details, and internal links removed for confidentiality.]

Deployment Plan for Indexes

The deployment plan for Option 1 focused on releasing the temporary filtering branch and applying the selected index changes.

• Deploy the approved filtering branch.
• Add the selected composite indexes for the reserve, browse, and read queries.
• Add supporting indexes for related lookup or reference data where needed.
• Validate that the expected indexes are present after deployment.
• Run the benchmark reserve queries again to confirm the expected performance improvement.
• Monitor API behavior, admin pages, and worker activity after deployment.

[Raw SQL, table names, index names, and internal column names removed for confidentiality.]

Rollback Plan for Indexes

The rollback plan for Option 1 restored the previous application branch and removed the new index changes if the deployment caused unexpected issues.

• Redeploy the previous release branch or latest approved production branch.
• Remove the newly added composite indexes.
• Restore the previous single-column indexes where required.
• Remove any supporting indexes added for related lookup or reference data.
• Run the original reserve and browse queries to confirm the system returned to the expected baseline behavior.
• Monitor the workflow after rollback to confirm workers can continue reserving jobs.

[Raw rollback SQL, table names, index names, and internal column names removed for confidentiality.]

Appendix

Create Table Query

The original document included the actual database command used to create the proposed partitioned table, assuming a selected partition key.

The raw query is not shown here because it exposes the database technology, table names, column names, index names, partition strategy, and internal data structure.

Notes

To reduce downtime, the suggested course of action was to create the partitioned table under a temporary name, copy the relevant job data into it, validate record counts, rename the original table as a backup, and then rename the partitioned table as the active table.

Specific table names and commands have been removed for confidentiality.