a) I have 1000000 domain names
b) Every domain has about 100000 sites
c) each site has about 10000 visits daily / (5000 unique visits daily)
d) As the owner of all those websites, I want to see, how many visitors on selected sites I had in a selected periods of time, for example:
How many unique visitor were from 4th December 1987 to 23 April 2010 on mydomain.com/tutorials
How many unique visitor were from 30 August 1996 to 16 July 2009 on yourdomain.com/reference ?
For a traditional SQL database this is a pain.
What is the smartest approach ? What storage engine to use ?
I have only SQL knowledge. Any additional resources greatly appreciated.
With the numbers and potential queries similar to those that you have listed I would very much dobut that a simple SQL (PSQL/TSQL) database would meet your needs. Instead you'll need some form of OLAP processing like SSAS (SQL Server Analysis Services) or an similar offering from Oracle.