The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would break around 500k rows and I tried but it seems to be running fine. My test is basically something like running a query on a books table in a jsonb column wanting to find matches for name = "Harry Potter". Is my test wrong? I was wondering if there were any other cases I could explore? I need some sort of reason if I want to redesign our database schema.

  • 3
    Whoever that "someone" was - he/she was wrong. We have a table with over 50 million rows and properly indexed, querying the JSONB column typically takes less than a second - even within a recursive query. – a_horse_with_no_name Jul 22 at 15:47
  • However before you go down the JSON road, make sure that de-normalizing solves more problems then it creates. You might want to read this: blog.2ndquadrant.com/… – a_horse_with_no_name Jul 22 at 15:48
  • @a_horse_with_no_name so it seems like there are no performance issues with jsonb but its just harder to enforce constraints and data-types and queries are look a bit different? – rahc01 Jul 22 at 15:57
  • @a_horse_with_no_name heap.io/blog/engineering/… There seem to be performance problems according to this though. Is that because the post is old? Or because there is a join involved? – rahc01 Jul 22 at 15:59
  • At the end of the day, you are the only one who can decide that. Race your horses – a_horse_with_no_name Jul 22 at 16:24

There is no number of rows where JSONB suddenly breaks.

However, JSONB doesn't have much in the way of statistics gathered on it. So the planner doesn't know if it will be returning half the table, or just one row; it has to rely on default estimates. This can lead it to make some pretty unfortunate choices for complex queries. For simple queries, it will likely be "good enough". You can get around the statistics problems by building an expression index on expressions which pull out certain fields which are frequently queried, as expressional indexes gather their own statistics; for example, create index on book ((doc->>'name')).

My test is basically something like running a query on a books table in a jsonb column wanting to find matches for name = "Harry Potter". Is my test wrong?

If that is the type of query you will actually do, then that test is not wrong. Except that that is not a JSONB query in the first place. It would really have to be something like doc->>'name' = 'Harry Potter', or doc @> '{"name":"Harry Potter"}'

  • Name is a property in the JSON. So like { 'name' = 'Harry Potter', 'author': 'J. K. Rowling', ... }. What are queries that aren't simple that it wouldn't be good enough for? I read this: heap.io/blog/engineering/… Is it the join in that query which destroys the performance? Also when you mention expression indexes -- is that like a GIN index? – rahc01 Jul 23 at 19:13
  • Yes, the join is a good example of a more complex query. I've added an example of an expressional index. It is in index over an expression, rather than over a column. – jjanes Jul 24 at 2:36

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