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

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

From: Erik Jones <erik(at)myemma(dot)com>
To: me(at)benjaminarai(dot)com
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
Date: 2007-08-25 19:58:06
Message-ID: 3AF74FE5-3A2F-4433-985C-310539845DAA@myemma.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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(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



In response to

Responses

pgsql-performance by date

Next:From: Erik JonesDate: 2007-08-25 20:09:01
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
Previous:From: Benjamin AraiDate: 2007-08-25 19:49:13
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

pgsql-general by date

Next:From: Erik JonesDate: 2007-08-25 20:09:01
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
Previous:From: Benjamin AraiDate: 2007-08-25 19:49:13
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

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