Full text search support for PostgreSQL (postgresql_psycopg2 backend)

[djangoproject.com@…]

The small patch attached to this ticket adds full text search support via the already implemented field lookup search__.

The one and only (maintained) way to have full text search in PostgreSQL is via the extension ​Tsearch2 which is already included in most of the distributions.

In order to be able to index a field for full text search you have to do the following steps:

• Make sure your PostgreSQL has the Tsearch2 module
• Set-Up Tsearch2 for your Django project (as postgres user):
  • psql yourdatabase < /path/to/tsearch2/contrib/script/tsearch2.sql
  • In psql shell: GRANT ALL ON public.pg_ts_cfgmap TO djangoDBuser;
  • In psql shell: GRANT ALL ON public.pg_ts_cfgmap TO djnagoDBuser;
  • In psql shell: GRANT ALL ON public.pg_ts_dict TO djangoDBuser;
  • In psql shell: GRANT ALL ON public.pg_ts_parser TI djangoDBuser;
• Add an extra column for the table you want to use the full text search index on (as djangoDBuser). Make sure that the column name (in the example fieldvariable_tsv) has the EXACT name of the field you want to search on plus _tsv on the end (otherwise it won't work)
  • Example: ALTER TABLE your_tablename ADD COLUMN fieldvariable_tsv tsvector;
• Create an index on the full text search field (as djangoDBuser):
  • CREATE INDEX djangoapp_columnname_tsv_index ON your_tablename USING gist(fieldvariable_tsv);
• Finally update the full text index for the data already in the database (as djngoDBuser):
  • UPDATE your_tablename SET fieldvariable_tsv = to_tsvector(fieldvariable);
• Finally apply the attached patch

Now you should be able to do something like this:

MyModel.objects.filter(fieldvariable__search = 'some text')

in your app which is normally blinding fast, even with millions of records in the database:

[djangoproject.com@…]

Patch againt svn revision 5526

[djangoproject.com@…]

You might also want to set up a PostgreSQL Trigger on your table for updates and inserts, which looks something like that:

CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON your_tablename FOR EACH ROW EXECUTE PROCEDURE tsearch2(fieldvariable_tsv, fieldvariable)

[Amirouche B.]

How can use this to search on several column ?

I tried to get some inspiration from ​http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
and ​http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html

but it's hard to mix with django.

[anonymous]

Replying to Amirouche B.:

How can use this to search on several column ?

I tried to get some inspiration from ​http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
and ​http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html

but it's hard to mix with django.

You can check this link, ​http://smelaatifi.blogspot.com/2007/09/full-text-search-with-tsearch2-and.html,
there is a solution to use it with django and an index over several columns.

[James Bennett]

Both this ticket and #3524 deal with the same issues and have patches dating from the same day. #3524 deals with more than just Postgres, though, so I'll close this one in favor of it; could the author of this patch take a look at the other ticket and look into joining efforts on this feature?

[Johann Queuniet <johann.queuniet@…>]

Referenced ticket is #3254, not #3524 (since Google seems to prefer this ticket to the other).