All Docs
FeaturesMaking Tax DigitalUpdated February 25, 2026

How Composite Indexes Cut Transaction Query Times by Up to 90%

How Composite Indexes Cut Transaction Query Times by Up to 90%

Release v1.0.43 — Performance

Starting with v1.0.43, the platform adds three composite indexes to the transactions table — the most-queried table in the entire application. For landlords with 100 or more transactions, every quarterly summary, every dashboard load, and every transaction list query will complete significantly faster.

The Problem: Sequential Scans on a High-Traffic Table

Every core operation in the platform touches the transactions table:

  • Quarterly MTD submissions — aggregate income and expenses over a date range per organisation
  • Dashboard summaries — total income, expenses, and profit for the current period
  • Transaction list views — paginated, filterable lists of all transactions
  • Uncategorised counts — badge counts showing how many transactions still need an HMRC category

All of these queries filter by orgId — and most also filter by date, propertyId, or hmrcCategory. Without indexes on these column combinations, Postgres had no choice but to read every row in the table and discard the ones that didn't match. This is a sequential scan, and its cost grows linearly with the number of rows.

For a landlord with 50 transactions, this is barely noticeable. For a landlord with 500 or 5,000 transactions, it becomes a real bottleneck — slowing down submissions, dashboard loads, and routine browsing of transaction history.

The Fix: Three Targeted Composite Indexes

v1.0.43 adds the following indexes to src/db/schema.ts:

// Quarterly summary computations and date-range queries
index('transactions_org_date_idx').on(transactions.orgId, transactions.date)

// Per-property transaction queries and property dashboard summaries
index('transactions_org_property_idx').on(transactions.orgId, transactions.propertyId)

// Uncategorised counts and category-filtered queries
index('transactions_org_category_idx').on(transactions.orgId, transactions.hmrcCategory)

Why composite indexes?

A composite index on (orgId, date) is more efficient than two separate single-column indexes because the query planner can satisfy both filter conditions — WHERE orgId = $1 AND date BETWEEN $2 AND $3 — in a single index scan rather than combining two separate index scans at query time. Since orgId is present in every transaction query, it always appears as the leading column.

What Gets Faster

OperationIndex UsedExpected Improvement
Quarterly MTD summary calculationtransactions_org_date_idx50–90% faster
Dashboard income/expense totalstransactions_org_date_idx50–90% faster
Transaction list paginationtransactions_org_date_idx50–90% faster
Per-property transaction viewstransactions_org_property_idx50–90% faster
Uncategorised transaction counttransactions_org_category_idx50–90% faster

The improvement is most pronounced for organisations with larger transaction histories. Organisations with fewer than ~50 transactions may see little difference (Postgres may still choose a sequential scan at very small table sizes, which is correct behaviour).

Do I Need to Do Anything?

If you are running the platform yourself, you will need to run the database migration included with this release to create the indexes. The migration is non-destructive — it does not modify any existing data, columns, or constraints. It only adds the three new indexes.

If you are on the managed hosted platform, this migration has already been applied and no action is required.

Technical Details

  • File changed: src/db/schema.ts
  • Change type: Additive — new indexes only, no schema or data changes
  • Database: PostgreSQL
  • ORM: Drizzle ORM index definitions
  • Estimated query time reduction: 50–90% for orgs with 100+ transactions