2

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 !

0

Welcome!

More RAM may make a big difference, but it's worth looking into the configuration and some specific queries to see where they're being constrained.

Here's a couple of next steps:

  1. Compare your current settings to those recommended by PGTune and try out the recommendations if there are big differences
  2. Use explain (ideally explain (analyze, buffers)) locally and on your remote server to see if it's memory constrained, or something else

Feel free to edit your question with more information from what you find! If you do, comment on this anwer to notify me :)

|improve this answer|||||
  • 1
    Thanks very much Michael, for your welcome and your answer! What a great resource is PGTune. I'll run some tests and come back to you. A.- – Alexandre Aug 14 '19 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.