just a brief note on postgres. I have been playing with postgres 9.2
and the "index only scan" feature, which implies that if all columns
in a query are included in an index, postgres will try to fetch all
data from that index without reading from the table rows, which avoids
disk seeks and improves performance.
In DHIS rev 8319 the indexes on the data mart tables have been
extended to help postgres to perform this type of scan. I have not
measured precisely but from tests, reports like report table and data
set report are now blazingly fast and several of the queries utilize
this feature. Very impressive from postgres.
The upgrade to 9.2 seems to go without issues. The easiest is to do a
pg_dump followed by a psql insert for all your databases.
On ubuntu, to upgrade from 9.1 start with dumping all your databases,
then removing the existing version:
sudo apt-get --purge remove postgresql postgresql-9.1
postgresql-client-9.1 postgresql-client-common postgresql-common
Then install 9.2 from a package provided by the guy maintaining the
ubuntu postgres packages:
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgres-9.2
To play with the new fix in DHIS go to data admin - maintenance and
run "Rebuild data mart index", or just re-run data mart completely.