zombodb | Making Postgres and Elasticsearch | SQL Database library

I currently have four tables that need to be joined with a left join. There are about 400_000 (400 thousand) of data records (which will grow in the future).

In addition to the left join, the following things must be able to be executed performantly:

• A full text search using the columns purchasing.mandant_id, purchasing.company_code, purchasing_mandant_names.name, company_codes.name, purchasing.sequence_number, purchasing.year, purchasing.month.
• The full text search must also work with partial words and ignore upper and lower case.
• A number of values found (to calculate how many pages there are).
• And an offset, limit (or an alternative, which basically works the same) to specify a fixed page number for each search (no cursor functionality and we don't know what the last found id is).
• We also don't know if the next page number is the next higher number. Basically, a much higher number of pages can be given immediately.

I myself had several ideas to optimise this requirement.

full text search:

• a separate table where the columns for the full text search are concatenated in a text field (p.client_id::text || ' '::text || pmn.name::text) and additionally contains the id for purchasing with a gin index (gin_trgm_ops)
• a completely different database system, like sonic or elasticsearch for the full text search, but I don't know how best to synchronise the data and query it in the program.
• I have found something along the lines of zombodb. Unfortunately, it has the disadvantage that deleted records are not removed from elasticsearch. And zombodb only works on a linux server and I prefer and use windows.

offset, limit:

• limit the search to purchasing.id (select purchasing.id from left join) and get all data after these ids (if this really leads to an optimisation is not sure)
• add another column which always contains the current row number with an index, this would then also have to be completely rebuilt in case of deletions. Unfortunately, it doesn't really help in general, because with a filter over this data, the row numbers are of course no longer leading.
• the same applies to the id with the addition that gaps can also occur in the id when deleting.

count

• I can't think of any optimisation at the moment, because the entire data set always has to be filtered through. The only current option would be to use the id search to return only the ids and count them. These ids can then be used to pull the rest of the required data.

Adding columns or tables to help is possible. Changing the structure is also possible as long as all data is retained.

It is also possible to change the database system if postgres does not have the required functions.

• relational
• open source
• connectable with php and nodejs

the tables: