Early in my career I worked on a two-sided marketplace — hosts listed lodging, guests searched by date and booked. The core search was “show me places available between these two dates.” It worked in demos. Then real bookings piled up, and search got slower every week, in a way that looked like it would eventually just stop.

Here’s the code that was doing it. Read it slowly, because the problem is hiding in the first three lines:

if params[:arrival_date].present? && params[:departure_date].present?
  @bookings = []
  Booking.all.each { |item| @bookings << item }          # every booking, ever
  ExternalBooking.all.each { |item| @bookings << item }  # ...and every external one

  @bookings.each do |item|
    case item.class.name
    when 'Booking'
      if cover?(arrival_date, departure_date, item.arrival_date) ||
         cover?(arrival_date, departure_date, item.departure_date)
        @offers = @offers.where.not(id: item.offer.id)   # rebuild the query + hit the DB for item.offer
      end
    when 'ExternalBooking'
      # ...same thing
    end
  end
end

Every search did this: pull all bookings into Ruby, loop over them, and for each one that overlaps the requested dates, tack another where.not(id: ...) onto the offers query — and reach through item.offer to get the id, which is a separate query per booking (a textbook N+1). So the cost of one search scaled with the total number of bookings in the entire system, times the offers query getting rebuilt on every iteration. It’s O(bookings), heading toward O(bookings × listings). A brand-new marketplace hides this beautifully; a year of real data does not.

The bug underneath the performance bug

There’s a correctness problem too, and it’s more interesting than the speed. The overlap logic checks whether the requested range “covers” a booking’s arrival or departure date. But that misses the case where a booking completely contains the requested dates — someone booked the whole month, you search for a weekend inside it, neither endpoint of the booking falls in your range, so the place shows as available when it isn’t. Doing overlap detection by poking at individual endpoints is how you get these gaps. There’s one correct rule, and it’s worth memorizing because it comes up constantly:

Two date ranges overlap iff a.start <= b.end and a.end >= b.start.

That single condition catches every case — partial overlap on either side, one range inside the other, identical ranges. You never need to enumerate the cases if you use it.

The fix was already in the codebase

Here’s the part that taught me the most. When I went to write the correct query, I found it was already there — three scopes on the Booking model that nobody had wired up:

scope :available, ->(arrival_date, departure_date) {
  outside_of(arrival_date, departure_date).not_pending(arrival_date, departure_date)
}
scope :outside_of, ->(arrival_date, departure_date) {
  where("arrival_date <= ? and departure_date >= ?", arrival_date, departure_date)
}

Someone (maybe an earlier me) had written the set-based version, then reached for the Booking.all.each loop in the controller anyway and never connected the two. So the abstraction existed; the calling code just didn’t trust it. That’s its own lesson: a scope no one calls is indistinguishable from a scope that doesn’t exist. Dead-but-correct code helps no one.

The right shape is to never load a booking into Ruby at all — ask the database for listings that have no overlapping booking, in one query:

overlapping = Booking
  .where("bookings.arrival_date <= ? AND bookings.departure_date >= ?", departure_date, arrival_date)
  .where(offer_id: Offer.arel_table[:id])

@offers = @offers.where.not(Offer.where(overlapping.arel.exists).arel.constraints.reduce(:and))

or, more legibly, an anti-join / NOT EXISTS: keep every listing for which there does not exist a booking whose range overlaps the requested one. The database does the overlap test on indexed date columns, returns only the listings you can actually book, and the work is bounded by the number of relevant bookings, not every booking in the system. Search stopped getting slower.

What I took from it

Two things, and they’ve held up for a decade. First, the second you find yourself pulling a whole table into your application to filter it, stop — that’s a query you haven’t written yet, and the database will do it faster and with less memory every time. Model.all.each in a request path is almost always a bug wearing a loop. Second, keep the one overlap rule in your head (a.start <= b.end && a.end >= b.start); a surprising amount of “availability,” “scheduling,” and “conflict” logic is just that condition, and hand-rolling the cases is how the edge cases slip through.

I’ll admit the honest version of this story: I wrote both the slow loop and the fast scopes, and shipped the slow one. The lesson wasn’t “know about `NOT EXISTS” — I knew. It was that the correct tool being present in the codebase means nothing if the calling code doesn’t reach for it.

I’ve spent years since making Rails and Postgres apps fast — usually by deleting the Ruby that was doing the database’s job. If yours is quietly getting slower as the data grows, I’m happy to look.