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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
