Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

On Aug 25, 2007, at 2:58 PM, Erik Jones wrote:

>
> On Aug 24, 2007, at 7:41 PM, Benjamin Arai wrote:
>
>> 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?
>
>
> You can use Postgres's inheritance mechanism for your partitioning
> mechanism and combine it with constraint exclusion to avoid the N^2
> issues. See:
>
> http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
>
> and
>
> http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
>
> Basically, create a table from which all of your partitioned tables
> inherit. Partition in such a way that you can use constraint
> exclusion and then you can treat the parent table like the view you
> were suggesting.
>
> Erik Jones
>

Sorry, I didn't see that you had crossposted and carried the
conversation on another list. Please, don't do that. Avoid the top
posting, as well. They both make it difficult for others to join in
or follow the conversations and issues.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com