# Gateway Connection Timeouts

> Timeouts at the gateway.

Canonical URL: <https://datadriven.io/problems/gateway_connection_timeouts>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

During an on-call investigation, find all error tracking entries where the service name contains 'gateway' and the message contains the phrase 'timed out'.

## Worked solution and explanation

### What this is really asking

`err_tracks` is 20M rows partitioned by `first_at`, but the filter is two unanchored `LIKE` substrings on `svc_name` and `message`. Neither touches the partition key, so every partition is read.

---

### Break down the requirements

#### Step 1: Match service substring

`svc_name LIKE '%gateway%'` lets `api-gateway-prod`, `gateway-eu`, and `payments-gateway` all qualify. Leading wildcard disables any btree on the column.

#### Step 2: Match message substring

`message LIKE '%timed out%'` matches the phrase anywhere. Equality returns nothing because real lines carry prefixes like `upstream connection timed out after 5s`.

#### Step 3: AND, not OR

Both clauses must hold. `OR` would return every gateway error plus every timeout from any other service.

---

### The solution

**GATEWAY TIMEOUT ERROR ENTRIES**

```sql
SELECT *
FROM err_tracks
WHERE svc_name LIKE '%gateway%'
  AND message LIKE '%timed out%'
```

> **Cost Analysis**
>
> Full scan across every `first_at` partition. Both `LIKE` patterns are leading-wildcard, so btrees are useless. A trigram (`pg_trgm`) GIN on `message` is the only structure that helps; otherwise bound `first_at` on-call.

> **Interviewers Watch For**
>
> Ask whether `LIKE` is case-sensitive here. Postgres `LIKE` is, and logs often emit `Timed Out` or `TIMED OUT`. Switching to `ILIKE` is the safe call; naming the assumption beats picking silently.

> **Common Pitfall**
>
> Forgetting the wildcards: `WHERE message LIKE 'timed out'` becomes equality and returns zero rows. The `%` on both sides is what turns it into a substring search.

> **The False Start**
>
> First instinct is to bound by `first_at >= NOW() - INTERVAL '1 hour'` so the scan is cheap. That changes the answer; the prompt says find all entries. Pivot to the unbounded scan and only add a time window if asked.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you make this case-insensitive? _(Pushes to `ILIKE` in Postgres or `LOWER(message) LIKE '%timed out%'` elsewhere.)_
- Also match the word `timeout` (no space). _(Forces a regex like `message ~* 'timed?\s*out'` or two `LIKE` clauses joined with `OR`.)_
- Make this query fast on 20M rows. _(Opens trigram GIN, partition pruning, or shipping log search to OpenSearch.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/gateway_connection_timeouts)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.