Queries on HSTORE values are slightly faster (10-20), so if performance is critical and string key/value pairs are sufficient, it is worth. You're using the > operator in the end, but not on the actual jsonb column but on your extracted json. The GIN index can be used for the > operator on jsonb columns, but it can't be used for arbitrary functions. Accessing JSONB values is about 2× slower than accessing a BYTEA column. The difference to your example is that I don't unpack the array, I just query the jsonb column directly. It stores parsed JSON in a binary format, so queries are efficient. PostgreSQL offers two types for storing JSON data: json and jsonb. Then you can compute author similarity based on comments in queries like Most applications should use JSONB for schemaless data. That will lead to a faster load and better indicesĪlter table posts add constraint posts_pk primary key(post_id) Ĭreate index post_author on posts(author_id) Īlter table comments add constraint comments_pk primary key(comment_id) Ĭreate index comment_author on comments(author_id) Ĭreate index comment_post on comments(post_id) I would have use postgres and store the data in two tables:
#Postgres jsonb query performance update
You won't pay for index update since you won't have any and I guess you have the extra storage for the index. I assume that you are interested in calculating similarities among the entities (e.g., post, author) so you'll mainly join the table with it self (e.g., by post or author) and aggregate.Īdd to that the fact that after the initial loading your database will be read only, what make the problem very suitable to index usage. You said that you are interested in improving performance in joins. I recommend on a design that can lead to significantly faster results.Īs answered before, in general postgresql is faster than mongo, some times more than 4 times faster.
Disk Usage - JSONB uses more space vs JSON, I assume this is due to its meta data it stores in the binary. However, queries are much faster for JSONB especially when using indexes. For the numbers you mention, I think all alternatives should work (read: you'll be able to finish your analysis in reasonable time). Performance - JSON is faster for inserts since it only odes JSON format verification, vs JSONB which also converts the jSON input to a binary JSONB with its meta-data.
0 kommentar(er)
