Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Say, I have a big database globalclients which is very very big.

Now, I divide this big database to 100 sub-databases, as:

chinaclients
japanclients
koreaclients
indiaclients
brazilclients
etc.

Each one running in the corresponding country.

Now, I want to do some analysis over all databases. I want to merge them all into the master-database at first: some tables are merged entirely, and some tables are aggregated before merging:

customer_contact:   merged entirely
product_sales:      `select sum(sales), ... group by month` before merging

Do you have any suggestion? What's the english terms related to this technique? "synchronize" or something else?

It will be a great bonus if I can upload only the difference from the sub-database.

I don't know whether I can do it in database-level or not, "synchronize" the distributed databases in Java would be a complex task. is there any support by PostgreSQL-8.4?

share|improve this question

2 Answers 2

You could use PL/Proxy to set up a frontend, and then write some functions that run the queries that you need. It could be quite tricky, though. You should plan this architecture carefully.

share|improve this answer

SQL queries can be run across multiple tables like this:

SELECT SUM(sales) FROM table1, table2 GROUP BY month

You can also use UNION to sequentially retrieve data from multiple queries:

SELECT SUM(sales) FROM table1  GROUP BY month UNION SELECT SUM(sales) FROM table2  GROUP BY month

I don't know about postgresql, but you might be able to create a VIEW table that gives you a way to access all the tables in a merged format to run simpler queries on.

share|improve this answer
    
Maybe I can create a view for tables in remote databases, but create a view for many tables may not work, because the performance is a big issue. –  Xiè Jìléi May 3 '11 at 23:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.