Home / server / Questions / 265773
In Process
Xiè Jìléi
Asked: 2011-05-04 02:46:57 +0800 CST

Push sub-database to master-database?

  • 772

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?

database postgresql scalability

2 Answers

  1. 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.

    • 1

  2. 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.

    • 0