Re: Table Partitioning and Rules

The problem is that Im worried about sequential scans. This particular table
can have upto 150 cols and 250 million records. Now we have a reporting
requirement that someone could select on ANY col and filter on any col as
well. Meaning someone could so a SELECT on col number 1,2,310,1000 from
contact where col num 75='X' and col num 139 = 'Y'.

I cant possibly index all the cols in the table. So I thought Id best manage
the data by splitting up the table into multiple partitions and eventually
depending on application logic, only scan those tables that are necessary to
scan sequentially instead of the whole big table.

Im getting a little confused here cause eventually I would want to join in
this 250 million gigantic table as well.. and that would be a real big
problem causing loads of sequential scans wouldn't it?

Thanks,
Girish

-----Original Message-----
From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
Sent: Thursday, July 17, 2003 2:03 PM
To: Richard Huxton; Girish Bajaj; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Table Partitioning and Rules

Girish,

> > Essentially Im trying to store a persons information in a table in the
> > database. Since we could have millions of people, with duplicates! Ive
> > decided we need to partition the table into segments where all people
with
> > the LastName starting from A to G will be in one table. H-N will be in
> > another table and O-Z in the third. Ive created a VIEW that does a UNION

on
> > all the tables.

This sounds hideously inefficient and a management headache besides. I
think
PostgreSQL will accept up to 2 billion rows in any one table, and splitting
stuff into 3 tables will not improve your performance ... quite the
opposite.

Change your database design.

--
-Josh Berkus
Aglio Database Solutions
San Francisco