Don’t read it. Watch it.

1# RFC: index the orders table

2

3## Summary

4Lookups by customer scan every row in orders. This RFC adds a

5sorted index on customer_id and migrates the three hot queries.

6

7## Motivation

8p95 for GET /customers/:id/orders has grown with the table.

9Every request reads the full table before filtering by customer.

10Support tooling and the nightly export share the same path.

11

12## Proposal

131. Add idx_orders_customer_id (customer_id, created_at DESC).

142. Build it concurrently so writes are never blocked.

153. Rewrite the three queries to use the new sort order.

164. Drop the old covering index once traffic has moved.

17

18## Migration plan

19- Create the index in a background migration.

20- Backfill statistics, then verify the query plans.

21- Roll out behind the orders_indexed_lookup flag.

22- Watch replica lag during the build window.

23

24## Query changes

25SELECT id, total, status FROM orders

26 WHERE customer_id = $1

27 ORDER BY created_at DESC LIMIT 50;

28

29## Alternatives considered

30- Partition orders by month. Heavier, and it defers the fix.

31- Cache per-customer order lists. Adds invalidation work.

32- A read model in the search cluster. Out of scope here.

33

34## Risks

35- Index build adds write amplification for about an hour.

36- Plans may regress for customers with very few orders.

37- Disk usage grows by roughly the size of the column.

38

39## Rollout

40Stage one: staging, with production-sized data.

41Stage two: ten percent of read traffic behind the flag.

42Stage three: all traffic, then remove the old index.

43

44## Open questions

45- Do we keep created_at in the index or sort in memory?

46- Should the export job move to the replica first?

47- Who owns the dashboard for the new query plans?

48

49## Appendix A: current query plans

50Seq Scan on orders (cost=0.00..48213.10 rows=41 width=36)

51 Filter: (customer_id = 1842)

52 Rows Removed by Filter: 1203311

53# RFC: index the orders table

54

55## Summary

56Lookups by customer scan every row in orders. This RFC adds a

57sorted index on customer_id and migrates the three hot queries.

58

59## Motivation

60p95 for GET /customers/:id/orders has grown with the table.

61Every request reads the full table before filtering by customer.

62Support tooling and the nightly export share the same path.

63

64## Proposal

651. Add idx_orders_customer_id (customer_id, created_at DESC).

662. Build it concurrently so writes are never blocked.

673. Rewrite the three queries to use the new sort order.

684. Drop the old covering index once traffic has moved.

69

70## Migration plan

71- Create the index in a background migration.

72- Backfill statistics, then verify the query plans.

73- Roll out behind the orders_indexed_lookup flag.

74- Watch replica lag during the build window.

75

76## Query changes

77SELECT id, total, status FROM orders

78 WHERE customer_id = $1

79 ORDER BY created_at DESC LIMIT 50;

80

81## Alternatives considered

82- Partition orders by month. Heavier, and it defers the fix.

83- Cache per-customer order lists. Adds invalidation work.

84- A read model in the search cluster. Out of scope here.

85

86## Risks

87- Index build adds write amplification for about an hour.

88- Plans may regress for customers with very few orders.

89- Disk usage grows by roughly the size of the column.

90

91## Rollout

92Stage one: staging, with production-sized data.

93Stage two: ten percent of read traffic behind the flag.

94Stage three: all traffic, then remove the old index.

95

96## Open questions

97- Do we keep created_at in the index or sort in memory?

98- Should the export job move to the replica first?

99- Who owns the dashboard for the new query plans?

100

101## Appendix A: current query plans

102Seq Scan on orders (cost=0.00..48213.10 rows=41 width=36)

103 Filter: (customer_id = 1842)

104 Rows Removed by Filter: 1203311

105# RFC: index the orders table

106

107## Summary

108Lookups by customer scan every row in orders. This RFC adds a

109sorted index on customer_id and migrates the three hot queries.

110

111## Motivation

112p95 for GET /customers/:id/orders has grown with the table.

113Every request reads the full table before filtering by customer.

114Support tooling and the nightly export share the same path.

115

116## Proposal

1171. Add idx_orders_customer_id (customer_id, created_at DESC).

1182. Build it concurrently so writes are never blocked.

1193. Rewrite the three queries to use the new sort order.

1204. Drop the old covering index once traffic has moved.

121

122## Migration plan

123- Create the index in a background migration.

124- Backfill statistics, then verify the query plans.

125- Roll out behind the orders_indexed_lookup flag.

126- Watch replica lag during the build window.

127

128## Query changes

129SELECT id, total, status FROM orders

130 WHERE customer_id = $1

131 ORDER BY created_at DESC LIMIT 50;

132

133## Alternatives considered

134- Partition orders by month. Heavier, and it defers the fix.

135- Cache per-customer order lists. Adds invalidation work.

136- A read model in the search cluster. Out of scope here.

137

138## Risks

139- Index build adds write amplification for about an hour.

140- Plans may regress for customers with very few orders.

141- Disk usage grows by roughly the size of the column.

142

143## Rollout

144Stage one: staging, with production-sized data.

145Stage two: ten percent of read traffic behind the flag.

146Stage three: all traffic, then remove the old index.

147

148## Open questions

149- Do we keep created_at in the index or sort in memory?

150- Should the export job move to the replica first?

151- Who owns the dashboard for the new query plans?

152

153## Appendix A: current query plans

154Seq Scan on orders (cost=0.00..48213.10 rows=41 width=36)

155 Filter: (customer_id = 1842)

156 Rows Removed by Filter: 1203311

157# RFC: index the orders table

158

159## Summary

160Lookups by customer scan every row in orders. This RFC adds a

161sorted index on customer_id and migrates the three hot queries.

162

163## Motivation

164p95 for GET /customers/:id/orders has grown with the table.

165Every request reads the full table before filtering by customer.

166Support tooling and the nightly export share the same path.

167

168## Proposal

1691. Add idx_orders_customer_id (customer_id, created_at DESC).

1702. Build it concurrently so writes are never blocked.

1713. Rewrite the three queries to use the new sort order.

1724. Drop the old covering index once traffic has moved.

173

174## Migration plan

175- Create the index in a background migration.

176- Backfill statistics, then verify the query plans.

177- Roll out behind the orders_indexed_lookup flag.

178- Watch replica lag during the build window.

179

180## Query changes

181SELECT id, total, status FROM orders

182 WHERE customer_id = $1

183 ORDER BY created_at DESC LIMIT 50;

184

185## Alternatives considered

186- Partition orders by month. Heavier, and it defers the fix.

187- Cache per-customer order lists. Adds invalidation work.

188- A read model in the search cluster. Out of scope here.

189

190## Risks

191- Index build adds write amplification for about an hour.

192- Plans may regress for customers with very few orders.

193- Disk usage grows by roughly the size of the column.

194

195## Rollout

196Stage one: staging, with production-sized data.

197Stage two: ten percent of read traffic behind the flag.

198Stage three: all traffic, then remove the old index.

199

200## Open questions

201- Do we keep created_at in the index or sort in memory?

202- Should the export job move to the replica first?

203- Who owns the dashboard for the new query plans?

204

205## Appendix A: current query plans

206Seq Scan on orders (cost=0.00..48213.10 rows=41 width=36)

207 Filter: (customer_id = 1842)

208 Rows Removed by Filter: 1203311