Faster Lifecycle Funnels: New Index on Subscriptions

Release: v1.0.10 · Category: Database Performance

Overview

v1.0.10 ships a targeted database performance improvement: a new composite index on the subscriptions table covering (customer_id, status). This change directly accelerates the lifecycle funnel query that powers subscription analytics across the platform.

Background

The lifecycle funnel feature counts subscriptions grouped by status for all customers belonging to a given project:

-- Simplified representation of the lifecycle funnel query
SELECT s.status, COUNT(*) AS count
FROM subscriptions s
JOIN customers c ON c.id = s.customer_id
WHERE c.project_id = $1
GROUP BY s.status;

Before this release, Postgres had no index to efficiently resolve the customer_id join or narrow the scan by status. As subscription tables grow, that sequential scan becomes the dominant cost in the query plan.

The Fix

A composite index is added via migration:

CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id_status
  ON subscriptions (customer_id, status);

The column order is intentional:

1. customer_id first — satisfies the equality join condition from the customers table, immediately narrowing the scan to rows owned by the relevant customers.
2. status second — enables efficient GROUP BY status aggregation on the already-filtered result set without an additional sort or heap fetch.

Performance Impact

Scenario	Before	After
Lifecycle funnel query (small project)	Sequential scan	Index scan
Lifecycle funnel query (large project)	Sequential scan (full table)	Index range scan (customer rows only)
Write throughput (INSERT/UPDATE on subscriptions)	Baseline	Negligible overhead from index maintenance

Deployment Notes

• The migration runs automatically on deploy. No manual steps are required.
• The IF NOT EXISTS guard makes the migration safe to run multiple times.
• There are no application code changes, API changes, or data migrations in this release.
• Existing queries continue to work identically; the query planner will adopt the index automatically.