I know very little about Postgres memory settings. I have developed a paid-access database, which mostly stores text (and some metadata) 2 tables have 10 millions rows with little text (1 paragraph for each row) 2 other tables have 100,000 rows with the full texts (40 pages for each row) Overall, the database size is about 10 GB.
The purpose of the project is to enable users to make complex text searches (sometimes combined with metadata queries such as title or data, which are unproblematic). Text searches are made on indexed tsvectors in the tables described above, which are automatically filled a trigger. They are rather fast on my personal computer (MacBook Pro 2019 - 2,6 GHz Intel Core i7 6 cores - RAM 16 GB 2400 MHz DDR4). Unfortunately, as I am going into production, they are rather slow on the remote ODS server (RAM 2 GB),
Hence my question: What are the ideal memory settings for complex text searches on indexed tsvectors?
Does it help when the server RAM size is higher than the database size (10 GB in my case), so that the database can be entirely stored in RAM?
Should I increase work_mem, or other settings?
As a side question, does it help to reduce the number of tables interrogated, or is that irrelevant? (say, if users were only allowed to interrogate the tables with paragraphs, and not the tables with full texts)
Thank you very much for your help !