Don't bet on SELECT's order: shuffle your rows and see what folds

If you SELECT from a fresh table without an ORDER BY, PostgreSQL reads the heap in physical order. On a table you’ve only ever inserted into, physical order is insertion order. So Post.take is the first post you created, and it stays that way until something disturbs the heap.

And there are plenty of things that can:

• An UPDATE doesn’t edit a row in place. PostgreSQL’s MVCC (multi-version concurrency control) writes a new version of the tuple: on the same page when there’s room, otherwise on whatever page the free space map offers, and at the end of the table when nothing has room. (Either way the row moves later in the scan: to the back of its page, or to the back of the table.)
• A DELETE leaves behind dead space. Once VACUUM reclaims the space, a later INSERT reuses it, dropping a brand new row into the middle of an old sequence.
• The planner changes its mind. Say you add an index and a sequential scan becomes an index scan; this reads rows in index order. If the table grows past the parallel threshold, the rows arrive in whatever order the workers finish.
• Someone else is reading at the same time. synchronize_seqscans is on by default: once a table is larger than a quarter of shared_buffers, a sequential scan starts at whatever block a scan already in flight has reached and wraps around at the end. This means the same rows, rotated, on a table no one has written to.
• VACUUM runs and statistics change, and PostgreSQL may choose a different query plan the next time around. VACUUM FULL and CLUSTER don’t wait for the planner: they rewrite the heap, so physical order becomes whatever they left behind.

Note that none of those represent changes to your code. The test was depending on row order. So, if a test has passed for two years, then suddenly fails on a branch that hasn’t changed anything nearby, you hit Retry, it passes, and you move on. But the failure wasn’t actually random.

An unordered SELECT is unspecified behavior, and unspecified behavior isn’t a feature you get to use.

These issues are hard to find because there’s nothing to find. The bug is a missing clause, and most queries without an ORDER BY are perfectly fine, so you can’t just search for them.

The problem only appears when some other code assumes those rows will come back in a particular order, and that code is usually somewhere else. So, instead of trying to find these assumptions in the code, we can make them fail on purpose.

Shuffle the rows

If the order isn’t specified, make it unpredictable. If we shuffle the rows of every unordered SELECT, anything that depends on the order breaks right away (instead of at some inconvenient time in the yet-to-be-determined future).

Other databases already do this. For instance, SQLite has shipped PRAGMA reverse_unordered_selects for years, and ClickHouse has inject_random_order_for_select_without_order_by.

If you write Ruby, you’re already performing the same trick, it’s just one level up.

Minitest randomizes the order your test methods run in, and you have to write i_suck_and_my_tests_are_order_dependent! to turn it off; RSpec has config.order = :random. That catches a test that only passes because an earlier test left something behind.

PostgreSQL has no such setting, which is why we wrote pg_disorder. More on that below.

Shuffling rows catches the same accident inside a single test: one that only passes because the database handed the rows back in a convenient order.

First, though: how exactly should the rows be scrambled?

Reverse or shuffle?

There are two reasonable answers to the “reverse or shuffle” question, as each approach catches different things.

Reverse flips the row order. It’s deterministic, needs no seed, and reproduces exactly, which makes it the low-friction option for CI. It’s also the single permutation most likely to break the common case, since “the first row is the one I inserted first” becomes “the first row is the one I inserted last” on the very first run.

Shuffle applies a pseudorandom permutation. It explores far more of the order space across runs, and it catches assumptions that one flip happens to satisfy. What it costs you is reproducibility, and how much depends on the implementation: pg_disorder draws one permutation per session and logs the seed, so a failure replays; Active Record reshuffles on every execution with no seed, so it doesn’t.

Neither proves a test is order-independent. In particular, reverse can be defeated by the plan it’s reversing:

CREATE TABLE ord (id int);
INSERT INTO ord SELECT generate_series(1, 10);
CREATE INDEX ord_desc ON ord (id DESC);

SET enable_seqscan = off;                 -- 10 rows: the planner would seq-scan
SET pg_disorder.mode = 'reverse';
SELECT id FROM ord;                       -- 1..10, assumption survives

There’s an additional argument for shuffle that has nothing to do with query plans. Think about how one of these assertions gets written. You run the test, look at what came back, and paste it into the expectation. Whatever the database hands you on the first run becomes the expected value; no one ever dictates this to be the canonical order. Reverse doesn’t help much here. It simply gives you a different stable order to paste.

In short, reverse is the cheap check you can run on every build; shuffling across a few seeds is the one that actually explores.

What shuffling exposes

Most of the problems shuffle finds are in tests, which is useful on its own. A flaky test is easy to fix, and it shows you where the code is relying on row order. But the same assumption can also exist in application code. In that case, it isn’t just a flaky test anymore; it’s a bug a user can encounter.

We found both of these in the same runs.

Gitea paginated Git LFS locks without a total order

Gitea’s GetLFSLockByRepoID applied LIMIT/OFFSET to a query with no ORDER BY:

e.Limit(pageSize, start)
lfsLocks := make(LFSLockList, 0, pageSize)
return lfsLocks, e.Find(&lfsLocks, &LFSLock{RepoID: repoID})

LIMIT and OFFSET without an order don’t paginate, they sample. The database is free to return a different slice of the same set on every call, so a client walking through pages can see one lock twice and never see another at all. In practice, LFS locks are never updated, the heap stays in insertion order, and the pages line up. (Of course, “in practice” is doing a lot of work in that sentence.)

The listing endpoint does have a test. api_repo_lfs_locks_test.go creates a handful of locks over the API, hits GET /info/lfs/locks, then walks the response by index and checks each entry against a fixed array of expected owners:

locksOwners: []*user_model.User{user2, user4},
// ...
for i, lock := range lfsLocks.Locks {
    assert.Equal(t, test.locksOwners[i].Name, lock.Owner.Name)
}

locksOwners is written in creation order, so the test asserts that the first row back is the first lock created. But note what it doesn’t do: it never sends a limit, so it never paginates, and it could not have caught the pagination bug head-on. What it does is lean on the same missing ORDER BY as the production code. The test relied on that order, and this managed to keep it green since 2022.

A test can share the code’s assumption instead of checking it, and then all it can do is agree with the bug. Shuffling breaks the test and the code at the same time. Here, the test broke first, which is what led back to the query.

The fix is one method call:

return lfsLocks, e.OrderBy("id").Find(&lfsLocks, &LFSLock{RepoID: repoID})

Rails dumped a non-deterministic schema.rb

Active Record’s PostgreSQL adapter reads a table’s parents from the pg_inherits catalog to dump the INHERITS option. It read them in whatever order the catalog query returned, so db/schema.rb could come out differently on two machines with identical databases and produce a diff nobody wrote.

pg_inherits has a column for exactly this, inhseqno, which records each parent’s declared position. The fix adds one line to the catalog query:

ORDER BY i.inhseqno

Two bugs, both with one-line fixes, in mature and heavily reviewed projects. And both were found by poking at the database, not by manually eyeballing the code.

The bigger patterns we uncovered

When we pointed this approach at two framework test suites, it turned up around 70 order-dependent tests across 27 files in Django (ticket #37255), and a further sweep across 14 files in Rails. Both suites are now green under reverse and shuffle.

Fixes can be categorized as four shapes:

(One note on row two: assertCountEqual is Python’s standard library, but assert_equal_unordered is a helper the Rails patch added to Active Record’s own test/cases/test_case.rb, so it isn’t an API your app gets. In Minitest you write the two-line equivalent yourself; in RSpec you already have contain_exactly.)

We’ve already shared relevant advice on this blog: add an order when the order matters, use an order-independent assertion when it doesn’t. Evil Martians applied this to ClickFunnels’ test suite of 9k+ unit and 1k+ feature tests and took its flakiest tests from ~80% to near-100% success.

Advice is one thing, but this post is about enforcement: finding every instance you already have, and making the next one impossible to merge.

Those fixes are simple once the failures show up. But the practically useful part is making them show up automatically, so let’s look at two ways to do that.

Switch one (Rails 8.2 will ship it)

Active Record’s new option shuffles the rows of unordered SELECTs that it builds itself. (Not all of them, as we’ll get to.) It works in Ruby, in ActiveRecord::Result, after the rows come back, so it’s database-agnostic: PostgreSQL, MySQL, SQLite, all the same.

# config/environments/test.rb, and config/environments/development.rb too
config.active_record.shuffle_unordered_selects = true

Development is worth turning on as well, not just test. You write the query, click through the page, and the order moves under you straight away, which is a much shorter loop than finding out from a red build later.

However, the Rails setting doesn’t catch every unordered query. Here’s what it skips:

• Raw SQL strings are untouched. Active Record can’t tell whether a string you handed it is ordered, so it leaves it alone.
• LIMIT 1 queries are unaffected. By the time take, pick, or a has_one returns, the database has already picked the row. Shuffling one row does nothing.
• A query that has an ORDER BY is never shuffled, even when that ordering isn’t a total order. It’s a syntactic check, rather than a semantic one.
• It needs the Arel abstract syntax tree to recognize a query, which leaves out anything served from a precompiled SQL string by ActiveRecord::StatementCache, including association loading, find, and find_by.

In practice, most queries go through shuffling. It doesn’t need to catch everything to be useful, as long as you understand what it misses and whether those misses can hide the bugs you’re looking for.

Rails also turned the guardrail on for itself: its own Active Record suite now runs SQLite with reverse_unordered_selects enabled, so a regression can’t sneak back.

Switch two: pg_disorder, which catches even more

pg_disorder is Evil Martians’ open-source PostgreSQL extension for doing the same job at the database level, and it reaches queries the Rails setting can’t. Active Record only shuffles what it built and can still recognize from its Arel.

pg_disorder sits at the database and sees every statement that arrives there, whatever produced it: raw SQL, queries served from the statement cache, a migration, a reporting script, an entirely different language.

It also handles cases that Rails structurally can’t. Active Record shuffles rows after they come back, so a query ending in LIMIT 1 was already decided by the time it gets them. pg_disorder adds its sort at plan time, before the limit applies, so take, pick, and an unordered has_one move too. Its own blind spots are about the shape of a query rather than about who wrote it.

pg_disorder is a shared library you load into a test or development database:

ALTER DATABASE myapp_test SET session_preload_libraries = 'pg_disorder';
ALTER DATABASE myapp_test SET pg_disorder.mode = 'reverse';

This library works at plan time. When a statement is a top-level SELECT with no ORDER BY, it adds a sort on a hidden column: reversed row position for reverse, a seeded hash of row position for shuffle. The hidden column is dropped before anything reaches the client, so the rows look normal and only their order has changed.

Why not ORDER BY random()? Because it re-rolls on every execution, so a failure you just watched happen is gone before you can look at it. (The Rails switch has the same gap, which is the one thing it gives up to stay inside Ruby.) Building the order out of a seed and the query text keeps it replayable.

LOG:  pg_disorder 0.1.0: session seed = 168799893 (replay with SET pg_disorder.seed = 168799893)

What pg_disorder won’t touch

Some queries are left alone on purpose; in particular, two of these exclusions were set because things go wrong otherwise.

WITH RECURSIVE is skipped because the added sort is a blocking operator: it has to read all of its input before it returns a single row. A recursive CTE is allowed to be infinite as long as a LIMIT stops it, and PostgreSQL’s own test suite has one. Sort that and a query which used to return in under a millisecond never returns at all, filling the disk with temporary files while it doesn’t.

FOR UPDATE and friends are skipped wherever they appear, including inside a subquery or CTE. Reordering a locking scan reorders lock acquisition, which invents deadlocks that have nothing to do with what you were testing. The blocking sort bites here too: a LIMIT would no longer stop the scan before it had locked every row.

Left alone for now as well: GROUP BY, DISTINCT, set operations, window functions, and SELECT with no FROM. Only the statement you submitted is touched, so nothing inside a function, procedure, trigger, or DO block changes.

One thing to watch when loading fixtures: INSERT ... SELECT is left alone, but CREATE TABLE AS SELECT is not. The same rows loaded the two ways end up in different orders.

Get shuffling

Here’s how to turn it on, depending on your stack:

• Rails 8.2 (on main today), any database: config.active_record.shuffle_unordered_selects = true in config/environments/test.rb, and in development.rb while you’re at it.
• Any stack testing on SQLite: PRAGMA reverse_unordered_selects = true. It’s been sitting in your database this whole time.
• Any stack testing on PostgreSQL: pgxn install pg_disorder, then mode = 'reverse' on every CI run and mode = 'shuffle' on a nightly soak with the seed captured from the log.

Your build will go red the first time. That’s the point. Those tests were already broken, you just didn’t know yet. The ones that really break are usually those with a real bug behind them, like a paginator or a schema dumper. pg_disorder is open source under the PostgreSQL License, and the work it kicked off has landed seven patches across Rails, Django, and Gitea so far.

The primary issue itself is bigger than row order: relying on unguaranteed behavior. Databases aren’t the only place this happens. Hash iteration, readdir, goroutine scheduling, floating-point work across threads, even test file order can all behave consistently right up until …they don’t.

You can’t catch all of that in review. Sometimes the better approach is to shuffle things up: make the behavior unstable on purpose and see what breaks.