When a Rails endpoint gets slow, the reflex is to cache it. Sometimes that’s right. More often the query is slow because ActiveRecord is doing in Ruby something Postgres would do in one pass — loading a thousand rows to pick one per group, filtering an array in memory, running a search it should have indexed. On a multi-tenant SaaS I ran the backend for, the biggest wins came from deleting Ruby and letting the database earn its keep. Reworking the hot endpoints this way — right indexes, latest-per-group in SQL, cached reference data — took the worst ones down by around 40% at the median, before a single new cache entry.

Here are the three Postgres features that did most of the work, and where each one stops helping.

DISTINCT ON, for “the latest one per group”

“Show each record with its most recent status change” is the single most common shape of slow query I see. The ActiveRecord-idiomatic version is an N+1 — load the records, then per record load its latest event — or a correlated subquery that the planner handles badly at scale. Postgres has a purpose-built tool: DISTINCT ON.

# the latest stage change per record, in one query
StageChange
  .select("DISTINCT ON (stageable_id) *")
  .order("stageable_id, created_at DESC")

DISTINCT ON (stageable_id) keeps the first row for each stageable_id, and the ORDER BY decides which “first” means — here, most recent by created_at. One index scan, one row per group, no N+1, no subquery. It reads a little strange the first time (the DISTINCT ON column has to lead the ORDER BY), but once it’s in your vocabulary you see uses everywhere: latest price per item, most recent login per user, one row per client on a statement.

Where it stops: DISTINCT ON is Postgres-only, so it ties that query to Postgres. I’m fine with that trade — I’d rather use my database than pretend it’s interchangeable — but it’s a real coupling worth naming.

Array columns + overlap, instead of a join table you’ll regret

Some tags don’t deserve their own table. A party (a company or a contact) can be a client, a supplier, or both — a small, closed set of roles. Rather than a party_roles join table and the joins that come with it, these live in a Postgres array column, and membership is an overlap test with the && operator:

def filter_by_role(scope)
  case params[:role]
  when 'supplier'
    scope.where("type IN (?) AND ARRAY['supplier'] && subtypes",
                ['Crm::Parties::Company', 'Crm::Parties::Contact'])
  when 'client'
    scope.where("type IN (?) AND ARRAY['client'] && subtypes",
                ['Crm::Parties::Company', 'Crm::Parties::Contact'])
  end
end

ARRAY['supplier'] && subtypes is true when the two arrays share any element — “is this party a supplier.” Backed by a GIN index on the array column, it’s fast, and it keeps a whole join table and its bookkeeping out of the schema. This lives in a small query object (PartyQuery) that pipes the record set through filter_by_type, filter_by_subtype, filter_by_role — each a plain method that takes a scope and returns a scope, so the filters compose and stay testable.

Where it stops: arrays are the right call only for small, bounded tag sets you won’t need to attach data to. The day a “role” needs its own attributes (a start date, a permission), you want the join table, and migrating an array column back into one is annoying. Use this for genuinely simple membership, not as a reflex to avoid a table.

Full-text search you already have

Before adding a search service, check what Postgres gives you for free. Search here runs on a GIN index over a tsvector — Postgres’s own full-text search — so “find the customer named…” doesn’t fan out to another system for the common case:

# db/schema.rb
t.index "to_tsvector('simple', name)", name: "index_parties_on_fulltext_vector", using: :gin

A GIN tsvector index handles prefix and token search across a lot of rows without leaving the database, and it stays transactionally consistent with your data — no sync lag, no “the search index is behind again” incidents.

Where it stops: Postgres full-text is great until you need typo tolerance, relevance tuning, or faceting, at which point a dedicated search engine earns its complexity. The point isn’t that you’ll never need one — it’s that you should find out you need one, instead of reaching for it on reflex when a GIN index would have carried you for another two years.

Then, and only then, cache

Some things genuinely can’t be made cheaper per request — per-tenant reference data that’s read on nearly every page and changes a few times a week. That’s what caching is for, and in a schema-per-tenant app the discipline is that every key is namespaced by tenant, so invalidation can wipe exactly one customer’s cache and warmups can prime a tenant on demand:

def clear_tenant_cache(tenant_name = nil)
  tenant_name ||= Apartment::Tenant.current
  Rails.cache.delete_matched("tenant/#{tenant_name}/*")
end

One honest note, because it’s the kind of thing an interviewer probes: there are no counter caches in this codebase. Counts that matter are either indexed aggregates or cached deliberately — I didn’t sprinkle counter_cache: true around, because a counter cache is a small consistency liability you take on forever, and most of the counts here didn’t justify it. “We added counter caches” is an easy line to claim; I’d rather tell you the counts are handled by indexes and targeted caching.

The thread through all of this: caching is what you do when you’ve run out of ways to make the work cheaper, not the first move. Ask what the database could do that Ruby is currently doing badly, and a surprising amount of “we need a cache” turns out to be “we needed the right query.”

I spent years making a multi-tenant Postgres app fast — indexes, query shape, and knowing which problems the database should own. If yours is getting slow in the usual places, let’s talk.