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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
