Re: shared data for different applications - Mailing list pgsql-general

Hi Alban,

Thanks a lot for your useful info!

> I think most companies have ended up at that point just by the progress
> of time. They have several different databases (often from different
> vendors even) that they need to aggregate their information from.

So, is this the result of lack of central coordination or a carefully
thought-out decision?

> Usually the different databases contain different kinds of contents, so
> the need for distributed transactions and such is quite minimal.

Let's say, if a customer would like to change his address through
a certain app (eg, the web interface for customers), and assuming
that customer info is shared across the whole company, then a
distributed transaction will be required, right? Or there is a better
approach?

> That said, separating things out would be an improvement. For example,
> for generating reports (big aggregations of many data sources at once),
> you tend to generate heavy-weight queries that are likely to impact
> other queries (unless your database server is capable of limiting I/O
> and such per process, that helps some).

I see. For reporting, I agree that using a replicated copy of the database
is a good way to do it.

> You limit access to who can touch what and you replicate.

Fully agree. I am just concerned that maintenance work for a schema may
have foreseen or un foreseen impacts on the other schemas as they're
in the same DB on the same server.

> I wouldn't go so far as to create a separate database for every business
> unit though, the distinction is more a role-based one than an
> organisational one - there will be overlap between who has access to
> what.

Fully agree. Therefore I've seen people suggesting doing it on a process
and data flow basis.

> That said, unless you're in a very large company, a central database will
> probably do for almost anything. The exceptions are more likely to be
> among the lines of reporting and frequent short but specialised queries
> for, for example, machine performance statistics.

Thanks for the clear conclusion. Wondering how common is it in practice
(I am not really familiar with the data management status quo)?

> At the company where I work we have a central DB2/400 database for
> production, shipping and sales. That database gets replicated for
> reporting. We also have a few separate MS SQL databases where for
> example machines on our production facility send their performance
> statistics, which they get back in an aggregated form every 5 minutes
> or so.
>
> It isn't ideal, but that 20-year old DB2/400 database (although the
> hardware's been upgraded to i5-series or so) can handle its load just
> fine.

Thanks a lot for sharing the practical info! It is very useful!