PostgreSQL does not seem to have a native data type for dictionaries. The new jsonb (and json) type seems to have the right interface: given a key, a jsonb object returns a value.
I am just wondering about the performance (computational complexity) of using jsonb or hstore as dictionaries. (I read that the json type is inefficient.) My question is:
Does the jsonb type have the same computational complexities as dictionaries? (e.g. logarithmic time for looking up a value, etc.)
If jsonb is not suitable, what's the idiomatic way to implement/use dictionaries (e.g. in PL/pgsql)
(this is with PostgreSQL 14+)
Related:
1 Answer 1
JSON in PostgreSQL isn't inefficient per se; it depends on what you do with it and what your expectations are.
Looking up a value by key will be an efficient operation with type jsonb. But much of that efficiency might be lost if you have a large dictionary, because the whole JSON gets read from disk and loaded into memory.
You may be better off storing each key-value pair in a single table row, but you should benchmark both solutions.
Why do you care how it's implemented?. What I care isn't really how it's implemented per se. It's more about whether SQL supports data types such as dictionaries. The jsonb/hstore types have an interface of a dictionary (so it seems). Hence the question whether the computational complexity match. It's really about the specification.