# It seems to be affecting our largest customers first ## Introduction ### Warm-up 1️⃣ **Have you ever had a production issue where a query suddenly became very slow?** What was the root cause, and how did you investigate it? 2️⃣ **What would you do if a critical dashboard endpoint started timing out during peak hours?** Describe the first three steps you would take to diagnose the problem. 3️⃣ **Have you ever dealt with rapid data growth in a database?** What strategies did you use (or would you use) to prevent performance degradation over time? * * * ## Speaking ### Dialog **Bill:** Hey, we’re `having an issue` in production. The `dashboard endpoint` is `timing out`. Users are reporting that it `either` takes forever to load or returns a 504 error. It seems to be affecting our largest customers first. **Patrick:** Alright. Do you have the exact query that the endpoint is `running`? And what’s the current `average execution time` compared to normal? I’d also like to know if this is happening consistently or only `during peak hours.` **Bill:** Yes, I `pulled it from` the logs. It used to run in about 200 milliseconds. Since this morning, it’s fluctuating between 8 and 12 seconds. The slow query log confirms the `spike`. **Patrick:** That’s a `significant degradation`. Did anything change recently? Any schema modifications, deployments, or noticeable `data growth`? Performance issues usually correlate with some recent change. **Bill:** We deployed a migration yesterday. We added a new column and updated some constraints. Also, the transactions table grew quite a bit this week due to a marketing campaign. We inserted a large `batch of records` over the weekend. **Patrick:** That could definitely impact performance. Let me check the execution plan. Did you already run an EXPLAIN or EXPLAIN ANALYZE? The actual execution plan will tell us where the time is being spent. **Bill:** I ran EXPLAIN, but I’m not sure how to interpret it. It showed high cost numbers and a lot of rows being processed. **Patrick:** No problem. I’m looking at it now. I can see it’s performing a `full table scan` on the transactions table. That explains the increase in latency. It’s scanning millions of rows before applying the filter. * * * * * * ### Vocabulary \[having an issue, `timing out,` dashboard endpoint, either, run a query, during peak hours, average execution time, data growth, pulled it from, spike, significant degradation, batch of records, full table scan \]

Expression	Portuguese	Explanation (English)	Example Sentence
having an issue	estar enfrentando um problema	Experiencing a problem or difficulty.	We’re having an issue with the payment gateway in production.
timing out	exceder o tempo limite	When a system stops waiting for a response because it took too long.	The API request is timing out after 30 seconds.
run a query	executar uma consulta	To execute a database command to retrieve or manipulate data.	I ran a query to check how many users signed up today.
data growth	crescimento de dados	The increase in the volume of stored data over time.	Rapid data growth can impact database performance.
average execution time	tempo médio de execução	The typical time it takes for a query or process to complete.	The average execution time increased from 200ms to 10 seconds.
dashboard endpoint	endpoint do painel (dashboard)	A backend route or API that provides data to a dashboard interface.	The dashboard endpoint aggregates metrics from multiple services.
during peak hours	durante o horário de pico	The busiest time period when system usage is highest.	The system becomes slower during peak hours.
pulled it from	extrai / peguei de	Retrieved information from a specific source.	I pulled it from the application logs.
spike	pico / aumento repentino	A sudden and sharp increase.	We saw a spike in database load after the deployment.
batch of records	lote de registros	A large group of records processed together.	The job inserted a batch of records overnight.
significant degradation	degradação significativa	A noticeable decline in performance or quality.	There was a significant degradation in response time.
full table scan	varredura completa da tabela	When the database reads every row in a table to find matching results.	Without an index, the database performs a full table scan.---

* * * * * * ## Exercises ### Reading Comprehension Questions 1. What was the **original average execution time** of the query, and how much did it increase? 2. Why did Patrick suspect that the issue might be related to a recent change? 3. What caused the **full table scan**, and why is it problematic for large tables? 4. How did **data growth** contribute to the performance issue? * * * ### Fill-in-the-Blanks Use the words below to complete the sentences: **Word Bank:** (having an issue, timing out, run a query, data growth, average execution time, dashboard endpoint, during peak hours, pulled it from, spike, batch of records, significant degradation, full table scan) * * * 1. We are \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ with the reporting service in production. 2. The API keeps \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ after 30 seconds. 3. I decided to \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ to check the number of failed transactions. 4. The system became unstable due to rapid \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ over the past month. 5. The \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ increased from 200ms to 10 seconds. 6. The \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ is responsible for loading the metrics page. 7. The slowdown is worse \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ when more users are online. 8. I \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ the execution plan from the database logs. 9. We noticed a sudden \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ in CPU usage. 10. The job inserted a large \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ over the weekend. 11. There was a \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ in performance after the migration. 12. Because there was no proper index, the database performed a \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_. * * * ### Role-Play Activity Use the words below to create one role-play (Conversation between two people). **Word Bank:** (having an issue, timing out, run a query, data growth, average execution time, dashboard endpoint, during peak hours, pulled it from, spike, batch of records, significant degradation, full table scan) * * * ## Done * * *