De 9,3 segundos a 12 ms: el stock en tiempo real con 1 millón de filas — Transcript

Full transcript

1. 0:00A cashier types "hammer," hits check
2. 0:03stock, and the spinner freezes for 9.3
3. 0:05seconds.
4. 0:07Today, that same register responds in 12
5. 0:09milliseconds using the same server, and
6. 0:11the error that froze it isn't what
7. 0:13you're thinking.
8. 0:14The table has a million inventory
9. 0:16records, a log of every incoming and
10. 0:19outgoing item. A million rows can fit on
11. 0:21a single Excel sheet. How does that
12. 0:23freeze a cash register?
13. 0:25Same thing in the terminal: 8.4 seconds
14. 0:28and nearly 2 GB of memory to return one
15. 0:30number.
16. 0:31The obvious explanation is that the
17. 0:32server is too small. False. Buying a
18. 0:34more expensive one doesn't fix it. The
19. 0:36error is in a line of code that almost
20. 0:38all of us write first. In development,
21. 0:41with 50 products, everything flies, no
22. 0:43one notices the problem, and that is
23. 0:44exactly where the error is born. Think
24. 0:46about what is at stake. A register that
25. 0:49freezes for 9 seconds means a line of
26. 0:51customers, a cashier who loses faith in
27. 0:53the system, and an owner who ends up
28. 0:54writing stock in a notebook.
29. 0:56The code that almost all of us write
30. 0:58first is this. Fetching all records with
31. 1:01the ORM—the layer that turns rows into
32. 1:03objects—and summing inputs and outputs
33. 1:05with a for loop. Look at the select line
34. 1:08, that's where the problem is, and it
35. 1:09isn't obvious at first glance: it
36. 1:11doesn't filter anything. It asks the
37. 1:12database for the full million rows and
38. 1:14converts them into a million Python
39. 1:16objects. Then, Python does the filtering
40. 1:19by product, row by row, just to keep a
41. 1:21few.
42. 1:22The result: 8.4 seconds and 1945 MB of
43. 1:26RAM.
44. 1:27Excessive memory consumption to obtain a
45. 1:29single number. But surely you're
46. 1:32thinking, "I use pandas, that's faster.
47. 1:34" Using pandas to sum inputs and outputs
48. 1:36looks more elegant.
49. 1:384.1 seconds—better, yes—but you are
50. 1:41still moving 1 million rows across the
51. 1:43data network and the application, plus
52. 1:451116 MB of RAM.
53. 1:47And that unnecessary transfer is
54. 1:49repeated with every sale. And the lesson
55. 1:52is simple.
56. 1:53The database is built for calculating.
57. 1:56Python is built for coordinating. This
58. 1:58is what the same query looks like with
59. 2:00the sum inside PostgreSQL. Same stock:
60. 2:03326,582
61. 2:05units, 34 milliseconds, and a single row
62. 2:09travels across the network. From 9
63. 2:12seconds to 34 milliseconds with one
64. 2:14query.
65. 2:15Problem solved, it would seem. Not yet,
66. 2:18because 34 milliseconds for a single
67. 2:20product is a lot, and the database
68. 2:22doesn't say what it spends them on.
69. 2:25To know, you don't guess—you ask.
70. 2:28The movements table stores every input
71. 2:30and every output.
72. 2:32A million inventory movements for 5,000
73. 2:34products. Run an explain analyze on the
74. 2:37same query.
75. 2:38The plan says Parallel Seq Scan,
76. 2:40sequential scan.
77. 2:43It checked a million rows, discarded
78. 2:45almost all of them, and kept 144 rows
79. 2:48for product 42, 27.8 milliseconds.
80. 2:51The number of discarded rows says it all
81. 2:54: 333,000 for each process. And there
82. 2:57are three processes; nearly 1 million
83. 2:59rows read and thrown in the trash to
84. 3:01return just a handful. It's like looking
85. 3:04for a screw by checking every box in the
86. 3:06warehouse, one by one, while the
87. 3:08customer waits at the counter.
88. 3:10Filtering by product and date without
89. 3:12the proper index forces the engine to
90. 3:14scan the entire table for every sale.
91. 3:16Every sale. With 50 registers, that's 50
92. 3:19full scans at once. The first fix is the
93. 3:22correct composite index. Order matters.
94. 3:25First the product, because it's what you
95. 3:27always filter by equality. Then the date
96. 3:29, because it's the range. If you reverse
97. 3:31it, the engine traverses almost the
98. 3:33entire index looking for your product.
99. 3:35And with "include," we store the type
100. 3:37and quantity inside the index so we
101. 3:38don't even have to touch the table.
102. 3:41Creating the index on 1 million rows
103. 3:43took 400 milliseconds, less time than it
104. 3:45takes for your coffee to cool down. Look
105. 3:47at what happens. And the drop in
106. 3:49response time is immediate. Index only
107. 3:52scan. From 27.8 milliseconds to 0.18.
108. 3:56That works because vacuum analyze lets
109. 3:58the engine know that the pages are clean
110. 4:00, so it can read only the index without
111. 4:03opening the table.
112. 4:04And if the video ended here, it would be
113. 4:07just another "add an index" tutorial,
114. 4:09but look at something almost no one
115. 4:11tests.
116. 4:11That product 42 is just any random
117. 4:14product. Now test it with the hammer,
118. 4:16the best-selling product. 200,000
119. 4:18movements, 1/5 of the entire table. With
120. 4:21the perfect index, the plan for that run
121. 4:24is still at 28 milliseconds. 28.
122. 4:27It gives you the same number as before
123. 4:29the index, because the index finds the
124. 4:31movements quickly, but it still has to
125. 4:33sum 200,000 every time someone goes to
126. 4:35checkout.
127. 4:36Today it's 200,000. If your business
128. 4:38doubles its sales, it will be twice as
129. 4:40much, and the query grows along with
130. 4:42your business. The better you do, the
131. 4:44slower it responds. This is where the
132. 4:46index, which seemed like the final
133. 4:48answer, falls short.
134. 4:50Even with indexes, summing thousands of
135. 4:52historical entries and exits for a
136. 4:54high-turnover product is still slow, and
137. 4:56it's exactly what every register queries
138. 4:58at the same time.
139. 4:59There is the error that almost no one
140. 5:01sees. It wasn't the query; it was the
141. 5:03question. Why recalculate the entire
142. 5:05history every time if history doesn't
143. 5:07change? That's how accounting does it.
144. 5:10We implement a balance or snapshot table
145. 5:12, a product, a cutoff date, and the
146. 5:15balance frozen up to that day. They are
147. 5:17daily or monthly balance closures, just
148. 5:20like in accounting. With a single
149. 5:22command, 168,000 monthly cutoffs were
150. 5:25created in 1.2 seconds.
151. 5:27In production, a scheduled job performs
152. 5:29that closure on the first of every month
153. 5:31.
154. 5:32The trick is to calculate inventory by
155. 5:34adding only the movements that occurred
156. 5:36after the last closed cutoff. Not the
157. 5:39million, just what's recent. This is the
158. 5:41function. It queries the last snapshot
159. 5:44and adds the recent difference. First
160. 5:46the last closed balance, then only the
161. 5:49movements since that date. The rest
162. 5:51isn't even touched.
163. 5:53The query no longer loads the entire
164. 5:55product history, it only loads what's
165. 5:56recent.
166. 5:58The balance doesn't get outdated as long
167. 6:00as you follow one rule. The cutoff only
168. 6:02freezes the closed past. Everything that
169. 6:04happens after the cutoff is added live,
170. 6:07so a sale from a minute ago appears
171. 6:09immediately. The price is a scheduled
172. 6:11task that closes each month and one
173. 6:13clear rule. What's closed isn't edited,
174. 6:15it's corrected with a new movement.
175. 6:18The hammer, now measured over 30 queries
176. 6:20and with the median; the 28 from before
177. 6:23were a single explain run. The
178. 6:25historical sum with an index, 14.7
179. 6:28milliseconds; the cutoff balance, 0.2.
180. 6:31The exact same stock, 65 times faster.
181. 6:35If this type of video with measured
182. 6:37numbers rather than theory helps you,
183. 6:39subscribe. There is more of this here
184. 6:41every week. But a quick query in a
185. 6:43terminal is not a real cash register.
186. 6:46On a Saturday at 6 p.m., one customer
187. 6:48doesn't walk in; 50 walk in at once and
188. 6:50they all ask for the hammer. That is
189. 6:52where systems truly crash. What matters
190. 6:54is what happens when all the registers
191. 6:57arrive at once. A nice average in an
192. 6:59individual test proves nothing. For that
193. 7:02, a load test simulating 50 simultaneous
194. 7:04requests with Locust.
195. 7:0650 registers. Each one queries the
196. 7:09hammer every half second. It is exactly
197. 7:11what a Saturday afternoon does to your
198. 7:13system. With everyone asking for the
199. 7:14same thing at the same time. The live
200. 7:17benchmark, first without an index. On
201. 7:19the graphs, response times skyrocket and
202. 7:21the CPU has almost no room to breathe.
203. 7:24Now, afterwards, cutoff balance, the
204. 7:26same 50 registers. The times hit the
205. 7:28floor and there isn't a single failure.
206. 7:31The comparison of the numbers. A
207. 7:32clarification, the median is what the
208. 7:34typical customer experiences. The worst
209. 7:365%is the one that complains out loud.
210. 7:39Without an index, a median of 910
211. 7:41milliseconds, the worst 5%at 1100, and
212. 7:44the CPU at 92%.
213. 7:47With the CPU like that, one more
214. 7:48checkout and the whole system queues up
215. 7:50.
216. 7:51The cashier sees the line grow and the
217. 7:53customer sees a spinner with a 240ms
218. 7:56index, 350 in the worst case, and 51%
219. 7:58CPU.
220. 8:00With a running balance, a 5ms median, 16
221. 8:03in the worst case, and CPU at 1%.
222. 8:07Same server, same million rows. The CPU
223. 8:10went from 92 to one. That means you no
224. 8:12longer need a more expensive server.
225. 8:14Your current one is more than enough,
226. 8:16with room for other queries. And so the
227. 8:18checkout from the beginning returns. The
228. 8:20same cashier, the same hammer, 12
229. 8:22milliseconds including browser network
230. 8:25time. Of course, three best practices so
231. 8:27a database doesn't freeze in a busy
232. 8:30business. Let the base calculate, let
233. 8:32the index respect your filter order, and
234. 8:35keep a running balance for what's
235. 8:37queried all day.
236. 8:38Between one checkout and the other, the
237. 8:40hardware is the same.
238. 8:42What changed is our understanding of
239. 8:43what the database does with every
240. 8:45question we ask it.
241. 8:46The customer no longer watches the
242. 8:48spinner; they watch the cashier handing
243. 8:50them the product. But there is a trap
244. 8:52worse than a slow query. You can have
245. 8:54the checkout at 12 milliseconds and the
246. 8:56number on screen can still be wrong. If
247. 8:58the logic for entries and exits is
248. 9:00poorly designed from the start, the
249. 9:02stock on screen never matches the shelf
250. 9:04. To make sure that doesn't happen to
251. 9:05you, go watch this video I’m leaving on
252. 9:07screen. Inventory errors that no one
253. 9:09tells you about in the hardware store.
254. 9:11There are the most common practical
255. 9:13inventory errors, the ones no
256. 9:14programming course teaches you. It's on
257. 9:17screen. Click it.