user daily visits - plus assez d'espace disque

[odelcroi]

user daily visits : lignes : 126M données : 39Go index : 60Go total : 99Go

optimiser la table :

• transformer visit_ts en date en non en timestampz (8 octet vers 4 octet) "Changer le type de colonne de timestamptz à date dans une table de 120 millions de lignes vous fera économiser environ 458 Mo d'espace disque"

optimiser les index

• unique_user_daily_idx : user_id is not necessary because device_id depends on - - user_visit_user_id_visit_ts_idx : can be used, but the mat view uses trunc() so it is not used

[odelcroi]

Ajout de 2 colonnes pour stocker 'platform' et 'device_type' -> a pris 20 Go

Idée pour alléger la base :

• [ ] normaliser les données de user_id (sortir user_id, domain et instance) pour ne garder que device_id...
• [ ] pour aller encore plus loin, normaliser : user_agent, platform, device_type

Drawback :

• l'exploitation de gros volume necessite des jointures, ce qui va couter cher mais normalement, on ne calcule les données qu'une seule fois car elles sont incrémentales

[odelcroi]

Idées pour accélerer les requetes

Comme nous accédons souvent aux lignes des derniers mois, créer un index de user_daily_visits inversé peut accélerer le traitement CREATE INDEX idx_visit_ts_desc ON user_daily_visits (visit_ts DESC);

[odelcroi]

L'index triple unique_user_visit UNIQUE (user_id, device_id, visit_ts) est très lourd, pour garantir l'unicité (device_id, visit_ts) est suffisant

[odelcroi]

/\ un device_id peut avoir plusieurs user_agent, en effet, un device_id est une session utilisateur (associée à un session token)

• le token peut etre partagé par un script et un browser -> plusieurs user_agent
• le browser du user peut etre mis à jour -> plusieurs user_agent

SELECT 
    device_id, 
    COUNT(DISTINCT user_agent) AS user_agent_count,      -- Compte les user_agents distincts pour chaque device_id
    STRING_AGG(DISTINCT user_agent, ', ') AS concatenated_user_agents  -- Concatène les user_agent distincts
FROM user_daily_visits
GROUP BY device_id
HAVING COUNT(DISTINCT user_agent) > 1;  -- Affiche uniquement les device_id avec plusieurs user_agents distincts