From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Smaller multiple tables or one large table? |
Date: | 2012-06-15 18:42:10 |
Message-ID: | 4FDB8202.8090908@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Benedict Holland | 2012-06-15 18:58:32 | Re: Smaller multiple tables or one large table? |
Previous Message | Benedict Holland | 2012-06-15 18:34:56 | Smaller multiple tables or one large table? |