Any tips on what is worth tweaking?
- Using a `SearchVectorField` is a must after 500K rows.
- Make keeping this field up to date easy for yourself by populating it using `SeachVector` with a Django pre_save signal or PostgreSQL trigger. This reduces CPU utilization significantly as the parsing and tokenization of the field your searching on is done a head of time.
- Adding a GIN Index on your `SearchVectorField` column will improve performance dramatically.
- You should specify your language configuration for postgres FTS parser. The default parser doesn't do much. It just removes spaces and normalize case. Specifying a language lets the parser make heavier optimizations that noticeably improve performance and the quality of results. If you need support for more then one langue, Django already makes it easy for this configuration to be dynamic.
You might be able to get away with if if you were indexing a less then large amount of very small documents.
I like your expression index idea a lot.
Unless you're saying that you would populate the field only on some rows and not all of them, and control this from the app. But you could do that with an expression index, too, assuming the rule is a simple, pure function:
CREATE INDEX index_posts_on_body
ON posts (to_tsvector(body, 'english'))
WHERE published = true;
Even more importantly in some circumstances, having the full column allows the optimizer to pick another index when it's totally relevant, and filter the relevant rows without needing to recompute the TSV one by one for the subset.