📝 TIL

How PostgreSQL Vacuum Made My Test Return Rows Backwards

How PostgreSQL Vacuum Made My Test Return Rows Backwards
#postgresql #rails #rspec #testing
Table of Contents

Today I learned something I technically knew already:

Without ORDER BY, PostgreSQL does not guarantee row order.

But today I learned exactly how that can turn into a flaky test.

And the mechanism is surprisingly interesting.

How PostgreSQL pages, dead tuples, VACUUM, and the free space map change sequential scan order

It starts with how PostgreSQL stores rows

PostgreSQL stores table data in 8 KB blocks, called pages.

Each row occupies a slot inside a page. PostgreSQL exposes this physical location through ctid:

(page, slot)

So a row with:

ctid = (190, 11)

is physically sitting in page 190, slot 11.

Now imagine this query:

SELECT *
FROM certificates
WHERE period_ending_date <= '2024-01-31';

There is no ORDER BY.

For a sequential scan, PostgreSQL reads the table pages roughly like:

page 0 → page 1 → page 2 → ... → page 190 → ...

So the result often looks like it is ordered by physical position.

But that’s not a promise about id, insertion order, or anything else.

It’s just an accident of how the rows happen to be stored.

Then vacuum enters the story

RSpec wraps each example in a transaction and rolls it back afterwards.

The rollback means the test data disappears logically.

But PostgreSQL doesn’t immediately remove the physical row.

It becomes a dead tuple.

So after a long test suite, the table can look something like:

page 0     [dead] [dead] [dead]
page 1     [dead] [row]
page 2     [row]  [dead] [row]
...
page 190   [row]  [row]

Then autovacuum comes along.

It cleans up those dead tuples and records the newly available space in PostgreSQL’s free space map.

Now an interesting thing can happen.

The insert order can become different from the scan order

Suppose our test does this:

cert1 = create_certificate
cert2 = create_certificate

Normally, both new rows might be appended near the end of the table:

page 190: cert1 → cert2

scan → cert1, cert2

Everything looks nicely ordered.

But suppose autovacuum runs between those two inserts.

It has just discovered some free space on an early page:

page 0: [free]
...
page 190: cert1

The next INSERT can reuse that free space:

page 0:   cert2
...
page 190: cert1

Now the sequential scan sees:

page 0 → ... → page 190

and returns:

cert2, cert1

The newer row comes back before the older row.

The insert order and scan order have effectively been reversed.

And that’s exactly what happened in the flaky spec.

The really fun part: reproducing it

The important detail is that vacuum has to happen between the two inserts.

When forced into that timing, the result reversed in 6 out of 6 trials:

trial 0 → [3002, 3001]
           ["(0,1)", "(166,13)"]

trial 1 → [6004, 6003]
           ["(0,1)", "(190,11)"]

The second certificate was sitting on page 0.

The first certificate was still sitting on a much later page.

So the sequential scan naturally found the second one first.

Interestingly, running vacuum before both inserts didn’t reproduce it: 8 out of 8 trials kept the expected order.

That’s because both inserts could then reuse the same available area in sequence.

So it’s not simply:

“Vacuum happened → rows got reordered.”

It’s:

“Vacuum happened at exactly the wrong time → the next insert reused an earlier page → sequential scan returned the newer row first.”

Why it only happened in the full suite

This finally explained the weirdest part of the bug.

The spec file alone takes about 1.7 seconds.

The full suite takes around 10 minutes.

Autovacuum’s naptime is around 60 seconds, and the table has enough dead tuples during a full run to cross its vacuum threshold:

50 + 0.2 × number_of_rows

So a single-file run is usually over before autovacuum gets an opportunity.

A full suite gives it roughly ten minutes of opportunities.

But even then, the vacuum has to land in the tiny window between the two inserts.

That’s why:

  • running the spec alone always passed
  • --order defined didn’t make it reproducible
  • rerunning the same commit could pass
  • the full suite occasionally failed

It wasn’t really a race between the RSpec examples.

It was a race between the test and a background PostgreSQL maintenance process.

The rule I’ll carry forward

I’ve always understood:

“If you care about ordering, use ORDER BY.”

Now I have a much better mental model for why.

Without ORDER BY, row order is a physical accident.

And physical storage can change because of things like:

  • VACUUM
  • INSERT
  • UPDATE
  • page reuse
  • table rewrites
  • maintenance operations

So a test that says:

expect(results).to eq([first, second])

is making a stronger claim than the query actually makes.

If the application only cares that both records are present, the test should say exactly that:

expect(results).to match_array([first, second])

The database never promised me the order. I just happened to get the same accident thousands of times.