
# When Your SDK Does Too Much (And Not Enough)

## What I Was Working On

We have a shared SDK gem that multiple apps use to talk to a third-party assessment API. The main app needed to start creating assessments directly (instead of routing through another service), so I needed to add write operations to the SDK client.

Sounds simple enough, right? Add some methods, ship it. But when I actually looked at the existing client, things got interesting.

## The Problem

The old client was doing two things at once — signing the API request (authentication) and sending it — all crammed into one method. There was no way to control what "action" (read vs. write) the request was signed with, because it was hardcoded. The HTTP layer was raw `Net::HTTP` with zero retry logic, so if the API rate-limited us (HTTP 429), we'd just... fail silently.

For read-only operations, this worked fine. Nobody noticed because reads are forgiving. But now that we needed writes, the lack of control over the signing action was a real problem — signing a write request as a read meant the API would reject it, and signing a read as a write could accidentally mutate data.

## How I Found It

I started by just trying to add the write methods. The first attempt was to bolt them onto the existing client, but I immediately hit a wall: the single `post()` method was signing everything with a hardcoded `'get'` action. There was no way to say "this is a write, sign it as `'set'`" without changing the method signature.

That led me down a rabbit hole of "wait, what else is wrong here?" and I found:
- No retry on rate limits (the API docs literally say "you will get 429'd, handle it")
- No structured error handling — errors were swallowed or raised as generic exceptions
- The HTTP transport and payload signing were tightly coupled — you couldn't test one without the other
- A scoring endpoint that explicitly requires a `'get'` action was being called with `'set'` after refactoring, which broke it with a cryptic 404

That last one was fun to debug. The API returned "Route not found" for a valid path, which made zero sense until I read the docs more carefully and found that the action parameter actually affects routing, not just auth.

## The Fix

I split the client into two clear responsibilities:

1. **`sign(data, action)`** — takes your payload and an explicit action (`'get'` or `'set'`), returns a signed request with security parameters. That's it. No HTTP, no side effects.

2. **`post(path, signed_data)`** — takes the path and the already-signed data, sends it via HTTP. Handles retry, rate limiting, error parsing.

For the HTTP layer, I swapped `Net::HTTP` for Faraday with middleware:
- Automatic retry on 429 with exponential backoff (2s, 4s, 8s, up to 3 retries)
- `raise_error` middleware so HTTP failures become proper exceptions
- JSON response parsing built in

The tricky part was backward compatibility. There were already a bunch of callers using the old pattern: `client.post(path, raw_unsigned_data)`. If I just shipped the new client, all those callers would break because they weren't signing first.

The solution: in `post()`, check if the incoming request already has a `security` key (meaning it's been signed). If not, auto-sign it with `'get'` — which matches what the old client was doing implicitly. Added a comment marking it for removal once all callers migrate.

```ruby
def post(path, request)
  request['consumer_key'] ||= @credentials[:consumer_key]
  # backward compat: auto-sign if caller hasn't signed yet
  request = sign(request, 'get') unless request.key?('security')
  conn.post(path, request)
end
```

This way, old code keeps working, and new code explicitly calls `sign()` first with the correct action.

## Results

| Aspect | Before | After |
|--------|--------|-------|
| Rate limit handling | None (silent failure) | Auto-retry 3x with backoff |
| Signing control | Hardcoded `'get'` | Explicit `'get'` or `'set'` per call |
| Read/write separation | Not possible | Caller chooses action |
| Error handling | Raw exceptions | Faraday errors + structured logging |
| Backward compatibility | N/A | Old callers work unchanged |

Tested both the new explicit-sign pattern and the old raw-data pattern against the live API via console — all methods (reads, writes, scoring, duplicating) passed.

## What I Learned

- **Separate signing from transport.** These are two different concerns. When they're coupled, you can't control either one properly. The caller should decide *what* action to sign with, and the client should handle *how* to send it.

- **Backward compatibility isn't optional for shared libraries.** If your gem is used by multiple apps, you can't just change the API and expect everyone to update simultaneously. Build a bridge, document it, plan for removal.

- **Read the API docs on actions, not just endpoints.** Some APIs use the "action" parameter for more than just auth scoping — it can affect routing, validation, and what the endpoint actually does. A 404 on a valid path might mean your *action* is wrong, not your URL.

- **Faraday middleware order matters.** `raise_error` before `json` means you get raw error bodies. `json` before `raise_error` means errors are parsed before being raised. Small difference, big impact on debugging. Match your test middleware stack to production or you'll chase phantom bugs.

- **Auto-sign fallbacks should match the old default.** When I first added the backward-compat auto-sign, I used `'set'` as the default action (because the new methods were writes). Broke every existing read caller. The old client implicitly used `'get'` — so the fallback had to match that.

## TL;DR

Refactored a shared API client to separate payload signing from HTTP transport, added Faraday with retry/rate-limiting, and built backward compatibility into the breaking change so existing callers keep working during migration.

---

*The scariest part of refactoring a shared library isn't the refactoring — it's all the callers you can't see.*


When Your SDK Does Too Much (And Not Enough)

# When Turbo Stream Replace Kills Your Search Input

## What I Was Working On

I was adding some summary statistics to a data table page — think averages displayed at the top of a list of records. The page already had a search bar and sort controls, and the table updated dynamically via Turbo Streams whenever you searched, sorted, or changed a filter.

The feature itself was straightforward: compute some averages on the backend, render them above the table. Sounds simple enough, right?

## The Problem

After shipping the first version, I noticed something maddening: every time you typed a character in the search bar, the cursor would jump back to the beginning. You'd type "Jo" looking for "John" and end up with "oJ" because the cursor reset after each keystroke.

The search was working — results were filtering correctly — but the typing experience was completely broken. Every keypress triggered a debounced form submission (Turbo Stream request), and when the response came back... the search input got nuked and replaced with a fresh one.

## How I Found It

The culprit was obvious once I looked at the Turbo Stream template:

```erb
<%= turbo_stream.replace "big_container" do %>
  
<% end %>
```

One big `turbo_stream.replace` was swapping out an entire container that held three things:
1. The summary metrics
2. The search form (with the text input)
3. The data table

When the response came back, the browser replaced the entire DOM subtree — including the `<input>` the user was actively typing in. New input, no cursor position, no selection state. Gone.

This was actually a consequence of a refactoring decision. Previously, the metrics and search controls lived in a separate header partial rendered by a different controller. To consolidate everything under one controller (so the data didn't need to be loaded in two places), I moved the search form and metrics inside the same container as the table. Good for architecture, bad for the DOM.

## The Fix

The fix was to **stop replacing everything in one big chunk** and instead target only the parts that actually change:

1. **Extract the table** into its own partial with its own DOM id
2. **Wrap the metrics** in a stable container div with its own DOM id
3. **Leave the search form in between** — not targeted by any Turbo Stream action

The Turbo Stream response went from this:

```erb
<%= turbo_stream.replace "big_container" do %>
  
<% end %>
```

To this:

```erb
<%= turbo_stream.update "metrics_container" do %>
  
<% end %>

<%= turbo_stream.replace "data_table" do %>
  
<% end %>
```

The search form sits in the DOM between `metrics_container` and `data_table`, untouched by either Turbo Stream action. Type away — the cursor stays put.

I also used `turbo_stream.update` (not `replace`) for the metrics container. The difference matters: `replace` swaps the entire element including its attributes, while `update` only changes the innerHTML. Since the metrics container had Stimulus `data-*` target attributes on it, using `replace` would have stripped those attributes and broken the JavaScript behavior. `update` preserves the outer element.

## Results

| Before | After |
|--------|-------|
| Cursor resets on every keystroke | Cursor stays in place |
| One `replace` for entire container | Two targeted stream actions |
| Search form re-rendered on every update | Search form never touched |

The initial HTML page load still renders everything together (metrics + form + table) from one controller action. Only the Turbo Stream response is granular — updating just the pieces that change.

## What I Learned

- **Turbo Stream `replace` replaces the entire element** — including any form inputs the user is interacting with. If your user is typing in something inside the replace target, their input state is gone.

- **Design your DOM ids around what changes independently.** If metrics update but the search form doesn't, they need separate containers. Don't group unrelated UI into one replaceable chunk just because they're visually adjacent.

- **`update` vs `replace` matters for Stimulus.** `replace` swaps the whole element, so any `data-*` attributes (including Stimulus targets) disappear. `update` only changes innerHTML and preserves the outer element's attributes.

- **Consolidating controllers doesn't mean consolidating DOM targets.** It's fine to have one controller serve all the data, but the Turbo Stream response should be surgical — update only what changed.

- **Extract partials along replacement boundaries.** If you're going to `turbo_stream.replace "some_id"`, there should be a partial that renders exactly that `some_id` element. This makes the initial render and the stream update use the same partial, keeping them in sync.

## TL;DR

If your search input loses cursor position during Turbo Stream updates, you're probably replacing a container that includes the input. Split your Turbo Stream targets so the search form is never inside a `replace` boundary — update the metrics and table separately, leave the form alone.

---

*The architectural instinct to consolidate is usually right. But the DOM doesn't care about your architecture — it cares about which nodes get swapped.*

When Turbo Stream Replace Kills Your Search Input


# The Unbounded Query That Took Down an Endpoint

## What I Was Working On

We have a feature where users can upload photos to their profile. There's a "See All" button that takes you to a page listing all the photos for that profile. Simple enough — click a link, see your photos.

Except the page was crashing in production with 504 gateway timeouts.

## The Problem

I pulled up the trace in our APM tool and saw the endpoint was taking over 30 seconds — way past the gateway timeout. The response never even made it back to the user.

When I looked at the controller code, I found this gem:

```ruby
def index
  @items = Item.all
end
```

That's it. No scoping. No filtering. Every single item in the entire database, loaded into memory, on every request. The table had thousands of rows across all accounts, and the view was rendering each one with its associated file attachments — triggering an avalanche of N+1 queries.

The local dev logs told the story clearly:

```
Number of queries: 228
  72 times ran query called "Attachment Load"
  71 times ran query called "ReadableItem Exists?"
  60 times ran query called "Blob Load"
Completed 200 OK in 3109ms
```

228 queries for a page that should show maybe 3-5 photos. Classic.

## How I Found It

The APM trace pointed directly at the endpoint — it showed a 504 timeout with the resource path clearly in the URL. From there, I checked the controller and immediately spotted `Item.all`. The funny thing is, the "See All" link that navigates to this page *already passes* a `profile_id` parameter in the URL. The controller just... never used it.

So the data was being sent, but nobody was listening.

## The Fix

The fix was straightforward — actually use the parameter that's already being passed:

```ruby
def index
  @items = if params[:profile_id].present?
               @profile = Profile.find(params[:profile_id])
               Item.where(folder: @profile.folders)
                   .includes(:folder, file_attachment: :blob)
                   .order(position: :asc)
             else
               Item.none
             end
end
```

Three key changes:
1. **Scope the query** — only load items belonging to the requested profile's folders
2. **Eager load associations** — `.includes(:folder, file_attachment: :blob)` eliminates the N+1 queries for attachments and blobs
3. **Safe default** — return nothing when no profile is specified, instead of everything

But fixing the controller surfaced two more issues I didn't expect:

**Orphaned file attachments:** Some items had attachment records pointing to blobs that no longer existed. The old code assumed every non-PDF file had a valid blob and tried to generate a thumbnail, which blew up with `Nil location provided. Can't build URI.` I added a guard to check both `file.attached?` AND `file.blob.present?` before rendering.

**Turbo Frame mismatch:** The "See All" link lived inside a Turbo Frame on the profile page. When clicked, Turbo intercepted the request and expected the response to contain a matching frame — but the items index page didn't have one. The fix was adding `data-turbo-frame="_top"` to tell Turbo to do a full page navigation instead of trying to swap frame content.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Total queries | 228 | 30 |
| N+1 attachment loads | 72 | 2 |
| N+1 blob loads | 60 | 0 |
| ActiveRecord time | 240ms | 167ms |
| Total response time | 3,109ms | 2,296ms |
| Production result | 504 timeout | Fast load |

And remember — this was on a small local dataset. In production with thousands of items, the before case was exceeding 30 seconds.

## What I Learned

- **Always check if an "all" query is actually what you want.** If a page is designed to show a subset of data, make sure the query reflects that. `Model.all` in a controller should be a red flag.
- **Parameters being passed doesn't mean they're being used.** The scoping parameter was right there in the URL the whole time. Someone built the link correctly but never updated the controller to use it.
- **Fixing one thing often surfaces others.** The scoping fix was clean, but testing it revealed orphaned data and a Turbo Frame issue. Always manually test the full flow after a fix.
- **Eager loading and scoping are a package deal.** Scoping gets you fewer rows. Eager loading gets you fewer queries per row. You usually want both.
- **Turbo Frames are invisible until they break.** If a link inside a Turbo Frame navigates to a page that doesn't have the matching frame, Turbo silently discards the response. The `_top` directive is the escape hatch.

## TL;DR

An endpoint was loading every row in a table because the controller used `Model.all` instead of scoping by the profile parameter that was already being passed in the URL. Fixed by scoping the query, adding eager loading, and handling two secondary issues (orphaned file attachments and a Turbo Frame mismatch). Went from 228 queries and production timeouts to 30 queries and fast page loads.

---

*Sometimes the fix is just... using the parameter that's already there.*


The Unbounded Query That Took Down an Endpoint


# The N+1 That Was Hiding Next to Its Own Fix

## What I Was Working On

We had a dashboard that shows each user a summary of recent activity — completed work, overdue items, upcoming tasks, and so on. It pulls data from several categories across all of that user’s active **subscriptions** (think: one row per product or plan the user is enrolled in). Everything rolls up into a single “home” view.

## The Problem

A performance alert showed the dashboard running about **48 separate database queries** on every load: one per subscription. It showed up thousands of times across a large share of active users and had been that way for months.

The query itself was fine — a lookup for the latest metadata record per subscription. The issue was that it ran **inside a loop**. Classic N+1.

## How I Found It

The error-tracking tool pointed at the controller action and showed the repeated query pattern. Tracing the call chain:

1. Controller builds “completed items” data.
2. A method loops over all subscriptions.
3. Each iteration fetches that subscription’s completed items.
4. That path calls a metadata resolver — **database hit per subscription**.

The ironic part: **Right above**, in the same file, an almost identical method for **“overdue items”** was **already fixed** — batch preloading had been added months earlier. The completed-items path never got the same change. They were about **15 lines apart**.

The plumbing was already there too: a class method that batch-loads metadata for every subscription in **one query** (PostgreSQL `DISTINCT ON`), and a resolver that checks **“if preloaded data exists, use it; otherwise query.”** Nothing was passing preloaded data for this call site, so it always fell through to per-row queries.

## The Fix

Three pieces:

**1. Load once before the loop.** Call the batch preloader up front so you get a hash like `{ subscription_id => result, ... }`.

**2. Thread it through.** Pass that hash down so the resolver can use it and skip the DB.

**3. Fill in missing keys.** The preloader only returned rows for subscriptions that **had** metadata. If there was no row, that ID wasn’t in the hash. The resolver treated “key missing” as “go query again” — so you still N+1’d on empties. **Fix:** Initialize the hash with `nil` for every requested ID, then merge real results. “No data” becomes `nil` with a present key, not a missing key.

Without (3), you might drop from 48 queries to ~18 instead of **1**.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Queries per page load | ~48 | 1
| Pattern | N+1 (one per subscription) | Single batch query |

There was also short-lived caching per subscription, which **hid** the N+1 most of the time. On cold cache—first load, expiry, or after a deploy—all 48 queries ran together. Batch preload fixes the worst case.

## What I Learned

- **When you spot a pattern, check sibling methods.** If N+1 was fixed in one branch, a twin branch may still be broken.
- **Preload hashes need explicit missing keys.** If the resolver uses `hash.key?(id)` and only “successful” IDs are in the hash, every empty case falls through to a per-item query.
- **Caching can mask N+1s.** Tools that flag repeated query patterns help catch what steady-state traffic doesn’t.
- **Reuse existing batch loaders** before building new ones. Often the fix is wiring and one edge case (missing keys).

## TL;DR

The dashboard ran ~48 queries (one per subscription) for completed-item metadata. Batch preloading already lived next door for overdue items. Reused the same pattern, fixed the preloader so empty subscriptions didn’t trigger fallback queries, and reduced the path to **1** query.

---

*Sometimes the best fix is noticing someone already solved the same problem next door.*


The N+1 That Was Hiding Next to Its Own Fix


# Why EXISTS Beats JOIN When You Just Need to Filter

## What I Was Working On

I had an API endpoint that returns membership data. Each membership belongs to a user, and each user can have multiple roles in the system (viewer, editor, admin, etc.). The API needed to:
1. Filter memberships by a user's external ID (stored in a separate roles table)
2. Include that user's external ID in the JSON response

Sounds simple enough, right?

## The Problem

Two things were making these endpoints slow:

**Problem 1: Row explosion from JOIN**

To filter memberships by a user's role ID, the old code joined the memberships table with the roles table. The issue? A single user can have 3+ roles in the same account. So joining these tables turned 1 membership row into 3 rows. Then we had to slap `.distinct` on the query to collapse them back down to 1.

Think of it like this: you're looking for people who own a dog. You join a "people" table with a "pets" table. Someone with 3 dogs now appears 3 times. So you add DISTINCT to de-duplicate. But the database already did all the work of creating those extra rows, only to throw them away. With thousands of memberships, that's a lot of wasted work.

**Problem 2: N+1 for user IDs in the response**

For each membership in the JSON response, we needed to look up the user's external ID. The old code did this in the application layer — for each membership, it would call a method on the user object that queried the roles table. 200 memberships = 200 extra database round trips. Classic N+1.

We tried to fix this with eager loading, which batches the lookups into a few queries. But that meant loading *all* the user objects, *all* their role records, and *all* the role metadata into application memory, just to pluck out one ID per membership. Like ordering the entire menu when you just want a side of fries.

## How I Found It

Honestly, it was staring me in the face once I looked at the query chain. The `.joins(...).distinct` pattern is a well-known smell. And the JSON template calling a method that hits the database per-row is textbook N+1.

## The Fix

**For the filtering: EXISTS instead of JOIN**

Instead of joining the tables and then de-duplicating, I used an EXISTS subquery. The concept is simple — instead of saying "combine these tables and give me the matches," you say "for each membership, does a matching role record *exist*? Yes or no."

The database checks the roles table for a match, finds one, immediately stops looking, and moves on. No extra rows created, no DISTINCT needed. It's like checking if someone owns a dog by peeking at the pet registry, rather than bringing all their pets to the table and then sending the duplicates home.

**For the response data: correlated subselect**

Instead of loading user objects into the app and calling methods on them, I put a small subquery right in the SELECT clause. It basically says "while you're fetching this membership, also grab the user's external ID from the roles table." The database does it all in one trip.

It's like telling the waiter "I'll have the pasta, and also grab me a side of fries" vs. ordering the pasta, waiting for it to arrive, then calling the waiter back for fries, then calling again for a drink, then calling again for dessert...

## Results

| What changed | Before | After |
|---|---|---|
| Filtering strategy | JOIN + DISTINCT (creates extra rows, then removes them) | EXISTS (just checks, no extra rows) |
| User ID lookup | 4 separate queries + app-layer filtering | 1 query, database does everything |
| Data loaded into app | Users, roles, role metadata — thousands of objects | Just the membership rows with one extra column |
| Database round trips | 4 | 1 |

## What I Learned

- **If you're JOINing just to filter (not to use the joined data in SELECT), EXISTS is almost always better.** JOIN is for when you need columns from both tables. EXISTS is for when you just need to know "does a match exist?"

- **DISTINCT is a code smell when paired with JOIN.** If you need DISTINCT after a JOIN, it usually means the JOIN created duplicates you didn't want. That's a sign you should be using EXISTS or a subquery instead.

- **The database is better at finding data than your application layer.** Loading everything into memory and filtering in your app feels natural in an ORM world, but the database has indexes, query planners, and decades of optimization for exactly this kind of work. Let it do its job.

- **Correlated subqueries aren't as scary as they sound.** "A query inside a query that runs for every row" sounds slow, but with proper indexes the database handles it efficiently. It's way faster than the alternative of loading everything into your application layer.

- **Ask yourself: am I bringing data to the code, or bringing the question to the data?** Bringing the question to the data (letting the DB handle it) is almost always faster.

## TL;DR

Was JOINing a table just to filter by it, which created duplicate rows and needed DISTINCT. Switched to EXISTS (just checks "does a match exist?" without creating extra rows). Also moved a per-row app-layer lookup into a SQL subselect so the database does it all in one trip instead of N+1 round trips.

---

*Sometimes the best optimization is just asking the database a better question.*


Why EXISTS Beats JOIN When You Just Need to Filter


# When Your Filter Logic Fights Your Archival Logic

## What I Was Working On

We have a dashboard where managers can filter by their active projects. The filter dropdown is supposed to show all the active projects they're currently assigned to. One particular type of manager — let's call them "primary managers" — was seeing 0 projects in the filter, even though they were clearly assigned to multiple active projects with active members.

## The Problem

A support ticket came in: "Manager can't filter by their projects. Shows 0 projects." We confirmed the user had active assignments, active members in those projects — everything looked fine from a data perspective. But the dashboard just showed nothing.

The weird part? They could still navigate to their projects from another tab. It was only the dashboard filter that was broken.

## How I Found It

I started by checking the obvious stuff — was the user's account in good standing? Were the assignments actually active? Was there a tenant mismatch? All clean.

Then I looked at the query building the filter list. It was doing something like:

```sql
SELECT projects.*
FROM projects
INNER JOIN groups ON groups.project_id = projects.id
WHERE projects.id IN (... active assignments ...)
  AND groups.archived = false
  AND projects.account_id = 123
```

See that `INNER JOIN` with `archived = false`? That's the culprit.

Turns out, there's a completely separate piece of logic that intentionally archives the discussion group for certain types of managers. These "primary managers" use a different kind of shared group (a "homeroom" style), so the per-project discussion groups get archived on purpose. Makes total sense for the discussion feature.

But the dashboard filter was using those same group records to determine which projects to show. Archived group = project disappears from the filter. The archival logic and the filter logic were fighting each other.

## The Fix

The fix was straightforward: for this type of manager, skip the group filter entirely. Their projects are already properly scoped by the assignment-based query upstream, which checks:

- Active manager assignment (correct role, active status, valid date range)
- At least one active member in the project
- Project status is active
- Correct tenant/account

That's more than enough filtering. The group archival status is about discussion visibility, not project validity.

```ruby
if primary_manager?
  scope
else
  scope.joins(:group)
       .where(groups: { archived: false })
end
```

For regular managers, we keep the existing behavior — archived groups should still hide those projects from the filter since they're truly done.

## Results

| Scenario | Before | After |
|----------|--------|-------|
| Primary manager dashboard | 0 projects shown | All active projects shown |
| Regular manager dashboard | Unchanged | Unchanged |
| Admin dashboard | Unchanged | Unchanged |

## What I Learned

- **Different concerns shouldn't share filter criteria.** Archival status for a discussion feature shouldn't determine visibility in a completely unrelated filter. When you add a filter condition, think about whether it could conflict with other intentional behaviors in the system.

- **`INNER JOIN` is a silent filter.** Every `INNER JOIN` is also a `WHERE EXISTS`. If the joined record doesn't exist or doesn't match your conditions, the parent row vanishes. This is especially dangerous when records can be intentionally removed or archived by other processes.

- **Start debugging from the query, not the data.** The data was perfect — all assignments were active, all members were active. The bug was in how the query assembled the results, not in the data itself.

- **User types matter in multi-tenant apps.** A feature that works perfectly for one user type can silently break for another if there's special logic that treats them differently. Always check: "does any other system modify the records this query depends on?"

## TL;DR

A dashboard filter used `INNER JOIN groups WHERE archived = false` to list projects. A separate feature intentionally archived those groups for a specific manager type. Result: those managers saw 0 projects. Fix: skip the group filter for that manager type since assignment-based scoping is already sufficient.

---

*The lesson here is really about coupling. Two features that seem unrelated — discussion group archival and dashboard filtering — were quietly coupled through a shared database record. When one feature did its job correctly, it broke the other.*


When Your Filter Logic Fights Your Archival Logic


# How PostgreSQL Actually Runs a Query

## Why I Went Down This Rabbit Hole

I was debugging a reporting query that kept timing out on a cold cache. The SQL itself was not especially exotic: scan a large event table, group rows by day and category, and count distinct users.

What slowed me down was not just the query. It was the fact that I did not have a good mental model for what PostgreSQL was actually doing under the hood.

So this post is the mental model I wish I had earlier.

## Two Kinds of Memory

The first useful thing to know is that PostgreSQL uses different types of memory for different jobs.

### `shared_buffers` -- The Shelf

`shared_buffers` is shared memory that all connections use together. Think of it like a shelf where PostgreSQL puts data it has already read from disk.

When a query needs rows from a table or an index, PostgreSQL checks whether those pages are already on the shelf. If they are, great. If not, it has to read them from disk first.

That is why the second run of the same query is often much faster than the first.

### `work_mem` -- The Workbench

`work_mem` is where PostgreSQL does active work: sorting, grouping, deduplicating, hashing, and building temporary structures while the query runs.

It is not shared. Each operation gets its own budget.

That one detail matters more than it sounds.

## The Simplified Flow

Here is a useful mental model for a large reporting query:

```text
1. Application cache check
   -> Hit? Return cached result. PostgreSQL does nothing.
   -> Miss? Continue.

2. PostgreSQL receives the query
   -> Query planner checks indexes and table statistics
   -> Chooses an execution plan

3. Read data
   -> If needed pages are in shared_buffers, read from memory
   -> Otherwise, load them from disk

4. Process data
   -> Sort, group, deduplicate, hash
   -> Each of these steps uses work_mem

5. Return result
   -> Optionally store it in application cache
```

The important part is step 4. Large queries are often slow not because the SQL is mysterious, but because one or more operations cannot stay in memory.

## Why `work_mem` Matters So Much

`work_mem` is allocated **per operation, not per query**.

So if one statement does both:

- a `GROUP BY`
- and a `COUNT(DISTINCT ...)`

that can mean multiple memory-hungry steps inside the same query. If those steps do not fit into their individual memory budgets, PostgreSQL spills to disk.

That is where things get slow.

## What Disk Spill Looks Like

When I started reading query plans more carefully, one of the most useful clues was this:

```text
Sort Method: external merge  Disk: 101984kB
```

That line means the sort could not stay in memory. PostgreSQL had to write temporary files to disk and finish the sort there instead.

Once you see that, the problem becomes much clearer:

- the query is not just "slow"
- the sort specifically is under-provisioned
- disk I/O is now part of the execution path

## A Practical `work_mem` Fix

One of the safest ways to test a higher memory budget is to use `SET LOCAL` inside the transaction that runs the heavy query:

```ruby
ActiveRecord::Base.connection.execute("SET LOCAL work_mem = '64MB'")
ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '30s'")
```

When the transaction ends, those settings revert automatically.

That makes `SET LOCAL` very different from changing the global server setting for every connection.

## Why Not Just Keep Raising It?

Because `work_mem` is per operation and per worker.

That means one query can multiply memory usage faster than you expect:

- one query
- multiple sort/hash steps
- parallel workers

A number that looks harmless for a single operation can get expensive when all of those stack together.

That is why the usual path is:

1. measure with `EXPLAIN ANALYZE`
2. raise memory conservatively
3. verify whether the query plan improves

## Parallel Workers

PostgreSQL can split a query across multiple processes.

For example:

- `max_parallel_workers_per_gather = 2`
- one main process plus two workers
- each process gets its own memory budget

That can make a query faster, but it also means memory math matters even more.

## `hash_mem_multiplier`

Newer PostgreSQL versions also use `hash_mem_multiplier` to give hash-based operations a bigger budget than sort-based ones.

That matters because spilling a hash operation to disk is often much worse than spilling a sort.

So when memory is tight, PostgreSQL may choose a sort-based plan because hashing would be even more expensive. Once memory increases, the planner may choose a different strategy.

## `log_temp_files`

One setting I wish more people knew about is `log_temp_files`.

It tells PostgreSQL to log whenever a query creates temporary files on disk.

```text
log_temp_files = -1   -> off
log_temp_files = 0    -> log every temp file
log_temp_files = 1024 -> only log temp files larger than 1MB
```

If this is off, disk spills can happen silently. If it is on, you get direct evidence that a sort or hash operation ran out of memory.

That turns guesswork into measurement.

## A Mental Checklist

When a large query feels mysteriously slow, these are the first questions I ask now:

- Is the query slow because the cache is cold?
- Is PostgreSQL reading from disk more than I expected?
- Is a sort or aggregation spilling because `work_mem` is too small?
- Is parallelism helping, or just multiplying memory pressure?
- Would the query plan improve if I tuned one operation locally instead of changing global settings?

## What I Learned

- **`shared_buffers` and `work_mem` solve different problems.** One helps PostgreSQL reuse data it already read. The other helps it do heavy work in memory.
- **`work_mem` is per operation, not per query.** That one fact explains a lot of confusing performance behavior.
- **`SET LOCAL` is a safe tuning tool.** It lets you test changes for one job without changing the whole database.
- **Disk spills are one of the clearest performance signals.** If a sort spills, PostgreSQL is telling you exactly where the bottleneck is.
- **`log_temp_files` is practical, not obscure.** It is one of the easiest ways to find memory-bound queries.

## TL;DR

PostgreSQL is not just "running SQL." It is deciding what to read from memory, what to read from disk, how much memory each operation gets, and whether it needs temporary files to finish the job. Once you understand `shared_buffers`, `work_mem`, parallel workers, and disk spills, slow query plans start to make much more sense.

---

*The useful shift for me was going from "this query is slow" to "I can explain where PostgreSQL is spending time, and why."*


How PostgreSQL Actually Runs a Query


# Why Index Only Scan Still Hits the Heap

## The Confusing Part

One of the most misleading lines in a query plan is `Index Only Scan`.

It sounds like PostgreSQL has everything it needs in the index and will never touch the main table. That is the ideal case. But it is not guaranteed.

If the plan also shows a large number of heap fetches, PostgreSQL is still going back to the table over and over again.

So the real question becomes:

Why does an Index Only Scan still need the heap?

## The Short Answer

Because PostgreSQL does not just care whether the index has the right columns. It also cares whether it can **trust** the index entry without checking the table row behind it.

That trust comes from the **visibility map**.

## The Visibility Map

PostgreSQL uses MVCC, which means updates and deletes do not immediately remove old row versions. A table page can contain a mix of live rows and dead rows.

The visibility map keeps track of which pages are fully safe:

- `all-visible` means every row on that page is visible to every transaction
- not `all-visible` means PostgreSQL may need to check the heap to confirm what is valid

If a page is not marked all-visible, PostgreSQL has to go back to the heap even during an Index Only Scan.

That is why this kind of output matters so much:

```text
Index Only Scan using some_covering_index
Heap Fetches: 3827231
```

The plan name says "index only," but the heap fetches tell you the real cost.

## Why Pages Stop Being All-Visible

This usually happens after:

- large updates
- deletes
- heavy backfills
- any workload that leaves a lot of dead tuples behind

When that happens, PostgreSQL can no longer trust the index alone. The visibility map becomes less useful, and heap fetches go up.

## How to Actually Inspect It

If you want to see the visibility map directly, PostgreSQL has an extension for that:

```sql
CREATE EXTENSION pg_visibility;

SELECT * FROM pg_visibility('analytics_events');
```

You can also get a summary view:

```sql
SELECT
  count(*) AS total_pages,
  count(*) FILTER (WHERE all_visible) AS visible_pages,
  count(*) FILTER (WHERE NOT all_visible) AS dirty_pages,
  round(100.0 * count(*) FILTER (WHERE all_visible) / count(*), 1) AS visible_pct
FROM pg_visibility('analytics_events');
```

That gives you a quick answer to a useful question:

How much of this table is actually clean enough for true index-only reads?

## What VACUUM Has To Do With It

`VACUUM` is what cleans up dead tuples and refreshes visibility information.

That is why heap fetches are often not just an index problem. They can also be a vacuum problem.

If a table has not been vacuumed recently enough, PostgreSQL may keep using the index but still pay the cost of checking the heap for row visibility.

## VACUUM vs VACUUM FULL

These are not interchangeable tools.

| | VACUUM | VACUUM FULL |
|--|--------|-------------|
| What it does | Cleans dead rows, updates visibility map | Rewrites the whole table |
| Locking | Normal reads and writes can continue | Exclusive lock |
| Production use | Usually safe | Needs real caution |

If you are trying to improve Index Only Scan behavior, regular `VACUUM ANALYZE` is usually the right place to start.

## When VACUUM Still Does Not Solve It

There are a couple of cases where you can vacuum and still see more heap fetches than you want.

### Long-Running Transactions

If an old transaction is still open, PostgreSQL may not be allowed to mark pages all-visible yet. The old row versions might still matter to that transaction.

So a long-running transaction can quietly reduce the benefit of an otherwise good index.

### PostgreSQL's Index Cleanup Optimization

PostgreSQL can skip index cleanup when the amount of dead data is small enough that a full index pass does not look worth it.

That is usually a good optimization. But it can also explain why heap fetches stay higher than expected after vacuuming.

If you need to force full cleanup, you can do:

```sql
VACUUM (INDEX_CLEANUP ON, ANALYZE) your_table;
```

Or make that behavior the default for a specific table:

```sql
ALTER TABLE your_table SET (vacuum_index_cleanup = on);
```

## A Small Detail I Did Not Know

PostgreSQL can also mark dead index tuples as it encounters them during scans. That means indexes can improve a little just by being read repeatedly.

VACUUM is still the main cleanup mechanism, but it is not the only one.

## What I Learned

- **A covering index is not the whole story.** PostgreSQL also needs clean enough pages to trust index entries without checking the heap.
- **Heap fetches are one of the best clues in a query plan.** They tell you whether "index only" is actually cheap.
- **The visibility map is a real performance feature.** It is not just internal trivia.
- **VACUUM affects read performance, not just storage cleanup.** That was the mental shift that made this click for me.
- **`pg_visibility` is worth knowing.** It gives you a direct way to verify what the database is doing.

## TL;DR

An Index Only Scan still hits the heap when PostgreSQL cannot trust the index entry on its own. That trust comes from the visibility map, and the visibility map depends heavily on vacuum health. So if heap fetches are high, the fix may not be "add another index." It may be "clean the table well enough that PostgreSQL can finally trust the one you already have."

---

*The surprising part was learning that the index could be technically correct and still not deliver the performance I expected.*


Why Index Only Scan Still Hits the Heap


# Why Autovacuum Defaults Stop Working on Large Tables

## The Trap

PostgreSQL's autovacuum defaults sound reasonable when you first read them.

Vacuum a table after a certain percentage of it has changed. Fair enough.

The problem is that percentage-based defaults age badly as tables grow.

On a small table, 10% churn is not a big deal. On a huge table, 10% can mean waiting far too long before cleanup happens.

## Why This Breaks Down

Imagine a table with hundreds of millions of rows.

If autovacuum waits for 10-20% of that table to change before cleaning it, you are no longer talking about a normal maintenance cycle. You are talking about millions or tens of millions of row changes before cleanup starts.

That delay can affect a lot of things:

- dead tuples accumulate longer
- visibility map quality gets worse
- Index Only Scans do more heap fetches
- query performance becomes less predictable

So even if autovacuum is technically enabled, it can still be effectively too late to help.

## Why the Default Is Not "Wrong"

The default exists because PostgreSQL has to work across many workloads.

Aggressive vacuuming is not free. It uses I/O, CPU, and maintenance bandwidth. So the defaults aim for broad safety, not perfect behavior for every large or write-heavy table.

That means the right conclusion is not "autovacuum is broken."

It is:

Large tables usually need table-specific settings.

## A Better Way To Think About It

Instead of asking:

"Is autovacuum on?"

Ask:

"Will autovacuum run early enough for this specific table?"

That is a much better question.

## Table-Specific Tuning

One practical approach is to lower the scale factor and set an explicit threshold for the tables that matter most.

```sql
ALTER TABLE analytics_events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000
);
```

The exact values depend on your write rate and table size, but the principle is simple:

- lower the percentage trigger
- add a fixed threshold
- tune the table that needs it instead of changing the whole server blindly

## One More Gotcha: Long-Running Transactions

Even a well-timed vacuum cannot fully help if an old transaction is still hanging around.

PostgreSQL may not be allowed to mark pages all-visible yet, because that transaction might still need to see old row versions.

So if vacuum seems less effective than expected, it is worth asking whether a long-running transaction is getting in the way.

## Why This Shows Up in Query Plans

Autovacuum tuning can feel like background maintenance work, but it shows up directly in query performance.

If a large table is not being vacuumed often enough:

- visibility map coverage stays worse
- heap fetches stay higher
- supposedly cheap plans stop being cheap

That is why this is not just a DBA topic. It changes how real application queries behave.

## The Long-Term Fix Is Often Not More Vacuum

Sometimes the right answer is not "vacuum harder."

If the application keeps asking for the same rollups from the same huge raw table, the long-term fix is often **pre-aggregation**.

Instead of recomputing the same summary from millions of rows on demand, you can store:

- daily totals
- per-category summaries
- cached rollups

Then the user-facing query reads a small summary table instead of the full event table.

That does not replace good vacuum settings, but it does reduce how much pressure you put on the raw table in the first place.

## What I Learned

- **Autovacuum defaults are general-purpose, not table-specific.** That is fine until one table becomes unusually large or hot.
- **Percentage thresholds get more expensive as tables grow.** The bigger the table, the more dangerous "wait for 10%" becomes.
- **Vacuum health affects read performance.** This is not just cleanup for cleanup's sake.
- **Large tables deserve explicit settings.** If a table matters to your application, tune it like it matters.
- **Pre-aggregation is often the more durable answer.** Maintenance helps, but reducing raw-query pressure helps even more.

## TL;DR

Autovacuum can be fully enabled and still be too slow for a very large table. The percentage-based defaults often wait too long before cleaning things up, which hurts visibility, heap fetches, and query performance. The practical fix is usually table-specific autovacuum settings, and the long-term fix is often to stop querying raw rows for every dashboard request.

---

*The useful shift here was realizing that "autovacuum is on" and "autovacuum is helping" are not the same thing.*


Why Autovacuum Defaults Stop Working on Large Tables


# Why My Analytics Dashboard Showed No Data

## What I Was Working On

I was working on an analytics dashboard that showed event data broken down by account and time range. One day, someone noticed it was showing "No Data" everywhere. Completely blank.

## The Problem

At first, I thought it might be a query timeout because the underlying table was fairly large. But when I checked locally with a tiny dataset, I saw the same thing. No data. That ruled out performance pretty quickly.

So I jumped into the Rails console and looked at recent event records. Every single one had `account_id = 0` and `category_name = nil`. The dashboard filters by account, so if every row says "account 0," nothing useful ever shows up.

## How I Found It

The event model had a `before_create` callback that was supposed to set `account_id` and `category_name`. That sounded reasonable. But the records were not being created one at a time through ActiveRecord. They were being buffered and then bulk-inserted using `insert_all`.

And `insert_all` **skips ActiveRecord callbacks**. No `before_create`, no `after_create`, nothing. So the callback that was supposed to populate those fields never ran.

This had probably been broken ever since the batch insert pipeline was introduced, but it went unnoticed because the dashboard was not heavily used yet.

## The Fix

**Part 1: Stop the bleeding.** I moved the `account_id` and `category_name` calculation upstream into the code path that captures the event, before it ever gets queued for batch insert. Now those values are part of the inserted hash from the beginning, and I removed the dead callback.

**Part 2: Backfill historical data.** I needed to fix about 246K records that had `account_id = 0`. So I wrote a maintenance task that finds those rows and enqueues background jobs to repair them.

This is where it got interesting.

## The Rabbit Hole: Backfilling at Scale

**Attempt 1: ID range stepping.** I grabbed the min and max IDs of the records needing repair and stepped through them in batches of 10,000. It worked perfectly locally. In production, the `MIN(id)` query timed out.

**Attempt 2: Replace MIN/MAX with ORDER BY + LIMIT.** Instead of `scope.minimum(:id)`, I used `scope.order(:id).limit(1).pick(:id)`. That used the primary key index and returned quickly. But then I realized the ID range was extremely sparse, so my range-based batching would create a huge number of mostly empty jobs.

**Attempt 3: Use `in_batches` with `pluck(:id)`.** Rails' `in_batches` pages through actual rows instead of walking empty ID space. That was much closer to the right approach. But when I tried putting it in the `collection` method so the maintenance task UI could show progress, the upfront query still timed out in production.

**Final approach: `in_batches` inside `process`.** I kept `collection` as `[1]` so nothing expensive happened up front, and moved the `in_batches` logic into `process`. Each iteration did a small indexed query, plucked the IDs, and enqueued a job. No full-table scan. No timeout.

## Results

| What | Before | After |
|------|--------|-------|
| `account_id = 0` in recent data | 246,280 | 0 |
| Analytics dashboard | "No Data" | Shows real data |
| New event records | Missing account/category | Correct from creation |

## What I Learned

- **`insert_all` skips callbacks.** If you need derived fields, they have to be present before the insert. Model callbacks will not save you.

- **What works locally can blow up in production.** Data volume changes the cost of even simple-looking queries.

- **ID ranges can be deceptive.** A reasonable record count can still map to a terrible batching strategy if the ID space is sparse.

- **Production databases have statement timeouts.** Even simple-looking queries can fail if they scan too much data.

- **Iterate toward production-safe code.** My first few attempts all had problems that only surfaced at real scale. That is normal.

## TL;DR

The dashboard showed "No Data" because bulk inserts were skipping the callback that populated key analytics fields. I fixed the write path by setting those values before `insert_all`, then rewrote the backfill a few times until it worked safely at production scale.

---

*Sometimes the real work starts after you find the bug, especially when you have to repair everything it already touched.*


Why My Analytics Dashboard Showed No Data


# The Day I Finally Used EXPLAIN ANALYZE in Production

## What I Was Working On

After fixing a data bug on an analytics dashboard, I cleared the cache so users would see fresh data. That's when things got interesting.

The largest dataset behind that dashboard has about 1.5 million pageview records in a 30-day window, and about 3.8 million over the full range. I cleared the cache, loaded the dashboard, and got a 500 error. Tried again, and it worked fine. That made it clear this was probably a cold-cache problem, not a permanently broken query.

## The Problem

The first dashboard load after clearing the cache was hitting our 20-second database timeout. The query behind the activity chart had to scan hundreds of thousands, sometimes millions, of rows, group them by date and role, and count distinct users. When nothing is cached, that means a lot of disk I/O all at once.

What confused me was that we already had a covering index for this query. On paper, it looked like the database had everything it needed.

## How I Found It

A teammate asked me to run `EXPLAIN ANALYZE` on the query in production. I had avoided it for too long because it always felt more intimidating than it should have. It turned out to be the fastest way to stop guessing.

So I ran it. Here's what I found for a 7-day window (~490K rows):

```
Index Only Scan using our_optimized_index (actual time=0.2..631 rows=489,867)
   Heap Fetches: 489,867
Sort (actual time=801..877 rows=489,867)
   Sort Method: external merge  Disk: 13184kB
Execution Time: 995ms
```

And for 30 days (~3.8M rows):

```
Index Only Scan using our_optimized_index (actual time=0.03..5190 rows=3,820,013)
   Heap Fetches: 3,827,231
Sort (actual time=7018..7471 rows=3,820,013)
   Sort Method: external merge  Disk: 101984kB
Execution Time: 8466ms
```

Two things jumped out at me immediately.

### Problem 1: Sorting Was Spilling to Disk

The first thing I noticed was `external merge Disk: 101984kB` on the 30-day query. That meant Postgres could not keep the sort in memory, so it spilled to disk. Even the 7-day query spilled about 13MB. Once I saw that, I stopped thinking "the query is kind of slow" and started thinking "the sort is definitely one of the bottlenecks."

The reason was simple: the default `work_mem` budget was too small for the amount of data being sorted. For this query, that meant Postgres had to create temporary files just to finish the operation.

The useful takeaway for me was that you can raise it just for one query:

```sql
SET LOCAL work_mem = '128MB';
```

That only applies to the current transaction, which makes it much safer than changing the global setting.

### Problem 2: The Index Was Not Saving Us as Much as I Expected

The second thing was more surprising. The plan said `Index Only Scan`, which sounded perfect. But the next line told a different story.

`Heap Fetches: 3,827,231` meant Postgres still had to go back to the main table millions of times. So yes, it was using the index, but not in the cheap way I thought it was.

That usually points to visibility map / vacuum state, not just index design. In other words, the index existed, but Postgres still could not fully trust it. That was a useful mental shift for me: having the right index is not always the same as getting the performance you expect from it.

### Bonus Discovery: Peak Hours Make Everything Worse

I ran `EXPLAIN ANALYZE` during off-peak hours and got 995ms for 7 days. During peak hours, the same query took 3,290ms. Same data, same query, same index. The only real difference was system load.

That was the moment it clicked for me: if a query takes 8.5 seconds off-peak, it does not take much for it to start timing out during busy hours.

## The Fix

The database-side fixes needed infrastructure or DBA help, so I focused on operational changes we could ship immediately:

1. **Extended cache TTL from 4 hours to 24 hours.**  
   This reduced how often users hit cold-cache paths during normal traffic.

2. **Added a cache warm-up cron at 3:00 AM Arizona time** (scheduled with EST in mind in our environment).  
   The goal was to pre-warm heavy dashboard caches during low-traffic hours, before daytime usage.

3. **Removed backoff-based retry chaining and split work into separate jobs.**  
   Instead of one chained retry flow with exponential delays, each section runs as its own job.

4. **Used `perform_in` with up to 5 retries per job.**  
   Each job now schedules its own retry attempts independently.

This combination worked better for our case: fewer cold-cache misses, warm data before peak traffic, and independent retry behavior per section rather than one shared backoff path.

## Results

| Scenario | Before | After |
|----------|--------|-------|
| First load after cache clear (large dataset) | 500 error, user sees error | Much less frequent cold-cache failures after TTL + warm-up changes |
| Peak hours, 30-day range | Sometimes times out, error displayed | Better stability due to off-peak pre-warming and isolated retries |
| Failure handling | One backoff chain for the flow | Per-section jobs with `perform_in` retries (up to 5) |

As a result, users were able to view data reliably even during peak traffic hours.

## What I Learned

- **Run `EXPLAIN ANALYZE` earlier than you think.** It turns vague slowness into something concrete you can reason about.

- **`Index Only Scan` does not automatically mean cheap.** If heap fetches are still high, the database is doing much more work than the plan name suggests.

- **Large sorts can fail for boring reasons.** Sometimes the issue is not the SQL shape, but simply that the operation does not have enough memory.

- **Database performance is not constant.** A query that looks acceptable in one moment can become a timeout during peak traffic.

- **Retry strategy matters as much as retry count.** In our case, independent per-section jobs with `perform_in` retries were more predictable than a single exponential-backoff chain.

- **The plan usually tells you more than the code does.** Once I looked at actual timings and heap fetches, the next steps became much clearer.

## TL;DR

After fixing a data bug, clearing the cache exposed a slow analytics query that sometimes timed out on first load. `EXPLAIN ANALYZE` showed sort spill and heavy heap fetches. The immediate fix was operational: increase cache TTL from 4 hours to 24 hours, warm caches at 3:00 AM Arizona time, and switch to separate per-section jobs with `perform_in` retries (up to 5) instead of one exponential-backoff chain. As a result, users were able to view data reliably even during peak traffic hours.

---

*The real win here was not just fixing one query. It was finally learning how to look at a query plan and explain what the database was actually doing.*


The Day I Finally Used EXPLAIN ANALYZE in Production


# Why Relationship Records Weren't Being Created

## What I Was Working On

I was working on a sync flow where one user type was supposed to be linked to another through a relationship record. Whenever an upstream system sent a matching record, our app was supposed to create that relationship automatically.

It was not working. The source records were showing up, but the relationship records were missing.

## The Problem

It turned out there were multiple bugs stacked on top of each other, and you could not really see the next one until you fixed the one before it.

**Bug #1: The key identifier never made it through the pipeline.**

The upstream payload had a field called `associated_user_id` that told us which secondary record should be linked. But the intermediary service that transformed the payload for our app was dropping that field. It never made it into the metadata passed downstream, so by the time the record reached our app, we had no way to know what it was supposed to be linked to.

**Bug #2: The metadata path was wrong, and the callback was too narrow.**

Even after fixing the intermediary service to include the field, our app was reading `associated_user_id` from the wrong place in the metadata structure. It was looking at the top level instead of the nested key where the value actually lived.

And even after fixing that, there was still another issue underneath it.

## The Sneaky Third Issue

Here was the tricky part: upstream, one user could generate multiple source records with different `associated_user_id` values. But in our app, those all mapped to the **same local record** because they shared the same uniqueness boundary. So each new sync overwrote the previous one.

The old callback only triggered relationship creation when the record's **status** changed. But when one synced record overwrote another with a different `associated_user_id`, the status did not change. So the callback never fired, and the new relationship record was never created.

## How I Found It

This was a real onion-peeling exercise:

1. **Started with the symptom**: a user was missing expected relationship records. I checked the synced record in our app and saw no `associated_user_id` in metadata. That pointed to the intermediary service.

2. **Fixed the intermediary service**, re-synced, and still got only partial results. The `associated_user_id` was showing up now, but our app was reading it from the wrong metadata path. Fixed that.

3. **Ran a backfill** to create the missing relationship records from existing data. That worked, but only for the local records that already existed in our app. In one test case, there were 21 upstream source records, but only 6 local records.

4. **Dug into why only 6**: it turned out the 21 upstream records were collapsing into 6 local records because of how uniqueness was defined. The last processed value won, and its `associated_user_id` was the only one stored.

5. **Found the callback gap**: even if all 21 source records flowed through, the callback would only fire when status changed, not when `associated_user_id` changed. So overwrites were being silently ignored.

## The Fix

Two changes across two services:

**In the intermediary service**: added `associated_user_id` to the metadata that gets passed along with each synced record.

**In the application**:
- Fixed the metadata read path to look in the right nested location
- Extended the callback trigger to also fire when `associated_user_id` changes, not just status. Used Rails' built-in dirty tracking (`metadata_before_last_save`) to compare the old and new values.

Now when record A linked to entity `523` gets overwritten by record B linked to entity `525`, the callback sees that `associated_user_id` changed from `"523"` to `"525"` and creates the missing relationship record.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Relationship records for test case | 11 (from initial sync + manual backfill) | All 21 expected |
| Backfill for one account | 0 auto-created | 31 created automatically |
| Pipeline: `associated_user_id` passed | No | Yes |

## What I Learned

- **Data pipeline bugs compound**: when data gets dropped early in the pipeline, every downstream system that depends on it silently fails. You often do not even see the second bug until the first one is fixed.

- **Understand the data model before assuming the storage layer is wrong**: I initially thought the sync layer was broken because multiple source records were collapsing into one. It turned out that behavior was intentional. The real fix was making the callback smarter, not changing how records were stored.

- **Callbacks need to match the data flow**: if your data can change via overwrites and not just creates, your callbacks need to handle that. "Only trigger on status change" works until metadata changes become meaningful.

- **Rails dirty tracking is useful here**: `metadata_before_last_save` and `saved_change_to_metadata?` let you detect changes within JSON or hstore fields without extra infrastructure.

- **Test with real edge cases**: one user linked to 21 upstream records collapsing into 6 local ones was exactly the kind of case that exposed the bug. Simpler tests would have missed it.

## TL;DR

The record coming from upstream carried the identifier needed to create a relationship, but that value was being dropped by one service, read from the wrong path in another, and ignored by a callback that only watched status changes. Fixing all three layers made the relationship records appear correctly.

---

*Sometimes the hardest bugs are the ones where three things are broken at once, and each one hides the next one.*


Why Relationship Records Weren't Being Created


# Why One Dashboard Page Kept 502-ing

## What I Was Working On

I was working on a dashboard page that showed a handful of aggregated metrics for a large customer account. It worked fine at small scale. But once the data set got large enough, the page kept timing out with 502 errors.

## The Problem

For one large account with a dataset of around 7,000 users, the page would not load at all. Every request hit a 502 timeout from the load balancer. On top of that, the frontend was throwing JavaScript errors about missing DOM targets, which made it look like a frontend bug at first.

The 502s were happening because the backend was taking 9-24 seconds to respond, well past our ALB timeout. But a dataset with 7,000 users should not be *that* bad, right? We had pagination. So what was going on?

## How I Found It

This took a little while to untangle. Here's the path:

**First dead end: the JavaScript error.** The browser console was complaining about a missing `parent` target in a Stimulus controller. I spent some time there first, adding guard clauses so the controller would not crash when its DOM targets were not ready yet. That fixed the JS error, but the 502s kept coming.

**Second look: Datadog traces.** I checked the traces and saw the backend was spending most of its time in database queries. So this was really a backend problem, not a frontend one.

**The breakthrough: following the query chain.** I traced through the code path that runs when a user hits the dashboard page. The controller calls `build_metrics_map`, which computes metrics for the full user set, and then `process_results`, which counts and paginates the result. The important detail was that pagination only kicks in at the very end. Before that, we were still running expensive queries across the entire population.

**The real aha moment:** I looked at the policy scope that builds the metrics query and found this:

```ruby
.joins(:related_records)
.left_joins(:related_metrics)
.includes(:related_records)  # <-- THIS GUY
```

That `.includes(:related_records)` looked reasonable at first. But the metrics were already being computed in SQL using `GROUP BY` and `AVG()`. We never actually iterated over individual related record objects in Ruby. So that one `includes` was triggering a separate query like:

```sql
SELECT * FROM related_records WHERE user_id IN (1, 2, 3, ..., 7000)
```

That returned about **156,000 related records** and loaded them all into Ruby memory for data we never used. On every page load when the cache was cold.

And after that, the code ran another expensive query, `users.count`, doing a full `COUNT` across the same large multi-join relation even though we had already built a hash map of all the users and effectively knew how many there were.

## The Fix

Four things:

1. **Removed the `.includes(:related_records)`** - since the metrics are computed in SQL via aggregation, we did not need Rails to eager-load all 156K related objects into memory. The `joins` and `left_joins` were already doing the actual query work.

2. **Reused the metrics map for the count** - instead of running a separate `users.count` query, I used the size of the metrics hash map we had already built. It falls back to the database count when the map is not available.

3. **Added guard clauses to the Stimulus controller** - so it handles the case where DOM targets are not rendered yet, for example during Turbo Frame loading, without crashing.

4. **Removed unnecessary bulk loading on a related page** - there was a separate code path on another page that was also loading the full user set, so I removed that too.

## Results

| What | Before | After |
|------|--------|-------|
| Related records loaded into memory | ~156,498 | 0 |
| SQL queries for metrics | 2 (aggregation + eager load) | 1 (aggregation only) |
| Redundant COUNT query | Yes | No (uses metrics map size) |
| Page load | 502 timeout | Loads successfully |
| JS console errors | Missing target crash | None |

## What I Learned

- **`.includes` is not always the right N+1 fix.** If your query uses `GROUP BY` and aggregation, the useful work is already happening in SQL. Eager-loading the raw records on top of that just loads data nobody uses.

- **"Prevent N+1" changes need context.** A query can look more optimized while actually doing more work. The real question is whether anything in Ruby ever touches the records you are preloading.

- **Pagination doesn't help if expensive queries run before it.** Our pagination was working fine, but it only applied to the final page of results. The metrics computation, the count query, and the eager-loading all happened before pagination ever limited the result set.

- **Follow the numbers, not the row count.** A dataset with 7,000 users sounds manageable. But each user had around 22 related records on average, and the `.includes` loaded all of them. The real scale was 156K records, not 7K.

## TL;DR

A dashboard page was 502-ing because an `.includes(:related_records)` was loading 156K records into memory that nobody used even though the real work was already being done in SQL. I removed it, reused existing data for the count query, cleaned up a related Stimulus issue, and the page went from timing out to loading normally.

---

*Sometimes the best optimization is removing code that shouldn't have been there in the first place.*


Why One Dashboard Page Kept 502-ing

# The XSS Concern That Wasn't

## What I Was Working On

I was working on a feature that launched an external app from inside our Rails app using LTI. Part of that flow involved rendering a page that auto-submitted a form with signed parameters.

## The Concern

During code review, someone flagged this as a potential XSS vulnerability:

```erb
<input type="hidden" name="<%= key %>" value="<%= value %>" />
```

The concern was straightforward: what if `value` contains something like `"` or `onclick="bad stuff"`? Could someone break out of the `value` attribute and inject JavaScript?

At first glance, that sounded completely reasonable.

## Digging Into It

So I started by thinking through the failure mode:

```html

<input value="foo" onclick="alert(1)" />
```

That would absolutely be a real problem.

But then I looked closer at two things:

### 1. Does Rails auto-escape?

Yes. The `<%= %>` syntax in ERB **automatically HTML-escapes** values before rendering them into the page. So if someone tried to inject a quote character:

```ruby
# What you write
'foo" onclick="alert(1)'

# What actually renders
'foo&quot; onclick=&quot;alert(1)'
```

Those quotes get escaped to `&quot;`, which means the payload stays inside the attribute as plain text instead of turning into a new attribute.

### 2. Where does this data come from?

I also traced where `@signed_params` came from:

```ruby
{
  'user_id' => user.id.to_s,           # Our database ID
  'oauth_timestamp' => Time.now.to_i,  # Current time
  'context_id' => section.sourced_id,  # A UUID we generated
  # ... all server-generated values
}
```

In this code path, the values were generated on our side. They weren't coming from user-submitted input, which matters a lot when you're evaluating XSS risk.

## When Would This Actually Be Dangerous?

The dangerous version of this pattern is when you **bypass** Rails' escaping:

```erb
<%# DANGEROUS - these bypass escaping %>
<input value="<%== value %>" />
<input value="<%= value.html_safe %>" />
<input value="<%= raw(value) %>" />

<%# SAFE - default escaping applies %>
<input value="<%= value %>" />
```

If you use `html_safe`, `raw`, or the double-equals `<%==`, you're telling Rails, "trust me, this string is already safe." That's where this kind of code can go wrong very quickly.

## The Verdict

The code was safe in this case because:
1. ERB escapes attribute values by default
2. The data in this flow was not attacker-controlled
3. We weren't using any escape bypasses like `raw`, `html_safe`, or `<%==`

I did come away thinking the code could be a little more idiomatic with form helpers:

```erb
<%= form_tag @launch_url, id: 'lti_launch_form' do %>
  <% @signed_params.each do |key, value| %>
    <%= hidden_field_tag key, value %>
  <% end %>
<% end %>
```

Same idea, just a little more Rails-y.

## What I Learned

- **ERB escapes more than people think** - `<%= %>` gives you important protection by default
- **Watch out for escape bypasses** - `html_safe`, `raw`, and `<%==` are the danger zones
- **Trace the data source** - what matters is whether the value is attacker-controlled in this code path
- **Code review concerns are still valuable** - even when the issue turns out to be harmless, the investigation sharpens your security instincts
- **Context matters** - the same code pattern could be dangerous in a different context (e.g., if we were rendering user-submitted data)

## TL;DR

I got flagged for a possible XSS issue in an ERB template, but the code was safe because ERB was escaping the value and the data wasn't attacker-controlled in that flow. The real risk starts when you bypass escaping or render untrusted input in the wrong context.

---

*Sometimes the useful part of a security review is proving why something is safe, not just finding where it's broken.*

The XSS Concern That Wasn't


# Flash vs Flash.now: The Rails Gotcha That Finally Made Sense

## What I Was Working On

I was working on a feature with some standard error handling. If something failed, I wanted to show a helpful message and send the user to the right place.

## The Problem

My linter kept yelling at me:

```
Use flash.now before render.
```

But I wasn't using `render`. I was using `redirect_to`.

That warning made me stop and think, though. I knew the rule by habit, but I realized I couldn't explain it as clearly as I wanted to. What's the real difference between `flash` and `flash.now`?

## The Aha Moment

The key insight is understanding what a request is and that **the server has amnesia**.

Every time the browser talks to the server, that's one request. When that request ends, the server forgets everything unless you save something somewhere.

That's why redirects matter so much here:

```
You: "Hey, there was an error!"
Server: "Okay, I'll redirect you..."
[Request ends, server forgets]

Browser arrives at new page
Server: "Hello! Who are you? What error?"
```

## How Flash Solves This

Flash messages use the session so they can survive across requests when needed.

**With `redirect_to` (two requests):**
```
Request 1: Something fails
  -> You set flash[:error] = "Oops!"
  -> Rails stores it in the session
  -> You redirect the user

Request 2: User lands on new page
  -> Rails reads the flash
  -> Shows "Oops!" to the user
  -> Rails clears the flash
```

**With `render` (same request):**
```
Request 1: Something fails
  -> You set flash.now[:error] = "Oops!"
  -> Rails shows the message immediately
  -> No extra request involved
```

## Why It Matters

If you mix them up:

| You do | What happens |
|--------|--------------|
| `flash` + `redirect_to` | Works. Message shows on the next page |
| `flash.now` + `render` | Works. Message shows immediately |
| `flash` + `render` | Wrong message timing. It can show up on the next page visit |
| `flash.now` + `redirect_to` | Message disappears before the next page loads |

## The Rule I Keep In Mind

- **Redirecting?** Use `flash[:error]`
- **Rendering?** Use `flash.now[:error]`

In my case, I was redirecting, so regular `flash` was the correct choice:

```ruby
flash[:error] = "Something went wrong"
redirect_to some_path
```

## What I Learned

- **HTTP is stateless** - the server forgets everything after each request
- **Flash uses the session** to carry a message into the next request
- **`flash`** = "remember this for the next page"
- **`flash.now`** = "show this right now"
- **The request boundary is the whole mental model**

## TL;DR

`flash` survives to the next request, so use it with redirects. `flash.now` only lives for the current request, so use it with renders. Once you think in terms of request boundaries, the difference becomes much easier to remember.

---

*Once request boundaries click, a lot of Rails behavior starts to feel much less magical.*


Flash vs Flash.now: The Rails Gotcha That Finally Made Sense


# Two Practical Lessons I Picked Up Building an LTI Integration

Recently I worked on embedding an external assessment tool inside our main Rails app using LTI.

At a high level, the job sounded simple: launch the tool in an iframe, pass the right parameters, and let teachers manage assessments without leaving the main app.

The integration itself worked, but the more interesting part was everything around it. While building it, I ran into two small lessons that were more useful than the LTI setup itself.

## 1. When to Use Stimulus Instead of Turbo

My first instinct was to use Turbo Frames for the tab switching between `Gradebook` and `Alternative Assessments`.

That turned out to be the wrong tool for this case.

Here's why:

1. **LTI requires a POST** - You can't just load a URL. You need to submit a form with signed OAuth parameters.
2. **Cross-origin iframe** - Turbo can't control content from another domain.
3. **Hidden form trick** - The solution is a page that auto-submits a hidden form to the LTI endpoint.

Stimulus ended up being the better choice because we needed JavaScript to:
- Switch tabs on click
- Load the iframe lazily (only when the tab is clicked)
- Show a loading spinner while the iframe loads

Turbo is great when you're swapping server-rendered content from your own app. But when you need to submit a signed POST to another service, deal with a cross-origin iframe, and manage loading states yourself, Stimulus gives you much more control.

## 2. exists? is More Efficient Than find_by

When checking authorization, I originally wrote:

```ruby
enrollment = user.enrollments.find_by(section: section)
is_authorized = enrollment&.role == 'teacher'
```

A reviewer pointed out that `exists?` was a better fit:

```ruby
is_authorized = user.enrollments.exists?(section: section, role: 'teacher')
```

Why? `find_by` loads the full record into memory, then checks the role in Ruby. `exists?` just asks the database whether a matching row exists and returns a boolean.

It's a small optimization, but it also reads more clearly. If the question is just "does this record exist?", `exists?` usually says exactly what you mean.

## TL;DR

I built an LTI iframe integration, but the most useful takeaways were broader than LTI itself: use Stimulus when Turbo stops fitting the problem, and prefer `exists?` when you only need a yes-or-no answer.

---

*Sometimes the most useful part of a task is the stuff you learn around it.*


Two Practical Lessons I Picked Up Building an LTI Integration


# That Time I Fixed a Sneaky N+1 Query in Grade Calculations

## What I Was Working On

So today I was looking into a performance issue in our grade calculation system. We have this feature where students get weighted grades based on different assignment categories (like homework worth 20%, tests worth 50%, etc.). The code loops through each category, calculates the score, and applies the weight.

Sounds simple enough, right?

## The Problem

Turns out, this thing was making way too many database calls. Like, 40+ queries just to calculate one student's grade. Every time we calculated grades for a section with a bunch of assignments, it was painfully slow.

Looking at our monitoring tools, I could see the same queries repeating over and over:

```
"Hey database, give me assignments for category 1"
"Hey database, give me assignments for category 2"
"Hey database, give me assignments for category 3"
...
```

And it got worse. For each assignment, we were also asking:

```
"Is this assignment exempt?"
"Is this one exempt?"
"What about this one?"
...
```

Classic N+1. But actually, it was more like N+1 times three because there were multiple loops doing this.

## How I Found It

I started by checking Datadog/Sentry traces to see the query breakdown. The trace showed dozens of nearly identical queries - dead giveaway for N+1.

Then I dug into the code and found the culprit: a loop that was querying the database on every iteration. The tricky part was that there were actually THREE separate N+1 patterns hiding in there:

1. Fetching assignments per category (instead of all at once)
2. Counting exempt assignments per category with a join query
3. Checking if each individual assignment was exempt with `.exists?`

That last one was the sneakiest. It looked innocent - just a simple check - but it was running once for every single assignment.

## The Fix

The solution was basically: **load everything upfront, then work with it in memory**.

Instead of asking the database questions inside the loop, I:

1. Grabbed ALL the visible assignments in one query, then grouped them by category in Ruby
2. Got ALL the exempt assignment IDs in one query and threw them into a Set (for fast lookups)
3. Got ALL the assignment results in one query

Now the loop just works with data that's already in memory. No more "hey database" calls inside the loop.

### The Key Trick

The `.to_set` trick was clutch. Before, we were checking "is this assignment exempt?" by hitting the database every time. After, we just check if the ID is in a Set - which is basically instant.

```ruby
# Before (slow - database call every time)
next if assignment.results.exists?(status: 'exempt')

# After (fast - just checking a Set in memory)
next if exempt_ids.include?(assignment.id)
```

## Results

| Before | After |
|--------|-------|
| 40+ queries | 5 queries |
| Slow as heck | Actually reasonable |

## What I Learned

1. **N+1 can hide in plain sight.** That innocent-looking `.exists?` check was actually a database query running hundreds of times.

2. **Look for loops inside loops.** Each nested loop that touches the database multiplies your problem.

3. **Sets are your friend.** When you need to check "is X in this list?" a bunch of times, convert the list to a Set first. O(1) vs O(n) lookup.

4. **Load once, use many.** If you're going to use the same data multiple times, grab it all at the start. Don't keep asking the database the same questions.

5. **Check your traces.** Monitoring tools make N+1 issues obvious. If you see the same query pattern repeating, that's your sign.

## TL;DR

Grade calculations were slow because we were asking the database too many questions inside loops. Fixed it by asking all the questions upfront and then working with the answers in memory. Went from 40+ queries to 5.

---

*Sometimes the fix isn't complicated - it's just about being smarter about when you talk to the database.*


That Time I Fixed a Sneaky N+1 Query in Grade Calculations


# ActiveRecord has_many Joins, DISTINCT, and When ORDER BY Needs a Subquery

## What I Was Working On

I worked through material on **querying `has_many` associations** in ActiveRecord — the same ideas as `belongs_to`, but with the join direction flipped so parent rows (e.g. locations) can appear multiple times when multiple children (people) match.

The walkthrough uses a small domain: `Person` belongs to `Location` and `Role`; `Location` and `Role` each `has_many :people`. The goal is to express “billable locations” (locations that have at least one person whose role is billable) and then combine that with ordering by region and location name.

## What I Learned

### Joining the “other side” of the association

`Location.joins(:people)` inner-joins `people` to `locations`. That is the `has_many` analogue of joining from the child in a `belongs_to` lesson: you get one row per (location, person) pair, so a location with two people shows up twice. That duplication is expected; it is what lets you filter and aggregate in SQL instead of looping in Ruby.

### Nested joins read like a temporary `has_many :through`

`Location.joins(people: :role)` chains `people` → `roles`. You did not have to declare a `has_many :roles, through: :people` on `Location` for this one-off query; ActiveRecord walks the graph you name.

### Filtering on joined tables

`Location.joins(people: :role).where(roles: { billable: true })` scopes by columns on the joined `roles` table. Hash conditions on the table name keep the intent obvious and avoid string SQL in app code.

### Why `distinct` shows up

After joins, the same location can repeat. `.distinct` (Postgres: `SELECT DISTINCT "locations".*`) collapses the result to unique locations. The “distinct” requirement is not cosmetic — it matches how joins multiply rows.

### Encapsulation

Named scopes or class methods (`Location.billable`) keep join logic on the model instead of scattering it through controllers. Composing smaller scopes (e.g. `Role.billable` → `Person.billable` → `Location.billable`) stays readable even when a single method would be shorter in a tutorial.

### Ordering combined scopes: the Postgres rule

Combining something like:

- `billable` → `joins(people: :role).where(roles: { billable: true }).distinct`
- `by_region_and_location_name` → `joins(:region).merge(Region.order(:name)).order(:name)`

as `Location.billable.by_region_and_location_name` yields SQL that orders by `regions.name` and `locations.name` while the outer select is `SELECT DISTINCT "locations".*`. Postgres errors:

**`for SELECT DISTINCT, ORDER BY expressions must appear in select list`**

The mental model: `DISTINCT` can collapse rows that differed only in columns you use for `ORDER BY` but did not select. The database forces you to resolve “unique rows” first, then order in a way that does not contradict that.

### Fix: subquery with `from`, then order on the outer query

`Location.from(Location.billable, :locations)` wraps the distinct billable locations in a subquery aliased as `locations`, so the outer query sees a normal `locations` table again. Then `.by_region_and_location_name` joins `regions` and orders — without fighting `DISTINCT` in the same `SELECT`.

Final shape:

`Location.from(Location.billable, :locations).by_region_and_location_name`

The inner query deduplicates; the outer query adds the join and `ORDER BY` on unambiguous columns.

### Let the database do the work

Pushing filtering, deduplication, and ordering into SQL (via ActiveRecord) usually beats loading collections into Ruby and using `Enumerable` for the same job — fewer round trips, less memory, and work the engine is built for.

## Practical Application at Work

- **When you see duplicate parents after `joins`,** decide whether you need `distinct`, `group`, or a different shape (e.g. `EXISTS` subqueries) depending on whether you only filter or also need aggregates.
- **When you mix `distinct` with `order` on joined columns,** Postgres may reject it; a subquery or selecting the order columns explicitly are the usual escapes — the `from(subquery, :alias)` pattern keeps the “dedupe first, sort second” story clear in ActiveRecord.
- **Keep composed scopes on the model** so the awkward combinations (like subquery wrappers) live in one place and callers stay readable.

## TL;DR

`joins` on `has_many` multiplies parent rows; `where` on nested associations filters; `distinct` dedupes parent rows. Stacking `distinct` with `order` on columns from other tables can break Postgres — wrap the deduped set in `from(...)`, then join and order on the outside. Prefer pushing this into the DB via ActiveRecord over reimplementing the same logic in Ruby.

---

*Based on learning from a course on advanced ActiveRecord querying (has_many associations).*


ActiveRecord has_many Joins, DISTINCT, and When ORDER BY Needs a Subquery


# ActiveRecord DISTINCT ORDER BY Error in PostgreSQL and Why a Subquery Fixes It

## What I Was Working On

I was trying to understand a common ActiveRecord pattern for `has_many` queries:

- build a scope that joins child tables
- filter on the joined rows
- remove duplicate parent rows with `distinct`
- then order the result by columns from another joined table

The example looked simple on the Rails side, but the SQL rule behind it was easy to miss.

## What I Learned

### `joins` on a `has_many` multiplies parent rows

If a location has multiple matching people, `Location.joins(:people)` does not return one row per location. It returns one row per `(location, person)` pair. That means duplicate parent rows are expected after the join.

That is why `distinct` shows up in patterns like:

`Location.joins(people: :role).where(roles: { billable: true }).distinct`

The goal is not cosmetic deduplication. It is correcting the row multiplication introduced by the join.

### `DISTINCT` and `ORDER BY` can pull in different directions

The confusing part is when I want something like:

- unique `locations`
- ordered by `regions.name`
- then ordered by `locations.name`

At first glance, this feels harmless. But PostgreSQL sees a real ambiguity:

- `DISTINCT` says: "figure out which selected rows count as the same"
- `ORDER BY` says: "sort those rows using these expressions"

If the ordering expression is not part of the selected distinct row, the database has to answer an awkward question:

"When I collapse duplicates, which ordering value am I supposed to keep?"

That is why PostgreSQL raises:

**`for SELECT DISTINCT, ORDER BY expressions must appear in select list`**

The important mental model is:

**deduplicate first, then sort the already-defined result set**

### The subquery is really an order-of-operations fix

The subquery pattern solves that by separating the steps:

1. Inner query: find distinct billable locations
2. Outer query: treat that result like a table, join what I need, and order it

In ActiveRecord, that shape looks like:

`Location.from(Location.billable, :locations).by_region_and_location_name`

The inner `Location.billable` handles the duplicate-removal part. The outer query handles ordering.

This is not just about syntax. It is about making the SQL stages explicit so Postgres no longer has to mix deduplication and cross-table ordering in the same select step.

### Why `:locations` is there

This part finally clicked for me once I stopped thinking of `from` as "just passing another relation" and started thinking of it as:

**"Use this subquery as the table for the rest of the query."**

So:

`Location.from(Location.billable, :locations)`

means:

- take the SQL generated by `Location.billable`
- place it in the `FROM (...)` clause
- alias that subquery as `locations`

That alias matters because the rest of the relation still wants to behave like it is querying a `locations` table.

So when I later call:

- `.joins(:region)`
- `.order(:name)`
- or any scope that expects `locations.region_id` or `locations.name`

ActiveRecord now has a table-like thing named `locations` to point at.

Without the alias, the outer query would not have a stable table name for those later joins and column references.

## Practical Application at Work

- When a `has_many` join produces duplicate parents, treat `distinct` as a row-shape decision, not a cleanup trick.
- When `distinct` and ordering on joined columns collide, think in stages: first produce the unique parent set, then order it in an outer query.
- When using `from(subquery, :alias)` in ActiveRecord, remember that the alias is what lets later scopes keep talking to the subquery as if it were a normal table.

## TL;DR

`joins` on `has_many` creates duplicate parent rows, so `distinct` is often necessary. But once `DISTINCT` is involved, PostgreSQL may reject `ORDER BY` on expressions that are not in the select list. Wrapping the distinct relation in `from(subquery, :locations)` makes the sequence explicit: dedupe inside, then join and sort outside. The `:locations` part is just the alias that lets ActiveRecord keep treating the subquery result like the `locations` table.

---

*The biggest insight was not "Postgres is picky." It was realizing that SQL was asking me to choose the order of operations more explicitly.*


ActiveRecord DISTINCT ORDER BY Error in PostgreSQL and Why a Subquery Fixes It


# Concurrent Indexes, Statement Timeouts, and When Migrations Stop Being “Deterministic”

## What I Was Working On

I needed to add a nullable foreign-key-style column to a very large append-only table used for analytics-style event records, then index it so lookups stayed cheap. The Rails guides describe migrations as a clean sequence: define `up`/`down` or `change`, run `db:migrate`, everyone’s database matches. Production had other plans.

## The Problem

The table was big enough that a normal `add_index` inside a single transaction would have been risky for availability. For PostgreSQL, **`CREATE INDEX CONCURRENTLY`** avoids blocking writes on the table, but it **cannot run inside a transaction block that also runs other DDL** the usual way. In Rails, that means you reach for **`disable_ddl_transaction!`** on the migration class so the migration runner does not wrap the whole thing in one transaction.

Even with `CONCURRENTLY`, building an index on a huge table can take a long time. Our environment applied a **statement timeout** to migrations. The column `ADD COLUMN` finished quickly; the **index build hit the timeout**, rolled back from the migration’s point of view, and left us in a messy state: **the column existed in production, but the index did not**.

So the next deploy could not pretend we were on a greenfield database. Re-running a “pure” migration that always runs `add_column` would **fail** because the column was already there.

## How I Found It

Deploy logs and the database state did not match what `schema_migrations` implied we should see. The migration had partially applied: schema change (column) succeeded; long-running step (index) did not complete under the timeout.

That is the moment the textbook story (“migrations should be deterministic”) bumps into **partial failure recovery**.

## The Fix

**1. Split concerns mentally, even if one file holds both steps**

- Add column (fast) vs. create index concurrently (slow) are different risk profiles. The guides allow both in one migration, but production may force you to treat them differently over time.

**2. Use the PostgreSQL-friendly knobs**

- `disable_ddl_transaction!` so `add_index ..., algorithm: :concurrently` is valid.
- Temporarily **`SET statement_timeout = 0`** (scoped to the migration connection/session) so the index build is not killed mid-flight—*with the understanding that this is a deliberate trade-off for a controlled deploy window*.

**3. Make the migration safe to re-run after a partial apply**

- `add_column ... unless column_exists?(...)`
- `add_index ..., algorithm: :concurrently, if_not_exists: true` (or equivalent guard)
- `down` paths that use `if_exists:` so rollback is safe

This is **not** the same as “edit old migrations casually.” It is **operational idempotency** because the world outside version control already diverged.

**4. A reviewer’s point (and a valid alternative)**

A teammate suggested keeping migrations maximally deterministic—e.g. column in one migration, concurrent index in another. That is often the right default. In our case, the column had already shipped and the index had failed once; **guards were the way to finish the job without hand-editing production**.

Another pattern worth knowing: a DBA builds the index manually in a session with appropriate settings; a follow-up migration only records “create index if not already present.” That keeps Rails history aligned with reality when humans intervene. I had not used that workflow before; it is now on my list for the next huge index.

## Results

| Aspect | Before | After |
|--------|--------|-------|
| Migration run | Column added; index build timed out | Redeploy with guards completes index |
| `add_column` | Would fail on second run | `unless column_exists?` — safe to replay |
| Index creation | Killed by `statement_timeout` | `SET statement_timeout = 0` for concurrent build |
| Story | Textbook “deterministic” migration | Recoverable migration after partial apply |

## What I Learned

- **`CREATE INDEX CONCURRENTLY` + Rails migrations = remember `disable_ddl_transaction!`.** Otherwise you chase confusing errors that make sense only if you know PostgreSQL’s rules.

- **Statement timeouts apply to more than you think.** I assumed concurrent index creation might be immune; it wasn’t in our setup. Explicitly resetting timeout for the long DDL step was the fix.

- **Partial deploys change the rules.** The guides warn against editing migrations after they have run in production; they also imply a happy path. When production is half-updated, **conditional DDL** can be the responsible move.

- **“Deterministic migrations” vs. “recoverable migrations.”** In greenfield, optimize for determinism. After a failed long step in prod, optimize for **safe replay** and clear `down` behavior.

## Practical Application at Work

- For large tables: plan index creation like an ops task—timeouts, locks (or avoidance), and rollback story—not only like an ActiveRecord API exercise.
- When a migration fails mid-way: first **inspect what actually landed** (columns, indexes, constraints), then adjust the *next* migration or a guarded re-run—not always a blind rollback.
- Document for the team when you use `statement_timeout = 0` and why; future you should see the intent.

## TL;DR

I added a column and a concurrent index on a massive table. The column landed; the index timed out under migration statement limits. I used `disable_ddl_transaction!`, disabled `statement_timeout` for the index build, and made `add_column` / `add_index` idempotent so a redeploy could finish what the first run started. The Rails migration guides are still the right baseline—production just adds chapters on timeouts, partial failure, and when “conditional migrations” are the professional option.

---

*Rails guides teach the happy path. Production teaches the recovery path.*


Blog