evento_sql_migrator/m0004/
mod.rs

1//! Migration adding a composite index for subscription cursor scans.
2//!
3//! Subscription cursor queries filter on `aggregator_type` (optionally `name`,
4//! always with a `routing_key` predicate) and order by
5//! `(timestamp, timestamp_subsec, version, id)`. The original `idx_event_type`
6//! covers only the filter, so the planner loads every matching row and sorts
7//! externally; this dominates polling cost as the event table grows.
8//!
9//! This migration replaces the single-column `idx_event_type` with one composite
10//! index that covers both filter and sort columns, allowing the planner to walk
11//! the index in sorted order without an external sort.
12
13mod event;
14
15use sqlx_migrator::vec_box;
16
17/// Migration that swaps `idx_event_type` for a composite cursor-scan index.
18///
19/// ## Changes
20///
21/// - Creates `idx_event_type_routing_cursor` on
22///   `(aggregator_type, routing_key, timestamp, timestamp_subsec, version)`.
23/// - Drops `idx_event_type` (redundant once the composite index exists — same
24///   leading column, planner will use the new one).
25///
26/// ## Notes
27///
28/// - `name` is intentionally not part of the index: it is only ever a
29///   per-aggregator-branch residual predicate inside an OR, and with the typical
30///   small set of event names per aggregator type the residual scan stays cheap.
31/// - The `ORDER BY` tail stops at `version`; `(timestamp, timestamp_subsec,
32///   version)` is unique in practice, so the trailing `id` adds storage without
33///   eliminating any sort work.
34///
35/// ## Dependencies
36///
37/// This migration depends on [`M0003`](crate::M0003).
38pub struct M0004;
39
40#[cfg(feature = "sqlite")]
41sqlx_migrator::sqlite_migration!(
42    M0004,
43    "main",
44    "m0004",
45    vec_box![crate::M0003],
46    vec_box![
47        event::create_type_routing_cursor_idx::Operation,
48        event::drop_type_idx::Operation,
49    ]
50);
51
52#[cfg(feature = "mysql")]
53sqlx_migrator::mysql_migration!(
54    M0004,
55    "main",
56    "m0004",
57    vec_box![crate::M0003],
58    vec_box![
59        event::create_type_routing_cursor_idx::Operation,
60        event::drop_type_idx::Operation,
61    ]
62);
63
64#[cfg(feature = "postgres")]
65sqlx_migrator::postgres_migration!(
66    M0004,
67    "main",
68    "m0004",
69    vec_box![crate::M0003],
70    vec_box![
71        event::create_type_routing_cursor_idx::Operation,
72        event::drop_type_idx::Operation,
73    ]
74);
evento_sql_migrator/m0004/mod.rs

evento_sql_migrator/m0004/
mod.rs
