PostgreSQL indeterminate ordering of results When I have this query:

SELECT DISTINCT "spree_products".id FROM "spree_products"
INNER JOIN "spree_variants"
   ON "spree_variants"."product_id" = "spree_products"."id"
  AND "spree_variants"."is_master" = 't'
INNER JOIN "spree_prices"
   ON "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons"
   ON "spree_products_taxons"."product_id" = "spree_products"."id" 
INNER JOIN "spree_taxons"
   ON "spree_taxons"."id" = "spree_products_taxons"."taxon_id"
WHERE "spree_taxons"."id" IN (13)
AND "spree_prices"."currency" = 'USD'
AND ("spree_products".deleted_at IS NULL
  or "spree_products".deleted_at &gt;= '2013-08-30 06:23:45.881488')
AND ("spree_products".available_on &lt;= '2013-08-30 06:23:45.881974')
AND (spree_prices.amount IS NOT NULL) LIMIT 12 OFFSET 36


I get some records returned in different offsets. For example, I see a record with ID=53 in both OFFSET 24 and OFFSET 36.

If I add an ORDER BY spree_products.id clause to this statement, it makes the order determinate and I never see the same record returned in different offsets.
That's because the SQL standard doesn't require PostgreSQL to return rows in any particular order without an ORDER BY clause. It can return them however it feels like. See the manual for SELECT. In particular:


  If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.


and


  When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.


This makes LIMIT and/or OFFSET without an ORDER BY unsafe in almost all cases. It's permitted in case you've forced the ordering in an inner query, as a bit of a performance hack, but it's rarely a good idea.

You must add an ORDER BY clause to make your statement deterministic.

In this case, with the DISTINCT there it's probably doing hash aggregation - which tends to return results in an inconsistent order from execution to execution. Even if you were doing an index scan that usually returns results in order, you cannot assume that it will unless you tell PostgreSQL you require the rows in a particular order.

This is closely related to people who ask how to select rows in the "natural" order of a table; tables and result sets don't have a natural order. They're sets not lists. If you want an ordering you must specify one on an operation by operation basis.