Re: Multithreaded query onto 4 postgresql instances

* Alessandro Candini wrote:

> Il 14/02/2011 21:00, Allan Kamau ha scritto:
>> On Mon, Feb 14, 2011 at 10:38 AM, Alessandro Candini<candini(at)meeo(dot)it> wrote:
>>> No, this database is on a single machine, but a very powerful one.
>>> Processors with 16 cores each and ssd disks.
>>>
>>> I already use partitioning and tablespaces for every instance of my db and I
>>> gain a lot with my splitted configuration.
>>> My db is pretty huge: 600 milions of records and partitioning is not
>>> enough...
>>> I performed tests with a query returning more or less 100000 records and
>>> using my C module I obtain the following results (every test performed
>>> cleaning cache before):
>>> - single db: 9.555 sec
>>> - splitted in 4: 5.496 sec

So your problem is that one query, which is executed by a single backend
process, is too slow. You fixed that by spreading the data across four
database clusters on the same machine, querying them in parallel and
merging the results in the client.

I think you may have thought too far out of the box here. What is the
performance if you leave all the data in a single database, suitably
partitioned, then open multiple connections to that same database and
run as many queries as you need to query each partition at the same
time? As others here, I can hardly imagine that a setup involving four
postmasters with one active backend each can be faster than having a
single postmaster with, say, four active backends.

It's true that PostgreSQL cannot parallelize a single query. But it is
very good at running multiple queries in parallel, especially if you can
limit I/O and lock contention by matching queries to partitions.

--
Christian