We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread and this AWS thread.

Is there any way to define a thesaurus within the database, and not from an external file? The docs, even for PG 12, do not suggest anyting of the kind.

I'm asking what sounds like a silly question, so here's what makes it less inane. Following some instructions from Erwin Brandstetter, I've defined custom dictionary that combines the simple dictionary with the skip word definitions from the english dictionary.

CREATE TEXT SEARCH DICTIONARY data.simple_skip_dictionary (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = english

CREATE TEXT SEARCH CONFIGURATION data.simple_skip (COPY = simple);
   ALTER MAPPING FOR asciiword WITH data.simple_skip_dictionary;  -- 1, 'Word, all ASCII'

CREATE INDEX record_changes_log_detail_old_value_tsv_gin
    ON record_changes_log_detail
    USING GIN (to_tsvector('simple_skip', old_value));

FROM   record_changes_log_detail
WHERE  to_tsvector('simple_skip', old_value) @@ to_tsquery('simple', 'double-horn');

This all works fine on RDS, and the PG docs here also refer to exteral files. I suspect I'll find I'm out of luck because what I'm looking for is a custom thesaurus dictionary, not leveraging an existing file that may already be defined on RDS.

Does anyone know of a trick to create a custom thesaurus dictionary on RDS, or should I just put this one on the "reaasons not to use RDS" list?

Putting another PG in Front

Assuming (as Laurenz Albe likely confirms) that a custom thesaurus is not possible on a hosted PG without file system access, what about putting another PG in front to generate the text search vector? So, set up one or more PG instances that pre-process the data and then push the vector up to our RDS Postgres?

The question buried in there is "does the thesaurus come into play during the query, or only during the indexing?" The to_tsquery() call obviously wouldn't have access to the thesaurus, but I'm imagining that it might not matter. The query term wouldn't normalize identically, but I'm hoping that's part of the point of the digestion-with-thesaurus process."

I know this question is remedial, I'm just getting my head around FTS in Postgres. It's a pretty substantial topic area and I've only been able to digest the docs in bits and pieces.

  • I think this is something you just cannot do with a hosted database. Oct 17, 2019 at 6:07
  • Darn. It's a pity the dictionary contents must be in external files. I've added another idea to see if it's possible to use a pre-processor to paper over the cracks a bit here. Oct 17, 2019 at 6:21


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.