Large database aggregates: the best platform?

Question

I have a postgres database with several million rows that runs a web application. The data is static: users do not write to it.

I would like to be able to offer users query-dependent aggregates (for example, the sum of all rows with a specific foreign key value), but the size of the database now means that it takes 10-15 minutes to calculate such aggregates.

Should I:

• start precalculating aggregates in the database (since the data is static)
• move away from postgres and use something else?

The only problem with 1. is that I don’t necessarily know what aggregate users will want, and this will obviously increase the size of the database even more.

If there was a better solution for such problems than postgres, then I would be very grateful for any suggestions.

Answer 1

You are trying to solve the problem of the OLAP (On-Line Analytical Process) database structure using the OLTP (On-Line Transactional Process) database structure.

You must create another set of tables that store only aggregates and update these tables in the middle of the night. Thus, your customers can query the aggregate set of tables and will not interfere with the transaction transaction system at all.

The only caveat is that aggregate data will always be one day behind.

Answer 2

• Yes
• . , , , . SQL Server, : SQL Server 2008

Answer 3

, , ( , OR/M).

: , . (GL). . , , , . .

account_balance. ( , ) , GL , . , , , .

Answer 4

(- MyAggragatedResult), :

ResultsProxy { calculateResult (param1, param2) {  ..  .. , }

}

java /, .Net. ( ) ( ..).

Answer 5

Postgres.

- 10-15 " ". . , " " Postgres (, , ) . .

- OLAP, Mondrian . .

Answer 6

, (, , ) / , .

- . , .

, , - , .

, , , , , .

Answer 7

Oracle Query Rewrite. :

, (WHERE ID = val) , . - . FROM ... , , , , , .

, - . -, "", . , DAILY_SALES . , , .

, , , . Oracle . , MV MV Log . , DAILY_SALES , ORACLE , . .

, ... , .