Search and update jsonb + Postgresql?

Question

PostgreSQL

Hello PostgreSQL Experts.
I got a legacy project for processing in which everything slows down, barely works, and in general it is necessary to update the code base. In fact, it turned out that the problem is not in the code - the project has a large database (2.5m+ records in the largest table, 11m+ in total), a terrible structure, and all this on an ancient muscle that spins on not fresh disks. After discussing the problems, the Customer agreed to upgrade at least the disks and put them in raid, and decided to replace the database with Postgresql.
I was given time to test and I decided to try jsonb as a replacement for EAV.
Clarification : I'm not a DBA, I'm a programmer. My knowledge ends with writing simple functions and views.
Here is an approximate structure of one table - a visitor's card, there are 7 such tables:
tenant_id |id | name | fields | tags | ... 8 more fields ...
----------------------------------------- ------------------
1|1|"Card name"|[{"id":4,"name":"Sem.gender","values": [{"value":"Single","enum":123}]},{...}] | [{"id":3,"value":"Surgut"},{...}]
where:
fields::jsonb - Completed card fields, their insane number, frequently updated, relation to the FIELDS table
tags:: jsonb - Tags, relation to the TAGS table
Each field has its own distinct storage structure. Such fields do not include things like comments and so on.
Benefits I got:
1.
2. The data is in one place, no need to make a bunch of joins.
Now problems and questions:
90% of all requests are a selection of data by ID, the rest is search and update.
Questions:
1. If I store data in the form '[{...},{...}]' then:
a) Searching data_json @> '[{"value":999}]' type works fine, look for LIKE or is there some kind of fuzzy search? If you do data_json::text like, then the search speed is slow, maybe there is some tricky index for this?
b) How to remove element '{...}' in bulk in multiple records? So far, only one is available.
c) If the tag name was updated, how to update in bulk in all posts? So far, only one is available.
2. If I store data as '{1:{...},22:{...
There is a search by element id like data_json->'321'->>'values', and there is even like by element id.
Question: how to search strictly by the value {value} or LIKE without knowing the id of the element?
3. In both cases, will indexes work on the values ​​of the "second" level? Those. for '[{"id":1, "values":[{"value":"Value"}]},{...}]' search by "value".
In general, I ask for advice, does it make sense to understand this now and will there be a real profit from jsonb in the search, or will it be done on EAV? (I have already made and tested the structure) Or maybe it is better to fasten the Elastic\Sphinx?

Answer 1

Thank you all, the solution was found, it was worth looking for it better.
If anyone is interested:
For a strict search I will use the ID of the entity element, for the rest I took the solution with the addition of RUM from here
http://komar.in/ru/mongodb-not-needed