Is a Postgres Array column more easily indexed than a JSONB column with a JSON array in it?
https://www.postgresql.org/docs/current/arrays.html
https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
-
What do you mean by easy?w08r– w08r2020-02-25 20:44:21 +00:00Commented Feb 25, 2020 at 20:44
-
I guess both in terms of effort from the DB and also effort for developeruser12834955– user128349552020-02-25 20:48:00 +00:00Commented Feb 25, 2020 at 20:48
-
1Unrelated to your question, but: Postgres 9.4 is no longer supported you should plan an upgrade as soon as possible.user330315– user3303152020-02-25 21:45:43 +00:00Commented Feb 25, 2020 at 21:45
Add a comment
|
1 Answer
Syntactically, the JSONB array may be easier to use as you don't have to wrap your query value in a dummy array constructor:
where jsonbcolumn ? 'abc';
vs
where textarraycolumn @> ARRAY['abc']
On the other hand, the planner is likely to make better decisions with the PostgreSQL array, as it collects statistics on its contents, but doesn't on JSONB.
Also, you should read the docs for the version of PostgreSQL you are using, which is hopefully greater than 9.4 and really really should be greater than 9.1.
2 Comments
jjanes
@a_horse_with_no_name True but that formulation won't use an index.