Vishnu Narayanan fefa0a6966 perf: add composite index for account-scoped conversation created_at sort (#15360) ...

## Description

Adds a composite index `(account_id, status, created_at)` on
`conversations`.

Sorting an account's conversation list by `created_at` ("newest first")
across all inboxes has no supporting index today, so Postgres falls back
to a backward scan of the global `index_conversations_on_created_at`
filtered by `account_id`. On large databases the planner estimates a
very deep scan and the query can exceed the statement timeout. The
composite index lets the ordered limit be served directly from the
`account_id`/`status` prefix. The default `last_activity_at` sort and
inbox-filtered views are unaffected (already indexed).

The index is created with `algorithm: :concurrently` (no table lock). On
very large installations, create it out-of-band concurrently before
deploying so the migration doesn't run a long build.

Fixes https://linear.app/chatwoot/issue/CW-7888

## Type of change

- [x] Bug fix (non-breaking change which fixes an issue)

2026-08-13 18:34:12 +05:30

72 KiB

Raw Permalink Blame History

View Raw