is Postgres jsonb scalabe for big documents and -> and --> operators



  • 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



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2