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

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

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