Cimplico experienced a minor incident on July 30, 2025 affecting Workpapers, lasting 6h 20m. The incident has been resolved; the full update timeline is below.
Affected components
Update timeline
- investigating Jul 30, 2025, 01:29 AM UTC
We are currently investigating degraded performance in the application.
- monitoring Jul 30, 2025, 05:55 AM UTC
A fix has been implemented and we are monitoring the results.
- resolved Jul 30, 2025, 07:49 AM UTC
We have recorded significant improvements to the load speeds within the application. We will continue to monitor the issue, but will be marking this incident as resolved.
- postmortem Jul 30, 2025, 10:04 AM UTC
# Post-Mortem: Trial Balance Performance Degradation ## Executive Summary A missing database index on the `account` table resulted in severe performance degradation for trial balance queries, causing excessive CPU utilisation and poor user experience. Implementation of the `account_worksheet_id_lookup_idx` index reduced query execution time by **98.5%**, from 1,286ms to 19ms with seed data. ## Issue Description Trial balance load times were significantly impacted due to PostgreSQL performing sequential scans on the `account` table, which contains ~**2 million records** in production. The query optimiser was forced to scan all rows instead of using an efficient index lookup, resulting in: * High CPU utilisation on RDS instances * User-facing latency * Increased database resource consumption affecting overall system performance ## Root Cause Analysis The `account` table had an existing index `account_id_worksheet_id_idx` with columns ordered as `(id, worksheet_id)`. However, the trial balance query filters by `worksheet_id`, making this index ineffective for the lookup pattern. PostgreSQL cannot efficiently use a compound index when searching by the second column only. ## Resolution Created a new partial index on `account(worksheet_id)` with a WHERE clause for non-null values, enabling efficient lookups for the trial balance query pattern. ## Impact & Metrics ### Performance Improvements | Metric | Before | After | Improvement | | --- | --- | --- | --- | | **Execution Time** | 1,286.6ms | 19.2ms | **98.5%** ↓ | | **Total Time** | 1,330ms | 42ms | **96.8%** ↓ | | **Rows Scanned** | 2,278,000 | 25 | **99.999%** ↓ | | **Query Cost** | 2,263,972 | 5,061 | **99.8%** ↓ | ### Key Statistics * **Performance Factor**: 67x faster execution * **CPU Impact**: Significant reduction in RDS CPU utilisation * **Verification**: Confirmed via RDS Performance Insights and CloudWatch logs ## Lessons Learned 1. **Index column order is critical** - PostgreSQL cannot efficiently use a compound index when filtering by the second column 2. **Sequential scans on large tables have a severe impact** - 2M row scans vs 25 targeted index lookups during testing 3. **Query plan analysis with production-scale data is essential** for identifying performance bottlenecks early