Re: Re: [GENERAL] Scaling PostgreSQL-9

On Tue, 28 Sep 2010 17:45:16 +0530 wrote
>On Tue, Sep 28, 2010 at 6:31 AM, sandeep prakash dhumale wrote:

I have a table with 400M records with 5 int columns having index only on 1 column.

How is your data used?  Is the update done by the primary key?  Are the queries segmented in some way that may divide the data based on one of the other columns?

You should investigate using partitions to hold your data.  I'd recommend at least 100 partitions.  I've done this with great success by dividing some tables along one of the foreign keys.  My table was just a pure relation relating the PKs of two other tables.  After analyzing the queries that were most often run, we decided to split along the one which resulted in the fewest partitions being referenced per search.

By splitting, we reduced a 200M+ row table into 100 tables of ~2M rows, and the constraint exclusion (or altering the queries to directly access the proper partition) reduced our query times dramatically.

Thanks Vivek for your replay,

We did have investigated the partitioning but looks like this wont be an ideal candidate for the same perhaps you might be able to share some more light on it.

Table contains unique mobile numbers and update is based on this mobile number. Initially we thought of partitioning by range of mobile series and ended up with about 50 partitions (can be increased as per your suggestion to 100), problem we faced update was also slow as update was based on mobile number and constraint was on mobile series. moreover if i have SELECT queries which has IN clause with random mobile numbers which may end up scanning all the tables.

Table has mobile number,status and expiry date. I can not partition on expiry date as all SELECT's are on mobile number.


Please suggest...



Snady