Обсуждение: database connections and presenting data on the web

We are trying to determine the best solution for a web based
application. We have 13 databases (separate postmaster for each
database) that we need to retrieve data from in order to produce the web
page.  This data is changing on a regular basis.  Question is:

1. Do we:

     for database in 1-13;do
         connect database
         retrieve data
         disconnect database
     done
     display data

2. Or, do we have a common table for all databases that a daemon keeps
updated and simply pull the data from that table?

The data that's being retrieved is changing literally by the minute.

The cgi code is perl.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Geoffrey wrote:
> We are trying to determine the best solution for a web based
> application. We have 13 databases (separate postmaster for each
> database) that we need to retrieve data from in order to produce the
> web page.  This data is changing on a regular basis.  Question is:
>
> 1. Do we:
>
>     for database in 1-13;do
>         connect database
>         retrieve data
>         disconnect database
>     done
>     display data
>
> 2. Or, do we have a common table for all databases that a daemon keeps
> updated and simply pull the data from that table?
>
> The data that's being retrieved is changing literally by the minute.
>
> The cgi code is perl.
3. Like 1 but with the use of a connection pooler like pgpool. Not sure
if pgpool supports asynchronous queries, but that would help as well by
pulling data from the 13 databases in parallel instead of serial: get
the queries onto the 13 servers without waiting for results, then as
soon as data is ready, get results and display.

regards,
Yeb Havinga

On 18/03/2010 9:19 PM, Geoffrey wrote:
> We are trying to determine the best solution for a web based
> application. We have 13 databases (separate postmaster for each
> database) that we need to retrieve data from in order to produce the web
> page. This data is changing on a regular basis.

A quick aside: do they really need to be separate databases? Have you
considered using schema within a single database instead? Doing things
that way has both advantages and disadvantages, so whether you want to
do it depends on your needs, but it's worth looking into.

Often, if you're pulling in data from separate databases that can be a
hint that your design is separating things into different DBs that
should perhaps be together in one.

--
Craig Ringer

Craig Ringer wrote:
> On 18/03/2010 9:19 PM, Geoffrey wrote:
>> We are trying to determine the best solution for a web based
>> application. We have 13 databases (separate postmaster for each
>> database) that we need to retrieve data from in order to produce the web
>> page. This data is changing on a regular basis.
>
> A quick aside: do they really need to be separate databases? Have you
> considered using schema within a single database instead? Doing things
> that way has both advantages and disadvantages, so whether you want to
> do it depends on your needs, but it's worth looking into.
>
> Often, if you're pulling in data from separate databases that can be a
> hint that your design is separating things into different DBs that
> should perhaps be together in one.

It's been debated ad naseum, but for now, that's the way it is, and it's
not about to change now. ;(

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Yeb Havinga wrote:
> 3. Like 1 but with the use of a connection pooler like pgpool. Not
> sure if pgpool supports asynchronous queries, but that would help as
> well by pulling data from the 13 databases in parallel instead of
> serial: get the queries onto the 13 servers without waiting for
> results, then as soon as data is ready, get results and display.


of course, you'd need a separate pool for each database.

then there's

    4...  use dblink to query these other databases from within one
master one.