Faster Revenue Queries: Composite Index on invoices(customer_id, paid_at)

Release: v1.0.50 · Category: Database Performance

---

Overview

Version 1.0.50 ships a targeted database performance improvement: a new composite index on the invoices table covering the customer_id and paid_at columns.

This is a behind-the-scenes change — no API or UI behaviour is altered — but it has a direct, measurable effect on the speed of revenue movement queries across the platform.

---

The Problem

Revenue movement sub-queries — the queries that power dashboards, subscription analytics, churn prediction, and billing reports — share a common pattern:

-- Typical revenue movement sub-query shape
SELECT *
FROM   invoices
WHERE  customer_id = $1
  AND  paid_at BETWEEN $2 AND $3;

Without an index that covers both columns together, Postgres had two options:

1. Seq scan — scan every row in the table and apply both filters.
2. Single-column index scan — use an index on one column, then re-check the other filter row-by-row.

Both approaches scale poorly as the number of invoices grows.

---

The Fix

A single composite index was added:

CREATE INDEX ON invoices (customer_id, paid_at);

Because customer_id is the leading column, Postgres can jump directly to the rows for a specific customer. The paid_at column is then stored in index order within each customer partition, so a date-range filter (BETWEEN, >=, <=) can be resolved without touching the table at all — a pure index range scan.

---

Impact

Area	Effect
Revenue movement dashboards	Faster page loads and data refresh
Subscription analytics sub-queries	Lower latency
Churn prediction data pipelines	Reduced I/O when scanning invoice history
Billing & invoicing reports	Quicker aggregation over date ranges
Database server load	Lower CPU and disk I/O under concurrent query load

---

No Action Required

This change is applied automatically as a database migration. No configuration changes, API updates, or code changes are required by consumers of the platform.