I am implementing a statistics driven web application. The statistics relate to sporting events.Right now I have a postgresql database with a table that holds a row for each match. This is a contrived example of the data in the DB:league | season | match_date | team_home | team_away | team_home_q1_score | team_home_q2_score | team_home_q3_score | team_home_q4_score | team_away_q1_score | team_away_q2_score | team_away_q3_score | team_away_q4_scoreNFL    | 2015/16| 2016-01-24 | Panthers  | Cardinals | 17                 | 7                  | 10                 | 15                 | 0                  | 7                  | 0                  | 8NFL    | 2015/16| 2016-01-24 | Broncos   | Patriots  | 7                  | 10                 | 0                  | 3                  | 6                  | 3                  | 3                  | 6Every row has the league, season, match date, home team and visiting team, then there are 54 smallint columns holding various other statistics. There are currently 28000 rows in the DB. The data is likely to be updated once a day.Ultimately users will visit a webpage, select a future match and be presented with various aggregated stats based on the teams' last 10 matches. There will likely be hundreds of calculated metrics based on the underlying data, things like % of matches where the team scored more than x points.I also have a Java application server sitting in front of the database, which is already handling some other related tasks.I need to figure out the best way to serve these statistics to users of the website. By best I mean performance from the users' point of view (speed of being able to view the stats) and making the best use of server resources.As I see it, these are my options, and I've included my thoughts on pros and cons of each method:Aggregate within the DBEach time the user selects a match on the webpage, a set of stored procedures are run to calculate all the aggregated metrics within the DB. These are then returned to the webpage.Pros:The calculations can all feasibly be done within the DB without the use of cursors etc. Performance will likely be better this way than looping methods in JavaCons:Large number of queries being executed against the DB for every match. Could potentially be mitigated using memcached Large amount of data returned by the DB serverAggregate within the app serverSelect the last x matches from the match results table, return this data to the application server, calculate the aggregated metrics and return them to the webpagePros:Less data returned by the DB serverCould still use memcached to further reduce load on the DB serverCons:Java will probably be much less efficient than SQL at calculating all the derived metricsETLRun an ETL task once a day when new stats are inserted into the DB, which aggregates the stats into additional tables, which can then simply be selected fromPros:Probably the best performance, with calculations only being done once a day rather than on-the-fly Again, could use caching of queries to reduce DB server loadCons:More storage space required to hold aggregated data Not very flexible. Can't simply select data for last 20 games instead of last 10 games without an additional DB table.Have I missed any better options, or failed to spot any particular pitfalls with my existing options?
Options for implementing database and programming logic for statistics application?
java;performance;sql