Skip site navigation (1) Skip section navigation (2)

Partioning tsearch2 a table into chunks and accessing via views

From: "Benjamin Arai" <me(at)benjaminarai(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Partioning tsearch2 a table into chunks and accessing via views
Date: 2007-08-25 00:41:48
Message-ID: 3147EF26-2649-4FDF-BD86-2DB0AF8460FC@benjaminarai.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
Hi,

I have an application which loads millions of NEW documents each month
into a PostgreSQL tsearch2 table.  I have the initial version completed
and searching performance is great but my problem is that each time a  
new
month rolls around I have to drop all the indexes do a COPY and re-index
the entire table. This is problematic considering that each month takes
longer than the previous to rebuild the indexes and the application in
unavailable during the rebuilding process.

In order to avoid the re-indexing I was thinking of instead creating  
a new
table each month (building its indexes and etc) and accessing them all
through a view. This way I only have to index the new data each month.

Does this work?  Does a view with N tables make it N times slower for
tsearch2 queries? Is there a better solution?

Benjamin

Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2007-08-25 01:29:18
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
Previous:From: Tom LaneDate: 2007-08-24 21:43:57
Subject: Re: When/if to Reindex

pgsql-general by date

Next:From: Phoenix KiulaDate: 2007-08-25 01:15:54
Subject: Re: Can tsearch do some basic text mining
Previous:From: StuartDate: 2007-08-24 23:30:52
Subject: connect by service name in psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group