Search and update of the jsonb + Postgresql?

Hello experts Postgresql.

I got to the processing legacy project in which everything is slow, barely works and generally need to update the code base. In fact it turned out that it is not in the code - the project has a big database (2.5 m+ records in the largest table, a total of 11m+), terrible structure and all this on an ancient muscle which turns on fresh disks. After discussing the issues the Customer agreed to upgrade to at least the drives and put them in raid, and the database decided to change to Postgresql.

I was given a time for testing, and I decided to try jsonb as a replacement for EAV.
Clarification: I am not a DBA, I'm a programmer. My knowledge ends at writing simple functions and vyuha.

Here is a sample strucure one table card, table 7:

tenant_id | id | name | fields | tags | 8 ... more fields ...
1/1|"Name card"|[{"id":4,"name":"FAM.Paul.","values":[{"value":"Idle","enum":123}]},{...}] | [{"id":3,"value":"Surgut"},{...}]

fields::jsonb - Filled card field, their mad number, are updated frequently, the relationship to table FIELDS
tags::jsonb Tags relevant to the TAGS table
For each field, its clear structure storage. In such fields does not include things like reviews, etc.

Of the benefits I got:
1. Database size is almost 3 times less than with the scheme EAV, jsonb 3750mb, EAV 11980mb
2. The data is in one place, no need to do a bunch joynow.

Now the problems and questions:
90% of all requests is the sample data ID, the rest is search and update.

1. If I store data in the form '[{...},{...}]' then:
a) Search type data_json @> '[{"value":999}]' works fine, but how to look LIKE or is there any fuzzy search? If you do data_json::text like, the search speed is slow, maybe there is some tricky index for this?
b) How to remove the '{...}' EN masse in multiple records? As it turns out only one at a time.
C) If you updated the title tag how to update EN masse in all records? As it turns out only one at a time.

2. If I store data in the form '{1:{...},22:{...}}' then:
Search by the id of the element like data_json->'321'->>'values', and even have like the id of the item.
Question: how to search strictly on the value of {value} or the LIKE at not knowing the id of the element?

3. In both cases will work the indexes on the values of the "second level"? I.e. '[{"id":1, "values":[{"value":"Value"}]},{...}]' looking at "value".

In General ask the Council whether it makes sense to figure it out now and will jsonb real profit search, or start on EAV? (the structure is already made and tested) Or maybe better to tie Elastic\Sphinx?
March 19th 20 at 08:49
1 answer
March 19th 20 at 08:51
Thank you all, solution found, cost it is better to look for.
If someone is interested :
For a strict search will use the element ID of the entity for the rest a decision is taken with the addition of RUM hereне-нужна

Find more questions by tags PostgreSQL