Re: Scaling PostgreSQL-9

On Tue, Sep 28, 2010 at 6:31 AM, sandeep prakash dhumale <
sandy9940(at)rediffmail(dot)com> 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.