Re: Partioning tsearch2 a table into chunks and accessing via views - Mailing list pgsql-performance

From Erik Jones
Subject Re: Partioning tsearch2 a table into chunks and accessing via views
Date
Msg-id 3AF74FE5-3A2F-4433-985C-310539845DAA@myemma.com
Whole thread Raw
In response to Partioning tsearch2 a table into chunks and accessing via views  ("Benjamin Arai" <me@benjaminarai.com>)
Responses Re: [GENERAL] Partioning tsearch2 a table into chunks and accessing via views
List pgsql-performance
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@myemma.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



pgsql-performance by date:

Previous
From: Benjamin Arai
Date:
Subject: Re: [GENERAL] Partioning tsearch2 a table into chunks and accessing via views
Next
From: Erik Jones
Date:
Subject: Re: [GENERAL] Partioning tsearch2 a table into chunks and accessing via views