I analyse web logs (nginx) and want store (organize) result in DB. After analyse and aggregate my result looks like:
- Date and Time (Truncate it by hours)
- URL (Page on the website)
- Referrer (source website)
And so on, about 30 columns.
For that data I prepare "big table"
CREATE TABLE public.cca_data_hours ( datetime timestamp without time zone NOT NULL, id_geo_country integer NOT NULL, id_geo_city integer NOT NULL, id_web_page bigint NOT NULL, id_web_referrer bigint NOT NULL, id_utm_campaign bigint NOT NULL, id_utm_medium bigint NOT NULL, c_sessions integer, c_views integer, avg_sd integer, c_jse_1 integer, c_jse_2 integer, c_jse_3 integer, ... CONSTRAINT cca_data_hours_pri PRIMARY KEY (datetime, id_web_page, id_web_referrer, id_utm_campaign, id_utm_medium, id_geo_country, id_geo_city) )
I expect 100 000 000 records.
- I want see all data by countries, cities
- I want see data per page
- I want see data per source (referrer)
- I want see data per page, source and utm campaign
- Some other combinations
Is it normal use so many columns as primary key (index)? Primary key now contains 7 columns, it may up to 10-15 columns in the future.
I think this is a simple solution how to organize this data in one object-relational DB table. Do you know better solution, practice?