Re: Smaller multiple tables or one large table?

On 06/15/12 11:34 AM, Benedict Holland wrote:
> I am on postgres 9.0. I don't know the answer to what should be a
> fairly straight forward question. I have several static tables which
> are very large (around the order of 14 million rows and about 10GB).
> They are all linked together through foreign keys and indexed on rows
> which are queried and used most often. While they are more or less
> static, update operations do occur. This is not on a super fast
> computer. It has 2 cores with 8gb of ram so I am not expecting queries
> against them to be very fast but I am wondering in a structural sense
> if I should be dividing up the tables into 1 million row tables
> through constraints and a view. The potential speedup I could see
> being quite large where postgresql would split off all of the queries
> into n table chucks running on k cores and then aggregate all of the
> data for display or operation. Is there any documentation to make
> postgesql do this and is it worth it?

postgres won't do that, one query is one process. your application
could conceivably run multiple threads, each with a seperate postgres
connection, and execute multiple queries in parallel, but it would have
to do any aggregation of the results itself.

>
> Also, is there a benefit to have one large table or many small tables
> as far indexes go?

small tables only help if you can query the specific table you 'know'
has your data, for instance, if you have time based data, and you put a
month in each table, and you know that this query only needs to look at
the current month, so you just query that one month's table.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast