News for hackers. Stuff that matters.
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by evadne on Sunday July 29, 2018 @01:09PM (#17639116)
The first solution uses a materialised view. It essentially precomputes what the query can possibly return and uses up additional disk space, plus WAL traffic unless if it is configured unlogged. There has been no mention of how long it takes to rebuild such a materialised view: you will still have full table scans then, but at build time against the primary read instance.

The second solution is better, but please read documentation [1] before replicating it. Also to_tsvector takes an optional regconfig parameter [2] and here is what I get right now

    $ psql
    psql (9.6.2)
    Type "help" for help.

    evadne=# \dF
                   List of text search configurations
       Schema   |    Name    |              Description              
    ------------+------------+---------------------------------------
     pg_catalog | danish     | configuration for danish language
     pg_catalog | dutch      | configuration for dutch language
     pg_catalog | english    | configuration for english language
     pg_catalog | finnish    | configuration for finnish language
     pg_catalog | french     | configuration for french language
     pg_catalog | german     | configuration for german language
     pg_catalog | hungarian  | configuration for hungarian language
     pg_catalog | italian    | configuration for italian language
     pg_catalog | norwegian  | configuration for norwegian language
     pg_catalog | portuguese | configuration for portuguese language
     pg_catalog | romanian   | configuration for romanian language
     pg_catalog | russian    | configuration for russian language
     pg_catalog | simple     | simple configuration
     pg_catalog | spanish    | configuration for spanish language
     pg_catalog | swedish    | configuration for swedish language
     pg_catalog | turkish    | configuration for turkish language
    (16 rows)

[1] https://www.postgresql.org/docs/current/static/textsearch-co...

[2] https://www.postgresql.org/docs/current/static/textsearch-in...

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by 1996 on Sunday July 29, 2018 @03:23PM (#17639803)
A larger problem is the need to precompute.

Regular views are just as slow (or fast) as the normal code.

It seems something inbetween would be needed- not just a trigger, but a regular update as data is appended, maybe in chunks?

I do not know psql much. It may already exist.

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by manigandham on Sunday July 29, 2018 @03:33PM (#17639846)
A trigger to do the update for the tsvector field works just fine, and is the normal implementation.