Supabase Silent Bug: PostgREST Sorts by ctid, Causing Data Discrepancies

The Fourth Call of the Week

Catherine called from Maisons-Laffitte on a Tuesday afternoon in early May. "It's buggy, but it's quickly fixed." That's her usual phrase, and she's normally right. She described the issue in three sentences: the newsletter export for registered users returned ninety-two names, the scheduler showed ninety-two active courses, but the counter page displayed eighty-nine. Three registered users were missing. She had checked the database directly; they were there. "Why three steps for this?" She wasn't asking for me, she was asking herself. However, as I hung up, I realized this was the fourth time this week I'd hung up thinking the same thing. Four Supabase incidents, four fixes, four tickets closed. And not a single exception raised by the database.

I reopened the previous three incidents in parallel and laid the four side-by-side on the desk. The common point? They all happened on Supabase. The fixes were all simple: add an `ORDER BY` clause to the PostgREST query. The problem: PostgREST, by default, sorts the results by `ctid` when no `ORDER BY` clause is specified. `ctid` is an internal PostgreSQL tuple identifier. It's not stable and can change over time. It's also not guaranteed to be unique across table reorganizations or vacuuming operations. This hidden default behavior is a silent killer for applications expecting consistent ordering. It's like having a filing cabinet where the drawers sometimes reorder themselves based on how recently a file was touched, rather than by the label on the file itself.

Understanding `ctid` and PostgREST's Default Behavior

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. It's incredibly powerful for rapid development, allowing developers to interact with their database schema using standard HTTP requests. However, its convenience comes with a crucial caveat: default behaviors that might not align with application logic expecting stable, predictable data ordering. When a client requests data from PostgREST without explicitly specifying an `ORDER BY` clause in the query parameters (e.g., `?order=column_name.asc`), PostgREST passes the request to PostgreSQL. PostgreSQL, in the absence of an explicit order, might return rows in an order determined by the physical storage location of the tuples, which is represented by `ctid`. This order is not guaranteed to be consistent. It can change after operations like `VACUUM FULL`, `CLUSTER`, or even routine `VACUUM` operations that can lead to tuple relocation and `ctid` changes. For applications that rely on the order of returned rows for pagination, deduplication, or even just consistent display, this is a critical failure point.

Consider the newsletter export example. The application likely fetches a list of registered users, processes them (perhaps to segment for a newsletter), and then displays a count or performs an action based on that list. If the underlying query to fetch these users from Supabase (via PostgREST) returns them in a different order each time due to `ctid` sorting, the processing logic might encounter the same user at a different position, or miss them entirely if the count mechanism is sensitive to order. The missing users in Catherine's case weren't actually deleted; they were just returned in an order that confused the application's processing logic, leading to an inconsistent count.

The `ORDER BY` Fix

The solution, as discovered across these four incidents, is straightforward: always provide an `ORDER BY` clause in your PostgREST requests when the order of results matters. This can be done via query parameters. For instance, to get a stable order for the user list, the request might look like: `/users?order=created_at.asc,id.asc`. This tells PostgREST to instruct PostgreSQL to sort the results first by the `created_at` timestamp and then by `id` as a tie-breaker. This ensures that the order of rows returned is deterministic and will not change unexpectedly. If a primary key or a unique combination of columns is available, it's best practice to use that for sorting.

The surprising detail here is not the complexity of the bug, but its subtlety. No errors were logged. The database itself wasn't failing; it was merely obeying a default behavior that, in the context of a web API serving application logic, becomes a functional bug. This kind of silent failure is particularly insidious because it doesn't trigger alarms. Developers might spend hours debugging their application logic, assuming the data retrieval is sound, only to discover the root cause lies in an unstated assumption about API response ordering.