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: Erik Jones <erik(at)myemma(dot)com>
Cc: me(at)benjaminarai(dot)com, 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 20:09:01
Message-ID: BE547F2F-8F81-4C7D-80BD-7A95388732BC@myemma.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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



In response to

pgsql-performance by date

Next:From: Martijn van OosterhoutDate: 2007-08-25 20:39:52
Subject: Re: Shared memory usage
Previous:From: Erik JonesDate: 2007-08-25 19:58:06
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2007-08-25 20:39:52
Subject: Re: Shared memory usage
Previous:From: Erik JonesDate: 2007-08-25 19:58:06
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