I’ve always used SQL Server and never encountered this issue. After switching to PostgreSQL, customers reported that the order of data in query results kept changing. Upon reviewing the code, I found that queries are sorted by CreateTime, but several rows share the exact same creation timestamp. As a result, the order of returned rows varies each time.

Why PostgreSQL Produces Unordered Results

PostgreSQL stores data by filling available space wherever it finds it, similar to tossing items randomly into a drawer.

Additionally, when you update a row, PostgreSQL doesn’t modify the original location; instead, it writes a new version in an empty spot and leaves the old one for background processes to clean up later. Once cleaned, that freed space may be reused by other data.

Consequently, the physical storage position of rows constantly changes. When multiple rows have identical sort values, their order becomes unpredictable.

Why SQL Server Doesn’t Have This Issue

Strictly speaking, SQL Server doesn’t guarantee order either, but in practice, you rarely encounter this problem.

SQL Server stores data on disk sorted by primary key, much like books arranged neatly on a shelf by ID. When querying by creation time and encountering identical timestamps, it retrieves rows sequentially from left to right along the shelf, ensuring consistent ordering every time.

Conclusion

If the ORDER BY column contains duplicate values, PostgreSQL does not guarantee the order of those duplicate rows. To ensure deterministic results, add a unique column as a secondary sort key:

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

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

Develop the habit of always including the primary key as a fallback after ORDER BY.

This content is automatically translated to English. View Original