0

Today we have a table with a large jsonb (20mb). We are reading/writing the entire documents, but it's nearly fine. (60000 rows)

Is it scalable (100mb-200mb, 400000 rows) if we evolve to select different small parts of the documents?

Maybe caching won't be efficient enough?

Is it a big jump to split this jsonb into many tables and to request them separately?

The requests will be on this form:

SELECT field->'part' -> 'of' -> 'jsonb'
FROM table
WHERE pk=:pk

UPDATE field 
  SET field = jsonb_set(field, '{part,of,jsonb}', new_value)
FROM table
WHERE pk=:pk
9
  • This needs way more detail to be answered. What will the SQL statements be? Feb 16, 2022 at 9:55
  • select and update a data which are part of the json of 1 record
    – Slim
    Feb 16, 2022 at 15:31
  • That is not a valid SQL statement... Feb 16, 2022 at 15:31
  • ERROR: syntax error at or near "and" Feb 16, 2022 at 15:32
  • :) ok sure it's not a request. I will be simple select/update on a json path, I updated the message, it can still have mistake let me know if this is enough to understand.
    – Slim
    Feb 16, 2022 at 15:55

1 Answer 1

1

In general one of the big drawbacks of large amounts of content in JSONB fields is that Postgres usually has to read the entire JSONB column even if you only extract one field. There are some optimizations in newer Postgres versions here if I remember correctly, but for the generic case you really have to assume that every single time you access something from that JSON blob you have to read the entire thing.

If you compare this with a conventional schema this leads to an enormous amount of additional pages that need to be read for every query. And this will slow down your queries, how much depends on how large your JSONB columns are. If you want to get a quick estimate here, do some select queries on your non-JSONB columns and then include your JSONB columns in the query. With EXPLAIN (ANALYZE, BUFFERS) you can see just how many more pages those queries need to read.

If you want to modify only parts of the JSONB column, you probably are using the wrong tool here. It's possible, but a conventional relational schema is just so much better in those cases.

JSONB is a good fit if you don't have a consistent schema and it's extremely powerful in those cases. Otherwise it makes everything more difficult, the queries are more difficult to write and generally less efficient.

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.