1

I searched the internet and PG documentation for hours and couldn't find any benchmark for it.

In MySQL 8.0 they specifically state that now the optimizer is smart and can do in-place modification of JSON column without updating the whole column. But I couldn't find any similar article about PG. jsonb is around for a long time already and it would be really expected that such common thing as updating a single field of large JSON would be optimized, but I couldn't find any notion about it. Moreover I found here an answer from 3 year ago (PostgreSQL update JSONB without jsonb_set) that proves that updating whole column as text is faster than using jsonb_set.

I'm trying to model a table with 2M rows, each will have 2 JSON fields with 3600 simple numeric fields (date: value). I will need to add 1 field to each JSON daily, meaning 4M json_sets. Is it feasible, or I need to switch to some NoSQL like MongoDB for this feature?

Thanks

2
  • No idea about performance, but have you thought about using normal table columns for your key/values instead? Feb 8, 2021 at 1:17
  • @Charlieface, yes that's what we have now and it's very inconvenient for a feature that requires selecting rows based on conditions on multiple fields. Currently we do a JOIN with itself for every condition which has almost random performance, and complicated code that generates query. Feb 13, 2021 at 11:10

2 Answers 2

4

Every UPDATE in PostgreSQL creates a new version of the row. There is no in-place update. So not only would a new JSON be created, but also all other columns in the table would be copied.

Updating part of a JSON is not common in relational databases, or at least it shouldn't be. If you feel the need to do so, you have chosen the wrong data model, and you would be much better off using table columns instead of JSON attributes. If you then split the data across several tables using a process called “normalization”, an UPDATE doesn't hurt quite as much.

3
  • 1
    Yea, I figured as much. During my benchmark I saw performance of about 200 updates per second, sequentialy. During this process CPU spiked to 60%, so it's definitely not usable for this purpose. Which is too bad, since the read usage of this data model worked fantastically. Feb 13, 2021 at 10:57
  • Btw, I don't agree about "it shouldn't be". There's no reason there shouldn't be an efficient inplace update of a single column and nested field. And if MySQL implemented efficient inplace update no reason PG can't. Databases can and should improve it's feature base and performance towards each other, and same way MongoDB has some kind of transactions and analytical functions, PG can have efficient JSON. Feb 13, 2021 at 11:06
  • 2
    Sure, everything can change. But it doesn't fit PostgreSQL's basic architecture well (which of course also could change, see Zheap). Structured BLOBs of data just isn't what relational databases are designed for, so I'm not ashamed if they don't excel at it. Judging from the questions in this forum, 99% of all people who use JSON in the database abuse it with a data model that could just as easily be designed with normalized database tables, and if they had done that, their problems would be gone. Feb 13, 2021 at 16:28
-3

It's been a while but if you are using PostgreSQL to store time series data you might be interested in TimescaleDB, a plugin for PostgreSQL that helps with this kind of data.

tl;dr from an IoT company's experience (also contains a nice introduction to using PostgreSQL as a Time Series Database):

If you are using PostgreSQL database in your application and need to store a large volume or handle high velocity of time series based data, consider using a TimescaleDB plugin. It is easy to configure, offers increased performance and new time series data handling functions, and will work seamlessly with other database objects that are part of your model.

P.S. I am curious about your queries that decidedly seem non-trivial.

4
  • Why do you think a time series database is appropriate for frequently updated jsonb columns?
    – mustaccio
    Jun 15, 2021 at 15:20
  • @mustaccio Thanks for your comment. My understanding is that the OP wants to store time series. The OP cites ``` simple numeric fields (date: value) ``` and needing to add data daily. Looks like time series to me. (Though a bit slow if only one add daily.) I might have missed something though.
    – GNRX
    Jul 20, 2021 at 20:20
  • This indeed might be more appropriate for the OP's use case, and the question itself might indicate an X-Y problem, but the question was not about the data model selection.
    – mustaccio
    Jul 20, 2021 at 20:41
  • @mustaccio I'm new to SE contribution and you are helpful: many thanks! --- > Is it feasible, or I need to switch to some NoSQL like MongoDB for this feature? --- The OP's only sentence with a question mark explicitly mentions changing tools. So I suggest a tool that would be suited to what I can understand of the issue he/she is facing. How is my answer not relevant?
    – GNRX
    Aug 24, 2021 at 15:10

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.