如何复现PostgreSQL中hstore的键值对顺序不可复现情况？

Great question! It's totally understandable that you haven't run into hstore's order inconsistency yet—lots of folks don't until they hit specific scenarios. Let's break this down clearly:

When does the order get messed up? #

• After modifying an hstore (adding/removing key-value pairs, merging two hstores): For example, if you insert hstore('a=>1, b=>2') then run SELECT hstore || 'c=>3' FROM your_table;, the resulting hstore might not return keys in a,b,c order.
• Using aggregation functions (like combining hstores with array_agg) or reading from different storage locations (e.g., after database page reorganization, or when hitting different cache versions): The underlying storage can shift the order without warning.
• Migrating data between PostgreSQL versions, or using different client tools to query: Sometimes clients might parse or display hstore data in a different order than the database returns it.

Why isn't order guaranteed? #

You hit the nail on the head with the sorting cost! Hstore is built on a hash table under the hood. Hash tables store key-value pairs based on the hash of the key, not insertion order—this makes lookups, inserts, and deletions super fast (O(1) time complexity). Maintaining insertion order would require a more complex structure (like a linked list paired with a hash table) which adds overhead, especially for large hstore objects.

The official docs call out the order being meaningless because PostgreSQL reserves the right to change the internal storage order whenever it makes sense for performance—your code should never rely on hstore's key order being consistent.

These two types behave very differently when it comes to order:

JSON #

The json type stores the raw input string exactly as you provide it, so it preserves the original key order. For example, if you insert '{"a":1, "b":2}'::json, every query will return that exact string with keys in a,b order.

A caveat: If you build a json object using functions like json_build_object, the order will match the order of your arguments. But once stored as json, that order is locked in until you rebuild the object.

JSONB #

jsonb works like hstore—its underlying structure is a hash table, so it does NOT preserve insertion order. It also automatically deduplicates duplicate keys (keeping only the last value provided). For example, inserting '{"a":1, "b":2, "a":3}'::jsonb will store {"a":3, "b":2}, and the order you see when querying might not match your input.

One thing to note: Starting in PostgreSQL 12, the jsonb_pretty function will sort keys alphabetically for output. This makes it look like there's a consistent order, but that's just a display trick—the underlying storage order is still unordered. If you need a specific order, you'll have to explicitly sort the keys using functions like jsonb_object_keys.

• hstore: Never relies on key order. Hash table implementation means order can shift after modifications, aggregation, or storage changes. Never build code that depends on hstore's key order.
• json: Preserves original input order since it stores the raw JSON string. Order only changes if you rebuild the json object via functions.
• jsonb: Unordered (hash table), deduplicates keys. PostgreSQL 12+ adds alphabetical sorting in jsonb_pretty, but storage order remains unpredictable.

内容的提问来源于stack exchange，提问作者Matt Bannert