I’ve been using SQL Server all along and never encountered this issue. After switching to PostgreSQL, a client reported that the data order in query lists keeps changing. I looked through the code and found that the query is sorted by CreateTime, but a few rows have identical creation timestamps, resulting in a different order each time the query is run.

Why PostgreSQL Gets Messy

When PostgreSQL stores data, it simply puts it wherever there's space, much like tossing items casually into a drawer.

Moreover, when you modify a row, PostgreSQL doesn't update it in its original location. Instead, it writes a new copy to a fresh empty spot, leaving the old version for a background process to clean up. Once cleaned, the freed space is quickly occupied by other data.

As a result, the physical location of data is constantly changing. When rows have identical sort values, their order becomes non-deterministic.

Why SQL Server Doesn't Have This Problem

Strictly speaking, SQL Server doesn't guarantee this either, but in practice you're unlikely to encounter any issues.

SQL Server stores data on disk in a sorted order by primary key, much like books arranged neatly on a shelf by number. When you query by creation time and encounter identical timestamps, it retrieves records sequentially from left to right along the shelf, ensuring the same order every time.

Conclusion

If the columns specified in ORDER BY contain duplicate values, PostgreSQL does not guarantee the order of these duplicate rows. Add a unique column as a tie-breaker to ensure consistent ordering:

-- 之前
SELECT * FROM records ORDER BY create_time DESC;

-- 改成
SELECT * FROM records ORDER BY create_time DESC, id DESC;

Make it a habit: always include the primary key in your ORDER BY clause as a fallback.