0

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:

https://stackoverflow.com/questions/45150617

3
  • Why do you care how it's implemented? You will have to do some benchmarking anyway using your data and your access patterns in order to find out if it performs to your expectations. PL/pgSQL isn't intended for mass data manipulation to begin with. If you need to search/update large amounts of data then SQL is a better approach. If all you do is manipulate JSON data (key/value "documents"), then maybe a different database technology is a better choice anyway.
    – user1822
    Feb 20, 2022 at 8:43
  • @a_horse_with_no_name 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.
    – tinlyx
    Feb 20, 2022 at 15:29
  • More specifically about plpgsql, I am interested because other PL languages such as PL/python and PL/lua might be more appropriate for implementing certain functions if PL/pgsql doesn't have dictionaries.
    – tinlyx
    Feb 20, 2022 at 15:31

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.